How to perform manual switchover for RAC DB in Oracle 11gR2 (11.2.0.4)
Posted by Mir Sayeed Hassan on April 15th, 2019
How to perform manual switchover for RAC DB in Oracle 11gR2 (11.2.0.4)
In brief – The below switchover is to perform manual switchover for RAC DB – Here the primary will act as standby & standby will act as primary.
My current RAC Test DB Resources are – 2 Node of Primary Database & 1 Single Node of Standby Database
The Priority before start of switchover, Shutdown all the primary/standby instances except one (If you have 2 node of primary & 2 node of standby – Shutdown each one of them)
Verify any gap exist in database
On Primary DB:
SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,(SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#; THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_ ARC_DIFF ----------------------------------------------------------------------------------------------------- 1 3169 3169 08-APR-19 0 2 3002 3001 08-APR-19 1
On Standby DB:
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------------------------------------------------------------------------------- ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 1 3170 14238 1 RFS IDLE 0 0 0 0 MRP0 WAIT_FOR_LOG 1 3170 0 0 RFS IDLE 0 0 0 0 RFS IDLE 2 3003 12481 1 RFS IDLE 0 0 0 0 PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS -------------------------------------------------------------------------------- RFS IDLE 0 0 0 0 12 rows selected. THREAD# MAX(SEQUENCE#) ------------------------------------------ 1 3169 2 3001
Switch log file on primary database.
On Primary DB
SQL> select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance; NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ---------------------------------------------------------------------------------------------------------------------------------- RAC rac_1 READ WRITE PRIMARY SESSIONS ACTIVE Or NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ---------------------------------------------------------------------------------------------------------------------------------- RAC rac_1 READ WRITE PRIMARY TO STANDBY
Note:
If switchover_status shows ‘SESSION ACTIVE’ – then try to kill the any process running in database
If switchover_status shows ‘TO_STANDBY’ – then proceed further for switch over process
SQL> select sid, process, program from v$session where type='user' and sid <>(select distinct sid from V$mystat); no rows selected
if any rows/process found then kill those process or wait until logout.
We have 2 Node of RAC – Shutdown the node 2 primary DB, If you have 2 Node in standby DB – try to shutdown (In my case I have only 1 node of standby DB)
Shutdown the Node 2 of Primary DB
[oracle(rac_2)@trac2 ~$srvctl stop instance -d rac -i rac_2
Check the status
[oracle(rac_2)@trac2 ~$srvctl status database -d rac Instance rac_1 is running on node trac1 Instance rac_2 is not running on node trac2 -- Hence shutdown
On Standby DB
Check the status of Standby before switchover
SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE -------------------------------------------------------------------------------- PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select switchover_status from v$database; SWITCHOVER_STATUS ---------------- NOT ALLOWED
Stop the apply process
SQL> alter database recover managed standby database cancel; Database altered.
On Primary DB – Start the switchover process
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered.
SQL> select status from V$instance; select status from V$instance * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 1266 Session ID: 29 Serial number: 101
Start the DB with Nomount Mode
SQL> startup nomount; ORACLE instance started. Total System Global Area 1904054272 bytes Fixed Size 2254384 bytes Variable Size 1241516496 bytes Database Buffers 654311424 bytes Redo Buffers 5971968 bytes
Alter the database in mount mode as standby
SQL> alter database mount standby database; Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered
On Standby DB
SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ----------------------------------------------------------------- PHYSICAL STANDBY READ ONLY
SQL> alter database commit to switchover to primary with session shutdown; Database altered.
Check the status of DB
SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY MOUNTED
Open the DB
SQL> alter database open; Database altered.
SQL> select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance; NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS ----------------------------------------------------------------------------------------------------------------------------------- RAC stby_rac READ WRITE PRIMARY TO STANDBY
Verify the Sync of Primary & Standby DB
Perform some switch logfile on converted on New Primary DB
SQL> alter system switch logfile; System altered. SQL> / System altered.
Therefore the DB is sync with Primary & Standby DB
Note:
-
If you face any issue related to the gap after switchover, try to check the service name, password file, listener status, archivelog location etc.
-
If you bring the Node 2 of Primary DB – The logs will not be apply as the standby will be sync with only 1 node of Primary DB.