Restore a tenant

To prevent irreversible data loss caused by malicious operations or misoperations, 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 allows you to use the FLASHBACK TENANT statement to restore a tenant from the recycle bin.

Limitations

You can drop tenants only from the sys tenant. Therefore, MySQL and Oracle tenants can be dropped only to the recycle bin of the sys tenant. You can restore tenants only from the sys tenant.

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

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

   For example, 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. Query the DBA_OB_TENANTS view and check the recycle bin to verify the execution result.

   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 | TENANT_ROLE | SWITCHOVER_STATUS | SWITCHOVER_EPOCH | SYNC_SCN            | REPLAYABLE_SCN      | READABLE_SCN        | RECOVERY_UNTIL_SCN  | LOG_MODE     | ARBITRATION_SERVICE_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 | NULL              | MYSQL              | NORMAL | NO            | NO     | PRIMARY     | NORMAL            |                0 | 1684396167132057328 | 1684396167132057328 | 1684396167051160964 | 4611686018427387903 | NOARCHIVELOG | DISABLED                   |
   +-----------+-------------+-------------+----------------------------+----------------------------+--------------+------------------------------+-------------------+--------------------+--------+---------------+--------+-------------+-------------------+------------------+---------------------+---------------------+---------------------+---------------------+--------------+----------------------------+
   1 row in set
   

References

For more information about tenant dropping and the recycle bin, see the following topics:

• Drop a tenant

• Recycle bin

• Restore objects from the recycle bin