Change the replicated table attribute

In OceanBase Database, DUPLICATE_SCOPE specifies the attribute of a replicated table. After you create a table, you can change the value of the DUPLICATE_SCOPE parameter to convert a replicated table into a normal table, and the other way around.

Limitations and considerations

• After you initiate a replicated table attribute change task in a tenant, if a load balancing task is triggered by actions such as scaling, primary zone change, or locality change in the tenant, the replicated table attribute change task is scheduled only after the current load balancing task is completed.

  Notice

  The replicated table conversion process is asynchronous. After you manually change the value of the DUPLICATE_SCOPE parameter, the table can be used based on the new attribute only after background partition balancing tasks are completed. For more information about partition balancing, see Intra-tenant balancing.

• Replicated tables and table groups are mutually exclusive. You cannot convert a normal table that belongs to a table group into a replicated table.

• The conversion between normal and replicated tables relies on the load balancing and transfer features of the tenant. If any of the tenant-level parameters enable_rebalance, enable_transfer, and enable_rereplication is set to False, the conversion cannot be executed. If you change the value of enable_rebalance or enable_transfer to False when a conversion task is in progress, the task is canceled.

Prerequisites

Before you perform conversion, make sure that the following parameters are set to True:

• enable_rebalance

  You can specify the tenant-level parameter enable_rebalance in the sys tenant to control whether to enable load balancing among tenants, and specify the parameter in a user tenant to control whether to enable load balancing within the tenant. The default value is True.

• enable_transfer

  You can use the tenant-level parameter enable_transfer to specify whether to perform a transfer in a tenant. The default value is True.

• enable_rereplication

  You can use the cluster-level parameter enable_rereplication to specify whether to enable automatic data synchronization to replicas. The default value is True.

Syntax

The SQL syntax for changing the replicated table attribute is as follows:

ALTER TABLE table_name DUPLICATE_SCOPE= 'none | cluster';

The parameters are described as follows:

• none: specifies to convert the current table into a normal table.
• cluster: specifies to convert the current table into a replicated table.

Example

Here is an example of converting a replicated table into a normal table:

1. Create a replicated table named tbl1.

   obclient [test]> CREATE TABLE tbl1 (c1 int,c2 int) DUPLICATE_SCOPE= 'cluster';
   

2. Convert the replicated table into a normal table.

   ALTER TABLE tbl1 DUPLICATE_SCOPE= 'none';
   

   Notice

   After the preceding SQL statement modifies the table attribute, a success message is immediately returned. However, the conversion is executed asynchronously by background threads.

3. View the replicated table attribute of the table.

   You can view the current replicated table attribute and log stream ID of the table by querying the oceanbase.DBA_OB_TABLE_LOCATIONS view. If the value of the DUPLICATE_SCOPE column is CLUSTER, the table is a replicated table. The value NONE indicates a normal table.

   SELECT database_name, table_name, table_id, tablet_id, ls_id, role, duplicate_scope
   FROM oceanbase.DBA_OB_TABLE_LOCATIONS
   WHERE table_name = 'tbl1';
   

   The return result is as follows:

   +---------------+------------+----------+-----------+-------+--------+-----------------+
   | database_name | table_name | table_id | tablet_id | ls_id | role   | duplicate_scope |
   +---------------+------------+----------+-----------+-------+--------+-----------------+
   | test          | tbl1       |   500011 |    200002 |  1002 | LEADER | NONE            |
   +---------------+------------+----------+-----------+-------+--------+-----------------+
   1 row in set
   

   Note

   After the result is returned, you can perform the following steps to check whether the distribution of partition replicas conforms to the new attribute.

4. View the conversion progress.

   1. View the log stream attribute.

      All replicated tables in a tenant reside in a dedicated broadcast log stream. You can view the log stream attribute by querying the oceanbase.DBA_OB_LS view. If the value of the FLAG column is DUPLICATE, the log stream is a broadcast log stream. If the value of the FLAG is empty, the log stream is a general log stream.

      SELECT * FROM oceanbase.DBA_OB_LS WHERE LS_ID = 1002;
      

      The return result is as follows:

      +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
      | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG      |
      +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
      |  1002 | NORMAL | zone1        |             0 |           0 | 1713164377091593001 |     NULL | 1713171198496433000 | 1713171198496433000 | DUPLICATE |
      +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
      1 row in set
      

   2. Determine whether to generate a replicated table conversion task.

      Replicated table conversion tasks are generated by the load balancing and transfer modules at the background. You can execute the following statement to determine whether to generate a replicated table conversion task. DUPLICATE_SCOPE indicates the table attribute and FLAG indicates the attribute of the log stream to which the current tablet belongs. The DUPLICATE value of FLAG indicates a broadcast log stream.

      SELECT A.duplicate_scope, B.flag
      FROM oceanbase.DBA_OB_TABLE_LOCATIONS AS A JOIN oceanbase.DBA_OB_LS AS B ON A.ls_id = B.ls_id
      WHERE A.table_name = 'tbl1'
      AND A.role = 'LEADER';
      

      The return result is as follows:

      +-----------------+-----------+
      | duplicate_scope | flag      |
      +-----------------+-----------+
      | NONE            | DUPLICATE |
      +-----------------+-----------+
      1 row in set
      

      Information in the return result is described as follows:

      • If the value of DUPLICATE_SCOPE is NONE and that of FLAG is DUPLICATE, a non-replicated table resides in the broadcast log stream. In this case, a replicated-table-to-normal-table conversion task needs to be generated to transfer the non-replicated table from the broadcast log stream to a general log stream.
      • If the value of DUPLICATE_SCOPE is NONE and that of FLAG is not DUPLICATE, a replicated table resides in a general log stream. In this case, a normal-table-to-replicated-table conversion task needs to be generated to transfer the replicated table from the general log stream to the broadcast log stream.

   3. Generate a conversion task immediately.

      You can temporarily decrease the value of the partition_balance_schedule_interval parameter to immediately generate a conversion task.

      ALTER SYSTEM SET partition_balance_schedule_interval = '30s';
      

   4. View information of the conversion task.

      You can view information of the conversion task by querying the DBA/CDB_OB_BALANCE_JOB_HISTORY or DBA/CDB_OB_BALANCE_JOBS view.

      1. View the execution information of historical conversion tasks.

         If no historical record exists, a conversion task is ongoing or has not been generated.

         SELECT * FROM oceanbase.DBA_OB_BALANCE_JOB_HISTORY;
         

         The return result is as follows:

         +--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
         | JOB_ID | CREATE_TIME                | FINISH_TIME                | BALANCE_STRATEGY  | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT |
         +--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
         | 777854 | 2024-04-15 16:54:29.220400 | 2024-04-15 16:54:39.231516 | partition balance | PARTITION_BALANCE |               1 |                       1 | COMPLETED | NULL    |
         +--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
         1 row in set
         

      2. View the execution information of the current load balancing task.

         SELECT * FROM oceanbase.DBA_OB_BALANCE_JOBS;
         

      3. View information of the subtasks corresponding to the conversion task, including the partition list.

         Use the job ID obtained from the preceding task in the following SQL statement:

         SELECT * FROM oceanbase.DBA_OB_BALANCE_TASKS WHERE JOB_ID = xxxx;
         

         SELECT * FROM oceanbase.DBA_OB_BALANCE_TASK_HISTORY WHERE JOB_ID = xxxx;
         

References

• Create a table
• Modify a table