Step‑by‑Step Oracle Data Guard Switchover and Failover Guide

Overview

Oracle Data Guard provides two role‑change operations: a loss‑less switchover when the primary database is healthy, and a failover for disaster recovery where data loss may occur. The following procedure demonstrates a switchover test in a correctly configured Data Guard environment.

Prerequisites

Data Guard configuration must be set up according to the “Oracle Data Guard Zero‑Downtime Configuration” guide.

Both primary (dg1) and standby (dg2) databases must be open and synchronized.

SQL*Plus or equivalent client with SYSDBA privileges.

Switchover Steps

1. Primary database preparation

Check switchover status SQL> SELECT switchover_status FROM v$database; The column returns TO STANDBY when the primary can be converted.

Convert primary to standby role

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

After execution the status becomes RECOVERY NEEDED.

Restart the instance in MOUNT mode

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

MOUNT mode is required for the standby to accept the role change.

2. Standby (new primary) actions

Verify standby status SQL> SELECT switchover_status FROM v$database; Convert standby to primary role

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Open the new primary database

SQL> ALTER DATABASE OPEN;

3. Confirm the role change

SQL> SELECT name,
       open_mode,
       database_role,
       protection_mode,
       switchover_status
  FROM v$database;

The query should show the former standby as PRIMARY and the former primary as PHYSICAL STANDBY (or MOUNT if not yet opened).

4. Convert the original primary back to standby

Apply any remaining redo logs

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
       USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Cancel managed recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database SQL> ALTER DATABASE OPEN; Re‑apply redo logs while the database is open

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
       USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Verification Test

Create a test table on the original primary (dg1)

SQL> CREATE TABLE test (id NUMBER);
SQL> INSERT INTO test VALUES (1);
SQL> COMMIT;

Query the table on the new primary (dg2) SQL> SELECT * FROM test; The row (1) should be returned, confirming that redo has been applied and the databases are synchronized.

Illustrative Screenshots