Oracle Data Guard — Switchover and Failover Procedures

Oracle Data Guard is the industry standard for Oracle high availability — a standby database that stays synchronized with production and can take over in minutes. But the switchover and failover procedures trip up even experienced DBAs because the steps differ, the order matters, and a mistake during failover can lose data or leave both databases in an unusable state. This article covers both procedures end to end.

Switchover vs Failover — Know the Difference

Switchover — planned role reversal. Both databases are available, no data loss, the original primary can come back as a standby. Use this for planned maintenance, DR testing, or datacenter migrations.

Failover — unplanned. The primary is unavailable (crashed, network isolated, datacenter down). The standby becomes the new primary. The old primary cannot rejoin without being reinstated. There may be data loss depending on your protection mode.

-- Check current Data Guard configuration
SELECT database_role, db_unique_name, open_mode, protection_mode
FROM v$database;

-- Check standby lag
SELECT name, value, datum_time
FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag');

Protection Modes

Your protection mode determines the data loss risk during failover:

• Maximum Protection — zero data loss. Primary waits for standby acknowledgment before committing. Primary shuts down if standby is unreachable.
• Maximum Availability — zero data loss when standby is reachable. Falls back to async if standby is down.
• Maximum Performance (default) — primary doesn't wait for standby. Some data loss possible on failover. Best performance.

-- Check current protection mode
SELECT protection_mode, protection_level FROM v$database;

-- Change protection mode (requires broker or manual steps)
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Pre-Switchover Checks

Always verify the configuration is healthy before a planned switchover:

-- On primary: check standby is synchronized
SELECT dest_id, status, target, archiver, schedule,
       destination, error
FROM v$archive_dest
WHERE target = 'STANDBY'
  AND status != 'INACTIVE';

-- Check apply lag is zero or near zero
SELECT name, value FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');

-- Check for any gaps
SELECT * FROM v$archive_gap;

-- Verify standby is in managed recovery
-- (Run this on standby)
SELECT process, status, sequence#
FROM v$managed_standby
WHERE process IN ('MRP0','RFS')
ORDER BY process;

Switchover Procedure

Step 1: Convert primary to standby

-- On PRIMARY: initiate switchover
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

