Suspend or resume a balance job

If a tenant has an ongoing balance job and other urgent changes need to be performed, to prevent the balance job from affecting the urgent changes, you can suspend the ongoing balance job first, and resume the Job to continue its execution after the urgent changes are completed.

Limitations

• You can suspend or resume a balance job only on the primary database, that is, the primary tenant.

• When you suspend or resume a balance job, the status of the balance job cannot be CANCELING.

Suspend an ongoing balance job

Suspend an ongoing balance job of a user tenant in the sys 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. Get the information about the tenant.

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

   The query result is as follows:

   +-------------+-----------+-------------+
   | TENANT_NAME | TENANT_ID | TENANT_ROLE |
   +-------------+-----------+-------------+
   | mq_t1       |      1002 | PRIMARY     |
   +-------------+-----------+-------------+
   1 row in set
   

3. Query the CDB_OB_BALANCE_JOBS view to get the ongoing tasks and their statuses.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, JOB_ID, JOB_TYPE, STATUS FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+--------+--------------------+--------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS |
   +-----------+--------+--------------------+--------+
   |      1002 |  62944 | TRANSFER_PARTITION | DOING  |
   +-----------+--------+--------------------+--------+
   1 row in set
   

   According to the query result, the current balance job is in the DOING state instead of the CANCELING state, so you can suspend the Job.

4. Execute the following statement to suspend the balance job.

   ALTER SYSTEM SUSPEND BALANCE JOB TENANT = 'tenant_name';
   

   Here is an example:

   obclient(root@sys)[oceanbase]> ALTER SYSTEM SUSPEND BALANCE JOB TENANT ='mq_t1';
   

5. After the statement is executed, query the CDB_OB_BALANCE_JOBS view again. You can see that the status of the task changes to SUSPEND.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, JOB_ID, JOB_TYPE, STATUS FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+--------+--------------------+---------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS  |
   +-----------+--------+--------------------+---------+
   |      1002 |  62944 | TRANSFER_PARTITION | SUSPEND |
   +-----------+--------+--------------------+---------+
   1 row in set
   

Suspend an ongoing balance job in a user tenant

1. Connect to the database as the tenant administrator of a MySQL tenant or an Oracle tenant.

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

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

2. Query the DBA_OB_BALANCE_JOBS view to get the ongoing tasks and their statuses.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

   The query statement in Oracle-compatible mode is as follows:

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION |   DOING |
   +--------+--------------------+---------+
   1 row in set
   

   According to the query result, the current balance job is in the DOING state instead of the CANCELING state, so you can suspend the Job.

3. Execute the following statement to suspend the balance job.

   obclient> ALTER SYSTEM SUSPEND BALANCE JOB;
   

4. After the statement is executed, query the DBA_OB_BALANCE_JOBS view again. You can see that the status of the task changes to SUSPEND.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

   The query statement in Oracle-compatible mode is as follows:

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION | SUSPEND |
   +--------+--------------------+---------+
   1 row in set
   

Resume a suspended balance job

Resume a suspended balance job of a user tenant in the sys 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. Get the information about the tenant.

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

   The query result is as follows:

   +-------------+-----------+-------------+
   | TENANT_NAME | TENANT_ID | TENANT_ROLE |
   +-------------+-----------+-------------+
   | mq_t1       |      1002 | PRIMARY     |
   +-------------+-----------+-------------+
   1 row in set
   

3. Query the CDB_OB_BALANCE_JOBS view to get the suspended tasks and their statuses.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, JOB_ID, JOB_TYPE, STATUS FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+--------+--------------------+---------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS  |
   +-----------+--------+--------------------+---------+
   |      1002 |  62944 | TRANSFER_PARTITION | SUSPEND |
   +-----------+--------+--------------------+---------+
   1 row in set
   

   According to the query result, the current balance job is in the SUSPEND state.

4. Execute the following statement to resume the suspended balance job.

   ALTER SYSTEM RESUME BALANCE JOB TENANT = 'tenant_name';
   

   Here is an example:

   obclient(root@sys)[oceanbase]> ALTER SYSTEM RESUME BALANCE JOB TENANT ='mq_t1';
   

5. After the statement is executed, query the CDB_OB_BALANCE_JOBS view again. You can see that the status of the task changes to DOING.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, JOB_ID, JOB_TYPE, STATUS FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+--------+--------------------+---------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS  |
   +-----------+--------+--------------------+---------+
   |      1002 |  62944 | TRANSFER_PARTITION |  DOING  |
   +-----------+--------+--------------------+---------+
   1 row in set
   

Resume a suspended balance job in a user tenant

1. Connect to the database as the tenant administrator of a MySQL tenant or an Oracle tenant.

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

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

2. Query the DBA_OB_BALANCE_JOBS view to get the suspended tasks and their statuses.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

   The query statement in Oracle-compatible mode is as follows:

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION | SUSPEND |
   +--------+--------------------+---------+
   1 row in set
   

   According to the query result, the current balance job is in the SUSPEND state.

3. Execute the following statement to resume the suspended balance job.

   obclient> ALTER SYSTEM RESUME BALANCE JOB;
   

4. After the statement is executed, query the DBA_OB_BALANCE_JOBS view again. You can see that the status of the task changes to DOING.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

   The query statement in Oracle-compatible mode is as follows:

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION |  DOING  |
   +--------+--------------------+---------+
   1 row in set
   

References

• Cancel a partition transfer task
• Cancel a balance job