Transfer a partition

When automatic load balancing cannot meet your aggregation and dispersion requirements for a specific partition, you can manually transfer a partition.

Applicable scenarios and considerations

• To transfer a partition, you must have the ALTER SYSTEM permission. For more information about permissions, see Users and permissions.

• You can transfer a partition only on a primary tenant, and the partition must be under a user tenant.

• If you disable partition transfer by setting enable_transfer to false, an error is returned when you execute the TRANSFER PARTITION statement on the tenant, and partition transfer tasks for TRANSFER PARTITION statements that have been executed successfully may be canceled.

• Partition transfer does not support system tables.

• Partition transfer does not support user tables in the sys tenant.

• A common table cannot be transferred to a broadcast log stream, nor a replica table to a common log stream.

• Partition transfer does not support dependent partitions, for example, partitions of local index tables and LOB tables.

• Before a partition transfer operation is completed on one partition, you cannot initiate another partition transfer on the partition.

• If a balancing task (BALANCE_JOB) is already being processed in the current cluster, manually triggered partition transfer tasks will not be immediately scheduled.

Prerequisites

• Before transferring a partition, you must enable the transfer feature. You can set the enable_transfer parameter to enable or disable the transfer feature. The default value of the parameter is true, indicating that the transfer feature is enabled. For more information about the enable_transfer parameter, see enable_transfer.

• The partition transfer policy may conflict with the automatic balancing policy. To ensure a fixed partition location, we recommend that you disable the automatic balancing policy before you perform a partition transfer, that is, set enable_rebalance to false for the corresponding tenant. For more information about the enable_rebalance parameter, see enable_rebalance.

  Partition transfer itself is not subject to the enable_rebalance parameter. However, if both enable_rebalance and enable_transfer are set to true, after a user transfers a partition to the corresponding log stream, the system may transfer the partition to another log stream based on the automatic balancing policy.

Initiate a partition transfer from the sys tenant

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

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

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

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

   obclient [oceanbase]> SELECT TENANT_ID FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'oracle_tenant';
   

   The result is as follows:

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

