Introduction
Data Guard Broker is a management tool for Oracle Data Guard that simplifies the configuration, monitoring, and administration of Data Guard environments. Instead of manually tracking parameters and switchover steps across each database individually, Broker gives you a centralized, automated interface, and it can also monitor apply lag and health status continuously rather than requiring manual checks. It can be managed using DGMGRL (the Data Guard Broker command-line interface) or Oracle Enterprise Manager Cloud Control. This guide walks through setting up Data Guard Broker on an existing primary and standby pair, step by step.
Prerequisites
- Oracle Database Enterprise Edition. Standard Edition does not support Data Guard.
- The same database version and patch level on both primary and standby.
- A working Data Guard configuration already in place on both systems, with the primary and standby in sync.
Environment Used in This Guide
This continues from our earlier standby database setup guide, using the same primary and standby pair.
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
Step 1: Clear LOG_ARCHIVE_DEST_2 on Both Databases
Data Guard Broker manages redo transport itself once it's enabled, and it conflicts with a manually configured LOG_ARCHIVE_DEST_2. If you leave the manual setting in place, adding the database to Broker later fails with ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set. Clear it on both sides now, we'll let Broker set its own equivalent value later once the configuration is enabled.
Primary:
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="orcldr", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300
db_unique_name="orcldr" net_timeout=30, valid_for=(online_logfile,all_roles)
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;
System altered.
Standby:
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="orcldc", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300
db_unique_name="orcldc" net_timeout=30, valid_for=(online_logfile,all_roles)
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;
System altered.
Step 2: Enable dg_broker_start on Both Databases
This parameter starts the DMON background process, which is what actually runs the Broker configuration on each instance. Without it running on both sides, DGMGRL has nothing to connect to.
Primary:
[oracle@source ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 23 11:32:27 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL>
SQL> def
DEFINE _DATE = "23-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1924000000" (CHAR)
SQL>
SQL> show parameter dg_broker
NAME TYPE VALUE
----------------------- ------------ ---------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr1ORCLDC.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr2ORCLDC.dat
dg_broker_start boolean FALSE
SQL>
SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------ ---------- --------
dg_broker_start boolean TRUE
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@source ~]$
Standby: we also cancel managed recovery here first, since Broker will restart and manage the apply process itself once the configuration is enabled in Step 5.
[oracle@target~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 23 11:32:10 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL>
SQL> def
DEFINE _DATE = "23-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldr" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1924000000" (CHAR)
SQL>
SQL> show parameter dg_broker
NAME TYPE VALUE
-------------------- ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr1ORCLDR.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr2ORCLDR.dat
dg_broker_start boolean FALSE
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------ ---------- --------
dg_broker_start boolean TRUE
Step 3: Create the Broker Configuration
With dg_broker_start enabled on both sides, connect via DGMGRL from the primary and create the configuration, then add the standby to it. Note that show configuration fails initially, that's expected, since no configuration exists yet at this point.
[oracle@source ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 23 11:55:25 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCLDC"
Connected as SYSDG.
DGMGRL> show configuration
ORA-16596: member not part of the Oracle Data Guard broker configuration
Configuration details cannot be determined by DGMGRL
DGMGRL>
Syntax: create configuration "Configuration Name" as primary database is "Primary DB unique name" connect identifier is "Primary TNS alias name";
DGMGRL> create configuration ORCLDG as primary database is ORCLDC connect identifier is ORCLDC;
Configuration "orcldg" created with primary database "orcldc"
DGMGRL>
DGMGRL> show configuration
Configuration - orcldg
Protection Mode: MaxPerformance
Members:
orcldc - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
Syntax: add database "Standby DB unique name" as connect identifier is "Standby TNS alias name";
DGMGRL> add database ORCLDR as connect identifier is ORCLDR;
Database "orcldr" added
DGMGRL>
DGMGRL> show configuration
Configuration - orcldg
Protection Mode: MaxPerformance
Members:
orcldc - Primary database
orcldr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> show configuration
Configuration - orcldg
Protection Mode: MaxPerformance
Members:
orcldc - Primary database
orcldr - Physical standby database
Warning: ORA-16853: apply lag has exceeded specified threshold
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 3 seconds ago)
DGMGRL> exit
The apply lag warning at this point is expected, we cleared redo transport in Step 1 and haven't re-enabled it yet, so the standby has genuinely fallen behind. It resolves once we set LOG_ARCHIVE_DEST_2 and enable the configuration in the next two steps.
Step 4: Set LOG_ARCHIVE_DEST_2 on the Primary
Now that the configuration exists, set the redo transport destination on the primary. This is the manual equivalent of what Broker will manage automatically once enabled, but it needs to be set once to establish the transport path.
SQL> alter system set log_archive_dest_2='SERVICE=ORCLDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR';
System altered.
Step 5: Enable the Broker Configuration
Enabling the configuration is what activates Broker's ongoing management of the Data Guard environment, monitoring, health checks, and coordinated role transitions all become available from this point on.
[oracle@source ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 23 11:55:25 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCLDC"
Connected as SYSDG.
DGMGRL> show configuration
Configuration - orcldg
Protection Mode: MaxPerformance
Members:
orcldc - Primary database
orcldr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration
Configuration - orcldg
Protection Mode: MaxPerformance
Members:
orcldc - Primary database
orcldr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 43 seconds ago)
Step 6: Set LOG_ARCHIVE_DEST_2 on the Standby and Confirm Apply Resumes
Set the matching redo transport parameter on the standby side. Once Broker's configuration is enabled, it automatically restarts managed recovery (MRP), which is why the first attempt to manually start it below returns an error, it's already running under Broker's control.
SQL> alter system set log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC';
System altered.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0 APPLYING_LOG 1 52 514
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR PHYSICAL STANDBY 1 57 55 2 23-JAN-2025 12:04:11 11.37 682
SQL>/
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR PHYSICAL STANDBY 1 58 58 0 23-JAN-2025 12:30:15 13.72 823
The second query shows ARC_DIFF back to 0, confirming the standby has fully caught up now that Broker is managing transport and apply.
Step 7: Verify Broker Status on Both Databases
As a final check, confirm both databases report a healthy configuration status and that transport and apply lag are both at zero from each side's perspective.
Primary:
[oracle@source ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 23 12:00:27 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCLDC"
Connected as SYSDG.
DGMGRL> show configuration
Configuration - orcldg
Protection Mode: MaxPerformance
Members:
orcldc - Primary database
orcldr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 43 seconds ago)
DGMGRL>
DGMGRL>
DGMGRL> show database orcldc
Database - orcldc
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcldc
Database Status:
SUCCESS
DGMGRL> show database orcldr
Database - orcldr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 7.00 KByte/s
Real Time Query: OFF
Instance(s):
orcldr
Database Status:
SUCCESS
DGMGRL> exit
Standby:
[oracle@target ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 23 12:00:09 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCLDR"
Connected as SYSDG.
DGMGRL> show configuration
Configuration - orcldg
Protection Mode: MaxPerformance
Members:
orcldc - Primary database
orcldr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 30 seconds ago)
DGMGRL> show database orcldc
Database - orcldc
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcldc
Database Status:
SUCCESS
DGMGRL> show database orcldr
Database - orcldr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 5.00 KByte/s
Real Time Query: OFF
Instance(s):
orcldr
Database Status:
SUCCESS
DGMGRL> exit
Both sides reporting SUCCESS with zero transport and apply lag confirms Data Guard Broker is fully configured and actively managing the environment. From here, switchovers and health monitoring can be done through DGMGRL instead of manual SQL commands.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment