Step by Step Oracle 11gR2 RAC Standby Database Switchover
Description:-
In this article, let us do the two node RAC to RAC manual switchover steps.
Let us consider, we have already configured primary site with two node RAC and standby site also with two node RAC in 11gR2. You can refer here, for two node RAC to RAC dataguard setup in 11gR2 and also we are using the same configuration from the referred one.
A) Pre-Switchover Checks:-
1) Verify Managed Recovery is running on the standby
2) Verify there are no large Gaps
B) Switchover:-
1) Cancel any apply delay for the target standby
2) Create Guaranteed Restore Points (optional)
3) Verify that the primary database can be switched to the standby role
4) If The Primary is a RAC, then shutdown all secondary primary instances
5) Switchover the primary to a standby database
6) Verify that the standby database can be switched to the primary role
7) Switchover the standby database to a primary
8) Open the new primary database
9) Restart the new standby
C) Post-Switchover Steps:-
1) Drop any Switchover Guaranteed Restore Points
Here, we can see the primary and standby configuration:-
Let us verify the status and configuration of the RAC database:-
Primary:
$ 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: open Stop options: immediate Database role: PRIMARY 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
Standby:
$ 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: read only Stop options: immediate Database role: PHYSICAL_STANDBY 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
A) Pre-Switchover Checks:-
Step 1 – Verify Managed Recovery is running on the standby:-
Execute the following statement to verify managed recovery is running in standby
SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%'; no rows selected
Execute the below statement in primary to verify that recovery is running with “REAL TIME APPLY” option
In our environment we have configured LOG_ARCHIVE_DEST_2 as the ship redo for the standby. So verify with LOG_ARCHIVE_DEST_2 parameter in primary
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2; RECOVERY_MODE ----------------------- IDLE
Here, managed standby recovery is not running or not started with real-time apply. So restart manager recovery in standby with below command. But, if the status is “REAL TIME APPLY”, we can go ahead for the next step.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Database altered.
Step 2 – Verify there are no large Gaps:-
Identify the current sequence number for each thread on the primary
SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD; THREAD# SEQUENCE# ---------- ---------- 1 53 2 49
Verify the target physical standby database has applied up to, but not including the logs from the primary query. On the standby the following query should be within 1 or 2 of the primary query result.
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES' AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 52 2 47
B) Switchover:-
Step 1 – Cancel any apply delay for the target standby:-
Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database. Execute the following statement in standby, if there is a delay. Here, we don’t have any delay, so we can skip this step
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT FROM SESSION;
Step 2 – Create Guaranteed Restore Points (optional):-
Create a guaranteed restore point on the primary and standby database participating in the switchover as a fallback plan in case of any failure
On the standby:
Stop the apply process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
Create a guaranteed restore point: SQL> CREATE RESTORE POINT SWITCHOVER_A_TO_B GUARANTEE FLASHBACK DATABASE; Restore point created.
Start the apply process: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Database altered.
On the primary
Create a guaranteed restore point
SQL> CREATE RESTORE POINT SWITCHOVER_A_TO_B GUARANTEE FLASHBACK DATABASE; Restore point created.
Note: If a guaranteed restore points are created, make sure they are dropped post-switchover!
Step 3 – Verify that the primary database can be switched to the standby role:-
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY
A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly
Here we have TO STANDBY. So, now we are ready to the switchover
Step 4 – If The Primary is a RAC, then shutdown all secondary primary instances:-
If The Primary is a RAC, then shutdown all secondary instances in the primary site
$ srvctl status database -d ORCL_PRODRAC1 Instance ORCL1 is running on node prodrac101 Instance ORCL2 is running on node prodrac102
Here we know instance ORCL2 is running on prodrac102. So, shutdown it by below command
$ srvctl stop instance -d ORCL_PRODRAC1 -i ORCL2
Step 5 – Switchover the primary to a standby database:-
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN; Database altered.
$ tail -f alert_ORCL1.log <---- In Primary 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; Archivelog for thread 1 sequence 54 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required USER (ospid: 3779): terminating the instance Instance terminated by USER, pid = 3779 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN Shutting down instance (abort) License high water mark = 5 Fri Aug 17 21:02:50 2018 Instance shutdown complete
Step 6 – Verify that the standby database can be switched to the primary role:-
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
Step 7 – Switchover the standby database to a primary:-
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered.
$ tail -f alert_ORCL1.log <---- In New Primary MRP0: Background Media Recovery process shutdown (ORCL1) Role Change: Canceled MRP All dispatchers and shared servers shutdown CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. Fri Aug 17 21:05:17 2018 SMON: disabling cache recovery Backup controlfile written to trace file /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL1/trace/ORCL1_ora_13556.trc SwitchOver after complete recovery through change 1976213 Online logfile pre-clearing operation disabled by switchover Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_1.265.984104543: Thread 1 Group 1 was previously cleared Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_2.264.984104545: Thread 1 Group 2 was previously cleared Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_3.263.984104545: Thread 1 Group 3 was previously cleared Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_4.262.984104547: Thread 2 Group 4 was previously cleared Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_5.261.984104547: Thread 2 Group 5 was previously cleared Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_6.260.984104547: Thread 2 Group 6 was previously cleared Standby became primary SCN: 1976211 AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file. Switchover: Complete - Database mounted as primary Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
Step 8 – Open the new primary database:-
SQL> ALTER DATABASE OPEN; Database altered.
$ srvctl status database -d ORCL_PRODRAC2 Instance ORCL1 is running on node prodrac201 Instance ORCL2 is running on node prodrac202
Step 9 – Start the new standby:-
SQL> STARTUP MOUNT; <--- In New Standby SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Database altered.
III) Post-Switchover Steps:-
Step – 1 Drop any Switchover Guaranteed Restore Points:-
On all databases where a Guaranteed Restore point was created
On New Standby:-
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> DROP RESTORE POINT SWITCHOVER_A_TO_B; Restore point dropped. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Database altered.
On New Primary:- SQL> DROP RESTORE POINT SWITCHOVER_A_TO_B; Restore point dropped.
Reference:
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
