Restore objects from the recycle bin

After an object is dropped, it is moved to the recycle bin. You can use the FLASHBACK statement to restore objects from the recycle bin.

Considerations

• When restoring tables, the order of Flashback operations must follow the dependency relationship: Flashback the database first, then Flashback the tables.

• Direct index restoration is not supported. When you restore a table using the FLASHBACK statement, the indexes on the table will also be restored.

• When restoring objects from the recycle bin, you can change the name of the object to be restored, but it cannot be the same as an existing object. Otherwise, an error will occur.

• For a MySQL tenant, before restoring a table from the recycle bin, if the database to which the table belongs has been deleted, you must first restore the database before restoring the table.

• If a table belongs to a table group before being moved to the recycle bin, it will be restored to the original table group by default. If the original table group has been deleted, the restored table will not belong to any table group.

Restore a tenant

Only the sys tenant can delete and restore tenants. Therefore, only the sys tenant can restore a tenant from the recycle bin to a normal tenant. When restoring a tenant, you can change the tenant name, but it cannot be the same as an existing tenant. Otherwise, an error will occur.

1. Log in to the database as the sys tenant.

   The following example shows how to connect to the database. Please replace the actual environment with the correct parameters.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

   For more information about how to connect to the database, see Overview of connecting to a database (MySQL mode) and Overview of connecting to a database (Oracle mode).

2. Execute the SHOW RECYCLEBIN statement to obtain the names of the 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
   

3. Based on your business needs, choose an appropriate scenario to restore a tenant from the recycle bin.

   • Restore the tenant to a normal tenant with the same name as before it was moved to the recycle bin.

     The statement is as follows:

     FLASHBACK TENANT tenant_name TO BEFORE DROP;
     

     In this statement, tenant_name can be the name in the recycle bin or the original name of the tenant. Since the name in the recycle bin is globally unique, it is recommended to use the name in the recycle bin to restore the tenant. If you use the original name, when multiple tenants in the recycle bin have the same original name, the tenant that was last moved to the recycle bin will be restored.

     Example:

     obclient [(none)]> FLASHBACK TENANT RECYCLE_$_100017_1672050541224936 TO BEFORE DROP;
     Query OK, 0 rows affected
     

   • Restore the tenant to a normal tenant with a new name.

     FLASHBACK TENANT tenant_name TO BEFORE DROP RENAME TO new_tenant_name;
     

     The parameters are described as follows:

     • tenant_name can be the name in the recycle bin or the original name of the tenant. Since the name in the recycle bin is globally unique, it is recommended to use the name in the recycle bin to restore the tenant. If you use the original name, when multiple tenants in the recycle bin have the same original name, the tenant that was last moved to the recycle bin will be 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 a database

The sys tenant or a MySQL user tenant can restore a database from the recycle bin. When restoring a database, you can change the database name, but it cannot be the same as an existing database. Otherwise, an error will occur.

1. Log in to the database as the tenant administrator of the sys tenant or a MySQL user tenant.

   Note

   The default administrator user for a MySQL tenant is root.

   The following example shows how to connect to the database. Please replace the actual environment with the correct parameters.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

   For more information about how to connect to the database, see Overview of connecting to a database (MySQL mode) and Overview of connecting to a database (Oracle mode).

2. Execute the SHOW RECYCLEBIN statement to obtain the names of the 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
   

3. Based on your business needs, choose an appropriate scenario to restore a database from the recycle bin.

   • Restore the database with the same name as before it was moved to the recycle bin.

     FLASHBACK DATABASE object_name TO BEFORE DROP;
     

     In this statement, object_name specifies the name of the database object in the recycle bin. The original name cannot be used.

     Example:

     obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP;
     Query OK, 0 rows affected
     

     After you execute this statement, if the database to be restored has the same name as an existing database in the tenant, an error will occur.

     obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP;
     ERROR 1007 (HY000): Can't create database 'infotest'; database exists
     

   • Restore the database with a new name.

     FLASHBACK DATABASE object_name TO BEFORE DROP RENAME TO new_database_name;
     

     The parameters are described as follows:

     • object_name specifies the name of the database object in the recycle bin. The original name cannot be used.

     • new_database_name: specifies the 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 in the recycle bin of the sys tenant, MySQL user tenant, or Oracle user tenant. When you restore a table, you can specify a new name for the table, but it must be unique. Otherwise, an error will be returned.

1. Log in to the database as the tenant administrator of the sys tenant or user tenant.

   Note

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

   The following example shows how to connect to the database. Replace the placeholder values with the actual values in your environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

   For more information about how to connect to the database, see Overview of connection methods (MySQL mode) and Overview of connection methods (Oracle mode).

2. Execute the SHOW RECYCLEBIN statement to obtain the names of the 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
   

3. Based on your business requirements, restore the table from the recycle bin in the appropriate scenario.

   • Restore the table from the recycle bin. The restored table retains its original name.

     FLASHBACK TABLE object_name TO BEFORE DROP;
     

     In this statement, object_name specifies the name of the table object in the recycle bin. You can also use the original name. The names in the recycle bin are globally unique, so we recommend that you use the names in the recycle bin.

     Here is an example:

     obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP;
     Query OK, 0 rows affected
     

     After you execute this statement, the restored table retains its original name. The database or schema object to which the table belongs remains the same as it was before the table was deleted. If the original name of the table already exists, an error will be returned.

   • Restore the table from the recycle bin and rename it.

     FLASHBACK TABLE object_name TO BEFORE DROP RENAME To new_table_name;
     

     In this statement, object_name specifies the name of the table object in the recycle bin. You can also use the original name. The names in the recycle bin are globally unique, so we recommend that you use the names in the recycle bin. new_table_name specifies the new name of the table after it is restored.

     Here is an example:

     obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME To infotable;
     Query OK, 0 rows affected
     

     After you execute this statement, the restored table is renamed. The database or schema object to which the table belongs remains the same as it was before the table was deleted. If the new name of the table already exists, an error will be returned.

   • Restore the table from the recycle bin to a specified database or schema object and rename it.

     FLASHBACK TABLE object_name TO BEFORE DROP RENAME To database_name.new_table_name;
     

     In this statement, object_name specifies the name of the table object in the recycle bin. You can also use the original name. The names in the recycle bin are globally unique, so we recommend that you use the names in the recycle bin. database_name.new_table_name specifies the new name of the table after it is restored and the database or schema object to which the table belongs.

     Here is an example:

     obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME To test.infotable;
     Query OK, 0 rows affected
     

     After you execute this statement, the restored table is renamed. The database or schema object to which the table belongs is the specified database or schema object. If the new name of the table already exists, an error will be returned.

References

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

• Overview of the recycle bin

• Enable or disable the recycle bin

• View objects in the recycle bin

• Purge the recycle bin