Modify attributes of a tenant

OceanBase Database allows you to modify the attributes of a user tenant, such as the number of replicas, zone list, primary zone setting, and system variable values. This topic describes how to modify the attributes of a tenant.

Background information

Tenant attribute modifications involve the following general scenarios:

• Modify the primary zone: The primary zone setting describes the preferred location of leaders that provide strong-consistency reads and writes. In other words, the primary zone setting determines the distribution of traffic in OceanBase Database. You can modify the primary zone setting to switch business traffic from one IDC to another or from one city to another in disaster recovery and scaling scenarios.

• Modify the locality: The locality of a tenant describes the types and distribution of replicas. You can modify the locality to adjust the deployment architecture of the tenant in a scenario in which a cluster is migrated from one IDC to another or the disaster recovery level is adjusted.

Procedure

1. Log in to the sys tenant of the cluster as the root user.

   obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. Access the database named oceanbase.

   USE oceanbase;
   

3. Query the DBA_OB_TENANTS view for the configuration information about a tenant.

   For example, to query the configuration information about the mq_t1 tenant, execute the following statement:

   obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'mq_t1';
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME                | MODIFY_TIME                | PRIMARY_ZONE | LOCALITY                     | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED | TENANT_ROLE | SWITCHOVER_STATUS | SWITCHOVER_EPOCH | SYNC_SCN            | REPLAYABLE_SCN      | READABLE_SCN        | RECOVERY_UNTIL_SCN  | LOG_MODE     | ARBITRATION_SERVICE_STATUS |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
   |      1006 | mq_t1       | USER        | 2023-05-18 15:48:57.447657 | 2023-05-18 15:49:12.857944 | zone1;zone2  | FULL{1}@zone1, FULL{1}@zone2 | NULL              | MYSQL              | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 | 1684396167132057328 | 1684396167132057328 | 1684396167051160964 | 4611686018427387903 | NOARCHIVELOG | DISABLED                   |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
   1 row in set
   

   For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

4. Execute the ALTER TENANT statement to modify the attributes of the tenant.

   Notice

   You must execute this statement as the root user in the sys tenant.

   The syntax is as follows:

   ALTER TENANT {tenant_name | all }
       [SET] [tenant_option_list] [opt_global_sys_vars_set]
   
   tenant_option_list:
       tenant_option [, tenant_option ...]
   
   tenant_option:
           COMMENT [=]'string' 
           |PRIMARY_ZONE [=] zone 
           |RESOURCE_POOL_LIST [=](poolname [, poolname...]) 
           |DEFAULT TABLEGROUP [=] {NULL | tablegroupname}
           |LOCALITY [=] 'locality description';
   
   opt_global_sys_vars_set:
       VARIABLES system_var_name = expr [,system_var_name = expr] ...
   

   The parameters are described as follows:

   • {tenant_name | all }: the name of the tenant. all specifies all tenants.
   • PRIMARY_ZONE: the primary zone setting of the tenant.
   • RESOURCE_POOL_LIST: the resource pool list. You can add or delete only one resource pool at a time.
   • LOCALITY: the distribution of replicas across zones. For example, F@zone1,F@zone2,F@zone3 specifies that replicas in zone1, zone2, and zone3 are full-featured replicas. To add a zone in this parameter, make sure that the zone is in the zone list specified by the ZONE_LIST parameter of the resource pool of the tenant.

   For more information about the ALTER TENANT statement, see ALTER TENANT.

   Here are some examples:

   • Change the primary zone setting to "zone1,zone2" and the locality to "F@zone1,F@zone2,F@zone3" for the mq_t1 tenant.

     obclient [oceanbase]> ALTER TENANT mq_t1 PRIMARY_ZONE='zone1,zone2';
     Query OK, 0 rows affected
     
     obclient [oceanbase]> ALTER TENANT mq_t1 LOCALITY="F@zone1,F@zone2,F@zone3";
     Query OK, 0 rows affected
     

   • You can add or delete only one resource pool at a time for a tenant. You cannot directly replace the resource pool of the tenant. Assume that the original resource pool of the tenant is mq_pool_01. If you execute the following statement to replace the original resource pool with mq_pool_02, the system returns an error.

     obclient [oceanbase]> ALTER TENANT mq_t1 RESOURCE_POOL_LIST=('mq_pool_02');
     ERROR 1210 (HY000): Incorrect arguments to resource pool list
     

5. Query related views to verify the modification results.

   The modifications on the primary zone setting and locality take effect asynchronously. You need to query related views to confirm whether the modifications take effect.

   • You can query the DBA_OB_TENANTS view for the primary zone setting and locality of the tenant. In the view, the PRIMARY_ZONE field indicates the primary zone setting of the tenant, the LOCALITY field indicates the locality of the tenant, and the PREVIOUS_LOCALITY field indicates the original locality. If the value of PREVIOUS_LOCALITY is not Null, the modification on the locality has not taken effect. If the value of PREVIOUS_LOCALITY is Null, the modification on the locality has taken effect.
   • You can query the CDB_OB_TABLE_LOCATIONS view in the sys tenant for the distribution of tables and partitions in a tenant, and query the DBA_OB_TABLE_LOCATIONS view in a user tenant for the distribution of tables and partitions in this user tenant. The modification results of the primary zone setting and locality are indicated in these views.