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
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
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 ~]$ dgmgrlDGMGRL for Linux: Release 19.0.0.0.0 – Production on Mon Jun 24 10:26:15 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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>