📦 Oracle Data Guard: Monitoring, Switchover, and Failover Guide

This article provides a professional and SEO-optimized reference for Oracle DBAs managing Oracle Data Guard. It covers:

• Reinstating Databases after Failover
• Monitoring Redo Transport and Apply
• Performing Switchover Operations (SQLPlus & DGMGRL)
• Handling Failover Scenarios

SELECT dest_id, dest_name, status 
FROM gv$archive_dest 
WHERE status NOT IN ('VALID', 'INACTIVE');

📊 Check Latest Log Apply and Receive Times

SELECT 'Last applied : ' AS Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') AS Time
FROM v$archived_log
WHERE sequence# = (
  SELECT MAX(sequence#) FROM gv$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last received : ' AS Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') AS Time
FROM v$archived_log
WHERE sequence# = (
  SELECT MAX(sequence#) FROM gv$archived_log
);

🩺 Monitor MRP & Apply Services

SELECT process, status, sequence# FROM gv$managed_standby;

🗂️ On Primary: Force Log Switch & Monitor Archive

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, status, fal 
FROM v$archived_log 
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;

📁 On Standby: Monitor Log Apply

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied 
FROM v$archived_log 
ORDER BY next_time;;

🔁 Switchover Pre-Checks

SELECT DB_UNIQUE_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS, SYNCHRONIZED 
FROM v$archive_dest_status;

SELECT thread#, sequence#, status, archived FROM v$log;

SELECT client_process, process, sequence#, status 
FROM v$managed_standby;

-- Make sure MRP0 is running; if WAIT_FOR_GAP, fix the gap before switchover.
-- Ensure no RMAN jobs are running:
SELECT process, operation, r.status, mbytes_processed, s.status 
FROM v$rman_status r, v$session s 
WHERE r.sid = s.sid;

Configure SSH equiv🔄 Switchover via SQLPlus

1. Ensure only one instance is running per DB (primary/standby).
2. Check password file and network services.
3. Ensure switchover status is TO STANDBY or TO PRIMARY:

SELECT switchover_status FROM v$database;

-- If status is 'SESSIONS ACTIVE':
SELECT program, type FROM v$session WHERE type='USER';

➡️ Switchover Steps

On Primary:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

On Standby (after MRP stops):

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP;

On Old Primary (Now Standby):

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;zip

✅ Switchover via DGMGRL

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

On Standby (after MRP stops):

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP;

On Old Primary (Now Standby):

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

✅ Switchover via DGMGRL

DGMGRL> SWITCHOVER TO <Standby_DB_Name>;

❗ Failover Steps

Check lag and SCN info:

DGMGRL> SELECT name, value, time_computed FROM v$dataguard_stats WHERE name LIKE '%lag%';

SELECT thread#, sequence#, last_change#, last_time FROM v$standby_log;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE OPEN;;

🔁 Reinstating the Failed Primary

DGMGRL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) AS FAILOVER_SCN FROM v$database;

On old primary:

DGMGRL> STARTUP MOUNT;
FLASHBACK DATABASE TO SCN <failover_scn>;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

🚨 Failover and Reinstate using DGMGRL

DGMGRL> DGMGRL> FAILOVER TO <Standby_DB_Name>;
DGMGRL> REINSTATE DATABASE <Old_Primary_DB>;

🧪 Validate DB Service Name / DB Links

Ensure service names and DB links are correctly registered and functioning post switchover/failover.

✅ You have now successfully updated the OPatch utility in both Grid Infrastructure and RDBMS homes across all nodes in your Exadata environment.

Tags: