oraclednaThis post demonstrates how to create and configure an Oracle Data Guard Broker configuration in an Oracle Database environment.
Oracle Data Guard Broker is a distributed management framework that simplifies and automates the administration, monitoring, and maintenance of Oracle Data Guard environments. It provides centralized management of primary and standby databases.
The Oracle Data Guard Broker’s Data Guard Monitor (DMON) process is responsible for managing and maintaining the broker configuration. It organizes the Data Guard environment as a collection of configuration objects that can be administered, monitored, and managed centrally as a single unit.
Environment:
==========
DB Name: Prod (CDB), PDB (prodpdb1)
DB Type: Multitenant (CDB with one PDB)
DB unique name of Primary DB: prod
DB unique name of Standby DB: stdby
DB Version: 19.27
OS: Oracle Linux 9.4
Note: The Data Guard environment has already been configured, and synchronization between the primary and standby databases has been successfully established.
Please refer to the post below for the detailed Data Guard setup procedure.
Creating multitenant physical standby database in oracle 19c
1. Cancel the MRP on Standby database.
SQL> alter database recover managed standby database cancel;
Database altered.
2. Reset the LOG_ARCHIVE_DEST_2 on both primary and standby database.
–On Primary
SQL> alter system set LOG_ARCHIVE_DEST_2=” scope=both;
System altered.
–On Standby
SQL> alter system set LOG_ARCHIVE_DEST_2=” scope=both;
System altered.
3. Update listener.ora of both primary and standby databases.
Data Guard Broker requires a dedicated static service for DGMGRL connections.
—Primary Database
[oracle@prod admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.166)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER_PROD =
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=prod)
(ORACLE_HOME=/u01/app/oracle/product/19c/db)
(SID_NAME=prod)
)
(SID_DESC=
(GLOBAL_DBNAME=prod_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19c/db)
(SID_NAME=prod)
)
)
—Standby Database
[oracle@stdby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_STDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.167)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER_STDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby)
(ORACLE_HOME = /u01/app/oracle/product/19c/db)
(SID_NAME = stdby)
)
(SID_DESC=
(GLOBAL_DBNAME=stdby_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19c/db)
(SID_NAME=stdby)
)
)
4. Enable DG broker on primary and standby database.
–On Primary
SQL> show parameter DG_BROKER_START
NAME TYPE VALUE
———————————— ———– ——–
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> show parameter DG_BROKER_START
NAME TYPE VALUE
———————————— ———– ———–
dg_broker_start boolean TRUE
–On Standby
SQL> show parameter DG_BROKER_START
NAME TYPE VALUE
———————————— ———– ——–
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> show parameter DG_BROKER_START
NAME TYPE VALUE
———————————— ———– ———–
dg_broker_start boolean TRUE
5. Create the Dataguard Broker configuration On the primary database.
–connect to the DGMGRL from OS level on the primary server
[oracle@prod ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Fri Oct 30 15:16:42 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
–connect to DB
DGMGRL> connect sys/manager@prod
Connected to “prod”
Connected as SYSDBA.
–Create configuration
DGMGRL> CREATE CONFIGURATION ‘PROD’ AS PRIMARY DATABASE IS ‘prod’ CONNECT IDENTIFIER IS prod;
Configuration “PROD” created with primary database “prod”
DGMGRL> show configuration;
Configuration – PROD
Protection Mode: MaxPerformance
Members:
prod – Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
6. Add physical standby database into the DG broker configuration.
–on primary
DGMGRL> add database stdby as connect identifier is stdby;
Database “stdby” added
–Check the configuration
DGMGRL> show configuration;
Configuration – PROD
Protection Mode: MaxPerformance
Members:
prod – Primary database
stdby – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
7. Enable DataGuard configuration on primary database.
DGMGRL> enable configuration;
Enabled.
8. Verify DataGuard Broker Configuration.
DGMGRL> show configuration;
Configuration – PROD
Protection Mode: MaxPerformance
Members:
prod – Primary database
stdby – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL> show database prod;
Database – prod
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prod
Database Status:
SUCCESS
DGMGRL> show database stdby;
Database – stdby
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: 16.00 KByte/s
Real Time Query: OFF
Instance(s):
stdby
Database Status:
SUCCESS
9. Verify the archivelog sync between primary and standby databases.
–On Primary
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch/prod
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
–On Standby
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— —————–
1 35 35 0
The DataGuard Broker configuration has been successfully configured, and redo/archive log transport and apply are operating normally, ensuring that the primary and standby databases remain synchronized.
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
