Transfer Partition

When automatic load balancing cannot meet the user's requirements for aggregating or dispersing specific partitions, you can manually initiate a Transfer Partition operation.

Limitations and considerations

• The user who executes the TRANSFER PARTITION statement must have the ALTER SYSTEM privilege. For more information about privileges, see Overview of users and privileges.

• You can execute the TRANSFER PARTITION statement only on the primary tenant and transfer partitions from a user tenant.

• After you disable the Transfer feature for a tenant by setting the enable_transfer parameter to false, executing the TRANSFER PARTITION statement on the tenant will return an error. If you have already executed the TRANSFER PARTITION statement, the corresponding Transfer Partition task may be canceled.

• You cannot execute the TRANSFER PARTITION statement on system tables.

• You cannot execute the TRANSFER PARTITION statement on user tables in a system tenant.

• You cannot transfer a regular table to a broadcast log stream or a replicated table to a regular log stream.

• You cannot transfer non-independent partitions, such as partitions of a local index table or a LOB table.

• You cannot initiate another TRANSFER PARTITION statement for the same partition before the current TRANSFER PARTITION statement is completed.

• If a balance job is running in the current cluster, a manually triggered TRANSFER PARTITION statement will not be scheduled immediately. To schedule the TRANSFER PARTITION statement as soon as possible, you can cancel the running balance job. For more information about how to cancel a balance job, see Cancel a Balance Job.

Prerequisites

• The Transfer feature must be enabled before you execute the TRANSFER PARTITION statement. The enable_transfer parameter at the tenant level controls whether the Transfer feature is enabled. By default, the value of this parameter is true, indicating that the Transfer feature is enabled. For more information about the enable_transfer parameter, see enable_transfer.

• To ensure that the partition location remains fixed, we recommend that you disable the automatic rebalance strategy before you execute the TRANSFER PARTITION statement. This means that you need to set the enable_rebalance parameter to false for the corresponding tenant. For more information about the enable_rebalance parameter, see enable_rebalance.

  The TRANSFER PARTITION statement is not affected by the enable_rebalance parameter. However, if both the enable_rebalance and enable_transfer parameters are set to true, the system may automatically migrate a partition to another log stream after you migrate it to the corresponding log stream.

Transfer a partition of a tenant

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

   Here is an example of the connection command. Replace the parameters with those in your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Query the DBA_OB_TENANTS view to obtain the TENANT_ID of the target tenant.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'oracle_tenant';
   

   The query result is as follows:

   +-----------+
   | TENANT_ID |
   +-----------+
   |      1006 |
   +-----------+
   1 row in set
   

3. Confirm the partition information.

   You can query the CDB_OB_TABLE_LOCATIONS view to obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified partition in the table.

   • Obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified partition in a non-partitioned table.

     Assume that a non-partitioned table named T1 exists in the oracle_tenant tenant. Here is an example of the query:

     obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T1' LIMIT 1;
     

     The query result is as follows:

     +----------+-----------+-----------+-------+
     | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
     +----------+-----------+-----------+-------+
     |   500002 |    500002 |    200001 |  1001 |
     +----------+-----------+-----------+-------+
     1 row in set
     

   • Obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified partition in a partitioned table.

     For a partitioned table, you need to specify the name of the partition.

     Assume that a partitioned table named TBL1_LOG_R exists in the oracle_tenant tenant.

     obclient(SYS@oracle_tenant)[SYS]> CREATE TABLE tbl1_log_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE)
      PARTITION BY RANGE(log_date) 
       (PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
        , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
        , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
        , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
        , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
        , PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
        , PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
        , PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
        , PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
        , PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
        , PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
        , PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
        , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
        );
     

     Here is an example of the query for the partition M202005 in the partitioned table TBL1_LOG_R:

     obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'TBL1_LOG_R' AND PARTITION_NAME = 'M202005' LIMIT 1;
     

     The query result is as follows:

     +----------+-----------+-----------+-------+
     | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
     +----------+-----------+-----------+-------+
     |   500003 |    500009 |    200006 |  1002 |
     +----------+-----------+-----------+-------+
     1 row in set
     

   • Obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified subpartition in a subpartitioned table.

     For a subpartitioned table, you need to specify the names of the partition and subpartition.

     Assume that a subpartitioned table named T2_F_RL exists in the oracle_tenant tenant.

     obclient(SYS@oracle_tenant)[SYS]> CREATE TABLE t2_f_rl(col1 INT,col2 VARCHAR2(50))
      PARTITION BY RANGE(col1)
      SUBPARTITION BY LIST(col2)
      (PARTITION p0 VALUES LESS THAN(100)
        (SUBPARTITION sp0 VALUES('01'),
         SUBPARTITION sp1 VALUES('02')
         ),
       PARTITION p1 VALUES LESS THAN(200)
        (SUBPARTITION sp2 VALUES('01'),
         SUBPARTITION sp3 VALUES('02'),
         SUBPARTITION sp4 VALUES('03')
        )
       );
     

     Here is an example of the query for the subpartition SP2 in the subpartitioned table T2_F_RL:

     obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T2_F_RL' AND PARTITION_NAME = 'P1' AND SUBPARTITION_NAME = 'SP2' LIMIT 1;
     

     The query result is as follows:

     +----------+-----------+-----------+-------+
     | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
     +----------+-----------+-----------+-------+
     |   500018 |    500023 |    200017 |  1003 |
     +----------+-----------+-----------+-------+
     1 row in set
     

