Skip to main content

Failover to Standby

  • 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. However, 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 methods such as RMAN Duplicate.

  • Prerequisites:
    • Oracle Data Guard should be configured.
    • We consider here that Primary is completely lost.
  • Environment:
    • 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

  • Now proceed to Failover activity step by step:

Please note that Failover (Activate Standby) is destructive and only used when Primary is completly lost. Kindly use below steps as per your environment and not used on Production for testing purpose.

1. Check Standby database status.

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>
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,
SQL> 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#;


SQL>  

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
2. Now cancel & finish MRP on Standby. 

 SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database finish;

Database altered.

SQL>
3. Now activate the standby. 

 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>
4. Now take a fresh bounce of the database. 

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>
6. Now take full database backup using below RMAN script.

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;
}


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

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

Follow Us

Comments

Post a Comment

↑