-- Wait for it to complete (can take 1-5 minutes depending on active sessions)
-- Monitor in alert log: grep "SWITCHOVER" $ORACLE_BASE/diag/rdbms/*/trace/alert*.log

-- Verify primary is now in standby role
SELECT switchover_status FROM v$database;
-- Should show: TO PRIMARY or SESSIONS ACTIVE (if still processing)

Step 2: Convert standby to primary

-- On STANDBY: convert to primary
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

-- Open the new primary
ALTER DATABASE OPEN;

-- Verify role
SELECT database_role, open_mode FROM v$database;
-- Should show: PRIMARY, READ WRITE

Step 3: Start managed recovery on new standby

-- On the OLD PRIMARY (now standby): start recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- Or with real-time apply (recommended)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

-- Verify recovery is running
SELECT process, status, sequence# FROM v$managed_standby;

Step 4: Update connection strings

Update your application connection strings, load balancers, and TNS entries to point to the new primary's host. For EBS environments, run AutoConfig on all tiers pointing to the new primary.

Failover Procedure

Failover is used when the primary is unavailable. Work quickly but carefully — mistakes here can require complete standby rebuild.

Step 1: Confirm primary is truly down

Before failing over, confirm the primary is actually unavailable — not just a network partition where the primary is still running. A split-brain scenario (both databases thinking they're primary) is far worse than a brief outage.

# Try to connect to primary from application server
tnsping PRIMARY_TNS_ALIAS

# Try SSH to primary host
ssh oracle@primary-host "sqlplus / as sysdba <<< 'SELECT status FROM v\$instance;'"

Only proceed with failover if primary is confirmed unreachable.

Step 2: Check standby apply status

-- On STANDBY: check how far apply has progressed
SELECT MAX(sequence#) applied_seq
FROM v$archived_log
WHERE applied = 'YES'
  AND standby_dest = 'NO';

-- Check for any unapplied logs
SELECT sequence#, applied FROM v$archived_log
WHERE applied = 'NO'
  AND standby_dest = 'NO'
ORDER BY sequence#;

Step 3: Apply any remaining redo (if accessible)

If the primary's redo logs are accessible via shared storage or backup:

-- Cancel managed recovery temporarily
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Apply any remaining archived logs manually
ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE
  UNTIL CANCEL USING BACKUP CONTROLFILE;
-- Type CANCEL when no more logs to apply

-- Or register and apply specific log files
ALTER DATABASE REGISTER LOGFILE '/path/to/archived/log';

Step 4: Activate the standby

-- OPTION A: Activate with possible data loss (faster)
ALTER DATABASE ACTIVATE STANDBY DATABASE;

-- OPTION B: Finish recovery then activate (safer, use if logs accessible)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

-- Open the new primary
SHUTDOWN IMMEDIATE;
STARTUP;

-- Verify it's open as primary
SELECT database_role, open_mode FROM v$database;

Step 5: Open with RESETLOGS

After activation, the database opens with a new incarnation:

ALTER DATABASE OPEN RESETLOGS;

This is permanent — the old primary cannot rejoin without reinstatement.

Post-Failover: Reinstating the Old Primary

Once the old primary comes back online, it cannot simply resume as a standby — it needs to be reinstated or rebuilt.

Option A: Flashback reinstatement (if Flashback Database was enabled)

-- On old primary (after it comes back up):
STARTUP MOUNT;

-- Flashback to before the failover
FLASHBACK DATABASE TO SCN <scn_at_failover>;

-- Convert to standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

-- Start managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Option B: RMAN duplicate from new primary

If Flashback wasn't enabled, rebuild the standby from scratch:

# From the standby server, duplicate from the new primary
rman target sys/<pwd>@new_primary auxiliary sys/<pwd>@old_primary_as_standby

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='OLD_PRIMARY_UNIQUE_NAME'
    SET log_archive_dest_2='SERVICE=new_primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=new_primary'
  NOFILENAMECHECK;

Data Guard Broker (Recommended)

The above manual procedures are error-prone. Oracle Data Guard Broker automates switchover, failover, and reinstatement through a single command:

# Connect to broker
dgmgrl sys/<pwd>@primary

# Verify configuration
DGMGRL> show configuration;
DGMGRL> show database verbose primary_db;

# Planned switchover (one command)
DGMGRL> switchover to standby_db;

# Fast-start failover (if configured)
DGMGRL> failover to standby_db;

Broker validates the configuration, performs pre-checks, executes the role change, and updates all members automatically.

Common Failure Points

ORA-16139: media recovery required — standby has unapplied logs. Cancel recovery, apply remaining logs, retry.

ORA-16820: fast-start failover target standby database is no longer synchronized — apply lag exceeded the FastStartFailoverThreshold. Increase the threshold or fix the lag.

Switchover status shows SESSIONS ACTIVE — active user sessions are blocking the role change. Either wait for them to disconnect or use WITH SESSION SHUTDOWN to force disconnect.

-- Check what's blocking switchover
SELECT switchover_status, database_role FROM v$database;

-- Find active sessions
SELECT sid, serial#, username, program, status
FROM v$session
WHERE type = 'USER' AND status = 'ACTIVE'
AND username NOT IN ('SYS','SYSTEM');

Alert log shows: GAP sequence — archived log gap between primary and standby. Usually resolves automatically via FAL (Fetch Archive Log). If not:

-- On primary: check gaps
SELECT * FROM v$archive_gap;

-- Manually register missing logs on standby
ALTER DATABASE REGISTER LOGFILE '/path/to/missing/log';

TuneVault monitors your Data Guard lag, apply status, and transport errors as part of the automated health check. Connect your standby database to track synchronization status without logging into each server manually.