Step by Step Data Guard Broker Configuration in Oracle 19c

If you want to manage an Oracle data guard easily, then you should use a data guard broker.

The Oracle Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations.

In my configuration, I have already created a Data Guard. You can check this post to see how I created it.

BCN is the primary database and PARIS is the standby database.

In the commands below you can remove the sid=’*’ if you don’t have a RAC database and you should be good to go.

Table of Contents

1. Reset LOG_ARCHIVE_DEST_2

First you need to clear the value for LOG_ARCHIVE_DEST_2 on the primary and on the standby database.

show parameter LOG_ARCHIVE_DEST_2

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

Once done you can continue.

2. Set dg_broker_config_file

Set the parameter dg_broker_config_file1 and dg_broker_config_file2 on all instances of primary and standby.

The default location of the broker configuration file is $ORACLE_HOME/dbs or $ORACLE_HOME/database.

If you want broker configuration files in a non default location, set these parameters.

In case of RAC database, set broker configuration file location to shared location and same value on all the instances.

If you don’t have a RAC database no need to use the sid=’*’.

ALTER SYSTEM SET dg_broker_config_file1 = '<path/file_name>.dat' scope=both sid='*'; 
ALTER SYSTEM SET dg_broker_config_file2 = '<path/file_name>.dat' scope=both sid='*';

OR in case of ASM file system use:

ALTER SYSTEM SET dg_broker_config_file1 = '<+disk group/file_name>.dat' scope=both sid='*'; 
ALTER SYSTEM SET dg_broker_config_file2 = '<+disk group/file_name>.dat' scope=both sid='*';

If you want the broker configuration files in the default location, then you can ignore this step.

3. Enable the broker

Enable the broker on both primary and standby databases.

If you don’t have a RAC database, no need to use sid=’*’.

ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';

4. Create the configuration

Connect to DGMGRL on primary:

$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Oct 19 17:47:37 2021
Version 19.8.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/password;
Connected.

Create configuration on primary:

DGMGRL> CREATE CONFIGURATION 'MYCONF' AS PRIMARY DATABASE IS 'BCN' CONNECT IDENTIFIER IS BCN;
Configuration "MYCONF" created with primary database "BCN"

Add the standby to the configuration:

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

5. Configure the listeners

Configure the listener on the primary server.

Make sure to add the entrance for the broker like that

vi $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv1.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=BCN.localdomain)
          (SID_NAME=BCN)
          (ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
        )
        (SID_DESC=
          (GLOBAL_DBNAME=BCN_DGMGRL.localdomain)
          (SID_NAME=BCN)
          (ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
        )
      )

And reload the listener

lsnrctl reload

Configure the listener on the standby server.

Make sure to add the entrance for _DGMGRL.

vi $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=PARIS.localdomain)
          (SID_NAME=PARIS)
          (ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
        )
        (SID_DESC=
          (GLOBAL_DBNAME=PARIS_DGMGRL.localdomain)
          (SID_NAME=PARIS)
          (ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1)
        )
      )

And reload the standby listener too

lsnrctl reload

6. Enable the configuration

Now you just need to enable the configuration you have just created.

DGMGRL> ENABLE CONFIGURATION;
Enabled.

Use the SHOW command to verify that the configuration and its databases have been successfully enabled and brought online:

DGMGRL> SHOW CONFIGURATION;

Configuration
Name: MYCONF
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED

Databases:
BCN - Primary database
PARIS - Physical standby database

Current status for "MYCONF"
SUCCESS