Performing a manual DR drill in Oracle Data Guard is an important activity to ensure your disaster recovery setup is working as expected. In this blog, you will learn step-by-step Oracle Data Guard manual DR drill steps, including required pre-checks, switchover process, and post-validation tasks. This guide is written in simple language to help DBAs confidently test their DR environment without data loss and ensure business continuity.
Oracle Data Guard Manual Switchover Steps
Pre-checks:
Before initiating the activity, take a full RMAN backup of the database to ensure data protection and recovery.
On Primary Database:
Check the primary database status and archive sequence details.
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
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
39
On Standby Database:
Check the standby database status and archive sequence details.
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
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
39
On Primary Database:
Verify Archive Log Destination Status on the Primary Database.
SQL> SELECT DEST_ID, DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST;
DEST_ID DEST_NAME STATUS ERROR
---------- ----------------------- --------- -------------------------------------
1 LOG_ARCHIVE_DEST_1 VALID
2 LOG_ARCHIVE_DEST_2 VALID
3 LOG_ARCHIVE_DEST_3 INACTIVE
4 LOG_ARCHIVE_DEST_4 INACTIVE
5 LOG_ARCHIVE_DEST_5 INACTIVE
6 LOG_ARCHIVE_DEST_6 INACTIVE
7 LOG_ARCHIVE_DEST_7 INACTIVE
8 LOG_ARCHIVE_DEST_8 INACTIVE
9 LOG_ARCHIVE_DEST_9 INACTIVE
10 LOG_ARCHIVE_DEST_10 INACTIVE
11 LOG_ARCHIVE_DEST_11 INACTIVE
12 LOG_ARCHIVE_DEST_12 INACTIVE
13 LOG_ARCHIVE_DEST_13 INACTIVE
14 LOG_ARCHIVE_DEST_14 INACTIVE
15 LOG_ARCHIVE_DEST_15 INACTIVE
16 LOG_ARCHIVE_DEST_16 INACTIVE
17 LOG_ARCHIVE_DEST_17 INACTIVE
18 LOG_ARCHIVE_DEST_18 INACTIVE
19 LOG_ARCHIVE_DEST_19 INACTIVE
20 LOG_ARCHIVE_DEST_20 INACTIVE
21 LOG_ARCHIVE_DEST_21 INACTIVE
22 LOG_ARCHIVE_DEST_22 INACTIVE
23 LOG_ARCHIVE_DEST_23 INACTIVE
24 LOG_ARCHIVE_DEST_24 INACTIVE
25 LOG_ARCHIVE_DEST_25 INACTIVE
26 LOG_ARCHIVE_DEST_26 INACTIVE
27 LOG_ARCHIVE_DEST_27 INACTIVE
28 LOG_ARCHIVE_DEST_28 INACTIVE
29 LOG_ARCHIVE_DEST_29 INACTIVE
30 LOG_ARCHIVE_DEST_30 INACTIVE
31 LOG_ARCHIVE_DEST_31 INACTIVE
31 rows selected.
Note the values of job queue processes and invalid database objects.
SQL> show parameter job_queue_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 40
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
Take a control file backup as trace and create a PFILE from the SPFILE.
SQL> alter database backup controlfile to trace as '/home/app/oracle/ctl_trace.bkp'; Database altered. SQL> create pfile='/home/app/oracle/pfile_backup.ora' from spfile; File created.Switch the log before initiating the activity and verify database sync again.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
43
On Standby Database:
Check the applied sequence on the standby database and ensure it matches the primary database.SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 43
Switchover Steps:
On Primary Database:
Set the job_queue_processes parameter to 0 before starting the switchover.SQL> alter system set job_queue_processes=0 scope=both; System altered.First, verify that the primary database is ready for switchover. Once verification is successful, initiate the switchover and convert the primary database to a standby role.
SQL> alter database switchover to ORCLDR verify; Database altered. SQL> alter database commit to switchover to standby with session shutdown nowait; Database altered.Restart the database and mount it as a standby database.
SQL> shut immediate; ORA-01012: not logged on SQL> SQL> startup nomount; 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 SQL> SQL> alter database mount standby database; Database altered. 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 RECOVERY NEEDEDDefer the log_archive_dest_state_2 parameter and start the Managed Recovery Process (MRP).
SQL> alter system set log_archive_dest_state_2='DEFER'; System altered. SQL> alter database recover managed standby database disconnect from session; Database altered.
On Standby Database:
Stop MRP, convert the standby to primary, bounce the database, and enable log_archive_dest_state_2.SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database commit to switchover to primary with session shutdown nowait; Database altered. SQL> shutdown 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. SQL> 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 SQL> alter system set log_archive_dest_state_2='ENABLE'; System altered.
On Primary (New Standby) Database:
Check that MRP and RFS are active on the new standby database.SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 DGRD ALLOCATED 0 DGRD ALLOCATED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 DGRD ALLOCATED 0 ARCH CONNECTED 0 MRP0 APPLYING_LOG 49 RFS IDLE 0 RFS IDLE 49 RFS IDLE 0
Oracle Data Guard Manual Switchback Steps
Pre-checks:
On Current Primary Database:
Check the database status and archive sequence details.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
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
48
On Current Standby Database:
Check the database status and archive sequence details.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
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
48
Take a control file backup as trace and create a PFILE from the SPFILE.
SQL> alter database backup controlfile to trace as '/home/app/oracle/ctl_trace.bkp'; Database altered. SQL> create pfile='/home/app/oracle/pfile_backup.ora' from spfile; File created.
Switchback Steps:
On Current Primary Database:
Verify the switchover status, perform the switchover, then restart the database and bring it up in mount state.
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> alter database commit to switchover to physical standby with session shutdown nowait; Database altered. SQL> shut immediate; ORA-01012: not logged on SQL> 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. SQL> 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 RECOVERY NEEDED
On Current Standby Database:
Stop MRP, convert the current standby to primary and bounce the database.
SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database commit to switchover to primary with session shutdown nowait; Database altered. SQL> shutdown 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 1056964608 bytes Database Buffers 469762048 bytes Redo Buffers 7835648 bytes Database mounted. Database opened. SQL> 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 NOT ALLOWEDEnable the log_archive_dest_state_2 parameter and restore the job_queue_processes parameter to its previously noted value.
SQL> alter system set log_archive_dest_state_2=ENABLE scope=both; System altered. SQL> ALTER SYSTEM SET job_queue_processes=40 SCOPE=BOTH; System altered.
On New Standby Database:
Defer log_archive_dest_state_2, start MRP, and verify MRP and RFS on the new standby database.
SQL> alter system set log_archive_dest_state_2=DEFER scope=both; System altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------------- ------------ ---------- ---------- ---------- ---------- ARCH CONNECTED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 RFS IDLE 1 0 0 0 RFS IDLE 1 50 77289 1 MRP0 APPLYING_LOG 1 50 77289 409600 35 rows selected.