4. Select the destination log stream for the transfer.

   1. Query the CDB_OB_LS view to obtain the log stream status and information of the tenant.

      obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_LS WHERE TENANT_ID = 1006;
      

      The query result is as follows:

      +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
      | TENANT_ID | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG | UNIT_LIST |
      +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
      |      1006 |     1 | NORMAL | zone1        |             0 |           0 |                NULL |     NULL | 1701244663685197789 | 1701244663685197789 |      |           |
      |      1006 |  1001 | NORMAL | zone1        |             0 |        1001 | 1701239786827662637 |     NULL | 1701244663685197789 | 1701244663685197789 |      | 1001      |
      |      1006 |  1002 | NORMAL | zone1        |             0 |        1002 | 1701239786831568305 |     NULL | 1701244664066906860 | 1701244664066906859 |      | 1002      |
      |      1006 |  1003 | NORMAL | zone1        |             0 |        1003 | 1701239786834300282 |     NULL | 1701244664175263949 | 1701244664175263948 |      | 1003      |
      +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
      4 rows in set
      

   2. Query the CDB_OB_TABLET_TO_LS view to obtain the Tablet distribution information on the log stream.

      obclient(root@sys)[oceanbase]> SELECT LS_ID, COUNT(*) AS C FROM oceanbase.CDB_OB_TABLET_TO_LS WHERE TENANT_ID = 1006 GROUP BY LS_ID;
      

      The query result is as follows:

      +-------+------+
      | LS_ID | C    |
      +-------+------+
      |     1 |  578 |
      |  1001 |    7 |
      |  1002 |    5 |
      |  1003 |    7 |
      +-------+------+
      4 rows in set
      

      Based on the information shown above, select a suitable log stream as the destination for the transfer.

