The Ora DBAPractical Oracle Database Administration Knowledge & Solutions

Saturday, August 29, 2026

Oracle Data Guard Failover: Step-by-Step Procedure to Activate a Physical Standby Database

Oracle DBA Data Guard Failover Physical Standby Oracle 19c Disaster Recovery High Availability

Oracle Data Guard provides a physical standby database that can be used to protect a production database from failures and disasters. When the primary database becomes unavailable or unusable, the standby database can be transitioned into the PRIMARY role through a Data Guard failover.

Unlike a switchover, which is a planned exchange of roles between a healthy primary and standby, a failover is generally performed during an unplanned outage or disaster. The objective is to restore database availability as quickly as possible by activating the standby database as the new primary.

Important: Failover should be treated as a disaster-recovery operation. Depending on the condition of the failed primary and the redo available on the standby, some transactions may not have reached the standby before the failure. Always determine the recoverable redo position and understand the possible data-loss scenario before performing a failover.

Environment Used in This Guide

The procedure below is based on an Oracle 19c Data Guard environment with a primary database and a physical standby database.

Component Current Primary Current Standby
Host production-vm standby-vm
IP Address 192.168.56.101 192.168.56.105
DB_NAME PROD PROD
DB_UNIQUE_NAME PROD PROD_STBY
ORACLE_SID PROD PROD
Database Role PRIMARY PHYSICAL STANDBY
Open Mode READ WRITE READ ONLY WITH APPLY
Oracle Version 19.28 19.28
DB_NAME vs DB_UNIQUE_NAME: Both databases use the same DB_NAME, while the DB_UNIQUE_NAME uniquely identifies each Data Guard member. In this environment, the primary is PROD and the standby is PROD_STBY.

What Happens During a Failover?

Before the failure, the environment normally looks like this:

Server DB_UNIQUE_NAME Role Open Mode
production-vm PROD PRIMARY READ WRITE
standby-vm PROD_STBY PHYSICAL STANDBY READ ONLY WITH APPLY

After failover, the standby becomes the new primary:

Server DB_UNIQUE_NAME New Role Open Mode
standby-vm PROD_STBY PRIMARY READ WRITE
production-vm PROD Failed / Former Primary Unavailable or Unknown
Important: During a failover, the original primary is not automatically converted back into a standby simply because the standby has been activated. After the emergency role transition, the former primary normally requires a separate reinstatement procedure before it can participate as a standby again.

Prerequisites Before Failover

Failover is normally initiated because the primary database is unavailable. However, if the primary is still accessible, perform as many checks as the incident permits before activating the standby.

  • Confirm that the primary database is genuinely unavailable or cannot be restored within the required recovery time.
  • Confirm that the standby database is healthy and accessible.
  • Confirm the current database role and open mode on the standby.
  • Check the latest redo received and applied by the standby.
  • Determine whether there is any transport or apply lag.
  • Check the standby alert log for Data Guard errors.
  • Verify that Redo Apply is running or determine whether recovery needs to be completed before activation.
  • Identify the last redo sequence successfully applied by the standby.
  • Assess the potential data-loss exposure before activating the standby.
  • Stop or isolate application traffic from the failed primary if it can still accept connections.
Do not activate the standby simply because the primary is slow. Failover changes the Data Guard role of the standby and can create a split-brain situation if the old primary later becomes available while applications are still allowed to write to it.

Step 1: Check the Standby Database Role

Connect to the standby server and verify that it is operating as a physical standby. 

SQL> SELECT
         NAME,
         DB_UNIQUE_NAME,
         DATABASE_ROLE,
         OPEN_MODE,
         LOG_MODE
     FROM V$DATABASE;
Example Output
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ------------------------------ ---------------- -------------------- ------------
PROD      PROD_STBY                      PHYSICAL STANDBY READ ONLY WITH APPLY ARCHIVELOG

The output confirms that PROD_STBY is currently operating as a physical standby and is open read-only while redo apply is active.

Step 2: Check Redo Apply Status

Before activating the standby, check the Data Guard processes. 

SQL> SELECT
         PROCESS,
         CLIENT_PROCESS,
         THREAD#,
         SEQUENCE#,
         STATUS
     FROM V$MANAGED_STANDBY
     ORDER BY PROCESS;
Example Output
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
DGRD      N/A               0 ALLOCATED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
DGRD      N/A               0 ALLOCATED
RFS       LGWR            179 IDLE
RFS       Archival          0 IDLE
RFS       UNKNOWN           0 IDLE
MRP0      N/A             179 APPLYING_LOG

The important processes are RFS and MRP0. RFS receives redo transported from the primary, while MRP0 applies redo to the physical standby.

