Purpose
The CREATE STANDBY TENANT statement is used to create an empty standby tenant in a physical standby database scenario. After a standby tenant is created in this way, it automatically enters the continuous synchronization state.
If you want to create a common user tenant, use the CREATE TENANT statement.
Limitations and considerations
You can use this statement to create a standby tenant only if the primary tenant is a newly created tenant, or if you can confirm that the primary tenant has all the logs since its creation. For detailed operations on how to check whether the primary tenant has all the logs, see Preparations before creating a standby tenant.
When you create a standby tenant in this way, you need a dedicated user that can access the
GV$OB_LOG_STAT,GV$OB_UNITS,GV$OB_PARAMETERS,DBA_OB_ACCESS_POINT,DBA_OB_TENANTS, andDBA_OB_LSsystem views in the primary tenant. For more information about this dedicated user, see Create an empty standby tenant.You need to create an empty standby tenant in the
systenant of the cluster where the standby tenant is to be created, and the created standby tenant must be in read-only mode.
Privilege requirements
Only the root user of the sys tenant (root@sys) can create a tenant. Other tenants do not support tenant creation.
Syntax
CREATE STANDBY TENANT [IF NOT EXISTS] tenant_name
{LOG_RESTORE_SOURCE [=] string_value} [tenant_option_list];
tenant_option_list:
tenant_option [, tenant_option...]
tenant_option:
LOCALITY [=] 'locality_description'
| PRIMARY_ZONE [=] primary_zone_name
| RESOURCE_POOL_LIST [=] (pool_name [, pool_name...])
| COMMENT [=] 'string'
Parameters
| Parameter | Description |
|---|---|
| tenant_name | The name of the standby tenant. It can be up to 63 bytes in length and must start with a letter or underscore. It can contain only uppercase and lowercase letters, digits, and underscores. It cannot be a keyword of OceanBase Database. |
| IF NOT EXISTS | If the specified tenant name already exists and the IF NOT EXISTS option is not specified, an error is returned. |
| LOG_RESTORE_SOURCE | The log restore source configuration. This parameter is required. The format is 'SERVICE=ip_list USER=user_name@tenant_name PASSWORD=password', where:
|
| LOCALITY | The distribution of replicas of the standby tenant across zones. This parameter is optional. For example: F@z1,F@z2,F@z3 indicates that z1, z2, and z3 are full-featured replicas.We recommend that you set the LOCALITY of the standby tenant to the same value as that of the primary tenant. |
| PRIMARY_ZONE | The primary zone of the tenant. Valid values or formats are as follows:
If you do not specify the PRIMARY_ZONE parameter when you create a tenant, the default value is RANDOM. |
| RESOURCE_POOL_LIST | The list of resource pools to be used by the standby tenant. This parameter is required. We recommend that you set the resource specifications of the standby tenant to the same value as that of the primary tenant. If multiple resource pools are specified, the UNIT_NUM values of the resource pools must be the same.
NoticeFor multiple resource pools of the same tenant, their corresponding |
| COMMENT | The comment. This parameter is optional. |
Examples
Assume that the primary tenant is named mysql, and a dedicated user test has been created for accessing the views in the primary tenant. The following example shows how to create an empty standby tenant named standby_tenant.
Log in to the
systenant of the cluster where the standby tenant is to be created as therootuser.Create a 1C5G unit.
obclient [oceanbase]> CREATE RESOURCE UNIT 1C5G MAX_CPU 1, MEMORY_SIZE = '5G';Create a resource pool named
pool_for_standby.obclient [oceanbase]> CREATE RESOURCE POOL pool_for_standby UNIT = '1C5G', UNIT_NUM = 1, ZONE_LIST = ('zone1', 'zone2', 'zone3');Set the
LOG_RESTORE_SOURCEparameter and create an empty standby tenant.obclient [oceanbase]> CREATE STANDBY TENANT IF NOT EXISTS standby_tenant LOG_RESTORE_SOURCE = 'SERVICE=11.xxx.xxx.xxx:17855;11.xxx.xxx.xxx:17857;11.xxx.xxx.xxx:17859 USER=test@mysql PASSWORD=******' RESOURCE_POOL_LIST=('pool_for_standby');Query the
DBA_OB_TENANTSview to check the status of the tenant. If theTENANT_ROLEfield isSTANDBYand theSTATUSfield isNORMAL, the tenant is in a normal state and the creation is successful.obclient [oceanbase]> SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant';The query result is as follows:
+----------------+-------------+----------------------------+--------+-------------+----------------------------+ | TENANT_NAME | TENANT_TYPE | CREATE_TIME | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) | +----------------+-------------+----------------------------+--------+-------------+----------------------------+ | standby_tenant | USER | 2023-04-14 21:06:48.787550 | NORMAL | STANDBY | 2023-04-14 21:12:59.183293 | +----------------+-------------+----------------------------+--------+-------------+----------------------------+ 1 row in set