Prerequisite
you should have a 2 node RAC primary and 2 node RAC standby database with DG broker configuration
follow this post to setup DG broker
https://dbatracker.com/2020/05/30/dataguard-broker-setup-for-2-node-rac-prod-and-2-node-dr/
check configurationof primary nodes and listener status
[oracle@ol6-112-rac1 trace]$ srvctl config database -d TROY -a
Database unique name: TROY
Database name: TROY
Oracle home: /u01/app/oracle/product/11.2.0.3/db_1
Oracle user: oracle
Spfile: +DATA/TROY/spfileTROY.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TROY
Database instances: TROY1,TROY2
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managedmake sure listener is aware if %_DGMGRL services on both primary nodes
[oracle@ol6-112-rac1 trace]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2020 17:55:53
Copyright (c) 1991, 2011, 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.3.0 - Production
Start Date 29-MAY-2020 15:48:49
Uptime 0 days 2 hr. 7 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0.3/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol6-112-rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.101)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.103)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "TROY.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
Service "TROYXDB.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
Service "TROY_DGB.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
Service "TROY_DGMGRL.localdomain" has 1 instance(s).
Instance "TROY1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol6-112-rac2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2020 17:59:31
Copyright (c) 1991, 2011, 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.3.0 - Production
Start Date 29-MAY-2020 15:57:20
Uptime 0 days 2 hr. 2 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0.3/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol6-112-rac2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.102)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.104)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "TROY.localdomain" has 1 instance(s).
Instance "TROY2", status READY, has 1 handler(s) for this service...
Service "TROYXDB.localdomain" has 1 instance(s).
Instance "TROY2", status READY, has 1 handler(s) for this service...
Service "TROY_DGB.localdomain" has 1 instance(s).
Instance "TROY2", status READY, has 1 handler(s) for this service...
Service "TROY_DGMGRL.localdomain" has 1 instance(s).
Instance "TROY2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
check configurationof standby nodes and listener status
[oracle@ol6-112-dr-rac1 trace]$ srvctl config database -d TROY_DR -a
Database unique name: TROY_DR
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.3/db_1
Oracle user: oracle
Spfile:
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY==>
Management policy: AUTOMATIC
Server pools: TROY_DR
Database instances: TROY1,TROY2
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managedsomehow database role is set PRIMARY so modify it to physical_standby before proceeding
[oracle@ol6-112-dr-rac1 trace]$ srvctl modify database -d TROY_DR -r PHYSICAL_STANDBY
[oracle@ol6-112-dr-rac1 trace]$ srvctl config database -d TROY_DR -a
Database unique name: TROY_DR
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.3/db_1
Oracle user: oracle
Spfile:
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY===>
Management policy: AUTOMATIC
Server pools: TROY_DR
Database instances: TROY1,TROY2
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managedmake sure listener is aware if %_DGMGRL services on both standby nodes
[oracle@ol6-112-dr-rac1 trace]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2020 18:00:14
Copyright (c) 1991, 2011, 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.3.0 - Production
Start Date 29-MAY-2020 15:50:20
Uptime 0 days 2 hr. 9 min. 56 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0.3/grid1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol6-112-dr-rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.131)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.133)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "TROYXDB.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
Service "TROY_DR.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
Service "TROY_DR_DGB.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
Service "TROY_DR_DGMGRL.localdomain" has 1 instance(s).
Instance "TROY1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol6-112-dr-rac2 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2020 18:00:19
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 29-MAY-2020 16:02:16
Uptime 0 days 1 hr. 58 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0.3/grid1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol6-112-dr-rac2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.132)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.134)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "TROYXDB.localdomain" has 1 instance(s).
Instance "TROY2", status READY, has 1 handler(s) for this service...
Service "TROY_DR.localdomain" has 1 instance(s).
Instance "TROY2", status READY, has 1 handler(s) for this service...
Service "TROY_DR_DGB.localdomain" has 1 instance(s).
Instance "TROY2", status READY, has 1 handler(s) for this service...
Service "TROY_DR_DGMGRL.localdomain" has 1 instance(s).
Instance "TROY2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
verify primary switchover_status
TROY1@ol6-112-rac1 > select name, db_unique_name, database_role, switchover_status, open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
--------- ------------------------------ ---------------- -------------------- --------------------
TROY TROY PRIMARY TO STANDBY READ WRITE
verify StaticConnectIdentifier property
in my case SERVICE_NAME=TROY_DGMGRL.localdomain for prod so make sure listener on prod (both nodes) is also aware of TROY_DGMGRL.localdomain service.
domain name (localdomain) is very important, if you use it then make sure it is used every where (listener static entry, tnsnames.ora, StaticConnectIdentifier
DGMGRL> show instance verbose 'TROY1' on database 'TROY';
Instance 'TROY1' of database 'TROY'
Host Name: ol6-112-rac1.localdomain
PFILE:
Properties:
SidName = 'TROY1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.103)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TROY_DGMGRL.localdomain)(INSTANCE_NAME=TROY1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'TROY_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Instance Status:
SUCCESS
DGMGRL> show instance verbose 'TROY2' on database 'TROY';
Instance 'TROY2' of database 'TROY'
Host Name: ol6-112-rac2.localdomain
PFILE:
Properties:
SidName = 'TROY2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.104)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TROY_DGMGRL.localdomain)(INSTANCE_NAME=TROY2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'TROY_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Instance Status:
SUCCESSsimilary for standby
DGMGRL> show instance verbose 'TROY1' on database 'TROY_DR';
Instance 'TROY1' of database 'TROY_DR'
Host Name: ol6-112-dr-rac1.localdomain
PFILE:
Properties:
SidName = 'TROY1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.133)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TROY_DR_DGMGRL.localdomain)(INSTANCE_NAME=TROY1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'TROY_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Instance Status:
SUCCESS
DGMGRL> show instance verbose 'TROY2' on database 'TROY_DR';
Instance 'TROY2' of database 'TROY_DR'
Host Name: ol6-112-dr-rac2.localdomain
PFILE:
Properties:
SidName = 'TROY2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.134)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TROY_DR_DGMGRL.localdomain)(INSTANCE_NAME=TROY2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'TROY_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Instance Status:
SUCCESScheck gap on standby before proceeding to few switch logfiles on prod
TROY1@ol6-112-dr-rac1 > select THREAD#,max(sequence#) from v$archived_log group by THREAD#;
select THREAD#,max(sequence#) from v$archived_log where applied='YES' group by THREAD#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 73
2 54
TROY1@ol6-112-dr-rac1 >
THREAD# MAX(SEQUENCE#)
---------- --------------
1 72
2 54
TROY1@ol6-112-dr-rac1 > select *from v$dataguard_stats;
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 05/29/2020 18:21:55 05/29/2020 18:21:53
apply lag +00 00:00:00 day(2) to second(0) interval 05/29/2020 18:21:55 05/29/2020 18:21:53
apply finish time +00 00:00:00.000 day(2) to second(3) interval 05/29/2020 18:21:55
estimated startup time 109 second 05/29/2020 18:21:55
TROY1@ol6-112-dr-rac1 >
swithcover
[oracle@ol6-112-rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@TROY
Connected.
DGMGRL> show configuration;
Configuration - broker_config
Protection Mode: MaxPerformance
Databases:
TROY - Primary database
TROY_DR - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESSDGMGRL> switchover to 'TROY_DR';
Performing switchover NOW, please wait...
New primary database "TROY_DR" is opening...
Operation requires shutdown of instance "TROY1" on database "TROY"
Shutting down instance "TROY1"...
ORACLE instance shut down.
Operation requires startup of instance "TROY1" on database "TROY"
Starting instance "TROY1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "TROY_DR"
If you plan to have switchover for a long time then make sure you update the open mode of new primary and standby using
srvctl modify database -d TROY -s 'READ ONLY'srvctl modify database -d TROY_DR -s 'OPEN'switch back
DGMGRL> switchover to 'TROY';
Performing switchover NOW, please wait...
Operation requires a connection to instance "TROY1" on database "TROY"
Connecting to instance "TROY1"...
Connected.
New primary database "TROY" is opening...
Operation requires shutdown of instance "TROY1" on database "TROY_DR"
Shutting down instance "TROY1"...
ORACLE instance shut down.
Operation requires startup of instance "TROY1" on database "TROY_DR"
Starting instance "TROY1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "TROY"
