We generally have Dataguard in the Production env and for some reason, if we have to flashback Production then we want to keep the standby in the Sync without rebuilding from scratch. Here this post talks about how to flashback database to restore point with data guard
Step for flashback database to restore point with data guard
(1)Disable Log Transport Services to the Standby Database.
On Primary Database alter system set log_archive_dest_state_n = 'defer' scope=both;
If dg broker is being used, then issue the below command
DGMGRL> edit database set state = 'TRANSPORT-OFF';
(2) Perform a Log Switch on all Primary Database Instances
Sqlplus / as sysdba
alter system switch logfile;
(3) Stop Managed Recovery on the Standby Database using SQL*PLUS
alter database recover managed standby database cancel;
if Dg broker is being used, then issue the below command
DGMGRL> edit database set state = 'APPLY-OFF';
(4) Shutdown the Primary database and startup in the mount state
shutdown immediate
startup mount
(5) Flashback the Primary Database to the desired Restore Point and open the database with resetlogs
flashback database to restore point ; alter database open resetlogs;
(6) Flashback the Standby Database to the desired Restore Point:
flashback database to restore point ;
(7) Enable Log Transport Services to the Standby Database
On Primary database, alter system set log_archive_dest_state_n = 'enable' scope=both; or if using DG broker DGMGRL> edit database <Primary Database> set state = 'TRANSPORT-ON';
(8)Perform a Log Switch on all Primary Database Instances
alter system switch logfile;
(9)Once Log Transport Services is running again you can enable Managed Recovery on the Standby Database:
alter database recover managed standby database using current logfile disconnect
If using DG broker,
DGMGRL> edit database set state = 'APPLY-ON';
10 . Verify the Standby Database is now following the Primary Database into the new Incarnation
SQL> SELECT INCARNATION#, RESETLOGS_ID, STATUS FROM V$DATABASE_INCARNATION ;
INCARNATION# RESETLOGS_ID STATUS
------------ ------------ -------
1 509191005 PARENT
2 509275501 CURRENT
I hope this post helps in flashback database to restore point with data guard. Please do provide the feedback
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
Oracle Flashback Database: Oracle Flashback Database is a new feature from 10g onwards which uses past images to back out changes .it is enabled using alter database flashback on
Flashback Database queries: Check out this post to find the top useful oracle Flashback Database queries, how to enable flashback, how to perform flashback
recover table using flashback standby database: Detailed steps to recover table using flashback standby database. Accidental delete, drop, and truncate can be recovered using this technique