Convert the type of a replica

You can convert a full-featured replica of a log stream to a read-only replica or a read-only replica of a log stream to a full-featured replica based on your business needs.

Considerations

OceanBase Database supports only the conversion between full-featured and read-only replicas.

Convert the type of a replica by modifying the locality

The following example describes how to modify the locality of the mysql001 tenant from F@zone1,F@zone2,F@zone3 to F@zone1,F@zone2,R@zone3.

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

   Here is an example:

   obclient -h172.30.xxx.xxx -P2883 -uroot@sys#obdemo -pxxxx -A
   

2. Access the database named oceanbase.

   obclient>use oceanbase;
   

3. View the locality.

   obclient> SELECT TENANT_ID,TENANT_NAME,TENANT_TYPE,PRIMARY_ZONE,LOCALITY FROM oceanbase.DBA_OB_TENANTS;
   

   The query result is as follows:

   +-----------+-------------+-------------+-------------------+---------------------------------------------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE | PRIMARY_ZONE      | LOCALITY                                    |
   +-----------+-------------+-------------+-------------------+---------------------------------------------+
   |         1 | sys         | SYS         | RANDOM            | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
   |      1001 | META$1002   | META        | zone1;zone3;zone2 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
   |      1002 | mysql001    | USER        | zone1;zone3;zone2 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
   |      1003 | META$1004   | META        | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
   |      1004 | oracle001   | USER        | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
   +-----------+-------------+-------------+-------------------+---------------------------------------------+
   5 rows in set
   

4. Modify the locality of the mysql001 tenant.

   obclient> ALTER TENANT mysql001 LOCALITY="F@zone1,F@zone2,R@zone3";
   

5. View the execution status of the locality modification job.

    obclient>SELECT * FROM oceanbase.DBA_OB_TENANT_JOBS WHERE JOB_TYPE = 'ALTER_TENANT_LOCALITY';
   

   The query result is as follows:

   +--------+-----------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+----------------+-------------+
   | JOB_ID | JOB_TYPE              | JOB_STATUS | RESULT_CODE | PROGRESS | START_TIME                 | MODIFY_TIME                | TENANT_ID | SQL_TEXT                                                                          | EXTRA_INFO                                                                                                 | RS_SVR_IP      | RS_SVR_PORT |
   +--------+-----------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+----------------+-------------+
   |      1 | ALTER_TENANT_LOCALITY | SUCCESS    |        NULL |        0 | 2024-04-10 11:16:46.242622 | 2024-04-10 11:16:46.242622 |      1002 | ALTER TENANT mysql001 LOCALITY="F@zone1,F@zone2,R@zone3"                          | FROM: 'FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3', TO: 'FULL{1}@zone1, FULL{1}@zone2, READONLY{1}@zone3' | 172.xx.xxx.192 |        2882 |
   +--------+-----------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+----------------+-------------+
   1 rows in set
   

   If the value of JOB_STATUS is SUCCESS, the locality modification job is successful.

6. View the modified locality.

   obclient> SELECT TENANT_ID,TENANT_NAME,TENANT_TYPE,PRIMARY_ZONE,LOCALITY FROM oceanbase.DBA_OB_TENANTS;
   

   The query result is as follows:

   +-----------+-------------+-------------+-------------------+-------------------------------------------------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE | PRIMARY_ZONE      | LOCALITY                                        |
   +-----------+-------------+-------------+-------------------+-------------------------------------------------+
   |         1 | sys         | SYS         | RANDOM            | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3     |
   |      1001 | META$1002   | META        | zone1;zone3;zone2 | FULL{1}@zone1, FULL{1}@zone2, READONLY{1}@zone3 |
   |      1002 | mysql001    | USER        | zone1;zone3;zone2 | FULL{1}@zone1, FULL{1}@zone2, READONLY{1}@zone3 |
   |      1003 | META$1004   | META        | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3     |
   |      1004 | oracle001   | USER        | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3     |
   +-----------+-------------+-------------+-------------------+-------------------------------------------------+
   5 rows in set
   

   The query results in step 3 and step 6 show that the locality of the mysql001 tenant is changed from FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 to FULL{1}@zone1, FULL{1}@zone2, READONLY{1}@zone3, and the type of a replica of the mysql001 tenant has changed.

