Recover drop/truncate/delete table on primary using flashback on 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 database without any interruption 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 limitation 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.
  • 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.

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

Standby Database has  flashback mode enabled
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON


YES

Steps:

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;

3) 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’);

4) Open the standby database in Read Only mode.

SQL> alter database open read only;

5) Check for the required data availability.

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

7) Import the table on the primary database.

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

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

9) You can enable the log shipping, 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


Leave a Reply