Data Guard Broker is a management tool for Oracle Data Guard, which simplifies the configuration, monitoring, and administration of Data Guard environments. It provides a centralized, automated interface to manage primary and standby databases.
Command-Line (DGMGRL) & GUI (Enterprise Manager) – Can be managed using DGMGRL (Data Guard Broker command-line) or Oracle Enterprise Manager Cloud Control.
This guide will walk you through the Data Guard Broker step-by-step.
Prerequisites: Below are the requirements for Data Guard Broker.
1. Oracle Database Enterprise Edition (Standard Edition does not support Data Guard).
2. Same Database Version & Patch Level on both primary and standby.
3. Oracle Data Guard Feature Installed on both systems.
Environment:
| 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 |
Now start DG Broker configuration step by step:
1. Reset Archive dest 2 parameter in both Primary & Standby in order to avoid "ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set" while adding database in DG Broker.
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.
2. Set dg_broker_start parameter true in both Primary & Standby.
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
[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
3. Now add configuration for DG Broker. We will do the same on Primary database.
[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
4. Now set Archive dest 2 on Primary.
SQL> alter system set log_archive_dest_2='SERVICE=ORCLDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR';
System altered.
5. Now enable the DG Configuration.
[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)
6. Now set Archive dest 2 on Standby. Also MRP will be started automatically as DG broker configuration has been enabled.
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
7. Now check DG broker status on both Primary & Standby.
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
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
