How To Recover From A Drop/Truncate/Delete Table Done On Primary Using Flashback On A Standby

Goal =>

In this article we will be discussing the steps needed to recover
from a drop/truncate/delete table done on primary using flashback on a standby database.

Recovering the table using flashback on standby will eliminate downtime being caused on primary database.


Solution ===>>>>>



Prerequisites: ++++


- Standby Database Must be in Flashback database mode.

- 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




Steps: ===>>>

1) Stop the managed recovery process.

SQL > recover managed standby database cancel;


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

Example
-------
SQL > shutdown immediate;

SQL > startup mount;

SQL > flashback database to timestamp to_date('29-MAY-2009 14:30:00','DD-MON-YYYY HH24:MI:SS');


3) Open the standby database in Read Only mode.

SQL> alter database open read only;


4) Check for the required data availability.

5) Take an export of the required table.

6) Import the table on the primary database.

7) Shutdown the standby database, mount and then roll forward the standby database to current time using the recover command.

SQL > shutdown immediate
SQL > startup mount
SQL > recover standby database;


7) Once all the archives are applied manually, you can start the MRP process to apply the archives automatically as and when they are shipped.

SQL > recover managed standby database disconnect from session;