Home / Databases / Oracle

DatabasesRunbookAdvanced

Oracle Data Guard: switchover, failover and reinstating with the broker

Run Oracle Data Guard role changes safely with DGMGRL. Covers health checks, protection modes, a planned switchover, an emergency failover, reinstating the old primary with Flashback Database, and Fast-Start Failover.

Deepak Bhardwaj3 min read

Applies toOracle Database 19c and 23ai with Data Guard (Enterprise Edition), on-premises, Exadata or OCI

1. Check health first

DGMGRL> SHOW CONFIGURATION VERBOSE;
DGMGRL> SHOW DATABASE 'SALES_DR';
DGMGRL> VALIDATE DATABASE 'SALES_DR';

Look for SUCCESS, Transport Lag and Apply Lag near 0, and Ready for Switchover: Yes.

SELECT name, value, time_computed FROM v$dataguard_stats;   -- on the standby

2. Protection modes

Mode Redo transport Data loss on failover Primary impact
Maximum Performance (default) ASYNC Seconds None
Maximum Availability SYNC / FASTSYNC Zero, if in sync Commit latency
Maximum Protection SYNC Zero; the primary stops if no standby can confirm Highest 
DGMGRL> EDIT DATABASE 'SALES_DR' SET PROPERTY LogXptMode='SYNC';
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

Use SYNC for a nearby standby (low latency) and ASYNC for one far away.

3. Planned switchover (no data loss)

DGMGRL> VALIDATE DATABASE 'SALES_DR';
DGMGRL> SWITCHOVER TO 'SALES_DR';
DGMGRL> SHOW CONFIGURATION;

The broker converts the primary to a standby and the standby to the primary, and restarts as needed. Make sure database services are set up with -role PRIMARY so they start on the new primary automatically:

srvctl add service -db SALES_DR -service sales_oltp -role PRIMARY -preferred SALES_DR1

4. Failover (primary lost)

DGMGRL> CONNECT sys@SALES_DR
DGMGRL> FAILOVER TO 'SALES_DR';             -- complete failover (applies all received redo)

An IMMEDIATE failover skips applying the remaining redo. Use it only if a complete failover can't finish.

5. Reinstate the old primary

If Flashback Database was on in the old primary:

DGMGRL> REINSTATE DATABASE 'SALES';

Start the old primary in MOUNT first if the broker asks. Without flashback, rebuild it as a standby from the new primary (RMAN DUPLICATE ... FOR STANDBY FROM ACTIVE DATABASE).

6. Fast-Start Failover (automatic)

DGMGRL> EDIT DATABASE 'SALES_DR' SET PROPERTY FastStartFailoverTarget='SALES';
DGMGRL> EDIT DATABASE 'SALES' SET PROPERTY FastStartFailoverTarget='SALES_DR';
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=30;
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> START OBSERVER;          -- run on a third site

The observer must run in a third location, not in the same site as either database.

7. Client failover

Use a connect string listing both sites and a role-based service, so clients reach whichever database is primary:

(DESCRIPTION=(FAILOVER=on)(CONNECT_TIMEOUT=10)(RETRY_COUNT=10)
  (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-scan-dc1)(PORT=1521))
                (ADDRESS=(PROTOCOL=TCP)(HOST=sales-scan-dc2)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales_oltp)))

8. Drill checklist

  • Switchover and switchback each quarter.
  • App connectivity verified after each role change.
  • Backups configured to run on whichever database is primary (or on the standby).
  • Wallets and TDE keys present on both sites.

Tip

Turn on Flashback Database on both primary and standby. After a failover it turns "rebuild a multi-terabyte standby" into a reinstate that takes minutes.

Related fixes