FLASHBACK

Purpose

You can use this statement to restore database objects, such as tenants, databases, and tables, from the recycle bin.

The recycle bin is enabled by default. The recyclebin parameter specifies whether to enable the recycle bin. You can use the SHOW VARIABLES LIKE 'recyclebin' statement to check whether the recycle bin is enabled. For more information about the recyclebin parameter, see System variables in the Reference Guide.

obclient> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
 Variable_name  Value 
+---------------+-------+
 recyclebin     ON    
+---------------+-------+
1 row in set (0.00 sec)

If the recycle bin is disabled, you can execute the SET recyclebin = on statement to enable it. Database objects in the recycle bin are not physically deleted and still occupy resources. You can execute the PURGE recyclebin statement to purge them from the recycle bin.

After a table is dropped, indexes on the table are also dropped. When you execute the FLASHBACK statement to restore the table, indexes on the table are also restored. Indexes that are directly dropped from tables are not placed in the recycle bin. In addition, OceanBase Database does not allow you to directly recover indexes from the recycle bin.

Syntax

 FLASHBACK {TENANT  DATABASE  TABLE} object_name TO BEFORE DROP [RENAME TO flashback_object_name];

flashback_object_name:
    tenant_name
   database_name
   database_name.table_name

Parameters

Examples

• Restore tenant tenant1 from the recycle bin.

  obclient> FLASHBACK TENANT tenant1 TO BEFORE DROP;
  Query OK, 0 rows affected (0.01 sec)
  

• Restore a database from the recycle bin.

  obclient> CREATE DATABASE da;
  Query OK, 1 row affected (0.03 sec)
  
  obclient> DROP DATABASE da;
  Query OK, 0 rows affected (0.04 sec)
  
  obclient> SHOW RECYCLEBIN;
  +--------------------------------------------------+---------------+----------+----------------------------+
   OBJECT_NAME                                       ORIGINAL_NAME  TYPE      CREATETIME                 
  +--------------------------------------------------+---------------+----------+----------------------------+
   __recycle_$_1_1099511628829_18446744073709551615  da             DATABASE  2017-10-20 17:36:15.838771 
  +--------------------------------------------------+---------------+----------+----------------------------+
  1 row in set (0.02 sec)
  
  obclient> FLASHBACK DATABASE __recycle_$_1_1099511628829_18446744073709551615 TO BEFORE DROP;
  Query OK, 0 rows affected (0.03 sec)
  

• Restore table t1 from the recycle bin, rename it as t2, and query the restoration status of indexes.

  obclient> CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT,c3 INT);
  Query OK, 0 rows affected (0.09 sec)
  
  obclient> ALTER TABLE t1 ADD INDEX ind2 (c2) USING BTREE;
  Query OK, 0 rows affected (0.58 sec)
  
  obclient> ALTER TABLE t1 ADD INDEX ind3 (c3) USING BTREE;
  Query OK, 0 rows affected (0.65 sec)
  
  obclient> SHOW INDEX FROM t1;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment    Index_comment  Visible 
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   t1              0  PRIMARY              1  c1           A                 NULL  NULL      NULL          BTREE       available                 YES     
   t1              1  ind2                 1  c2           A                 NULL  NULL      NULL    YES   BTREE       available                 YES     
   t1              1  ind3                 1  c3           A                 NULL  NULL      NULL    YES   BTREE       available                 YES     
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  3 rows in set (0.01 sec)
  
  obclient> ALTER TABLE t1 DROP INDEX ind2;
  Query OK, 0 rows affected (0.06 sec)
  
  obclient> SHOW RECYCLEBIN;
  Empty set (0.04 sec)
  
  obclient> DROP TABLE t1;
  Query OK, 0 rows affected (0.03 sec)
  
  obclient> SHOW RECYCLEBIN;
  +---------------------------------+--------------------------+-------+----------------------------+
   OBJECT_NAME                      ORIGINAL_NAME             TYPE   CREATETIME                 
  +---------------------------------+--------------------------+-------+----------------------------+
   __recycle_$_10_1628157070059520  __idx_1099511677777_ind3  INDEX  2021-08-05 17:51:10.060761 
   __recycle_$_10_1628157070067712  t1                        TABLE  2021-08-05 17:51:10.068062 
  +---------------------------------+--------------------------+-------+----------------------------+
  2 rows in set (0.00 sec)
  
  obclient> FLASHBACK TABLE __recycle_$_10_1628157070067712 TO BEFORE DROP RENAME TO t2;
  
  obclient> SHOW INDEX FROM t2;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment    Index_comment  Visible 
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   t2              0  PRIMARY              1  c1           A                 NULL  NULL      NULL          BTREE       available                 YES     
   t2              1  ind3                 1  c3           A                 NULL  NULL      NULL    YES   BTREE       available                 YES     
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  2 rows in set (0.01 sec)