All Products
Search
Document Center

FLASHBACK DATABASE

Last Updated: Jun 18, 2021

Description

You can execute the FLASHBACK DATABASE statement to restore a deleted database from the recycle bin.

Prerequisites

The recycle bin is enabled. You can execute the following statement to check whether the recycle bin is enabled:

show variables like 'recyclebin';

OceanBase(admin@test)> 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 following statement to enable it:

set recyclebin = on;

Databases in the recycle bin are not deleted from the system and still consume resources. To delete them from the system, execute the following statement:

purge recyclebin;

Syntax

 FLASHBACK DATABASE object_name TO BEFORE DROP [RENAME TO db_name];

Parameters

Parameter

Description

object_name

The name of the object that you want to restore. You cannot specify a database name. If you restore a database, the tables and indexes in the database are also restored.

RENAME to

Rename the database that you want to restore.

Examples

  • Restore a deleted database from the recycle bin.
OceanBase(admin@test)> create database da;
Query OK, 1 row affected (0.03 sec)

OceanBase(admin@test)> drop database da;
Query OK, 0 rows affected (0.04 sec)

OceanBase(admin@test)> 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)

OceanBase(admin@test)> flashback database __recycle_$_1_1099511628829_18446744073709551615 to before drop;
Query OK, 0 rows affected (0.03 sec)