Implementing Oracle Data Guard Broker 19c in an Oracle Standby RAC Database

Before I start, I would like to do a disclaimer and say that this is a procedure that might help you. However, I do not recommend you test it in a production environment before reading Oracle documentation and notes to understand the concepts that I will write here.

Oracle Data Guard Broker is a utility that can help you manage your Oracle Data Guard. Among many benefits of using this utility, I highlight that while using it, it will not need manual intervention to recover the databases or eventually a switchover in case the primary database becomes unavailable. Therefore, I will be explaining how you can implement this utility in your configured Data Guard. The first thing that has to be done is to add a static entry for the Data Guard Broker in both Standby and Primary database as below:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TECHP_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/19.3.0/db)
      (SID_NAME = techp)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TECHDR_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/19.3.0/db)
      (SID_NAME = techdr)
      (ENVS="TNS_ADMIN=/u01/app/oracle/19.3.0/db/network/admin")
    )
  )

Perform the restart of the listener and proceed with the next step that it will be to change parameters in both Primary and Standby database:

PRIMARY:

ALTER SYSTEM SET dg_broker_start=false SCOPE=BOTH sid='*';
SQL>alter system set dg_broker_config_file1='+DATA/TECHP/DGBROKER/dr1techp.dat' SCOPE=BOTH sid='*';

System altered.

SQL>ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/TECHP/DGBROKER/dr2techp.dat' SCOPE=BOTH sid='*';

System altered.

SQL>ALTER SYSTEM SET dg_broker_start=true SCOPE=BOTH sid='*';

System altered.

#STANDBY:

ALTER SYSTEM SET dg_broker_start=false SCOPE=BOTH sid='*';
SQL>alter system set dg_broker_config_file1='+DATA/TECHDR/DGBROKER/dr1techdr.dat' SCOPE=BOTH sid='*';

System altered.

SQL>ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/TECHDR/DGBROKER/dr2techdr.dat' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET dg_broker_start=true SCOPE=BOTH sid='*';

System altered.

This previous step performs the configuration of the location of the files for the DATA Guard Broker and enables the possibility for the Data Guard broker (DMON) process to start.

Besides the parameter above, another parameter must be changed to the Oracle Data Guard works adequately. And it is to disable the log_archive_dest_2, which is used to configure SERVICES to help the Oracle Data Guard send archive log files to the Standby database from the Primary. However, it is great to remember that while using Data Guard Broker, the utility is  responsible for that and if this parameter is not configured to null the following error will be returned while adding the Standby database in the configuration:

DGMGRL>ADD DATABASE 'TECHDR' AS CONNECT IDENTIFIER IS TECHDR MAINTAINED AS PHYSICAL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

After the configuration on the parameters, it needs to add the SERVICE names in the tnsnames.ora located in $ORACLE_HOME/network/admin( this is the default, if not check the TNS_ADMIN variable in the system) that it will be used in the Data Guard Broker configuration, and this case, it will be as below:

TECHP for the database TECHP, the primary database
TECHDR for the database TECHDR , the standby database

TECHP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scanclusterdb)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = scanclusterdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = techp.world)
    )
  )
TECHDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scanclusterdb)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = scanclusterdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = techdr.world)
    )
  )

I opted to have the same name for the SCAN for both environments under different IPs. Therefore, after the tnsnames is set up correctly, it is time now to configure the Data Guard Broker, and the steps to do it are as it is below:

1-Create the configuration using the Primary Service

dgmgrl
DGMGRL>connect sys/<syspasswordhere>@TECHP
DGMGRL>CREATE CONFIGURATION 'BROKERTECHCONFIG' AS PRIMARY DATABASE IS 'TECHP' CONNECT IDENTIFIER IS TECHP;

2- Adding the Standby Service in the configuration

DGMGRL>ADD DATABASE TECHDR AS CONNECT IDENTIFIER IS TECHDR MAINTAINED AS PHYSICAL;
Database "TECHDR" added

3-Enabling the configuration

DGMGRL>enable configuration;

4-Enabling the configuration for the Primary database

DGMGRL>enable database 'TECHP';

5-Enabling the configuration for the Standby database

DGMGRL>enable database 'TECHDR';

6-Checking the configuration

DGMGRL>show configuration;
Configuration - BROKERTECHCONFIG
 Protection Mode: MaxPerformance
Members:
 TECHP - Primary database
 TECHDR   - Physical standby database
Fast-Start Failover:  Disabled
Configuration Status:SUCCESS   (status updated 27 seconds ago)

So that is it! I hope this post helps you!

brunors

Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)