Restore a tenant

To prevent irreversible data loss caused by malicious or accidental operations, OceanBase Database provides the recycle bin feature, which supports the quick restore of tenant-level data. This topic describes how to restore a tenant from the recycle bin.

OceanBase Database supports restoring a tenant from the recycle bin using the FLASHBACK TENANT statement.

Limitations

Since only the sys tenant can drop tenants, tenants in MySQL mode and Oracle mode can only be moved to the recycle bin of the sys tenant after being dropped. Only the sys tenant can perform tenant restore operations.

Procedure

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

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

2. Access the database named oceanbase.

   obclient(root@sys)[(none)]> USE oceanbase;
   

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

   obclient(root@sys)[oceanbase]> SHOW RECYCLEBIN;
   

   The returned result is as follows:

   +-------------------------------------+---------------+--------+----------------------------+
   | 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 recycle bin parameters, see Recycle bin.

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(root@sys)[oceanbase]> FLASHBACK TENANT mq_t1 TO BEFORE DROP;
   

   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(root@sys)[oceanbase]> SHOW RECYCLEBIN;
   Empty set
   

   If the query result is empty, check the DBA_OB_TENANTS view again to confirm that the tenant has been restored.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE, CREATE_TIME, MODIFY_TIME, PRIMARY_ZONE, LOCALITY, COMPATIBILITY_MODE, STATUS FROM DBA_OB_TENANTS WHERE TENANT_NAME = 'mq_t1';
   

   The returned result is as follows:

   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+--------------------+--------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE | CREATE_TIME                | MODIFY_TIME                | PRIMARY_ZONE | LOCALITY                     | COMPATIBILITY_MODE | STATUS |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+--------------------+--------+
   |      1036 | mq_t1       | USER        | 2023-01-11 00:23:36.317242 | 2023-05-18 15:49:12.857944 | zone1;zone2  | FULL{1}@zone1, FULL{1}@zone2 | MYSQL              | NORMAL |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+--------------------+--------+
   1 row in set
   

References

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