Configuring Oracle Data Guard Broker for High Availability

 

How I recently standardized an existing Primary/Standby setup using Broker (DGMGRL) for simpler HA operations.

In one of my recent HA/DR exercises, we already had a working Oracle Data Guard Physical Standby (redo transport + apply was working), but daily operations were still manual for checking log shipping, monitoring apply lag, handling gaps, and performing role transitions with multiple steps.

To reduce operational effort and human error, I enabled Oracle Data Guard Broker, which logically groups the Primary and Standby into a single broker configuration and lets us manage/monitor it as one unit (via DGMGRL or OEM). Oracle also positions Broker as the standard interface to manage Data Guard configurations.

 

Benifits of enabling Broker in this setup

In practical terms, Broker helped in:

• Centralize Data Guard configuration/monitoring
• Run switchover/failover operations more cleanly (and automate options like FSFO later)
• Control redo transport and redo apply with simple broker states
• Reduce the number of “moving parts” we manually tweak under pressure

 

My starting point and assumptions

This procedure assumes:

• You already have an existing Primary + Standby Data Guard setup built manually.
• Oracle Net connectivity is already in place (TNS connect identifiers work).
• You want Broker to take over management (so we’ll stop MRP briefly and clear the manual LOG_ARCHIVE_DEST_2 configuration as part of the transition).

I used the same sample database names shown in the original reference:

• Primary: proddb 

• Standby: proddb_st

 

 

Step-by-step: Configure Data Guard Broker

1) Edit listeners (Primary + Standby)

This is the part many teams miss during Broker enablement.

Broker needs a dedicated static service for DGMGRL connections. In my case, I ensured the listener had a service entry exactly in the format:

<SID>_DGMGRL (example: proddb_DGMGRL)

Then I restarted the listener on both primary and standby.

Use the same listener snippet:

su – grid

cd $ORACLE_HOME/network/admin

cat listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.211)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=proddb)
(SID_NAME=proddb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=proddb_DGMGRL)
(SID_NAME=proddb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
)

What I validated here

• The _DGMGRL service exists and matches the naming format exactly.

• Listener restart completed cleanly on both sides.

 

 

2) Stop MRP on standby + clear manual LOG_ARCHIVE_DEST_2 (Primary + Standby)

Because I wanted Broker to manage the configuration cleanly, I paused apply (MRP) and removed the manual destination parameter used in the earlier setup.

On standby:

SQL> alter database recover managed standby database cancel;
SQL> alter system set LOG_ARCHIVE_DEST_2=” SCOPE=BOTH sid=’*’;

 

On primary:

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

Why I do this

• Prevents “dual control” (manual Data Guard parameters fighting Broker-managed properties).

• Ensures Broker becomes the single source of truth.

 

 

3) Enable broker on both databases

Now I started the Broker background processes by setting dg_broker_start=true on both sides.

On Primary

SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;

On Standby:

SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;

 

Quick check I always do

Confirm parameter is TRUE in both databases before moving to DGMGRL steps.

 

 

4) Register Primary with Broker (from Primary host)

Next I connected to DGMGRL and created the broker configuration, setting proddb as the primary database.

On Primary

dgmgrl sys/sys@proddb
DGMGRL> create configuration proddb as primary database is proddb connect identifier is proddb;
DGMGRL> show configuration;

 

 

5) Register Standby with Broker (still from Primary host)

While still inside DGMGRL on the primary server, I added the standby database proddb_st

DGMGRL> add database proddb_st as connect identifier is proddb_st;
DGMGRL> show configuration;

 

 

6) Enable the Broker configuration

Once both databases were registered, I enabled the configuration and reviewed details for both Primary and Standby

DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE proddb;
DGMGRL> SHOW DATABASE proddb_st;

At this point, Broker becomes the main control plane for Data Guard management (which is exactly what we want in HA operations).

 

Day-2 Broker Operational Controls

A) Manage Redo Apply via Broker (Standby)

Instead of starting/stopping MRP manually, I used Broker state changes.

 

Stop log apply

dgmgrl sys/sys@proddb
DGMGRL> show configuration;
DGMGRL> show database proddb_st;
DGMGRL> edit database proddb_st set state=APPLY-OFF;
DGMGRL> show database proddb_st;

 

Start log apply