Oracle Data Guard (DG) Broker Configuration ~ OraEasy

Introduction: 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 both Primary.
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

Thanks for visiting!!

OraEasy

Saturday, March 1, 2025

Oracle Data Guard (DG) Broker Configuration

0 comments:

Post a Comment

Popular Posts

Recent Posts

Search This Blog

About us

Contact form

Technical Disclaimer