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-compatible mode and Oracle-compatible 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 [(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;
   

   Result:

   +-------------------------------------+---------------+--------+----------------------------+
   | 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
   

   When the query result is empty, query the DBA_OB_TENANTS view 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';
   

   Query result:

   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+--------------------+--------+
   | 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