Flashback Technology : Flashback to a restore point having standby database also enabled

Yesterday we were in a scenario where we have to do some changes in our production database which if needed can be rollbacked.

We planned to go with a restore point option so that if change not needed by Development team, we can move back to before change time.

This was a big database and it has standby also enabled with it. So if we do a flashback on primary then incarnation of primary will differ from standby and recovery will be stopped.

Below are the steps which we used to handle both primary and standby in case of a Flashback database in Primary :-

1. Backup Primary database

2. Note the Current SCN of primary :-

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   15883324

3. Create a restore point 

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

4. Do your changes in database 

Now you want to do the flashback in your primary Database as the changes which you did, development team does not require those. PFB steps :-

SQL> startup force mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2260088 bytes

Variable Size            1124074376 bytes

Database Buffers         3137339392 bytes

Redo Buffers               12107776 bytes

Database mounted.

SQL> flashback database to restore point before_upgrade;

Flashback complete.

SQL> alter database open RESETLOGS;

Database altered.

Now when you check your standby database , MRP is stopped and it's showing that incarnation is different from primary database.

MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

Move your standby database to that SCN which was before doing a flashback.

Standby Database :-

SQL> flashback database to scn 15883324;

Flashback complete.

Now start the MRP and check the alert log, it will show MRP started successfully

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

Attempt to start background Managed Standby Recovery process (dr3fia)

Fri Jul 24 06:10:35 2015

MRP0 started with pid=49, OS id=14805

MRP0: Background Managed Standby Recovery process started (dr3fia)

 started logmerger process

Fri Jul 24 06:10:40 2015

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 12 slaves

Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log +Amit_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

I hope this article helped you.

Thanks

Amit Rath