Flash back objects from the recycle bin|V3.2.4|OceanBase Database| docs|Distributed Database

Flash back objects from the recycle bin

Last Updated：2023-10-27 09:57:43 Updated

This topic describes how to flash back databases, tables, and tenants from the recycle bin.

Limitations

The flashback order should follow a hierarchical relationship, meaning that the database should be flashed back first, followed by the tables in the database.
When you execute the FLASHBACK statement to restore a table, indexes on the table will also be restored.
Indexes can be purged by using the PURGE statement, but they cannot be restored by using the FLASHBACK statement.
A table in the recycle bin is restored to its original table group by default after the flashback. If the table group is dropped, the table will not belong to any groups after the flashback.

Restore databases and tables in MySQL mode

The tenant administrator can execute the FLASHBACK statement to restore databases and tables from the recycle bin. The object name can be modified upon restore, but it must be different from an existing object name.

Restore a database from the recycle bin

Restore a database from the recycle bin
```
obclient> FLASHBACK DATABASE object_name TO BEFORE DROP;
```
The object_name parameter in the statement indicates the name of the database object in the recycle bin, which can be obtained through the SHOW RECYCLEBIN statement. For more information about how to query the recycle bin, see Recycle bin for databases, tables, and indexes.

After the statement is executed, the restored database will have the same name as it had before entering the recycle bin.
Restore a database from the recycle bin and rename the database
```
obclient> FLASHBACK DATABASE object_name TO BEFORE DROP RENAME TO database_name;
```
where
- object_name indicates the name of the database object in the recycle bin.
- database_name indicates the name of the database after it is restored from the recycle bin.

Restore a table from the recycle bin

Before you flash back a table from the recycle bin, you must restore the database where the table belongs if this database is dropped.

Restore a table from the recycle bin
```
obclient> FLASHBACK TABLE object_name TO BEFORE DROP;
```
After the statement is executed, the restored table will have the same name as it had before entering the recycle bin, and the database to which the table belongs will remain the same as it was before the table was deleted.
Restore a table from the recycle bin and rename the table
```
obclient> FLASHBACK TABLE object_name TO BEFORE DROP RENAME to new_table_name;
```
After the statement is executed, the restored table will be renamed, and the database to which the table belongs will remain the same as it was before the table was deleted.
Restore a table from the recycle bin to the specified database and rename the table
```
obclient> FLASHBACK TABLE object_name TO BEFORE DROP RENAME to database_name.new_table_name;
```
After the statement is executed, the restored table will be renamed, and the database to which the table belongs will be the specified database.

In the statement:
- object_name indicates the name of the table object in the recycle bin, which can be obtained through the SHOW RECYCLEBIN statement. For more information about how to query the recycle bin, see Recycle bin for databases, tables, and indexes.
- new_table_name indicates the new name assigned to a table after it is restored.
- user_name.new_table_name indicates the new name assigned to a table after it is restored, along with the database to which it belongs.

Restore tables in Oracle mode

The Oracle mode of OceanBase Database only allows for restoring tables from the recycle bin.

Restore a table from the recycle bin
```
obclient> FLASHBACK TABLE object_name TO BEFORE DROP;
```
After the statement is executed, the restored table will have the same name as it had before entering the recycle bin, and the schema object to which the table belongs will remain the same as it was before the table was deleted.
Restore a table from the recycle bin and rename the table
```
obclient> FLASHBACK TABLE object_name TO BEFORE DROP RENAME to new_table_name;
```
After the statement is executed, the restored table is renamed, and the schema object to which the table belongs will remain the same as it was before the table was deleted.
Restore a table from the recycle bin to the specified schema object and rename the table
```
obclient> FLASHBACK TABLE object_name TO BEFORE DROP RENAME to user_name.new_table_name;
```
After the statement is executed, the restored table is renamed, and the schema object to which the table belongs will be the specified schema object.

In the statement:
- object_name indicates the name of the table object in the recycle bin, which can be obtained through the SHOW RECYCLEBIN statement. For more information about how to query the recycle bin, see Recycle bin for databases, tables, and indexes.
- new_table_name indicates the new name assigned to a table after it is restored.
- user_name.new_table_name indicates the new name assigned to a table after it is restored, along with the schema object to which it belongs.

Restore tenants

In OceanBase Database, only the sys tenant supports the recycle bin for tenants.

By default, when the recycle bin is enabled for the sys tenant, the DROP TENANT statement places the specified tenant in the recycle bin. You can execute the FLASHBACK statement as the administrator of the sys tenant (the root user) to restore tenants in the recycle bin as normal tenants.

Flash back a tenant from the recycle bin of the sys tenant as a normal tenant
```
obclient> FLASHBACK TENANT tenant_name TO BEFORE DROP;
```
Flash back a tenant from the recycle bin of the sys tenant as a normal tenant and rename the tenant
```
obclient> FLASHBACK TENANT tenant_name TO BEFORE DROP RENAME TO new_tenant_name;
```

In the statement:

tenant_name can be specified as the original tenant name or the tenant name in the recycle bin. The tenant name in the recycle bin is globally unique and can identify the tenant to be restored. If multiple tenants have the same original name, the last tenant placed in the recycle bin is the one restored.
new_tenant_name specifies the new name of the tenant after it is restored from the recycle bin.

Example

Assume that you want to restore the Oracle1 tenant from the recycle bin as a normal tenant and rename the tenant Oracle2.

Log on to the sys tenant with the root account.

Query objects in the recycle bin.

obclient> SHOW RECYCLEBIN;
+-----------------------------------+---------------+--------+----------------------------+
| OBJECT_NAME                       | ORIGINAL_NAME | TYPE   | CREATETIME                 |
+-----------------------------------+---------------+--------+----------------------------+
| RECYCLE_$_100017_1637551598530048 | Oracle1       | TENANT | 2021-11-22 11:27:14.548125 |
+-----------------------------------+---------------+--------+----------------------------+
1 row in set

Execute the following statement to flash back the Oracle1 tenant and rename it Oracle2:
```
obclient> FLASHBACK TENANT Oracle1 TO BEFORE DROP RENAME TO Oracle2;
```

Execute the following statement to verify that the tenant has been restored:

obclient>  SELECT * FROM gv$tenant;
+-----------+-------------+-----------+--------------+----------------+---------------+-----------+---------------+
| tenant_id | tenant_name | zone_list | primary_zone | collation_type | info          | read_only | locality      |
+-----------+-------------+-----------+--------------+----------------+---------------+-----------+---------------+
|         1 | sys         | zone1     | zone1        |              0 | system tenant |         0 | FULL{1}@zone1 |
|      1001 | MySQL       | zone1     | zone1        |              0 |               |         0 | FULL{1}@zone1 |
|      1002 | Oracle      | zone1     | zone1        |              0 |               |         0 | FULL{1}@zone1 |
|      1003 | Oracle2     | zone1     | zone1        |              0 |               |         0 | FULL{1}@zone1 |
+-----------+-------------+-----------+--------------+----------------+---------------+-----------+---------------+
4 rows in set

obclient>  SHOW RECYCLEBIN;
Empty set