Perform the Switchover
Posted by Mir Sayeed Hassan on October 2nd, 2017
Perform the Switchover in our Test DB ENV
Primary database – IP (10.0.0.1)
=====================================
Before Switchover from primary to standby
===================================== SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PR READ WRITE PRIMARY
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- pr
SQL> select process from v$managed_standby; PROCESS --------- ARCH LNS
[oracle@pr ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL>; connect sys/xxx Connected
DGMGRL> show configuration Configuration - PRDG Protection Mode: MaxPerformance Databases: pr - Primary database std - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
=======================================
Now convert the Primary Database to Standby
=======================================
[oracle@pr ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/xxx Connected
DGMGRL> switchover to std Performing switchover NOW, please wait... Operation requires a connection to instance "std" on database "std" Connecting to instance "std"... Connected.
New primary database “std” is opening…
Operation requires startup of instance “pr” on database “pr”
Starting instance “pr”…
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE. — This we can resolve & start the primary db as mount automatically (Working to resolve)
Please complete the following steps to finish switchover:
start up and mount instance “pr” of database “pr”
Note: Other mrp process will be started automatically
======================================
After Converting the Primary database to Standby – Verify Sync, config
======================================
In Primary Database converted as standby database – IP (10.0.0.1)
SQL> startup mount ORACLE instance started. Total System Global Area 1503199232 bytes Fixed Size 2253424 bytes Variable Size 1392512400 bytes Database Buffers 100663296 bytes Redo Buffers 7770112 bytes Database mounted.
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PR MOUNTED PHYSICAL STANDBY
SQL> select instance_name from v$instance; INSTANCE_NAME --------------- pr
SQL> select process from v$managed_standby; PROCESS --------- ARCH ARCH RFS RFS MRP0
=============================
Now – Verify the Standby database
=============================
Here Standby Database converted as primary database – Successfully Perform the Switchover & Open the database as “OPEN” Mode
SQL> select status from v$instance; STATUS ----------- OPEN
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PR READ WRITE PRIMARY
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- std
SQL> select process from v$managed_standby; PROCESS --------- ARCH LGWR
SQL> alter system switch logfile; System altered.
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 1245 1243 30-MAY-17 2
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 1245 1244 30-MAY-17 1
Hence the MRP Process is started & shipped the archivelog etc – Therefore the Primary DB as Standby DB & Standby DB as Primary DB
====================================
Before Switchover from Standby to Primary – Perform the Vice Versa (Standby DB as Standby & Primary DB as Primary)
====================================
In Standby DB
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE -------- -------------------- ---------------- PR READ WRITE PRIMARY
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- std
SQL> select process from v$managed_standby; PROCESS --------- ARCH LGWR
[oracle@std ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/sys Connected.
DGMGRL> show configuration Configuration - PRDG Protection Mode: MaxPerformance Databases: std - Primary database pr - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
======================================================================
Now – Revert backup the DB as before – Primary to Primary & Standby to Standby
======================================================================
[oracle@std ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/xxx Connected.
DGMGRL> switchover to pr Performing switchover NOW, please wait... Operation requires a connection to instance "pr" on database "pr" Connecting to instance "pr"... Connected.
New primary database “pr” is opening…
Operation requires startup of instance “std” on database “std”
Starting instance “std”…
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
Due to the above error – Just mount the Standby DB – Other mrp process will be started automatically
SQL> startup mount ORACLE instance started. Total System Global Area 1503199232 bytes Fixed Size 2253424 bytes Variable Size 1392512400 bytes Database Buffers 100663296 bytes Redo Buffers 7770112 bytes Database mounted.
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PR MOUNTED PHYSICAL STANDBY
SQL> select instance_name from v$instance; INSTANCE_NAME --------------- std
SQL> select process from v$managed_standby; PROCESS --------- ARCH RFS MRP0
DGMGRL> show configuration Configuration - PRDG Protection Mode: MaxPerformance Databases: pr - Primary database std - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Switch some archive from the primary database & verify
SQL> alter system switch logfile; System altered.
SQL> @/home/oracle/scripts/pritest.sql; THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_ ARC_DIFF ---------- ---------- ----------- --------- ---------- 1 1253 1246 30-MAY-17 7
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@pr ~]$ !sq SQL> @/home/oracle/scripts/pritest.sql; THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_ ARC_DIFF ---------- ---------- ----------- --------- ---------- 1 1253 1252 30-MAY-17 1
Hence the MRP Process is started & shipped the archivelog etc – Therefore revert back to the original stage Primary DB as Primary DB & Standby DB as Standby DB