Restore objects from the recycle bin|V4.1.0| docs|Distributed Database

Restore objects from the recycle bin

Last Updated：2023-08-01 06:02:28 Updated

You can execute the FLASHBACK statement to restore objects from the recycle bin.

Considerations

The order of flashing back objects must comply with their dependency. A database is flashed back before tables in the database.
Indexes cannot be restored directly. When you execute the FLASHBACK statement to restore a table, indexes on the table are restored together with the table.
You can modify the name of an object when you restore it from the recycle bin, but the object name must be different from an existing one. Otherwise, the system returns an error.
If the database of a MySQL tenant is deleted, before you restore a table from the recycle bin, restore the database first.
If a table in the recycle bin originally belongs to a group, it is restored to the group by default. If the group is deleted, the restored table does not belong to any group.

Restore tenants

You can delete tenants only in the sys tenant. Therefore, you can restore tenants from the recycle bin only in the sys tenant. You can modify the name of a tenant when you restore it from the recycle bin, but the tenant name must be different from an existing one. Otherwise, the system returns an error.

Log on to the database as an administrator of the sys tenant.

Example (the command can vary in practice):
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys -p***** -A
```
For more information, see Database connection overview.

Execute the SHOW RECYCLEBIN statement to query for the names of objects in the recycle bin.

obclient [(none)]> SHOW RECYCLEBIN;
+-------------------------------------+-------------------+----------+----------------------------+
| OBJECT_NAME                         | ORIGINAL_NAME     | TYPE     | CREATETIME                 |
+-------------------------------------+-------------------+----------+----------------------------+
| RECYCLE_$_100017_1672050541224936   | oracle001         | TENANT   | 2023-01-16 11:01:40.258000 |
| __recycle_$_100017_1673426335319344 | __idx_500788_idx1 | INDEX    | 2023-01-11 16:38:55.318878 |
| __recycle_$_100017_1673426335331800 | t1                | TABLE    | 2023-01-11 16:38:55.331481 |
| __recycle_$_100017_1673854707660504 | infotest          | DATABASE | 2023-01-16 15:38:27.660436 |
+-------------------------------------+-------------------+----------+----------------------------+
4 rows in set

Restore a tenant from the recycle bin in an appropriate way based on your business needs.
- Restore a tenant from the recycle bin to a regular tenant with the original name.
  
  Sample statement:
```
FLASHBACK TENANT tenant_name TO BEFORE DROP;
```
  tenant_name specifies the name of the tenant in the recycle bin or the original name of the tenant. The name in the recycle bin is globally unique and therefore can accurately specify the tenant to be restored. We recommend that you use the name in the recycle bin to restore the tenant. If you use the original name and multiple tenants in the recycle bin have the same original name, the last tenant moved to the recycle bin is restored.
  
  Example:
```
obclient [(none)]> FLASHBACK TENANT RECYCLE_$_100017_1672050541224936 TO BEFORE DROP;
Query OK, 0 rows affected
```
- Restore a tenant in the recycle bin to a regular tenant and rename it.
```
FLASHBACK TENANT tenant_name TO BEFORE DROP RENAME TO new_tenant_name;
```
  Parameters:
  - tenant_name specifies the name of the tenant in the recycle bin or the original name of the tenant. The name in the recycle bin is globally unique and therefore can accurately specify the tenant to be restored. We recommend that you use the name in the recycle bin to restore the tenant. If you use the original name and multiple tenants in the recycle bin have the same original name, the last tenant moved to the recycle bin is restored.
  - new_tenant_name specifies the new name of the tenant after it is restored from the recycle bin.
  Example:
```
obclient [(none)]> FLASHBACK TENANT RECYCLE_$_100017_1672050541224936 TO BEFORE DROP RENAME TO new_oracle;
Query OK, 0 rows affected
```

Restore databases

You can restore databases from the recycle bin in the sys tenant or a MySQL user tenant. You can modify the name of a database when you restore it from the recycle bin, but the database name must be different from an existing one. Otherwise, the system returns an error.

Log on to the database as an administrator of the sys tenant or a MySQL user tenant.

Note

The default administrator user of a MySQL user tenant is root.

Example (the command can vary in practice):
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys -p***** -A
```
For more information, see Database connection overview.

Execute the SHOW RECYCLEBIN statement to query for the names of objects in the recycle bin.

obclient [(none)]> SHOW RECYCLEBIN;
+-------------------------------------+-------------------+----------+----------------------------+
| OBJECT_NAME                         | ORIGINAL_NAME     | TYPE     | CREATETIME                 |
+-------------------------------------+-------------------+----------+----------------------------+
| __recycle_$_100017_1673426335319344 | __idx_500788_idx1 | INDEX    | 2023-01-11 16:38:55.318878 |
| __recycle_$_100017_1673426335331800 | t1                | TABLE    | 2023-01-11 16:38:55.331481 |
| __recycle_$_100017_1673854707660504 | infotest          | DATABASE | 2023-01-16 15:38:27.660436 |
+-------------------------------------+-------------------+----------+----------------------------+
3 rows in set

