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
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.
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
