Home
Data Guard
DR Drill

Oracle Data Guard DR Drill (Switchover & Switchback) – Complete Step-by-Step Guide

The easiest Oracle Data Guard DR Drill guide with step-by-step Switchover & Switchback commands for Oracle 19c.

Introduction

A Disaster Recovery (DR) Drill is one of the most important activities for every Oracle DBA. It validates that your Oracle Data Guard environment can successfully switch production to the standby database during planned maintenance or disaster scenarios.

In this article, we'll perform a complete Oracle Data Guard Switchover followed by a Switchback to restore the original roles.

This guide is applicable to:
  • Oracle Database 19c
  • Oracle Data Guard Physical Standby
  • Maximum Performance Protection Mode
  • Manual Switchover (without Data Guard Broker)

Why Perform a DR Drill?

A DR drill helps verify:
  • Disaster Recovery readiness
  • Data synchronization between Primary and Standby
  • Successful role transition
  • Application connectivity after switchover
  • Recovery process after switchback
Performing periodic DR drills ensures that your standby database is fully prepared when an actual disaster occurs.

Environment

Database Initial Role
ORCL Primary
ORCLDR Physical Standby

Prerequisites

Before starting the switchover, ensure:
  • No archive gap exists
  • Standby is fully synchronized
  • MRP is running
  • No blocking sessions or long-running transactions
  • Backup of SPFILE and Control File is available
  • Application downtime is approved

Oracle Data Guard Switchover Procedure (Step-by-Step)

On Primary Database:

Step 1: Verify the Primary Database Status

SQL> select name, open_mode, database_role, log_mode, switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------ --------------------
ORCL      READ WRITE           PRIMARY          ARCHIVELOG   TO STANDBY
Check Invalid Objects
SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         7
It is always recommended to note the number of invalid objects before the DR activity so they can be compared after the switchover.

Step 2: Backup Critical Configuration

Backup the control file.
SQL> alter database backup controlfile to trace as '/backup/ctl_trace.bkp';

Database altered.
Backup the SPFILE.
SQL> create pfile='/backup/pfile_backup.ora' from spfile;

File created.
These backups can be extremely helpful if recovery is required after the DR drill.

Step 3: Force Archive Log Switch

Generate fresh archive logs.
SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.
Verify the latest archive sequence.
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            61
Record this sequence number and ensure the standby has applied it before proceeding.

On Standby Database:

Step 4: Verify Standby Synchronization

SQL> select name, open_mode, database_role, log_mode, switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------ --------------------
ORCL      MOUNTED              PHYSICAL STANDBY ARCHIVELOG   NOT ALLOWED
Verify applied archive logs.
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            61
The applied sequence should match the latest archive generated on the primary database.

On Primary Database:

Step 5: Verify Switchover Readiness

Run verification.
SQL> alter database switchover to ORCLDR verify;

Database altered.
If verification succeeds:
SQL> alter database switchover to ORCLDR;

Database altered.
The primary database now transitions into the standby role.

On New Primary Database:

Step 6: Open the New Primary Database

Shutdown and start the former standby.
SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1543502248 bytes
Fixed Size                  8939944 bytes
Variable Size            1073741824 bytes
Database Buffers          452984832 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.
Enable remote archive destination.
SQL> alter system set log_archive_dest_state_2=ENABLE scope=both;

System altered.
Verify role.
SQL> select name, open_mode, database_role, log_mode, switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------ --------------------
ORCL      READ WRITE           PRIMARY          ARCHIVELOG   TO STANDBY

On New Standby Database:

Step 7: Start Recovery on the New Standby

Mount the old primary.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1543502248 bytes
Fixed Size                  8939944 bytes
Variable Size            1056964608 bytes
Database Buffers          469762048 bytes
Redo Buffers                7835648 bytes
Database mounted.
Disable remote archive shipping temporarily.
SQL> alter system set log_archive_dest_state_2=DEFER scope=both;

System altered.
Start Managed Recovery Process.
SQL> alter database recover managed standby database disconnect from session;

Database altered.
Verify recovery.
SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
DGRD      ALLOCATED             0
DGRD      ALLOCATED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                 64
RFS       IDLE                  0
DGRD      ALLOCATED             0
MRP0      APPLYING_LOG         64

11 rows selected.
Ensure the MRP0 process is in APPLYING_LOG status.

