Configuring Oracle Data Guard Broker for HA

 

Oracle Data Guard Broker is a built-in management and automation tool that comes with Oracle Database to help you configure, monitor, and control Oracle Data Guard environments easily — without running lots of manual SQL commands.

Think of it as a control center for standby databases.

---

📌 What problem does it solve?

Normally with Oracle Data Guard, you must:

• Manually create standby databases
• Configure redo transport
• Manage switchover/failover
• Check synchronization

👉 Broker automates and simplifies all of this.

---

⚙️ What Data Guard Broker does for you

✅ Automatically configures primary & standby
✅ Continuously monitors health
✅ Manages switchover (planned role change) you can automate switchover system
✅ Manages failover (disaster recovery)   you can automate failover system 
✅ Detects problems and alerts you

🛠 Tools used with Broker

You usually manage it using:

• DGMGRL (Data Guard Manager command line)
• Oracle Enterprise Manager (GUI)

---

📊 Key Benefits (for DBAs)

✔ Less human error
✔ Faster disaster recovery
✔ Easier administration
✔ Built-in monitoring

Benefits of Data Guard Broker

One of the biggest benefits of Data Guard broker is that is centralizes the configuration, management and monitoring of Oracle Data Guard configurations.

• Some of the operations that Data Guard broker simplifies are
• Create Data Guard configuration between primary and standby databases
• Add additional standby databases to existing Data Guard configuration
• Mange Data Guard protection modes
• Start switchover / failover by just one single command
• Automate failover in case of primary not reachable
• Monitor redo apply, gaps and data guard performance
• Perform all above operations locally or remotely !

Edit listeners

If you look at the listener configuration file, there is a dedicated service we have to create for DGMGRL. This is required in order enable Data Guard Broker. If this is not set, add below entry (test_DGMGRL and it has to be exactly in the same format <SID>_DGMGRL) and restart listener on both primary and standby

su - oracle
cd $ORACLE_HOME/network/admin
cat listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