Manually convert the type of a replica

When you convert the type of a replica by modifying the locality of the tenant, the system automatically converts the type of the replica. However, if the system does not properly convert the type of the replica due to exceptions, you can execute the ALTER SYSTEM MODIFY REPLICA statement to convert the type of the replica.

Limitations

• You can convert the types of replicas for log streams of all tenants in the sys tenant but only for log streams of the current tenant in a user tenant.

• One log stream of a tenant supports only one replica task at a time, such as a task to add replicas, remove replicas, convert the type of a replica, modify the number of Paxos replicas of a log stream, or migrate replicas.

  You can query ongoing replica tasks from the CDB_OB_LS_REPLICA_TASKS view in the sys tenant or from the DBA_OB_LS_REPLICA_TASKS view in a user tenant.

Prerequisites

• You have the ALTER SYSTEM privilege.

• You have the SELECT privilege on the following views:

  • DBA_OB_TENANTS

  • DBA_OB_LS/CDB_OB_LS

  • DBA_OB_LS_LOCATIONS/CDB_OB_LS_LOCATIONS

Procedure

Assume that the locality of a tenant named tenant1 is F@zone1,F@zone2,R@zone3, and log stream 1001 of the tenant1 tenant has a full-featured replica in each of zone1, zone2, and zone3. To convert the full-featured replica in zone3 to a read-only replica, perform the following steps:

1. Log in to the target tenant.

   Here is an example:

   obclient -h172.30.xxx.xxx -P2883 -uroot@tenant1#obdemo -pxxxx -A
   

   For more information about how to connect to a database, see Overview (MySQL mode) or Overview (Oracle mode).

2. Query the tenant ID and locality of the target tenant.

   • sys tenant

     obclient [oceanbase]> SELECT TENANT_NAME,TENANT_ID,LOCALITY FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='tenant1';
     

   • User tenant

     MySQL mode

     Oracle mode

     Execute the following statement in MySQL mode:

     obclient [oceanbase]> SELECT TENANT_NAME,TENANT_ID,LOCALITY FROM oceanbase.DBA_OB_TENANTS;
     

     Execute the following statement in Oracle mode:

     obclient [SYS]> SELECT TENANT_NAME,TENANT_ID,LOCALITY FROM SYS.DBA_OB_TENANTS;
     

     A sample query result is as follows:

     +-------------+-----------+-------------------------------------------------+
     | TENANT_NAME | TENANT_ID | LOCALITY                                        |
     +-------------+-----------+-------------------------------------------------+
     | tenant1     |      1002 | FULL{1}@zone1, FULL{1}@zone2, READONLY{1}@zone3 |
     +-------------+-----------+-------------------------------------------------+
     1 row in set
     

     The query result shows that the tenant ID is 1002.

     For more information about the fields in the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

3. Query all log streams of the target tenant.

   • sys tenant

     obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_LS WHERE TENANT_ID=1002;
     

     For more information about the fields in the CDB_OB_LS view, see CDB_OB_LS.

   • User tenant

     MySQL mode

     Oracle mode

     Execute the following statement in MySQL mode:

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_LS;
     

     Execute the following statement in Oracle mode:

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_LS;
     

     A sample query result is as follows:

     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
     | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG |
     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
     |     1 | NORMAL | zone1;zone2  |             0 |           0 |                NULL |     NULL | 1712122872503652000 | 1712122872503652000 |      |
     |  1001 | NORMAL | zone1;zone2  |          1002 |        1001 | 1711951977207156000 |     NULL | 1712122872503652000 | 1712122872503652000 |      |
     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
     2 rows in set
     

     The query result shows that the tenant1 tenant has log streams 1 and 1001.

     For more information about the fields in the DBA_OB_LS view, see DBA_OB_LS.

