Oracle Data Guard Switchover Procedure
Document Title: Oracle Data Guard Manual Switchover Procedure
Prepared For: DBA Team
Prepared By: Nagendrababu Guda
Version: 1.0
Applicable Oracle Versions: Oracle 12c, 18c, 19c, 21c
Date: June 2025
Table of Contents
1. Prerequisites
2. Switchover Preparation Steps
3. Switchover Execution Steps
4. Post-Switchover Steps
5. Validation Queries
1. Prerequisites
Before starting the switchover, ensure:
• Unique DB_UNIQUE_NAME for both Primary and Standby
• Proper archive log shipping (LOG_ARCHIVE_DEST_n)
• FAL parameters are configured
• STANDBY_FILE_MANAGEMENT is set to AUTO
• Standby redo logs exist on both databases
• Data Guard Broker status is SUCCESS (if used)
• Confirm both databases are OPEN (Primary) or MOUNTED (Standby)
Sample Commands:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PrimaryDB,StandbyDB)'
SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=StandbyDB ASYNC
VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=StandbyDB'
SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER='StandbyDB' SCOPE=BOTH;
ALTER SYSTEM SET FAL_CLIENT='PrimaryDB' SCOPE=BOTH;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;
2. Switchover Preparation Steps
2.1 Check Archive Gap (On Standby)
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
2.2 Check Roles and Open Modes
SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
2.3 Check Switchover Status
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
• PRIMARY: TO STANDBY or SESSIONS ACTIVE
• STANDBY: TO PRIMARY or SESSIONS ACTIVE
2.4 Check Archive Errors
SELECT ERROR FROM V$ARCHIVE_DEST_STATUS WHERE ERROR IS NOT NULL;
2.5 Perform Manual Log Switch (Optional but Recommended)
ALTER SYSTEM SWITCH LOGFILE;
3. Switchover Execution Steps
3.1 On PRIMARY (Convert Primary to Standby)
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION
SHUTDOWN;
3.2 Restart PRIMARY (Now Standby)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
3.3 On STANDBY (Convert Standby to Primary)
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
3.4 Restart New PRIMARY
SHUTDOWN IMMEDIATE;
STARTUP;
or
ALTER DATABASE OPEN;
3.5 Start Managed Recovery on New STANDBY
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT;
3.6 Verify Roles on Both Databases
SELECT NAME, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS FROM V$DATABASE;
4. Post-Switchover Steps
4.1 Verify Archive Destination on New PRIMARY
ALTER SYSTEM SWITCH LOGFILE;
SELECT DEST_ID, STATUS, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS NOT IN ('INACTIVE');
4.2 Verify Redo Apply on New STANDBY
SELECT THREAD#, SEQUENCE#, BLOCKS, APPLIED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# DESC
FETCH FIRST 5 ROWS ONLY;
SELECT THREAD#, SEQUENCE#, PROCESS, STATUS
FROM GV$MANAGED_STANDBY;
4.3 Check Data Guard Lag
SELECT VALUE FROM V$DATAGUARD_STATS
WHERE NAME IN ('transport lag','apply lag');
5. Validation Queries
5.1 Verify Open Mode & Database Role
SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
5.2 Verify Protection Mode
SELECT PROTECTION_MODE FROM V$DATABASE;
5.3 Verify Redo Apply Progress
SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#;
5.4 Verify Switchover Status
SELECT NAME, SWITCHOVER_STATUS FROM V$DATABASE;
End of Document