SID_LIST_LISTENER=
 (SID_LIST=
   (SID_DESC=
     (GLOBAL_DBNAME=test)
     (SID_NAME=test)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
   (SID_DESC=
     (GLOBAL_DBNAME=test_DGMGRL)
     (SID_NAME=test)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
 )   

Stop MRP on standby
We would like to manage our data guard configuration using Data Guard Broker. Stop MRP and clear Log_Archive_dest_2 parameter

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> show parameter LOG_ARCHIVE_DEST_2
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

this parameter LOG_ARCHIVE_DEST_2 defines the second server where the logs will be shipped if you remember we define value for it when configuraing physical standby manually but now we keep it empty because we are now dependent to Data Guard Broker which is automate system and knows how to set your archive destination so you need not worry about this parameter so what we do we completely remove this parameter. so remove it from both Primary and Standby
 
On primary:
===========
SQL> show parameter LOG_ARCHIVE_DEST_2
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';




Enable broker
We need to start the broker by enabling it on both primary and standby

On primary:
===========
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;
 
On standby:
===========
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;




Register primary with broker


On primary, connect to DGMGRL utility and register the primary database with broker

On primary:
===========
dgmgrl sys/sys@test
DGMGRL> show configuration;
DGMGRL> create configuration my_dgb as primary database is test connect identifier is test;
DGMGRL> show configuration;




Register standby with broker


In the same DGMGRL utility, register standby from primary server itself
DGMGRL> add database test_s as connect identifier is test_s;
DGMGRL> show configuration;




Enable Data Guard broker


Once primary and standby are registered, we must enable broker
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE test;
DGMGRL> SHOW DATABASE test_s;




Manage Redo Apply via Broker


Like how we start / stop MRP manually, we can start / stop redo apply on standby using broker
Stop log apply:
===============
dgmgrl sys/sys@test
DGMGRL> show configuration;
DGMGRL> show database test_s;
DGMGRL> edit database test_s set state=APPLY-OFF;
DGMGRL> show database test_s;
 
Start log apply:
================
dgmgrl sys/sys@test
DGMGRL> show configuration;
DGMGRL> show database test_s;
DGMGRL> edit database test_s set state=APPLY-ON;
DGMGRL> show database test_s;




Start/stop log shipping via Broker


How we can manually enable log shipping from primary to standby, the same way we can use broker to enable log shipping
Disable log shipping/transport:
===============================
dgmgrl sys/sys@test
DGMGRL> show configuration;
DGMGRL> show database test;
DGMGRL> edit database test set state=TRANSPORT-OFF;
DGMGRL> show database test;
 
Enable log shipping/transport:
==============================
dgmgrl sys/sys@test
DGMGRL> show configuration;
DGMGRL> show database test;
DGMGRL> edit database test set state=TRANSPORT-ON;
DGMGRL> show database test;
 

Convert Physical Standby into Active Data Guard

 

When Do Clients Need Active Data Guard?

1. Reporting Off the Standby:

   • When the client wants to run reports, analytics, or queries without slowing down the primary database.

2. Offload Backups:

   • When the client wants to take RMAN backups from the standby instead of the primary.

3. High Availability with Extra Use:

   • When the client wants the standby to stay fully synchronized with the primary and still do useful work.

4. Minimize Primary Load:

• When the primary database is very busy, and they want to reduce performance impact by offloading read-only tasks

Key DBA Considerations:

• Requires Active Data Guard license.

• Standby remains transactionally consistent with the primary.

• Supports flashback, RMAN backups, and queries simultaneously.

Enable Active Data Guard

Lets Open Physical Standby and test active data guard

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select name, open_mode, database_role from v$database;
SQL> alter database recover managed standby database disconnect;

Verify MRP is Running - Use below query to check if MRP is running in the background or not

On standby (active data guard):
===============================
SQL> select process, status, sequence# from v$managed_standby;

with apply means the background manage recovery processes is up and running in Active data

Test Active Data Guard

As our active data guard is open for read only queries and background recover is active, let us create a table on primary and see if it is reflected on standby

On primary:
===========
SQL> create table dg_test(sno number(2),sname varchar2(10));

SQL>select username from dba_users;

we can perform select queries on Data Guard Standby.
for Active Data Guard you need to buy license 

Revert back to physical standby

If you want to convert active data guard back to physical standby, follow below commands

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select name, open_mode, database_role from v$database;
SQL> alter database recover managed standby database disconnect;
SQL> select process, status, sequence# from v$managed_standby;

Convert Physical Standby into Snapshot Standby

 

1️⃣ What is happening?

• Physical Standby = Exact copy of production database, read-only.

• Snapshot Standby = Temporary version of standby that is read/write, used for testing.

---

2️⃣ Why convert to Snapshot Standby?

• Suppose the application team wants to test new features or queries on real production data.

• Instead of making a new clone of production (which is slow and heavy), we can reuse the existing physical standby.

• Conversion allows safe testing without touching production.

---

3️⃣ Key Advantage

• Reversible: After testing, the snapshot standby can be converted back to physical standby.

• Repeatable: This cycle can be done any number of times – convert → test → revert → sync with production.

---

4️⃣ How it works (simple cycle)

  PRODUCTION DB
        |
        v
PHYSICAL STANDBY (read-only)
        |
   Convert to Snapshot
        v
SNAPSHOT STANDBY (read/write) → Testing happens here
        |
   Revert back
        v
PHYSICAL STANDBY (read-only) → Resumes syncing with production

---

5️⃣ Student-friendly analogy

• Physical Standby = Photocopy of homework (can look, but can’t write)

• Snapshot Standby = Practice sheet (you can write, erase, test)

• After testing → erase practice sheet and revert to original photocopy

---

💡 One-liner for memory:

“Snapshot Standby lets us safely test on production data using standby, then revert and sync back without touching production.”

Convert physical standby to snapshot standby: We will now convert the physical standby database to snapshot standby

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> select name, open_mode from v$database; 		>> make sure its mounted
SQL> alter database convert to snapshot standby;
SQL> alter database open;				>> open the DB
SQL> select name, open_mode, database_role from v$database;

Verifying snapshot standby: Now you must be able to read-write on snapshot standby. Meanwhile, we can even check the standby alert log. The archives received from primary are not applied on standby. We can even check that there is a guaranteed restore point has been created. So that when you convert snapshot back to physical standby, it will be used.

On standby:
===========

First check if oracle created restore Point when we convert physical standby to snanpshot standby before.

SQL> select name, guarantee_flashback_database from v$restore_point;

Now when you convert it to physical standby Oracle will flashback to this point and then convert this snapshot standby to physical standby

lets try do our testing assume development team are testing at this stage

SQL> create table ss_test(sno number(2));
SQL> insert into ss_test values(1);
SQL> insert into ss_test values (2);
SQL> commit;
SQL> select * from ss_test;

Revert back snapshot standby to physical standby: Once application testing is done, you can revert back snapshot standby to same point when it was converted from physical standby to snapshot standby

On standby:
===========
SQL> select name, open_mode, database_role from v$database;
SQL> shut immediate;
SQL> startup mount;
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database recover managed standby database disconnect; 

SQL> select * from ss_test;