Oracle Data Guard Broker (DGMGRL) – Configuration & Operations Guide

 

---

Oracle Data Guard Broker (DGMGRL) – Configuration & Operations Guide

1. Introduction

Oracle Data Guard Broker (DGMGRL) is a management and monitoring framework that simplifies the creation, maintenance, and monitoring of Data Guard configurations.

It helps in:

• Automating switchover/failover
• Managing redo transport & apply
• Monitoring health centrally

---

2. Prerequisites

Before configuring Broker:

• Primary & Standby databases must be configured manually
• TNS entries must exist for both databases
• DG_BROKER_START=TRUE should be set on both databases

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

---

3. Create Data Guard Broker Configuration

Step 1: Connect to DGMGRL

dgmgrl /

Step 2: Create Configuration

CREATE CONFIGURATION testdb_dg_config

AS PRIMARY DATABASE IS testdb

CONNECT IDENTIFIER IS testdb_fx;

✅ Output:

Configuration "testdb_dg_config" created with primary database "testdb"

---

Step 3: Add Standby Database

ADD DATABASE ndrtestdb

AS CONNECT IDENTIFIER IS ndrtestdb_fx

MAINTAINED AS PHYSICAL;

---

Step 4: Enable Configuration

ENABLE CONFIGURATION;

 

ENABLE DATABASE testdb;

ENABLE DATABASE drtestdb;

ENABLE DATABASE ndrtestdb;

---

Step 5: Verify Configuration

SHOW CONFIGURATION;

SHOW DATABASE testdb;

---

4. Common Error: ORA-16532

Error

ORA-16532: broker configuration does not exist

Cause

• Connected to standby instead of primary

Solution

dgmgrl sys@primary

Verify

SHOW CONFIGURATION;

---

5. Start / Stop Redo Apply (MRP) using DGMGRL

Stop Redo Apply

EDIT DATABASE standby SET STATE='APPLY-OFF';

Start Redo Apply

EDIT DATABASE standby SET STATE='APPLY-ON';

---

Using SQL (Standby)

-- Stop MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

-- Start MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

---

6. Redo Transport Control (Primary Side)

Stop Redo Transport

EDIT DATABASE primary SET STATE='TRANSPORT-OFF';

Start Redo Transport

EDIT DATABASE primary SET STATE='TRANSPORT-ON';

---

Using SQL

-- Stop

ALTER SYSTEM SET log_archive_dest_state_2=DEFER;

 

-- Start

ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

---

7. Monitoring Data Guard

Using DGMGRL

SHOW DATABASE VERBOSE standby;

SHOW CONFIGURATION;

Key Metrics

• Transport Lag
• Apply Lag
• Apply Rate
• Real-Time Query

---

Using SQL

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

Important Processes

• MRP0 → Redo Apply
• RFS → Receiving logs
• ARCH → Archiver

---

8. Switchover Using DGMGRL

Command

SWITCHOVER TO standby;

---

9. ORA-16516 During Switchover

Error

ORA-16516: current state is invalid for the attempted operation

---

Root Cause

• Standby is in READ ONLY WITH APPLY (Active Data Guard)

---

Solution Steps

Step 1: Stop Apply (DGMGRL)

EDIT DATABASE standby SET STATE='APPLY-OFF';

---

Step 2: Cancel Recovery (SQL)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

---

Step 3: Restart Standby in MOUNT

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

---

Step 4: Retry Switchover

SWITCHOVER TO standby;

---

10. If Switchover Still Fails

Check Status

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Possible Outputs:

• NOT ALLOWED
• RESOLVABLE GAP
• TO STANDBY

---

Check MRP

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

---

Fix Transport Issue

ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

---

Wait for Sync

RESOLVABLE GAP → TO STANDBY

Then retry switchover.

---

11. Full Health Check Commands

DGMGRL

SHOW CONFIGURATION;

SHOW DATABASE VERBOSE standby;

---

SQL Checks

-- Check Apply

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

 

-- Check Lag

SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

 

-- Check Role

SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

---