Converting a Failed Primary Into a Standby Database Using Flashback Database
Description:-
In this article, we will be performing the reinstate of failed Oracle 11gR2 two node RAC into a physical standby database.
Let us assume, we have primary site with two node RAC in 11gR2 and standby site similar to primary with two nodes in 11gR2 and both the nodes of primary site was completely down and we have already failed over primary to standby. Now instead of building the physical standby using rman backup in the failed primary site, we have the option of reinstate the failed primary into physical standby from Oracle version 11.2.0.3 if we have already enabled the flashback in the failed primary before the failover.
Below are the steps to be followed to reinstate the failed primary RAC database into physical standby database.
1) Flashback the Failed Primary Database into a Physical Standby Database
2) Flash back the failed primary database
3) Convert the failed primary database to a physical standby database
4) Start the transport and apply redo to the new physical standby database
Below is the primary and standby configuration:Let us verify the status and configuration of the RAC database:-
New Primary Site:-
$ srvctl config database -d ORCL_PRODRAC2 -a Database unique name: ORCL_PRODRAC2 Database name: ORCL Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1 Oracle user: oracle Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: ORCL_PRODRAC2 Database instances: ORCL1,ORCL2 Disk Groups: DBWR_DATA,DBWR_FRA Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed
Standby:- (Failed Primary Site)
$ srvctl config database -d ORCL_PRODRAC1 -a Database unique name: ORCL_PRODRAC1 Database name: ORCL Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1 Oracle user: oracle Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora Domain: Start options: read only Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: ORCL_PRODRAC1 Database instances: ORCL1,ORCL2 Disk Groups: DBWR_DATA,DBWR_FRA Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed
Step 1 – Flashback the Failed Primary Database into a Physical Standby Database:-
The following steps brings the old primary database back into the Data Guard configuration as a physical standby database.
On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 2398590
Step 2 – Flashback the failed primary database:-
Shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in Step 1
$ srvctl status database -d ORCL_PRODRAC1 Instance ORCL1 is not running on node prodrac101 Instance ORCL2 is not running on node prodrac102 $ srvctl start database -d ORCL_PRODRAC1 -i ORCL1 -o mount $ srvctl status database -d ORCL_PRODRAC1 -v Instance ORCL1 is running on node prodrac101. Instance status: Mounted. Instance ORCL2 is not running on node prodrac102
Flashing back in Node1 (ORCL1) of failed primary:
SQL> FLASHBACK DATABASE TO SCN 2398590; Flashback complete.
Step 3 – Convert the failed primary database to a physical standby database:-
Issue the following statement on the old primary database.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered.
This statement will dismount the database after successfully converting the control file to a standby control file. We can verify it from the alert log.
$ tail -f alert_ORCL1.log ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ORCL1) Flush standby redo logfile failed:1649 Clearing standby activation ID 1512257592 (0x5a233838) The primary database controlfile was created using the 'MAXLOGFILES 192' clause. There is space for up to 186 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 52428800; Shutting down archive processes Archiving is disabled Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Shut down and restart the database and start the recovery process in new standby database.
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Database altered.
$ tail -f alert_ORCL1.log ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT Attempt to start background Managed Standby Recovery process (ORCL1) Sun Aug 19 16:52:57 2018 MRP0 started with pid=80, OS id=1356 MRP0: Background Managed Standby Recovery process started (ORCL1) Serial Media Recovery started Sun Aug 19 16:53:02 2018 Managed Standby Recovery starting Real Time Apply Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 2398592 Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Clearing online redo logfile 1 +DBWR_DATA/orcl_prodrac1/onlinelog/group_1.261.983984787 Clearing online log 1 of thread 1 sequence number 84 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT Clearing online redo logfile 1 complete Clearing online redo logfile 2 +DBWR_DATA/orcl_prodrac1/onlinelog/group_2.262.983984795 Clearing online log 2 of thread 1 sequence number 82 Clearing online redo logfile 2 complete Clearing online redo logfile 3 +DBWR_DATA/orcl_prodrac1/onlinelog/group_3.263.983984807 Clearing online log 3 of thread 1 sequence number 83 Clearing online redo logfile 3 complete Clearing online redo logfile 4 +DBWR_DATA/orcl_prodrac1/onlinelog/group_4.267.983985363 Clearing online log 4 of thread 2 sequence number 66 Clearing online redo logfile 4 complete Clearing online redo logfile 5 +DBWR_DATA/orcl_prodrac1/onlinelog/group_5.268.983985375 Clearing online log 5 of thread 2 sequence number 67 Sun Aug 19 16:53:07 2018 Clearing online redo logfile 5 complete Clearing online redo logfile 6 +DBWR_DATA/orcl_prodrac1/onlinelog/group_6.269.983985387 Clearing online log 6 of thread 2 sequence number 65 Clearing online redo logfile 6 complete Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_84.410.984588465 Identified End-Of-Redo (failover) for thread 1 sequence 84 at SCN 0x0.249980 Resetting standby activation ID 1512257592 (0x5a233838) Media Recovery End-Of-Redo indicator encountered Media Recovery Continuing Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_1.412.984588469 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_2.413.984588469 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_3.411.984588467 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_1.407.984588457 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_2.406.984588457 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_3.408.984588457 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_4.414.984588469 Sun Aug 19 16:53:29 2018 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_5.415.984588469 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_4.409.984588461 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_5.417.984588593 Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_6.416.984588577 Media Recovery Waiting for thread 1 sequence 7 (in transit) Recovery of Online Redo Log: Thread 1 Group 8 Seq 7 Reading mem 0 Mem# 0: +DBWR_DATA/orcl_prodrac1/onlinelog/group_8.272.984080839 Media Recovery Waiting for thread 2 sequence 6 (in transit) Recovery of Online Redo Log: Thread 2 Group 11 Seq 6 Reading mem 0 Mem# 0: +DBWR_DATA/orcl_prodrac1/onlinelog/group_11.275.984080865
Step 4 – Verify the redo transport and apply to the new physical standby database:-
Perform the following steps on the new primary database:
Issue the following query to see the current state of the archive destinations
SQL> col DEST_NAME for a20 SQL> col DESTINATION for a20 SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS where DEST_ID=2; DEST_ID DEST_NAME STATUS PROTECTION_MODE ---------- -------------------- --------- -------------------- 2 LOG_ARCHIVE_DEST_2 VALID MAXIMUM PERFORMANCE DESTINATION ERROR SRL --------------- ----------------- ---------------------------------- ORCL_PRODVIP101 YES
Here, we have a valid destination. So, let’s verify the redo transport from primary to new physical standby database.
SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
$ tail -f alert_ORCL1.log <-- New Standby Site - Node1 Tue Aug 21 00:47:11 2018 RFS[7]: Selected log 8 for thread 1 sequence 13 dbid 1511765518 branch 984586385 Tue Aug 21 00:47:11 2018 Archived Log entry 265 added for thread 1 sequence 12 ID 0x5a248ea9 dest 1: Tue Aug 21 00:47:11 2018 Media Recovery Waiting for thread 1 sequence 13 (in transit) Recovery of Online Redo Log: Thread 1 Group 8 Seq 13 Reading mem 0 Mem# 0: +DBWR_DATA/orcl_prodrac1/onlinelog/group_8.272.984080839
Reference:-
https://docs.oracle.com/cd/E11882_01/server.112/e41134/scenarios.htm#SBYDB00910
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba