Mastering Oracle Data Guard: Semi‑Automated Setup, DG Broker, Failover & Migration Tips

1. Semi‑Automated Data Guard Setup

Manual Data Guard configuration is time‑consuming and error‑prone, so the author proposes a semi‑automated approach that focuses on simplifying the 70‑80% of work spent on pre‑installation configuration.

Perceived workload is large

Many configuration items on both primary and standby

Potential for trivial yet hard‑to‑debug issues (hostnames, db_unique_name, parameters, listener.ora, tnsnames.ora, profiles)

The goal is to standardise and automate these steps to improve safety and consistency.

2. Using DG Broker

DG Broker is an Oracle tool that manages Data Guard configurations via a background process ( DMON) controlled by the dg_broker_start parameter (set to true to enable, false to disable). It reduces manual command work, especially in complex primary‑multiple‑standby environments, but should only be used after understanding the underlying Data Guard concepts.

3. Practical Scenarios

3.1 Failover Testing Without Affecting the Primary

A user needed to test failover while keeping the primary online. The solution is to promote the standby to primary, perform the test, then switch back. Key command: SQL> alter database convert to physical standby; Images illustrate the state before and after failover.

3.2 Switchover Rollback

When a planned switchover encounters problems, a rollback to the original state is possible using archive‑log based flashback. The article shows the architecture of a primary‑two‑standby setup and the steps to revert.

3.3 Jumping Archive Recovery (SCN‑Based Incremental Backup)

In a 10gR2 primary‑two‑standby environment, one standby fell far behind. The author recommends creating an SCN‑based incremental backup on standby 1, copying it to standby 2, and restoring without impacting the primary. This method reduces downtime compared to full Datapump transfers.

4. Oracle 12c Data Guard Improvements

Oracle 12c adds two notable features:

Far Sync : a lightweight virtual instance (only parameter, password, and control files) that relays redo to a distant standby, eliminating data‑file latency and enabling near‑zero data loss.

VALIDATE : a pre‑switchover check that ensures the operation can succeed, a useful addition over 11g.

Configuration examples and diagrams demonstrate adding a Far Sync node and using VALIDATE before switchover.

5. Diagnosis Case: Standby Batch Query Failure

In a 10gR2 primary‑two‑standby setup, a nightly batch query on a standby started failing with ORA‑00604 and ORA‑16000 errors. Investigation revealed that the synonym TEST_BILLING on the standby was INVALID, causing the read‑only query to error. The fix was to refresh the synonym on the primary (e.g.,

select count(*) from TEST_SHINK.TEST_BILLING where cn_master='...';

), after which the standby query succeeded.

6. Data Migration Using Data Guard

Data Guard can facilitate major version upgrades and platform migrations without direct downtime. The recommended workflow:

Create a new 11g standby on the existing standby host.

Perform a switchover or failover to make the new standby primary.

Export the tablespace metadata from the old primary.

Stop the old 10g standby, import the metadata into the new 11g database, and optionally export/import specific schemas for objects not covered by tablespace transport.

This approach yields a fast migration compared to traditional Datapump or XTTs methods. Diagrams illustrate each step.