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 permission to transfer a partition. For more information about permissions, see Users and permissions.

• You can perform the TRANSFER PARTITION operation 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 will be 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 partition transfer for a system table.

• You cannot perform 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 will not be 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. 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 for the ID of the target tenant.

   obclient [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 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 non-partitioned table T1 exists in the oracle_tenant tenant. 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 query result is as follows:

     +----------+-----------+-----------+-------+
     | 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 partitioned table TBL1_LOG_R exists in the oracle_tenant tenant. 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 code 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 query result is as follows:

     +----------+-----------+-----------+-------+
     | 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 subpartitioned table T2_F_RL exists in the oracle_tenant tenant. 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 code 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 query 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 for the log stream status and information of the tenant.

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

      +-------+------+
      | 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 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 ID of the table.

   • object_id indicates the unique ID of the partition.

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

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

   The following sample code 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 has been constructed 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 TRANSFER_TASK_ID, which will change several times. A partition transfer task involves multiple transfers.

      If the query on the CDB_OB_TRANSFER_PARTITION_TASKS view returns an empty result set, you can query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view for the task execution result.

   2. Query the CDB_OB_BALANCE_JOBS or CDB_OB_BALANCE_JOB_HISTORY view based on the obtained BALANCE_JOB_ID value to confirm 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 column 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 was successfully executed.
      • CANCELING: The load balancing job is being canceled.
      • CANCELED: The load balancing job has been canceled.

   3. Query the CDB_OB_TRANSFER_TASKS or CDB_OB_TRANSFER_TASK_HISTORY view based on the obtained TRANSFER_TASK_ID value for the execution status of the 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 column 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 was aborted.
      • COMPLETED: The task was successfully executed.
      • FAILED: The task failed.
      • CANCELED: The task has been canceled.

7. Query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view based on the obtained TASK_ID value for the execution result of the partition transfer task.

   The CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the historical partition transfer tasks of 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 column in the query result indicates the execution result of the partition transfer task. Valid values:

   • COMPLETED: The task was successfully 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 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 no longer exist on the source log stream. The system will re-check whether the partition exists when generating the next load balancing task.

8. After successful execution of the partition transfer task, 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 query result is as follows:

   +----------+-----------+-----------+-------+
   | 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 has been 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_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