Prerequisite
Make sure you complete all steps as mentioned in this document
https://dbatracker.com/2020/05/28/setup-2-node-rac-dataguard-for-2-node-rac-primary/
once it is done you will have 2 node RAC at both primary and standbu site up and running and in sync.
Tnsnames.ora
make sure these tns entries exists on all 4 nodes (2 primary and 2 standby) DATABASE oracle home (not GRID oracle home).it is also mentioned in above post for setting up dataguard
TROY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TROY.localdomain)
)
)
TROY_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DR.localdomain)
)
)
#####this one for transfer of archives from standby to PROD - - used in standby dest_2
TROY1_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-rac1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY.localdomain)
)
)
#####this one for transfer of archives from prod to standby - used in prod dest_2
TROY1_DR_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DR.localdomain)
)
)
####these are in case PROD node 1 or DR node 1 is down
#### we will use them to transfer archivelogs
######from standby to PROD
TROY2_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-rac2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY.localdomain)
)
)
#####from prod to standby
TROY2_DR_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DR.localdomain)
)
)
######## this one is for static entry for duplicate
TROY_DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac1)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DUP)
)
)
add static entry in GRID_HOME listener.ora
oel6-112-rac1
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TROY_DGMGRL.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
(SID_NAME = TROY1)
)
)oel6-112-rac2
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TROY_DGMGRL.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
(SID_NAME = TROY2)
)
)oel6-112-dr-rac1
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TROY_DR_DGMGRL.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
(SID_NAME = TROY1)
)
)oel6-112-dr-rac2
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TROY_DR_DGMGRL.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
(SID_NAME = TROY2)
)
)reboot the listener and verify that now lsitener is aware of these %_DGMGRL services on each node
[oracle@ol6-112-rac1 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2020 16:44:09
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[oracle@ol6-112-rac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-MAY-2020 00:21:34
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 15:48:49
Uptime 0 days 8 hr. 32 min. 45 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 "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 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2020 16:47:49
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[oracle@ol6-112-rac2 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2020 16:50:11
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 15:57:20
Uptime 0 days 0 hr. 52 min. 53 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_DGMGRL" has 1 instance(s).
Instance "TROY2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully[oracle@ol6-112-dr-rac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-MAY-2020 00:22:41
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 15:50:20
Uptime 0 days 8 hr. 32 min. 23 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 30-MAY-2020 00:23:17
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 16:02:16
Uptime 0 days 8 hr. 21 min. 3 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
[oracle@ol6-112-dr-rac2 admin]$
Enable DG Broker Parameters on primary node 1- ol6-112-rac1
ASMCMD> mkdir +DATA/TROY/DATAGUARDCONFIG
ASMCMD> pwd
+DATA/TROY
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
DATAGUARDCONFIG/
ONLINELOG/
TEMPFILE/
spfiletroy.ora
ASMCMD>TROY1@ol6-112-rac1 > ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/TROY/DATAGUARDCONFIG/dgbrk_config01.ora' SCOPE=BOTH sid='*';
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/TROY/DATAGUARDCONFIG/dgbrk_config02.ora' SCOPE=BOTH sid='*';
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';
System altered.Enable DG Broker Parameters on standby node 1 – ol6-112-dr-rac1
ASMCMD> ASMCMD> cd +DATA/TROY_DR/
pwd
ASMCMD> +DATA/TROY_DR
ASMCMD> mkdir +DATA/TROY_DR/DATAGUARDCONFIG
ASMCMD> ls
ARCHIVELOG/
DATAGUARDCONFIG/
PARAMETERFILE/
TEMPFILE/
TROY1@ol6-112-dr-rac1 > ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/TROY_DR/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';
System altered.
TROY1@ol6-112-dr-rac1 > ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/TROY_DR/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';
System altered.
TROY1@ol6-112-dr-rac1 > ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';
System altered.
Create configuration for dataguard broker using dgmgrl
[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;
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration 'broker_config' as primary database is 'TROY' connect identifier is TROY;
Configuration "broker_config" created with primary database "TROY"
DGMGRL> show configuration;
Configuration - broker_config
Protection Mode: MaxPerformance
Databases:
TROY - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> ADD DATABASE 'TROY_DR' AS CONNECT IDENTIFIER IS TROY_DR;
Database "TROY_DR" added
DGMGRL> show configuration;
Configuration - broker_config
Protection Mode: MaxPerformance
Databases:
TROY - Primary database
TROY_DR - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
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> show database verbose 'TROY';
Database - TROY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
TROY1
TROY2
Properties:
DGConnectIdentifier = 'troy'
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)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL> show database verbose 'TROY_DR';
Database - TROY_DR
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
TROY1 (apply instance)
TROY2
Properties:
DGConnectIdentifier = 'troy_dr'
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)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Database Status:
SUCCESS
DBATracker 