Recover drop/truncate/delete table on primary using flashback on standby database

This post talks about how to recover table using flashback standby database

• Many times because of human error, Table accidentally get dropped/truncated or partially deleted in the primary database. Such errors can result in “logical” data corruption or cause one or more components of the IT infrastructure to cease operating
• We will discuss about recovering from this error by enabling Flashback technology on the standby database. we will be able to recover the database without any interruption of the primary database and Business
• while we have flashback table and flashback query also available to recover from this error  and they do also have limitations and here we assume that is not working here and data cannot be restored from it

Prerequisites for this recovery

• Standby Database Must be in Flashback database mode.
• The time at which Drop/Truncate/Delete Table happened should be within the db_flashback_retention_target and all the flashback and archive logs should be available

Enabling Flashback Database functionality provides the following benefits.

(1) Eliminate the time required to restore a backup. When a database is down because it runs into a catastrophic problem, millions of dollars in revenue is lost because a company can no longer operate.
(2)   Eliminate redo apply delay on standby database. Flashback Database is seamlessly integrated with Data Guard. A standby database can now be quickly and easily flashed back to any point in time so there is no need for a delay in the redo apply.
(3) Unanticipated error correction. Flashback Database provides a continuous snapshot of the Oracle database. The database can be rewound back to an SCN or timestamp.

How to Recover table on primary using flashback on standby database

Some user has deleted an important data in the table accidentally. Here are the steps followed to recover that data from the standby

Standby Database has  flashback mode enabled

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON
-------
YES

Steps to Recover

(1) Stop the managed recovery process on the standby side

SQL > recover managed standby database cancel;

(2) Create a restore point on standby before we flashback

CREATE RESTORE POINT before_flashback GUARANTEE FLASHBACK DATABASE;

(3) Deactivate the log transfer from Primary to standby side

ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER scope=memory;

(4) In order to recover lost data, use the flashback database feature to a time approximately before the Drop/Truncate/Delete Table happened.

SQL > shutdown immediate;
SQL > startup mount;
SQL > flashback database to timestamp to_date('20-MAY-2017 14:30:00','DD-MON-YYYY HH24:MI:SS');

(5) Open the standby database in Read Only mode.

SQL> alter database open read only;

(6) Check for the required data availability.

(7) Take an export of the required table or transfer that data to primary using db_link

(8) Import the table to the primary database.

(9) Shut down the standby database, mount and then roll forward the standby database to the current time using the flashback command.

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

or

you can use

SQL>recover standby database;

(10) You can enable the log shipping, and you can start the MRP process to apply the archives automatically as and when they are shipped.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable scope=memory;
SQL > recover managed standby database disconnect from session;

Related Articles

Recovering a Standby database from a missing archivelog
How to do physical Standby creation
Active Dataguard in Oracle 11g
Oracle database backup: Type of backup and backup strategy
how to Flashback when we have dataguard