Oracle Rac Database Switchover Using Dataguard Broker

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

Leave a Reply

Your email address will not be published. Required fields are marked *