On New Primary Database:

Step 8: Validate Archive Apply

Generate new archive logs from the new primary.
SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.
Verify latest archive.
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            66

On New Standby Database:

Verify latest archive.
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            66
Both sequence numbers should match, confirming that redo transport and apply are functioning correctly.

Switchback to the Original Primary (Step-by-Step)

On New Primary Database:

Step 1 – Verify Roles

Confirm the new primary is ready.
SQL> select name, open_mode, database_role, log_mode, switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------ --------------------
ORCL      READ WRITE           PRIMARY          ARCHIVELOG   TO STANDBY
Check Invalid Objects Again.
SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         7

Step 2: Backup Critical Configuration

Backup the control file.
SQL> alter database backup controlfile to trace as '/backup/ctl_trace.bkp';

Database altered.
Backup the SPFILE.
SQL> create pfile='/backup/pfile_backup.ora' from spfile;

File created.

Step 3: Force Archive Log Switch

Generate fresh archive logs.
SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.
Verify the latest archive sequence.
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            70

On New Standby Database:

Step 4: Verify New Standby Synchronization

SQL> select name, open_mode, database_role, log_mode, switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------ --------------------
ORCL      MOUNTED              PHYSICAL STANDBY ARCHIVELOG   NOT ALLOWED
Verify applied archive logs on new standby database.
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            70
Verify that the standby has applied the latest sequence before switching back.

On New Primary Database:

Step 5: Perform Switchback

Run verification.
SQL> alter database switchover to ORCL verify;

Database altered.
Execute switchback.
SQL> alter database switchover to ORCL;

Database altered.

On Original Primary Database:

Step 6: Open the Original Primary

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1543502248 bytes
Fixed Size                  8939944 bytes
Variable Size            1056964608 bytes
Database Buffers          469762048 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.
Enable redo transport.
SQL> alter system set log_archive_dest_state_2=ENABLE scope=both;

System altered.
Verify that the database is once again the primary.
SQL> select name, open_mode, database_role, log_mode, switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------ --------------------
ORCL      READ WRITE           PRIMARY          ARCHIVELOG   FAILED DESTINATION

On Original Standby Database:

Step 7: Start Recovery on the Standby

Mount the standby.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1543502248 bytes
Fixed Size                  8939944 bytes
Variable Size            1073741824 bytes
Database Buffers          452984832 bytes
Redo Buffers                7835648 bytes
Database mounted.
Disable remote archive shipping.
SQL> alter system set log_archive_dest_state_2=DEFER scope=both;

System altered.
Start managed recovery process.
SQL> alter database recover managed standby database disconnect from session;

Database altered.
Verify recovery.
SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
DGRD      ALLOCATED             0
ARCH      CONNECTED             0
DGRD      ALLOCATED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                 73
RFS       IDLE                  0
DGRD      ALLOCATED             0
MRP0      APPLYING_LOG         73

37 rows selected.
Ensure the MRP0 process is in APPLYING_LOG status.

On Original Primary Database:

Step 8: Final Validation

Generate a few archive logs from the primary.
SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.
Verify latest archive.
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            75

On Original Standby Database:

Verify latest archive.
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            75
Matching archive sequence numbers confirm that redo transport and recovery are working correctly after the switchback.

Post DR Drill Validation Checklist

  • Database roles are correct
  • Applications connect successfully
  • Archive logs are shipping without errors
  • MRP is running
  • No archive gap
  • Invalid object count unchanged
  • Alert logs reviewed
  • Data synchronization verified

Best Practices

  • Always perform ALTER DATABASE SWITCHOVER ... VERIFY before the actual switchover.
  • Take SPFILE and control file backups before role transitions.
  • Confirm there is no archive gap before initiating the DR drill.
  • Validate application functionality after each role transition.
  • Monitor the standby until all generated archive logs have been applied.
Conclusion

A successful Oracle Data Guard DR Drill is more than just executing switchover commands it confirms that your disaster recovery environment can seamlessly assume production responsibilities and return to its original state without data loss. By validating synchronization, performing controlled role transitions, and verifying redo transport after both the switchover and switchback, you can ensure your Oracle Data Guard environment is ready for real-world failover scenarios.

Post a Comment

© ‧ Ayaan Israr - All rights reserved. Premium By Ayaan Israr