3. Confirm the partitioning information.

   Query the CDB_OB_TABLE_LOCATIONS view to obtain the values of TABLET_ID, OBJECT_ID, and LS_ID of the specified partition.

   • Obtain the values of TABLET_ID, OBJECT_ID, and LS_ID of a non-partitioned table.

     Assume that the oracle_tenant tenant has a non-partitioned table named T1. Here is a query example:

     obclient [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 result is as follows:

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

   • Obtain the values of TABLET_ID, OBJECT_ID, and LS_ID of a partitioned table.

     You need to only specify a partition name when you query a partitioned table.

     Assume that the oracle_tenant tenant has a partitioned table named TBL1_LOG_R. Here is a query example:

     obclient [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)
        );
     

     The following example shows how to query the M202005 partition of the partitioned table:

     obclient [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 result is as follows:

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

   • Obtain the values of TABLET_ID, OBJECT_ID, and LS_ID of a subpartitioned table.

     You need to specify a partition name and a subpartition name when you query a subpartitioned table.

     Assume that the oracle_tenant tenant has a subpartitioned table named T2_F_RL. Here is a query example:

     obclient [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')
        )
       );
     

     The following example shows how to query the SP2 subpartition of the subpartitioned table:

     obclient [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 result is as follows:

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

4. Select the transfer destination.

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

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

      The 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 |
      +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
      |      1006 |     1 | NORMAL | zone1        |             0 |           0 |                NULL |     NULL | 1701244663685197789 | 1701244663685197789 |      |
      |      1006 |  1001 | NORMAL | zone1        |          1006 |        1001 | 1701239786827662637 |     NULL | 1701244663685197789 | 1701244663685197789 |      |
      |      1006 |  1002 | NORMAL | zone1        |          1007 |        1002 | 1701239786831568305 |     NULL | 1701244664066906860 | 1701244664066906859 |      |
      |      1006 |  1003 | NORMAL | zone1        |          1008 |        1003 | 1701239786834300282 |     NULL | 1701244664175263949 | 1701244664175263948 |      |
      +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
      4 rows in set
      

   2. Query the CDB_OB_TABLET_TO_LS view to obtain the tablet distribution on log streams.

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

      The result is as follows:

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

      Based on the preceding information, select an appropriate log stream as the transfer destination.

5. Execute the following statement to perform a partition transfer:

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

   where

   • table_id indicates the table ID.

   • object_id indicates the partition ID.

   • ls_id indicates the ID of the destination log stream of the transfer.

   • tenant_name indicates the tenant of the partition to be transferred.

   The following example transfers the M202005 partition of the TBL1_LOG_R table in the SYS database of the oracle_tenant tenant from the current 1002 log stream to the 1003 log stream.

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

6. After the partition transfer statement is executed successfully, you can query the task status in the following views.

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

      The CDB_OB_TRANSFER_PARTITION_TASKS view shows the partition transfer tasks that are being processed on all tenants. A sample query statement is as follows:

      obclient [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 result is as follows:

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

      You can check the STATUS field in the query result to confirm the task status and further view the task execution progress. The field has the following valid values:

      • WAITING: The task is waiting to be executed and has not been scheduled.
      • INIT: A BALANCE_JOB job has been constructed for the task. You can view the execution progress of the BALANCE_JOB job based on its BALANCE_JOB_ID value.
      • DOING: The transfer task has already been started. You can query partition transfer tasks by TRANSFER_TASK_ID. The value of TRANSFER_TASK_ID changes many times, because a partition transfer task involves multiple transfers.

      If the query of the CDB_OB_TRANSFER_PARTITION_TASKS view returns an empty query result, you can view the task result in the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view.

   2. Confirm the execution status of the associated BALANCE_JOB job based on the BALANCE_JOB_ID value obtained from the CDB_OB_BALANCE_JOBS or CDB_OB_BALANCE_JOB_HISTORY view.

      The CDB_OB_BALANCE_JOBS view shows the load balancing tasks that are being performed on all tenants. Each tenant has only one ongoing load balancing job (BALANCE_JOB) at a time, and each job generates multiple load balancing tasks (TRANSFER_TASK). The CDB_OB_BALANCE_JOB_HISTORY view shows the history of load balancing jobs that have been performed on all tenants. Sample query statements are as follows:

      obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE JOB_ID = 17304;
      

      obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE JOB_ID = 17304;
      

      Replace 17304 with the BALANCE_JOB_ID value obtained in the previous step.

      In the query result, the STATUS field shows the execution status of the BALANCE_JOB job. Valid values of the field are as follows:

      • DOING: The load balancing job is being performed.
      • COMPLETED: The load balancing job is successfully executed.
      • CANCELING: The load balancing job is being canceled.
      • CANCELED: The load balancing job is canceled.

   3. Query the CDB_OB_TRANSFER_TASKS or CDB_OB_TRANSFER_TASK_HISTORY view with the obtained TRANSFER_TASK_ID value to check the execution status of the TRANSFER_TASK task associated with the job.

      The CDB_OB_TRANSFER_TASKS view displays the load balancing tasks that are being performed on all tenants. Multiple load balancing tasks may be running at the same time. These tasks (TRANSFER_TASK) belong to the same load balancing job (BALANCE_JOB). The CDB_OB_TRANSFER_TASK_HISTORY view displays the history of load balancing tasks that have been performed on all tenants. Sample query statements are as follows:

      obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASKS WHERE TASK_ID = 1;
      

      obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASK_HISTORY WHERE TASK_ID = 1;
      

      Replace 1 with the TRANSFER_TASK_ID value obtained in the previous step.

      In the query result, the STATUS field shows the execution status of the TRANSFER_TASK task. The field has the following valid values:

      • INIT: The transfer task is being created.
      • START: The transfer task execution starts.
      • DOING: The transfer task is being executed.
      • ABORTED: The transfer task failed to execute and the task is terminated.
      • COMPLETED: The transfer task is successfully executed.
      • FAILED: The transfer task failed to execute.
      • CANCELED: The transfer task is canceled.

7. Query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view with the obtained TASK_ID value to confirm the result of the partition transfer task.

   The CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the history of partition transfer tasks that have been performed on all tenants.

   obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;
   

   Replace 1 with the TASK_ID value obtained in the previous step.

   In the query result, the STATUS field shows the result of the partition transfer task. The field has the following valid values:

   • COMPLETED: The partition transfer task is successfully executed.

   • FAILED: The partition transfer task failed to execute. You can further check the cause of the failure by using the COMMENT field. The following information is common in the COMMENT field:

     • LS not exist or may be in DROPPING/WAIT_OFFLINE status: The destination log stream does not exist or is in the DROPPING or WAITOFFLINE state.
     • LS status is not NORMAL or is in BLOCK_TABLET_IN state: The destination log stream is not in the NORMAL state. For example, it may be in the CREATING or CREATED state, or the log stream is in the BLOCK_TABLET_IN state and cannot accept new partitions.
     • Table has beed dropped: The table of the partition to be transferred is deleted.
     • Partition has beed dropped: The partition to be transferred is deleted.
     • Partition is already in dest LS: The partition is already on the transfer destination.
     • Need retry, partition may be dropped: The partition is deleted when the transfer task is executed. The system will re-check whether the partition exists when generating the next load balancing task.
     • Need retry, partition may be dropped or be transferred: The partition does not exist when the transfer task is executed. The partition may have been deleted or be no longer on the source log stream. The system will re-check whether the partition exists when generating the next load balancing task.

8. After the partition transfer task is successfully executed, check the corresponding partition information again.

   obclient [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 result is as follows:

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

   The query result shows that the M202005 partition of the TBL1_LOG_R table in the SYS database has been transferred from the 1002 log stream to the 1003 log stream. The partition transfer operation succeeded.

Initiate a partition transfer from a user tenant

References

• 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