Introduction
Failover occurs when the primary database is completely lost or becomes inaccessible. In such events, the standby database is promoted to serve as the new primary to ensure service continuity. The original primary does not automatically transition to a standby role, particularly if it is damaged or unrecoverable. If Flashback Database is not enabled on the original primary, it becomes necessary to re-create the primary database from scratch, using a method such as RMAN Duplicate.
Prerequisites
- Oracle Data Guard should already be configured between the primary and standby.
- This guide assumes the primary database is completely lost.
Environment Used in This Guide
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
Please note that failover (activating the standby) is destructive and should only be used when the primary is completely lost. Use these steps as appropriate for your own environment, and never run this against production purely for testing.
Step 1: Check the Standby Database Status
Before doing anything destructive, confirm the standby is in a healthy state and check how far behind it is from the primary's last known sequence. A small or zero apply gap gives confidence that failing over won't lose much data.
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- ------------------------------ ---------------- --------------- ------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED ARCHIVELOG
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0 APPLYING_LOG 1 284 868
SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
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#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR PHYSICAL STANDBY 1 283 283 0 04-MAY-2025 21:29:47 2335.62 140137
Step 2: Cancel and Finish Managed Recovery on the Standby
Stop redo apply and let the standby finish applying anything already in progress before activating it. Activating a standby while managed recovery is still running can leave the database in an inconsistent state.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database finish;
Database altered.
SQL>
Step 3: Activate the Standby
This is the step that actually promotes the standby to the primary role. Once activated, there's no automatic path back, so this should only be done when the original primary is confirmed lost.
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- ------------------------------ ---------------- --------------- ------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED ARCHIVELOG
SQL> alter database activate standby database;
Database altered.
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- ------------------------------ ---------------- --------------- ------------
ORCL ORCLDR PRIMARY MOUNTED ARCHIVELOG
SQL>
Step 4: Bounce the Database
A fresh restart brings the newly promoted primary fully online in READ WRITE mode, ready to serve normal application traffic.
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- ------------------------------ ---------------- --------------- ------------
ORCL ORCLDR PRIMARY MOUNTED ARCHIVELOG
SQL>
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 1224736768 bytes
Database Buffers 218103808 bytes
Redo Buffers 7598080 bytes
Database mounted.
Database opened.
SQL>
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- ------------------------------ ---------------- --------------- ------------
ORCL ORCLDR PRIMARY READ WRITE ARCHIVELOG
SQL>
Step 5: Take a Full Database Backup
With the new primary open and serving traffic, take an immediate full backup. There's no standby protecting this database anymore, so this backup is your only safety net until a new standby is built.
run
{
allocate channel ch1 device type disk;
backup as compressed backupset database format '/u01/app/oracle/rmanbkp/Fullback_%T_%U';
backup as compressed backupset archivelog all format '/u01/app/oracle/rmanbkp/Archive_%T_%U';
backup current controlfile format '/u01/app/oracle/rmanbkp/Controlback_%T_%U';
release channel ch1;
}
The former standby is now running as the primary, fully open, and backed up. If the original primary is ever recovered, it can no longer resume as primary without being rebuilt from scratch and re-instantiated as a new standby, using a method such as RMAN Duplicate.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment