Restore a tenant

OceanBase Database provides the recycle bin feature to ensure the recoverability of data that is lost due to malicious operations or misoperations. This topic describes how to restore a tenant from the recycle bin.

OceanBase Database allows you to use the FLASHBACK TENANT statement to restore a tenant from the recycle bin.

Limitations

At present, only the sys tenant can drop tenants. Therefore, dropped MySQL and Oracle tenants are moved to the recycle bin of the sys tenant. Only the sys tenant can restore tenants.

Procedure

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

   obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. Execute the following statement to go to the database named oceanbase:

   obclient [(none)]> USE oceanbase;
   

3. Execute the SHOW RECYCLEBIN statement to view the objects in the recycle bin of the current sys tenant.

   obclient [oceanbase]> SHOW RECYCLEBIN;
   +-------------------------------------+---------------+--------+----------------------------+
   | OBJECT_NAME                         | ORIGINAL_NAME | TYPE  | CREATETIME                   |
   +-------------------------------------+---------------+--------+----------------------------+
   | __recycle_$_100017_1673367572701376 | mq_t1         | TENANT | 2023-01-11 00:23:36.317242 |
   +-------------------------------------+---------------+--------+----------------------------+
   1 row in set
   

   For more information about the parameters related to the recycle bin, see recyclebin.

4. Execute the FLASHBACK TENANT statement to restore a tenant in the recycle bin.

   To restore the mq_t1 tenant, execute the following statement:

   obclient [oceanbase]> FLASHBACK TENANT mq_t1 TO BEFORE DROP;
   Query OK, 0 rows affected
   

   For more information about the FLASHBACK TENANT statement, see FLASHBACK.

5. Verify the execution result in the DBA_OB_TENANTS view and recycle bin.

   obclient [oceanbase]> SHOW RECYCLEBIN;
   Empty set
   
   obclient [oceanbase]> SELECT * FROM DBA_OB_TENANTS WHERE TENANT_NAME = 'mq_t1';
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME                | MODIFY_TIME                | PRIMARY_ZONE | LOCALITY                                    | PREVIOUS_LOCALITY | COMPATIBILITY_MODE | STATUS | IN_RECYCLEBIN | LOCKED |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+
   |      1036 | mq_t1       | USER        | 2023-01-10 22:44:59.788717 | 2023-01-11 00:27:06.651798 | zone1,zone2  | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | NULL              | MYSQL              | NORMAL | NO            | NO     |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+---------------------------------------------+-------------------+--------------------+--------+---------------+--------+
   1 row in set
   

More information

For more information about tenant dropping and the recycle bin, visit the following links:

• Drop a tenant

• Recycle bin

• Restore objects from the recycle bin