Step by Step Configuration Of Data Guard Broker For Rac Database

Description:-

We have already configured Oracle RAC database with physical standby in 11gR2 here. Now, we are going to configure dataguard broker for the same environment.

Below is the High level steps involved in the configuring dataguard broker:-

1) Listener Entry for dataguard broker
2) Enable DG Broker Parameters
3) Create configuration for dataguard broker using dgmgrl
4) Check the configuration

Environment Details:-

Primary Configuration:-

$  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 Configuration:-

$ 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

1) Listener Entry for dataguard broker:-

Make the below entry in the listener.ora file which already exist in the GRID_HOME:-

Primary Node 1:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC1_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL1)
)
)
Primary Node 2:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC1_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL2)
)
)
Standby Node 1:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC2_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL1)
)
)
Standby Node 2:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC2_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL2)
)
)

Stop and start the listener in all the Nodes of both primary & standby:-

$ lsnrctl stop
$ lsnrctl start

Make sure the below entry is available in the tnsnames.ora file available in  ORACLE_HOME of all the nodes.

ORCL_PRODRAC1 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn101.oracledbwr.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
)
)

ORCL_PRODVIP101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip101.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL1)
)
)

ORCL_PRODVIP102 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip102.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL2)
)
)

ORCL_PRODRAC2 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn201.oracledbwr.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(UR = A)
)
)

ORCL_PRODRAC201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodrac201.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)
)
)


ORCL_PRODVIP201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip201.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)
)
)

ORCL_PRODVIP202 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip202.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL2)
(UR = A)
)
)

2) Enable DG Broker Parameters:-

Create the required directories in the ASM diskgroup for the dataguard configuration file and start the DMON process by enabling “dg_broker_start” parameter.

Primary:-

ASMCMD> mkdir +DBWR_DATA/ORCL_PRODRAC1/DATAGUARDCONFIG
ASMCMD> mkdir +DBWR_FRA/ORCL_PRODRAC1/DATAGUARDCONFIG

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DBWR_DATA/ORCL_PRODRAC1/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DBWR_FRA/ORCL_PRODRAC1/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';

System altered.

Standby:-

ASMCMD> mkdir +DBWR_DATA/ORCL_PRODRAC2/DATAGUARDCONFIG
ASMCMD> mkdir +DBWR_FRA/ORCL_PRODRAC2/DATAGUARDCONFIG

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DBWR_DATA/ORCL_PRODRAC2/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DBWR_FRA/ORCL_PRODRAC2/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';

System altered.

3) Create configuration for dataguard broker using dgmgrl:-

$ dgmgrl

DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration;
DGMGRL> create configuration 'ORACLEDBWR' as primary database is 'ORCL_PRODRAC1' connect identifier is ORCL_PRODRAC1;

Configuration "ORACLEDBWR" created with primary database "ORCL_PRODRAC1"

DGMGRL> show configuration;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> ADD DATABASE 'ORCL_PRODRAC2' AS CONNECT IDENTIFIER IS ORCL_PRODRAC2;

Database "ORCL_PRODRAC2" added

DGMGRL> show configuration;

Configuration - ORACLEDBWR
Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database
ORCL_PRODRAC2 - Physical standby database

Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> enable configuration;

4) Check the configuration:-

DGMGRL> show configuration;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database
ORCL_PRODRAC2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

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 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 1 second ago)
Apply Rate: 242.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

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 *