5. Execute the following command to transfer a partition.

   ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] table_id, OBJECT_ID [=] object_id TO LS ls_id TENANT = 'tenant_name';
   

   where:

   • table_id: the ID of the table.

   • object_id: the unique identifier of the partition.

   • ls_id: the ID of the destination log stream.

   • tenant_name: the name of the tenant to which the partition belongs.

   For example, transfer the M202005 partition of the TBL1_LOG_R table in the SYS schema of the oracle_tenant tenant from the current log stream 1002 to the log stream 1003.

   obclient(root@sys)[oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500003, OBJECT_ID = 500009 TO LS 1003 TENANT = 'oracle_tenant';
   

6. After the Transfer Partition command is executed, you can query the following views to view the task status.

   1. Query the CDB_OB_TRANSFER_PARTITION_TASKS view to obtain the TASK_ID, TRANSFER_TASK_ID, and BALANCE_JOB_ID of the task.

      The CDB_OB_TRANSFER_PARTITION_TASKS view displays all the partition transfer tasks currently being processed by all tenants. Here is an example of the query:

      obclient(root@sys)[oceanbase]> SELECT TASK_ID, BALANCE_JOB_ID, TRANSFER_TASK_ID, STATUS FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASKS WHERE TENANT_ID = 1006 AND TABLE_ID = 500003 AND OBJECT_ID = 500009;
      

      The query result is as follows:

      +---------+----------------+------------------+--------+
      | TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS |
      +---------+----------------+------------------+--------+
      |       1 |          17304 |                1 | DOING  |
      +---------+----------------+------------------+--------+
      1 row in set
      

      You can view the task status based on the value of the STATUS column in the query result. You can further view the task execution progress as follows:

      • WAITING: indicates that the task is waiting for scheduling to start.
      • INIT: indicates that the BALANCE_JOB has been built. You can view the execution progress of the associated BALANCE_JOB based on the BALANCE_JOB_ID.
      • DOING: indicates that the task has started to transfer the partition. You can view the execution progress of the associated Transfer Partition task based on the TRANSFER_TASK_ID. The TRANSFER_TASK_ID changes frequently. A Transfer Partition task involves multiple transfers.

      If the query result of the CDB_OB_TRANSFER_PARTITION_TASKS view is empty, you can query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view to view the task result.

   2. Query the CDB_OB_BALANCE_JOBS or CDB_OB_BALANCE_JOB_HISTORY view based on the obtained BALANCE_JOB_ID to view the execution status of the associated BALANCE_JOB.

      The CDB_OB_BALANCE_JOBS view displays all the load balancing tasks currently being executed by all tenants. Each tenant has only one load balancing task (BALANCE_JOB) at a time. Each task generates multiple load balancing subtasks (TRANSFER_TASK). The CDB_OB_BALANCE_JOB_HISTORY view displays the history of all load balancing tasks executed by all tenants. Here are examples of the queries:

      obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE JOB_ID = 17304;
      

      obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE JOB_ID = 17304;
      

      where 17304 is replaced with the BALANCE_JOB_ID obtained in the previous step.

      The STATUS column in the query result displays the execution status of the BALANCE_JOB:

      • DOING: indicates that the load balancing task is being executed.
      • COMPLETED: indicates that the load balancing task has been successfully executed.
      • CANCELING: indicates that the load balancing task is being canceled.
      • CANCELED: indicates that the load balancing task has been canceled.

   3. Query the CDB_OB_TRANSFER_TASKS or CDB_OB_TRANSFER_TASK_HISTORY view based on the obtained TRANSFER_TASK_ID to view the execution status of the associated TRANSFER_TASK.

      The CDB_OB_TRANSFER_TASKS view displays the load balancing tasks that are currently being executed for all tenants. At the same time, multiple load balancing tasks (TRANSFER_TASK) can be executed, and these tasks belong to the same load balancing job (BALANCE_JOB). The CDB_OB_TRANSFER_TASK_HISTORY view displays the history of load balancing tasks executed for all tenants. Here are some query examples:

      obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASKS WHERE TASK_ID = 1;
      

      obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASK_HISTORY WHERE TASK_ID = 1;
      

      In the query, 1 needs to be replaced with the TRANSFER_TASK_ID obtained in the previous step.

      The STATUS column in the query result displays the execution status of the TRANSFER_TASK:

      • INIT: Indicates that the task is being created.
      • START: Indicates that the transfer is starting.
      • DOING: Indicates that the transfer is in progress.
      • ABORTED: Indicates that the transfer task has failed and has been terminated.
      • COMPLETED: Indicates that the transfer task has been completed.
      • FAILED: Indicates that the transfer task has failed.
      • CANCELED: Indicates that the transfer task has been canceled.

   4. Query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view using the obtained TASK_ID to confirm the result of the transfer partition task.

      The CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the history of transfer partition tasks executed for all tenants.

      obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;
      

      In the query, 1 needs to be replaced with the TASK_ID obtained in the previous step.

      The STATUS column in the query result displays the result of the transfer partition task:

      • COMPLETED: Indicates that the transfer partition task has been completed.

      • FAILED: Indicates that the transfer partition task has failed. You can check the COMMENT column to determine the cause of the failure. The following are some common messages in the COMMENT column:

        • LS not exist or may be in DROPPING/WAIT_OFFLINE status: The log stream at the destination may not exist or may be in the DROPPING or WAIT_OFFLINE state.
        • LS status is not NORMAL or is in BLOCK_TABLET_IN state: The log stream at the destination may not be in the NORMAL state, for example, it may be in the CREATING or CREATED state, or it may be in the BLOCK_TABLET_IN state, which means that the log stream cannot receive tablets.
        • Table has beed dropped: The table to which the partition belongs has been dropped.
        • Partition has beed dropped: The partition has been dropped.
        • Partition is already in dest LS: The partition already exists at the destination.
        • Need retry, partition may be dropped: The partition was dropped during the transfer. The system will check whether the partition exists when it generates the next load balancing task.
        • Need retry, partition may be dropped or be transferre: The partition does not exist during the transfer, which may be because the partition was dropped or the partition is no longer in the source log stream. The system will check whether the partition exists or whether a new transfer task needs to be generated when it generates the next load balancing task.

7. After the transfer partition task is completed, check the partition information again.

   obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'TBL1_LOG_R' AND PARTITION_NAME = 'M202005' LIMIT 1;
   

   The query result is as follows:

   +----------+-----------+-----------+-------+
   | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
   +----------+-----------+-----------+-------+
   |   500003 |    500009 |    200006 |  1003 |
   +----------+-----------+-----------+-------+
   1 row in set
   

   The result indicates that the M202005 partition of the TBL1_LOG_R table in the SYS database has been migrated from log stream 1002 to log stream 1003. The transfer partition operation was successful.

Transfer partition for a user tenant

References

• Cancel a balance job

• CDB_OB_TABLE_LOCATIONS

• DBA_OB_TABLE_LOCATIONS

• CDB_OB_LS

• DBA_OB_LS

• CDB_OB_TABLET_TO_LS

• DBA_OB_TABLET_TO_LS

• CDB_OB_BALANCE_JOBS

• DBA_OB_BALANCE_JOBS

• CDB_OB_BALANCE_JOB_HISTORY

• DBA_OB_BALANCE_JOB_HISTORY

• CDB_OB_TRANSFER_TASKS

• DBA_OB_TRANSFER_TASKS

• CDB_OB_TRANSFER_TASK_HISTORY

• DBA_OB_TRANSFER_TASK_HISTORY