Restore a database from the recycle bin in an appropriate way based on your business needs.
- Restore a database from the recycle bin and keep the original name of the database.
```
FLASHBACK DATABASE object_name TO BEFORE DROP;
```
  object_name specifies the name of the database in the recycle bin. You cannot set this parameter to the original name of the database.
  
  If the name of the database to be restored is the same as that of an existing database, the system returns an error after you execute the statement.
  
  Example:
```
obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP;
Query OK, 0 rows affected
```
- Restore a database from the recycle bin and rename it.
```
FLASHBACK DATABASE object_name TO BEFORE DROP RENAME TO new_database_name;
```
  Parameters:
  - object_name specifies the name of the database in the recycle bin. You cannot set this parameter to the original name of the database.
  - new_database_name specifies the new name of the database after it is restored from the recycle bin.
  Example:
```
obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP RENAME TO new_infotest;
Query OK, 0 rows affected
```

Restore tables

You can restore tables from the recycle bin in the sys tenant, a MySQL user tenant, or an Oracle user tenant. You can modify the name of a table when you restore it from the recycle bin, but the table name must be different from an existing one. Otherwise, the system returns an error.

Log on to the database as an administrator of the sys tenant or a user tenant.

Note

The default administrator user of a MySQL user tenant is root and that of an Oracle user tenant is SYS.

Example (the command can vary in practice):
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys -p***** -A
```
For more information, see Database connection overview.

Execute the SHOW RECYCLEBIN statement to query for the names of objects in the recycle bin.

obclient [(none)]> SHOW RECYCLEBIN;
+-------------------------------------+-------------------+----------+----------------------------+
| OBJECT_NAME                         | ORIGINAL_NAME     | TYPE     | CREATETIME                 |
+-------------------------------------+-------------------+----------+----------------------------+
| __recycle_$_100017_1673426335319344 | __idx_500788_idx1 | INDEX    | 2023-01-11 16:38:55.318878 |
| __recycle_$_100017_1673426335331800 | t1                | TABLE    | 2023-01-11 16:38:55.331481 |
| __recycle_$_100017_1673854707660504 | infotest          | DATABASE | 2023-01-16 15:38:27.660436 |
+-------------------------------------+-------------------+----------+----------------------------+
3 rows in set

Restore a table from the recycle bin in an appropriate way based on your business needs.
- Restore a table from the recycle bin and keep the original name of the table.
```
FLASHBACK TABLE object_name TO BEFORE DROP;
```
  object_name specifies the name of the table in the recycle bin or the original name of the table. The name in the recycle bin is globally unique. Therefore, we recommend that you use the name in the recycle bin.
  
  Example:
```
obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP;
Query OK, 0 rows affected
```
  The name of the restored table is the original name of the table before it is moved to the recycle bin. The database or schema to which the table belongs remains unchanged. If the original name of the table is the same as that of an existing table, the system returns an error.
- Restore a table from the recycle bin and rename it.
```
FLASHBACK TABLE object_name TO BEFORE DROP RENAME To new_table_name;
```
  object_name specifies the name of the table in the recycle bin or the original name of the table. The name in the recycle bin is globally unique. Therefore, we recommend that you use the name in the recycle bin. new_table_name specifies the new name of the table after it is restored from the recycle bin.
  
  Example:
```
obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME To infotable;
Query OK, 0 rows affected
```
  The name of the restored table is the new name of the table after it is restored. The database or schema to which the table belongs remains unchanged. If the new name of the table is the same as that of an existing table, the system returns an error.
- Restore a table from the recycle bin to a specified database or schema and rename it.
```
FLASHBACK TABLE object_name TO BEFORE DROP RENAME To database_name.new_table_name;
```
  object_name specifies the name of the table in the recycle bin or the original name of the table. The name in the recycle bin is globally unique. Therefore, we recommend that you use the name in the recycle bin. database_name.new_table_name specifies the new name of the table after it is restored from the recycle bin, as well as the name of the database to which the table belongs. Schema object names of Oracle user tenants are the same as usernames.
  
  Example:
```
obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME To test.infotable;
Query OK, 0 rows affected
```
  The name of the restored table is the new name of the table after it is restored. The database or schema to which the table belongs is the specified one. If the new name of the table is the same as that of an existing table, the system returns an error.

More information

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

Restore objects from the recycle bin

Considerations

Restore tenants

Restore databases

Note

Restore tables

Note

More information