4. Query the replica distribution of the target log stream of the tenant.

   • sys tenant

     obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_LS_LOCATIONS WHERE LS_ID=1001 AND TENANT_ID=1002;
     

     For more information about the fields in the CDB_OB_LS_LOCATIONS view, see CDB_OB_LS_LOCATIONS.

   • User tenant

     MySQL mode

     Oracle mode

     Execute the following statement in MySQL mode:

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_LS_LOCATIONS WHERE LS_ID=1001;
     

     Execute the following statement in Oracle mode:

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_LS_LOCATIONS WHERE LS_ID=1001;
     

     A sample query result is as follows:

     +---------------------+---------------------+-------+----------------+----------+----------+-------+----------+-------------------------------------------------------------------+----------------------+--------------+--------------+---------+
     | CREATE_TIME         | MODIFY_TIME         | LS_ID | SVR_IP         | SVR_PORT | SQL_PORT | ZONE  | ROLE     | MEMBER_LIST                                                       | PAXOS_REPLICA_NUMBER | REPLICA_TYPE | LEARNER_LIST | REBUILD |
     +---------------------+---------------------+-------+----------------+----------+----------+-------+----------+-------------------------------------------------------------------+----------------------+--------------+--------------+---------+
     | 01-APR-24 02.13.04. | 01-APR-24 02.13.04. |  1001 | 172.xx.xxx.192 |     2882 |     2881 | zone3 | FOLLOWER | NULL                                                              |                 NULL | FULL         | NULL         | FALSE   |
     | 01-APR-24 02.13.04. | 01-APR-24 02.13.04. |  1001 | 172.xx.xxx.212 |     2882 |     2881 | zone2 | FOLLOWER | NULL                                                              |                 NULL | FULL         | NULL         | FALSE   |
     | 01-APR-24 02.13.04. | 01-APR-24 02.13.10. |  1001 | 172.xx.xxx.226 |     2882 |     2881 | zone1 | LEADER   | 172.xx.xxx.192:2882:1,172.xx.xxx.212:2882:1,172.xx.xxx.226:2882:1 |                    3 | FULL         | NULL         | FALSE   |
     +---------------------+---------------------+-------+----------------+----------+----------+-------+----------+-------------------------------------------------------------------+----------------------+--------------+--------------+---------+
     3 rows in set
     

     The query result shows that log stream 1001 has a full-featured replica on each of OBServer nodes 172.xx.xxx.192:2882, 172.xx.xxx.212:2882, and 172.xx.xxx.226:2882. However, the locality of the tenant shows that a read-only replica is required in zone3. Therefore, you must convert the full-featured replica on OBServer node 172.xx.xxx.192:2882 to a read-only replica.

     For more information about the fields in the DBA_OB_LS_LOCATIONS view, see DBA_OB_LS_LOCATIONS.

