Description:-
In the previous article, we have configured dataguard broker for Oracle RAC database with physical standby in 11gR2. Here, we will continue with the switchover process. Below is the environment.
Steps Involved in Switchover using dataguard broker:-
A) Check Database Configuration before Switchover
B) Performing Switchover
C) Check database configuration after Switchover
A) Check Database Configuration before Switchover:-
Configuration check in Cluster:-
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
Primary Node 1:-
$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:09:45 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 24-AUG-2018 02:18:33 Uptime 0 days 22 hr. 51 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac101/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.211)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.214)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "ORCLXDB" has 1 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC1" has 1 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC1_DGB" has 1 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s). Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Primary Node 2:-
$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:02 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 24-AUG-2018 02:18:45 Uptime 0 days 22 hr. 52 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac102/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.212)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.215)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "ORCLXDB" has 1 instance(s). Instance "ORCL2", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC1" has 1 instance(s). Instance "ORCL2", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC1_DGB" has 1 instance(s). Instance "ORCL2", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s). Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Standby Node 1:-
$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:29 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 24-AUG-2018 02:18:46 Uptime 0 days 22 hr. 52 min. 43 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac201/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.221)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.224)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "ORCLXDB" has 1 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC2" has 1 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC2_DGB" has 1 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s). Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Standby Node 2:-
lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:12:10 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 24-AUG-2018 02:18:55 Uptime 0 days 22 hr. 53 min. 14 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac202/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.222)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.225)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC2" has 1 instance(s). Instance "ORCL2", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC2_DGB" has 1 instance(s). Instance "ORCL2", status READY, has 1 handler(s) for this service... Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s). Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Configuration check in database level (Primary):-
$ sqlplus / as sysdba SQL> select name, db_unique_name, database_role, switchover_status, open_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ------ -------------- ------------- ----------------- --------- ORCL ORCL_PRODRAC1 PRIMARY TO STANDBY READ WRITE SQL> @stby_gap
DG Broker configuration:-
$ dgmgrl DGMGRL> connect sys/manager@ORCL_PRODRAC1 DGMGRL> show configuration verbose; Configuration - ORACLEDBWR Protection Mode: MaxPerformance Databases: ORCL_PRODRAC1 - Primary database ORCL_PRODRAC2 - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Primary database setting:-
DGMGRL> show database verbose 'ORCL_PRODRAC1';
Database - ORCL_PRODRAC1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL1
ORCL2
Properties:
DGConnectIdentifier = 'orcl_prodrac1'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC1';
Instance 'ORCL1' of database 'ORCL_PRODRAC1'
Host Name: prodrac101.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.214)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Instance Status:
SUCCESS
DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC1';
Instance 'ORCL2' of database 'ORCL_PRODRAC1'
Host Name: prodrac102.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.215)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Instance Status:
SUCCESS
Standby database setting:-
DGMGRL> show database verbose 'ORCL_PRODRAC2'; Database - ORCL_PRODRAC2 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 187.00 KByte/s Real Time Query: ON Instance(s): ORCL1 ORCL2 Properties: DGConnectIdentifier = 'orcl_prodrac2' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '30' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC2'; Instance 'ORCL1' of database 'ORCL_PRODRAC2' Host Name: prodrac201.oracledbwr.com PFILE: Properties: SidName = 'ORCL1' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.224)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))' StandbyArchiveLocation = '+DBWR_FRA' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'ORCL_%t_%s_%r.arc' TopWaitEvents = '(monitor)' Instance Status: SUCCESS DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC2'; Instance 'ORCL2' of database 'ORCL_PRODRAC2' Host Name: prodrac202.oracledbwr.com PFILE: Properties: SidName = 'ORCL2' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.225)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))' StandbyArchiveLocation = '+DBWR_FRA' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'ORCL_%t_%s_%r.arc' TopWaitEvents = '(monitor)' Instance Status: SUCCESS
B) Performing Switchover:-
It is ready for the switchover. Note that we do not need to shutdown any instances
DGMGRL> switchover to 'ORCL_PRODRAC2'; Performing switchover NOW, please wait... Operation requires a connection to instance "ORCL1" on database "ORCL_PRODRAC2" Connecting to instance "ORCL1"... Connected. New primary database "ORCL_PRODRAC2" is opening... Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1" Starting instance "ORCL1"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "ORCL_PRODRAC2"
C) Check database configuration after Switchover:-
Old Primary:-
$ srvctl status database -d ORCL_PRODRAC1 -v
Instance ORCL1 is running on node prodrac101. Instance status: Open.
Instance ORCL2 is running on node prodrac102. Instance status: Mounted (Closed).
$ 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: 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
New Primary:-
$ srvctl status database -d ORCL_PRODRAC2 -v
Instance ORCL1 is running on node prodrac201. Instance status: Open.
Instance ORCL2 is running on node prodrac202. Instance status: Open.
$ 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: 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
In the above configuration we can see that database role for both the databases has been changed automatically in cluster, whereas in the manual switchover this has to be done manually. Also, we can notice that the “Start options” is open and read only in old primary and new primary respectively. It is recommended to change this in the cluster manually.
Old Primary:-
$ srvctl modify database -d ORCL_PRODRAC1 -s 'READ ONLY' $ 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
New Primary:-
$ srvctl modify database -d ORCL_PRODRAC2 -s 'OPEN' $ 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
Let us verify in the database for archive gap.
$ sqlplus / as sysdba
SQL> @stby_gap
We can also verify the log shipping from the new primary to old primary by switch log in new primary and tail the alert log in old primary.
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
