Recover Drop, Truncate Or Deleted Table With Flashback

Melek Durdu

2 years ago

In this article, I will talk about how to recover accidentally drop, truncate or deleted table in Production database by using flashback in standby database.

First of all, we enable the flashback mode of the standby database.

SQL> alter database flashback on;

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback on;

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

We stop the Apply process.

SQL > recover managed standby database cancel;

1	SQL > recover managed standby database cancel;

The restore point is created.

SQL> create restore point before_flashback guarantee flashback database;

1	SQL> create restore point before_flashback guarantee flashback database;

Sending archives from the primary database to standby is stopped.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

1	SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Flashback returns the table before it was dropped, truncated or deleted.

SQL > shutdown immediate;
SQL > startup mount;
SQL > flashback database to timestamp to_date('01-NOV-2022
12:05:00','DD-MON-YYYY HH24:MI:SS');

SQL > shutdown immediate;

SQL > startup mount;

SQL > flashback database to timestamp to_date('01-NOV-2022

12:05:00','DD-MON-YYYY HH24:MI:SS');

We open the standby database in read only mode.

SQL> alter database open read only;

1	SQL> alter database open read only;

After the database is opened in read-only mode, the table can be transferred to the Production environment with two methods.

1.Export — Import

We export the table.

$ expdp melek/<password>@orcl_stby directory=DUMP_DIR 
tables=HR.EMPLOYEES dumpfile=exp_Employees.dmp 
logfile=exp_Employees.log

$ expdp melek/<password>@orcl_stby directory=DUMP_DIR

tables=HR.EMPLOYEES dumpfile=exp_Employees.dmp

logfile=exp_Employees.log

After copying the Dump file to the Production environment, we import the table.

$ impdp melek/<password>@orcl directory=DUMP_DIR 
dumpfile=exp_Employees.dmp logfile=imp_Employees.log

1 2	$ impdp melek/<password>@orcl directory=DUMP_DIR dumpfile=exp_Employees.dmp logfile=imp_Employees.log

2. DBLink

By connecting the Production database to the Standby database with DBLink, we recover the table related to the CTAS method.

SQL> CREATE TABLE HR.EMPLOYEES AS SELECT * FROM HR.EMPLOYEES@PROD_TO_STBY;

1	SQL> CREATE TABLE HR.EMPLOYEES AS SELECT * FROM HR.EMPLOYEES@PROD_TO_STBY;

After the table is recovered, the Standby database is closed, opened in mount mode, and the restored point created is returned.

SQL > shutdown immediate
SQL > startup mount
SQL > FLASHBACK DATABASE TO RESTORE POINT before_flashback;

SQL > shutdown immediate

SQL > startup mount

SQL > FLASHBACK DATABASE TO RESTORE POINT before_flashback;

We start the process of sending archives from the Production database to the Standby database.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

1	SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

We close the flashback, start the apply process in the Standby database and drop the restore point created.

SQL> alter database flashback off;
SQL> recover managed standby database disconnect from session;
SQL> DROP RESTORE POINT before_flashback;

SQL> alter database flashback off;

SQL> recover managed standby database disconnect from session;

SQL> DROP RESTORE POINT before_flashback;