How To Setup Dataguard Broker Configuration (DG Broker) In 19c

Dataguard Broker Configuration

Description :

Dataguard broker is used to automate monitoring and controlling standby setups.

It is very much useful, when the organization has multiple standby sites.

Broker keeps its configuration details in flat file. The files are stored at each database nodes in the Data Guard configuration.Additionally two copies of the configuration files are always stored on each database for redundancy.
Below parameters control where the configuration files will be stored.
DG_BROKER_CONFIG_FILE1 & DG_BROKER_CONFIG_FILE2

Step by Step to configure Oracle 19c Data Guard Physical Standby

DETAILS	SOURCE	TARGET
IP_ADDRESS	192.168.1.44	192.168.1.37
SERVER_NAME	PRIMARY	STANDBY
DB_VERSION	19.3.0.0	19.3.0.0
DB_NAME	PROD	STANDBY

Primary Databse = PROD
Standby Database = STANDBY

Check the Data Guard Broker is enabled

PRIMARY

SQL> sho parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE
SQL>

SQL> alter system set dg_broker_start=true;

System altered.

SQL> sho parameter dg_brok

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr1STANDBY.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/dr2STANDBY.dat
dg_broker_start boolean FALSE

STANDBY :

SQL> sho parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL> sho parameter dg_brok

LISTENER FOR DG_BROKER :

LISTENER44 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.44)(PORT = 1544))
)
)
SID_LIST_LISTENER44 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(INSTANCE_NAME = prod)
(SID_NAME = prod)
)
)

TNS ENTRY FOT DG_BROKER :

prodTNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.44)(PORT = 1544))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod_dgmgrl)
)
)
STANDBYTNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1544))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby_dgmgrl)
)
)

REGISTER THE LOCAL LISTENER IN BOTH NODES :

PRIMARY :

SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.44)(PORT = 1544))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod_dgmgrl)))’;

System altered.

STANDBY :

SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1544))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby_dgmgrl)))’;

System altered.

SET THE SERVICE ATTRIBUTE BE ‘N’ VALUE otherwise the error will occur:

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

SQL> alter system set LOG_ARCHIVE_DEST_2=” scope=both;

System altered.

Configure the Data Guard Configuration

[oracle@trichy ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Mon Jun 24 10:26:15 2019
Version 19.3.0.0.0

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/********
Connected to “PROD”
Connected as SYSDBA.
DGMGRL>

CREATE A CONFIGURATION :

DGMGRL> create configuration ‘sathish’ as primary database is ‘PROD’ connect identifier is PRODTNS;
Configuration “sathish” created with primary database “PROD”
DGMGRL>

ADD A STANDBY DATABASE TO CONFIGURATION :

DGMGRL> add database ‘STANDBY’ as connect identifier is STANDBYTNS;
Database “STANDBY” added
DGMGRL>
DGMGRL> show configuration

Configuration – sathish

Protection Mode: MaxPerformance
Members:
PROD – Primary database
STANDBY – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
DISABLED

ENABLE THE PARAMETERS ON BOTH NODES :

PRIMARY :

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=STANDBYTNS LGWR ASYNC NOAFFIRM VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY’;

System altered.

STANDBY :

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=PRODTNS LGWR ASYNC NOAFFIRM VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD’;

System altered.

ENABLE CONFIGURATION :

DGMGRL> enable configuration
Enabled.

VERIFY THE STATUS :
DGMGRL> show configuration

Configuration – sathish

Protection Mode: MaxPerformance
Members:
PROD – Primary database
STANDBY – Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 42 seconds ago)

DGMGRL>

Sathish Lakshmanan