Note: The exact sequence numbers will differ between environments. Do not use the sequence number shown in this example as a fixed value.

Step 3: Check Redo Gap on the Standby

The next step is to determine how much redo has been received and applied. This helps establish the standby's recovery position before failover. 

SQL> SET LINES 200 PAGES 300

SQL> ALTER SESSION SET NLS_DATE_FORMAT =
     'DD-MON-YYYY HH24:MI:SS';

SQL> SELECT
         d.db_unique_name,
         d.database_role,
         a.thread#,
         b.last_seq,
         a.applied_seq,
         b.last_seq - a.applied_seq ARC_DIFF,
         a.last_app_timestamp,
         ROUND((SYSDATE-a.last_app_timestamp)*24*60,2)
             GAP_IN_MINS,
         ROUND((SYSDATE-a.last_app_timestamp)*24*60*60,2)
             GAP_IN_SECONDS
     FROM
     (SELECT thread#,
             MAX(sequence#) applied_seq,
             MAX(next_time) last_app_timestamp
      FROM gv$archived_log
      WHERE REGISTRAR='RFS'
        AND applied='YES'
      GROUP BY thread#) a,
     (SELECT thread#,
             MAX(sequence#) last_seq
      FROM gv$archived_log
      GROUP BY thread#) b,
     (SELECT db_unique_name,
             database_role
      FROM v$database) d
     WHERE a.thread#=b.thread#;

A result such as ARC_DIFF=0 indicates that, for the sequences represented by this check, the latest registered redo is also applied.

Best Case: If the standby is fully synchronized and the failed primary generated no additional redo that is unavailable to the standby, the potential data loss may be minimal. Always verify the actual environment before proceeding.
Important: A zero ARC_DIFF on the standby does not by itself prove that every transaction committed on the failed primary was received by the standby. Redo transport and commit timing must also be considered.

Step 4: Check the Last Applied Redo

It is useful to identify the latest sequence that the standby has successfully applied. 

SQL> SELECT
         THREAD#,
         MAX(SEQUENCE#) LAST_APPLIED_SEQUENCE
     FROM V$ARCHIVED_LOG
     WHERE REGISTRAR='RFS'
       AND APPLIED='YES'
     GROUP BY THREAD#;
Example Output
THREAD#  LAST_APPLIED_SEQUENCE
--------  ---------------------
       1                    178

This provides a reference point for the standby's recovery position.

Step 5: Check the Standby Alert Log

Before activating the standby, check the alert log for recovery errors, transport issues, or other Data Guard messages. 

[oracle@standby-vm ~]$ tail -f \
$ORACLE_BASE/diag/rdbms/prod_stby/PROD/trace/alert_PROD.log

Look for messages related to:

  • MRP0
  • RFS
  • redo apply
  • media recovery
  • Data Guard errors
  • missing archived logs
  • sequence gaps
  • database activation
Note: The exact alert-log path depends on the Oracle installation, database name, and diagnostic destination. Always use the actual path for your environment.

Step 6: Stop Redo Apply

Once the decision has been made to fail over to the standby, stop managed recovery before completing the role transition. 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Output
Database altered.

The managed recovery process is now stopped.

Step 7: Complete Standby Recovery

If additional redo is available locally on the standby, recovery can be completed before activation. This allows the standby to apply recoverable redo before it becomes the new primary. 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Output
Database altered.

If recovery can be completed successfully, the standby has applied the available redo before the role transition.

Important: If the failed primary cannot provide additional redo, the standby can only recover the redo that has already reached the standby or is otherwise available to it. Do not assume that transactions committed on the failed primary are present on the standby.

Step 8: Activate the Standby Database

After completing the recovery that is possible on the standby, activate the physical standby as the new primary. 

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Output
Database altered.

The standby database has now transitioned from the PHYSICAL STANDBY role to the PRIMARY role.

Step 9: Verify the New Primary Role

After activation, immediately verify the database role and open mode. 

SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
Output After Activation
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ------------------------------ ---------------- -------------------- ------------
PROD      PROD_STBY                      PRIMARY          MOUNTED              ARCHIVELOG

The database is now a PRIMARY, but it may initially be mounted. It must be opened before applications can use it.

Important: The DB_UNIQUE_NAME remains PROD_STBY. The database role changes from PHYSICAL STANDBY to PRIMARY; the DB_UNIQUE_NAME does not automatically change to PROD.

Step 10: Open the New Primary

Open the activated database. 

SQL> ALTER DATABASE OPEN;
Output
Database altered.

Verify the final state: 

SQL> SELECT
         NAME,
         DB_UNIQUE_NAME,
         DATABASE_ROLE,
         OPEN_MODE,
         LOG_MODE
     FROM V$DATABASE;
Output
NAME      DB_UNIQUE_NAME  DATABASE_ROLE  OPEN_MODE   LOG_MODE
--------- --------------- -------------- ----------- ------------
PROD      PROD_STBY       PRIMARY        READ WRITE  ARCHIVELOG
Failover Completed: The former physical standby PROD_STBY has successfully become the new primary database and is open in READ WRITE mode.

Step 11: Verify PDB Status

If the environment is a multitenant database, verify that the required PDBs are open after the failover. 

SQL> SHOW PDBS;
Example Output
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PRODPDB                        READ WRITE NO

The required application PDBs should be opened according to the application's normal startup procedure.

Step 12: Verify the New Primary Is Generating Redo

Once the standby has become the new primary, generate or verify new redo so that the database can be tested as the production system. 

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 13: Verify Database Services and Application Connectivity

The database role change does not automatically guarantee that applications will connect to the new primary. Verify the services and connectivity required by the application.

  • Verify database services.
  • Verify listener status.
  • Verify application connection strings or aliases.
  • Verify connection pools.
  • Verify application users can connect.
  • Verify critical application transactions.
  • Verify scheduled jobs that should run on the primary.
SQL> SELECT
         NAME,
         DATABASE_ROLE,
         OPEN_MODE
     FROM V$DATABASE;
Application Validation: Do not consider the disaster-recovery operation complete merely because DATABASE_ROLE=PRIMARY. Application connectivity and critical business functionality must also be validated.

Step 14: Check the Former Primary

After the standby has been activated, the original primary should remain isolated until it is confirmed safe to use.

Split-Brain Warning: Never allow the old primary and the newly activated primary to independently accept application writes at the same time. The old primary must be isolated, shut down, or otherwise prevented from serving production traffic until the DR situation is fully resolved.

The former primary will normally require a separate recovery/reinstatement procedure before it can be converted into a physical standby for the new primary.

Step 15: Re-establish Data Guard Protection

After the emergency has been handled and the new primary is stable, the next objective should be to restore Data Guard protection.

The former primary can potentially be reinstated as the standby using an appropriate Oracle Data Guard reinstatement procedure, depending on the database version, Flashback configuration, and condition of the old primary.

The exact reinstatement procedure is a separate operation and should not be confused with the activation of the standby.

Failover vs Switchover

Item Switchover Failover
Purpose Planned role transition Emergency/disaster role transition
Primary available? Normally yes Normally unavailable or unusable
Data synchronization Should be fully verified before transition Depends on the condition of the failed primary and standby
Possible data loss Normally designed to avoid data loss Possible, depending on redo received/applied
Former primary Normally becomes standby Normally requires reinstatement
Typical use Maintenance, DR testing, planned migration Production outage, disaster recovery

Important Checks After Failover

  • Verify DATABASE_ROLE=PRIMARY.
  • Verify OPEN_MODE=READ WRITE.
  • Verify the required PDBs are open.
  • Verify database services.
  • Verify listener connectivity.
  • Verify application connectivity.
  • Verify critical application transactions.
  • Check the alert log for errors.
  • Check database and application jobs.
  • Verify that the old primary is isolated.
  • Determine the final redo/data-loss position.
  • Plan reinstatement of the former primary.
  • Re-establish Data Guard protection as soon as practical.

Final Database State

After a successful failover, the expected database state is:

Server DB_UNIQUE_NAME Role Open Mode
standby-vm PROD_STBY PRIMARY READ WRITE
production-vm PROD Former Primary / Failed Isolated
Failover Successfully Completed: The physical standby PROD_STBY has been activated and is now serving as the new primary database. The next priority is to validate application connectivity, keep the old primary isolated, and restore Data Guard protection by reinstating the former primary when appropriate.

Summary

Oracle Data Guard failover is an emergency role transition used when the primary database is unavailable or cannot provide service within the required recovery window.

The most important part of failover is not simply executing ALTER DATABASE ACTIVATE STANDBY DATABASE. Before activation, the DBA should determine the standby's recovery position, understand the potential data-loss exposure, verify Redo Apply, and ensure that the failed primary cannot continue serving application traffic.

After activation, the new primary must be opened read-write and validated from both the database and application perspective. The former primary should then be isolated and subsequently reinstated as a standby when the environment is stable.

Key Takeaway: Switchover is a planned role exchange. Failover is an emergency recovery operation. In a failover, protecting data integrity and preventing split-brain are just as important as bringing the new primary online quickly.
at
Labels: ,