5. Execute the ALTER SYSTEM MODIFY REPLICA statement to convert the type of the replica.

   The syntax is as follows:

   ALTER SYSTEM MODIFY REPLICA LS [=] ls_id SERVER [=] 'svr_ip:svr_port' REPLICA_TYPE [=] 'replica_type' [PAXOS_REPLICA_NUM [=] paxos_replica_num] [TENANT [=] 'tenant_name'];
   

   The parameters in the syntax are described as follows:

   • ls_id: the ID of the log stream for which you want to convert the type of the replica.

   • svr_ip:svr_port: the IP address and port number of the OBServer node on which you want to convert the type of the replica, for example, 172.xx.xxx.192:2882.

   • replica_type: the replica type to convert to. Conversion between the following two types of replicas is supported:

     • Full-featured replica: denoted as FULL or F.

     *Read-only replica: denoted as READONLY or R.

   • paxos_replica_num: the modified number of Paxos replicas of the log stream, which is the number of full-featured replicas specified in the locality of the tenant. A log stream supports a maximum of seven full-featured replicas.

     When you specify the paxos_replica_num parameter, take note of the following considerations:

     • Converting a full-featured replica to a read-only replica is equivalent to removing a full-featured replica. In this case, you must decrease the value of the paxos_replica_num parameter by 1.

     • Converting a read-only replica to a full-featured replica is equivalent to adding a full-featured replica. In this case, you must increase the value of the paxos_replica_num parameter by 1.

     • After the value of the paxos_replica_num parameter is modified, the available replicas are the majority.

     • The value of the paxos_replica_num parameter must be greater than or equal to the number of members in the MEMBER_LIST of the leader. You can query the MEMBER_LIST from the CDB_OB_LS_LOCATIONS view in the sys tenant or from the DBA_OB_LS_LOCATIONS view in a user tenant.

     • The absolute value of the variation of the paxos_replica_num value is not greater than 1. For example, if the value of the paxos_replica_num parameter is 3, you can change it only to 2 or 4.

     If any one of the preceding conditions is not met, the system will return an error. If this parameter is not specified, the default value is used. In this example, the default value is 3.

   • tenant_name: the name of the target tenant. You can specify another tenant in the sys tenant and specify only the current tenant in a user tenant. If this parameter is not specified, the name of the current tenant is used. You cannot use the all, all_user, or all_meta option in this statement to specify all tenants, all user tenants, or all meta tenants.

   • This statement converts the type of one replica at a time.

   Here is an example:

   obclient> ALTER SYSTEM MODIFY REPLICA LS = 1001 SERVER='172.xx.xxx.192:2882'  REPLICA_TYPE='R' PAXOS_REPLICA_NUM=2;
   

6. Query the replica distribution of the target log stream again.

   • sys tenant

     obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_LS_LOCATIONS WHERE LS_ID=1001 AND TENANT_ID=1002;
     

   • User tenant

     MySQL mode

     Oracle mode

     Execute the following statement in MySQL mode:

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_LS_LOCATIONS WHERE LS_ID=1001;
     

     Execute the following statement in Oracle mode:

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_LS_LOCATIONS WHERE LS_ID=1001;
     

     A sample query result is as follows:

     +---------------------+---------------------+-------+----------------+----------+----------+-------+----------+---------------------------------------------+----------------------+--------------+-------------------------+---------+
     | CREATE_TIME         | MODIFY_TIME         | LS_ID | SVR_IP         | SVR_PORT | SQL_PORT | ZONE  | ROLE     | MEMBER_LIST                                 | PAXOS_REPLICA_NUMBER | REPLICA_TYPE | LEARNER_LIST            | REBUILD |
     +---------------------+---------------------+-------+----------------+----------+----------+-------+----------+---------------------------------------------+----------------------+--------------+-------------------------+---------+
     | 01-APR-24 02.13.04. | 03-APR-24 01.52.17. |  1001 | 172.xx.xxx.192 |     2882 |     2881 | zone3 | FOLLOWER | NULL                                        |                 NULL | READONLY     | NULL                    | FALSE   |
     | 01-APR-24 02.13.04. | 03-APR-24 01.52.18. |  1001 | 172.xx.xxx.212 |     2882 |     2881 | zone2 | FOLLOWER | NULL                                        |                 NULL | FULL         | NULL                    | FALSE   |
     | 01-APR-24 02.13.04. | 03-APR-24 01.52.17. |  1001 | 172.xx.xxx.226 |     2882 |     2881 | zone1 | LEADER   | 172.xx.xxx.212:2882:1,172.xx.xxx.226:2882:1 |                    2 | FULL         | 172.xx.xxx.192:2882:1:0 | FALSE   |
     +---------------------+---------------------+-------+----------------+----------+----------+-------+----------+---------------------------------------------+----------------------+--------------+-------------------------+---------+
     3 rows in set
     

     The query result shows that the full-featured replica of log stream 1001 on OBServer node 172.xx.xxx.192:2882 is converted to a read-only replica.

References

• Locality

• Modify locality

• View locality

• Add replicas

• Remove replicas

• Modify the number of Paxos replicas of a log stream

• Migrate replicas