How to setup Data Guard Broker Configuration

Setting up Data Guard Broker Configuration involves the following steps. Before DG Broker setup you must have a Data Guard setup

Read How to switchover in Data Guard

Step 1. Current status of Broker

Check the current status of DG Broker on both sides:

Primary Side:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
dg_broker_start                      boolean     FALSE

Standby Side:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
dg_broker_start                      boolean     FALSE

Step 2: Very the Archive Gap

Before configuring of DG broker, you must verify the archive gap between primary and standby.

Status on Primary

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            15

Status on Standby

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            15

In my case, both servers are in sync.

Step 3: Disable log_archvie_dest_2 on both sides

To avoid "ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set" and perform the below steps.

Primary side Action

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=digidr async valid_for
                                                 =(online_logfiles,primary_role
                                                 ) db_unique_name=digidr

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

SQL>
SQL>
SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string

Standby side Action

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=digidr async valid_for
                                                 =(online_logfiles,primary_role
                                                 ) db_unique_name=digidr


SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string

Step 4: Enable the Data Guard Broker

To enable the Data Guard Broker, set the parameter DG_BROKER_START to TRUE in both the primary and standby database initialization parameter file.

Primary:

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
dg_broker_start                      boolean     TRUE

SQL> show parameter dg_broker_config

Standby:

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
dg_broker_start                      boolean     TRUE

SQL> show parameter dg_broker_config

Step 5: Configure Listener on both sides

Add the below entries on both sides listener.ora files.

Primary listener.ora

(SID_DESC =
 (GLOBAL_DBNAME=digital_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/oradata/19c_home)
 (SID_NAME=digital)
)

Standby listener.ora

(SID_DESC =
 (GLOBAL_DBNAME=digidr_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/oradata/19c_home)
 (SID_NAME=digidr)
)

After adding the above entries in the "listener.ora" files you must reload the listener.

$lsnrctl reload LISTENER

Step 6: Create the Broker Configuration

Use the DGMGRL command-line interface to create the broker configuration. This involves creating a broker configuration object, adding the primary and standby databases to the configuration, and configuring other parameters such as the observer.

$ which dgmgrl

$ dgmgrl

DGMGRL>connect sys/Oracle#123@digital
Connected to "digital"
Connected as SYSDBA.

DGMGRL> create configuration 'digital' as primary database is 'digital' connect identifier is digital;
Configuration "digital" created with primary database "digital"

DGMGRL> show configuration;

Configuration - digital

  Protection Mode: MaxPerformance
  Members:
  digital - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

Add standby in Broker

DGMGRL> add database 'digidr' as connect identifier is digidr maintained as physical;

Database "digidr" added

DGMGRL> show configuration;

Configuration - digital

  Protection Mode: MaxPerformance
  Members:
  digital - Primary database
    digidr  - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

Step 7: Enable Data Guard Broker

Enable the Data Guard Broker by executing the DGMGRL command ENABLE CONFIGURATION on the
primary database.

DGMGRL> enable configuration;
Enabled.


DGMGRL> show configuration;

Configuration - digital

  Protection Mode: MaxPerformance
  Members:
  digital - Primary database
    digidr  - Physical standby database
      Warning: ORA-16856: transport lag could not be determined

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 7 seconds ago)

Monitor the Broker Configuration

Use the DGMGRL command-line interface to monitor the broker configuration. Check the status of the databases, the broker configuration, and the observer.

Manage the Broker Configuration

Use the DGMGRL command-line interface to manage the broker configuration. This includes adding or removing databases from the configuration, configuring switchover or failover, and configuring other parameters such as the observer.

FAQ