You can execute the FLASHBACK statement to restore objects from the recycle bin.
Considerations
When you restore objects from the recycle bin, the restore order must comply with the dependency relationship. That is, restore the database first and then the tables in the database.
You cannot directly restore an index. When you execute the
FLASHBACKstatement 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.
Before you restore a table from the recycle bin in a user tenant, you must restore the database to which the table belongs if the database has been deleted.
If a table in the recycle bin originally belongs to a table group, it is restored to the table group by default. If the table group is deleted, the restored table does not belong to any table 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 in to the database as an administrator of the
systenant.Here is an example of the connection command. Use the actual database connection information in your production environment.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AFor more information about how to connect to a database, see Overview of connection methods.
Execute the
SHOW RECYCLEBINstatement to query for the names of objects in the recycle bin.obclient [(none)]> SHOW RECYCLEBIN; +-------------------------------------+-------------------+----------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-------------------------------------+-------------------+----------+----------------------------+ | RECYCLE_$_100017_1672050541224936 | mysql001 | 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 setRestore a tenant from the recycle bin in an appropriate way based on your business needs.
Restore a tenant in the recycle bin to a regular tenant with the original name.
The syntax is as follows:
FLASHBACK TENANT tenant_name TO BEFORE DROP;Here,
tenant_namespecifies 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.For example:
obclient [(none)]> FLASHBACK TENANT RECYCLE_$_100017_1672050541224936 TO BEFORE DROP; Query OK, 0 rows affectedRestore 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;Where:
tenant_namespecifies 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_namespecifies the new name of the tenant after it is restored from the recycle bin.
For example:
obclient [(none)]> FLASHBACK TENANT RECYCLE_$_100017_1672050541224936 TO BEFORE DROP RENAME TO new_oracle; Query OK, 0 rows affected
Restore databases
The sys tenant or a user tenant can restore databases from the recycle bin. 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 in to the database as the tenant administrator of the
systenant or a user tenant.Note
The default administrator user of a user tenant is the
rootuser.Here is an example of the connection command. Use the actual database connection information in your production environment.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AFor more information about how to connect to a database, see Overview of connection methods.
Execute the
SHOW RECYCLEBINstatement 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 setRestore 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;Here,
object_namespecifies 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.
For example:
obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP; Query OK, 0 rows affectedAfter executing this statement, if the database to be restored has the same name as an existing database of the tenant, the system will report an error.
obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP; ERROR 1007 (HY000): Can't create database 'infotest'; database existsRestore a database from the recycle bin and rename it.
FLASHBACK DATABASE object_name TO BEFORE DROP RENAME TO new_database_name;Where:
object_namespecifies the name of the database in the recycle bin. You cannot set this parameter to the original name of the database.new_database_namespecifies the new name of the database after it is restored from the recycle bin.
For example:
obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP RENAME TO new_infotest; Query OK, 0 rows affected
Restore tables
Both the sys tenant and user tenants can restore tables from the recycle bin. 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 in to the database as the tenant administrator of the
systenant or a user tenant.Note
The default administrator user of a user tenant is the
rootuser.Here is an example of the connection command. Use the actual database connection information in your production environment.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AFor more information about how to connect to a database, see Overview of connection methods.
Execute the
SHOW RECYCLEBINstatement 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 setRestore 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;Here,
object_namespecifies 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.For example:
obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP; Query OK, 0 rows affectedThe 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;Here,
object_namespecifies 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_namespecifies the new name of the table after it is restored from the recycle bin.For example:
obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME TO infotable; Query OK, 0 rows affectedThe 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;Here,
object_namespecifies 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_namespecifies 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.For example:
obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME TO test.infotable; Query OK, 0 rows affectedThe 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.
References
For more information about the recycle bin, see the following topics: