Transfer a partition

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

Limitations and considerations

• You must have the ALTER SYSTEM privilege to transfer a partition. For more information about privileges, see Overview.

• You can perform a partition transfer only in the primary tenant to transfer only a partition of a user tenant.

• After you set the enable_transfer parameter to false to disable the transfer feature for a tenant, an error is returned if you execute the TRANSFER PARTITION statement for this tenant, and a partition transfer task initiated by a successfully executed TRANSFER PARTITION statement may be canceled.

• You cannot perform a partition transfer for a system table.

• You cannot perform a partition transfer for a user table in the sys tenant.

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

• You cannot transfer a non-independent partition, such as a partition of a local index table or large object (LOB) table.

• If a transfer operation is in progress for a partition, you cannot initiate another transfer operation for this partition before the current transfer operation is completed.

• If a load balancing job (BALANCE_JOB) is in progress in the current cluster, a manually initiated partition transfer task is not immediately scheduled. If you want to schedule the partition transfer task as soon as possible, you can cancel the load balancing job. For more information, see Cancel a load balancing job.

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 for a tenant. The default value of the parameter is true, indicating that the transfer feature is enabled.

• The partition transfer policy may conflict with the automatic load balancing policy. To ensure a fixed partition location, we recommend that you disable the automatic load balancing policy before you perform a partition transfer. In other words, you must set the value of the enable_rebalance parameter to false for the corresponding tenant.

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

Initiate a partition transfer from the sys tenant

1. Log in 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 for the ID of the target tenant.

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

   The following result is returned:

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

3. Confirm the partition information.

   You can query the CDB_OB_TABLE_LOCATIONS view for TABLET_ID, OBJECT_ID, and LS_ID values of the specified partition.

   • Query TABLET_ID, OBJECT_ID, and LS_ID values of a non-partitioned table

     Assume that the oracle_tenant tenant has a non-partitioned table named T1. A sample query is as follows:

     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 following result is returned:

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

   • Query TABLET_ID, OBJECT_ID, and LS_ID values of a partitioned table

     You need to specify only the partition name for the query.

     Assume that the oracle_tenant tenant has a partitioned table named TBL1_LOG_R. A sample query is as follows:

     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 sample statement queries related information about the M202005 partition in 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 following result is returned:

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

   • Query TABLET_ID, OBJECT_ID, and LS_ID values of a subpartitioned table

     You need to specify both the partition and subpartition names for the query.

     Assume that the oracle_tenant tenant has a subpartitioned table named T2_F_RL. A sample query is as follows:

     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 sample statement queries related information about the SP2 subpartition in 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 following result is returned:

     +----------+-----------+-----------+-------+
     | 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 for the log stream status and information of the tenant.

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

      The following result is returned:

      +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
      | 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 for the distribution of tablets on the 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 following result is returned:

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

      Select an appropriate log stream as the transfer destination based on the preceding information.

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';
   

   The parameters in the statement are described as follows:

   • table_id: the ID of the table.

   • object_id: the unique ID of the partition.

   • ls_id: the ID of the log stream to which the partition is to be transferred.

   • tenant_name: the tenant to which the partition to be transferred belongs.

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

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

6. After the statement is executed, query the status of the partition transfer task.

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

      The CDB_OB_TRANSFER_PARTITION_TASKS view displays the ongoing partition transfer tasks of all tenants. A sample query 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;
      

      A sample query result is as follows:

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

      The value of the STATUS field indicates the status of the task. Valid values:

      • WAITING: The task is waiting to be scheduled.
      • INIT: A load balancing job is generated for the task. You can view the execution progress of the load balancing job based on its BALANCE_JOB_ID value.
      • DOING: The task is in progress. You can view the task based on the TRANSFER_TASK_ID value. Multiple transfers are performed during the task, which updates this value several times.

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

   2. Query the CDB_OB_BALANCE_JOBS or CDB_OB_BALANCE_JOB_HISTORY view based on the obtained BALANCE_JOB_ID value and check the execution status of the associated load balancing job.

      The CDB_OB_BALANCE_JOBS view displays the ongoing load balancing jobs of all tenants. Each tenant can have only one running load balancing job (BALANCE_JOB) at a time. A load balancing job generates multiple transfer tasks (TRANSFER_TASK). The CDB_OB_BALANCE_JOB_HISTORY view displays the historical load balancing jobs of all tenants. A sample query is 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;
      

      You must replace 17304 with the BALANCE_JOB_ID value obtained in previous steps.

      The STATUS field in the query result indicates the execution status of the load balancing job. Valid values:

      • DOING: The load balancing job is in progress.
      • COMPLETED: The load balancing job is 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 based on the obtained TRANSFER_TASK_ID value and check the execution status of the associated transfer task.

      The CDB_OB_TRANSFER_TASKS view displays the ongoing transfer tasks of all tenants. Multiple ongoing transfer tasks (TRANSFER_TASK) that belong to the same load balancing job (BALANCE_JOB) can exist concurrently in a tenant. The CDB_OB_TRANSFER_TASK_HISTORY view displays the historical transfer tasks of all tenants. A sample query is 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;
      

      You must replace 1 with the TRANSFER_TASK_ID value obtained in previous steps.

      The STATUS field in the query result indicates the execution status of the transfer task. Valid values:

      • INIT: The task is being created.
      • START: The task starts to be executed.
      • DOING: The task is in progress.
      • ABORTED: The task is aborted.
      • COMPLETED: The task is executed.
      • FAILED: The task failed.
      • CANCELED: The task is canceled.

   4. Query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view based on the obtained TASK_ID value to confirm the result of the partition transfer task.

      The CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the historical partition transfer tasks executed in all tenants.

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

      You must replace 1 with the TASK_ID value obtained in previous steps.

      The STATUS field in the query result indicates the execution result of the partition transfer task. Valid values:

      • COMPLETED: The task is executed.

      • FAILED: The task failed. You can view the cause of the failure in the COMMENT column. 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 status, or the log stream is in the BLOCK_TABLET_IN state and cannot accept new partitions.
        • Table has been dropped: The table of the partition to be transferred is deleted.
        • Partition has been 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 re-checks whether the partition exists when generating the next load balancing task.
        • Need retry, partition may be dropped or be transferre: The partition does not exist when the transfer task is executed. The partition may have been deleted or no longer exist on the source log stream. The system re-checks whether the partition exists when generating the next load balancing task.

7. After the partition transfer task is executed, query the information about the partition 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 following result is returned:

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

   The result shows that the M202005 partition of the TBL1_LOG_R table in the SYS database is transferred from log stream 1002 to log stream 1003.

Initiate a partition transfer from a user tenant

References

• Cancel a partition transfer task
• Cancel a load balancing job
• CDB_OB_TABLE_LOCATIONS
• DBA_OB_TABLE_LOCATIONS
• CDB_OB_BALANCE_JOBS
• DBA_OB_BALANCE_JOBS
• CDB_OB_BALANCE_JOB_HISTORY
• DBA_OB_BALANCE_JOB_HISTORY