oraclednaThis post demonstrates the manual failover of physical standby database when primary database is completely unavailable and having gap on standby database.
When the primary database is unavailable and there is no chance of recovering it, a failover can be performed. After the failover, the standby database will transition to a primary database. This will allow business continuity.
Note: Fail over may lead to loss of data if the standby database has not received all redo data from the primary.
1. Cancel the managed recovery process if it is running.
SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
———- ———- —————— ——————–
PROD MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> select process,thread#,sequence#,status,pid,delay_mins from V$managed_standby where process like ‘%MRP%’;
PROCESS THREAD# SEQUENCE# STATUS PID DELAY_MINS
——— ———- ———- ———— ———————— ———-
MRP0 1 83 WAIT_FOR_LOG 3765 0
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
2. Perform the failover by activating the standby database to primary database.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
———- ———- —————— ——————–
PROD MOUNTED PRIMARY MAXIMUM PERFORMANCE
Alert log of Standby database:
======================
ALTER DATABASE ACTIVATE STANDBY DATABASE
2026-03-08T05:54:18.377869+05:30
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE [Process Id: 3429] (stdby)
2026-03-08T05:54:18.384651+05:30
…. (PID:3429): Begin: SRL archival
…. (PID:3429): End: SRL archival
RESETLOGS after incomplete recovery UNTIL CHANGE 2278063 time 03/08/2026 05:39:16
Resetting resetlogs activation ID 608653432 (0x24475078)
Online log /u01/app/oracle/oradata/STDBY/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/STDBY/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/STDBY/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2278061
2026-03-08T05:54:18.641449+05:30
Setting recovery target incarnation to 3
2026-03-08T05:54:18.743012+05:30
…. (PID:3429): RT: Role transition work is not done
…. (PID:3429): The Time Management Interface (TMI) is being enabled for role transition
…. (PID:3429): information. This will result in messages beingoutput to the alert log
…. (PID:3429): file with the prefix ‘TMI: ‘. This is being enabled to make the timing of
…. (PID:3429): the various stages of the role transition available for diagnostic purposes.
…. (PID:3429): This output will end when the role transition is complete.
…. (PID:3429): Redo network throttle feature is disabled at mount time
2026-03-08T05:54:18.854868+05:30
…. (PID:3429): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
ACTIVATE STANDBY: Complete – Database mounted as primary
Completed: ALTER DATABASE ACTIVATE STANDBY DATABASE
3. Shutdown the database, start the database, verify the database role and protection mode.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2113927120 bytes
Fixed Size 8898512 bytes
Variable Size 486539264 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
———- ———- —————— ——————–
PROD READ WRITE PRIMARY MAXIMUM PERFORMANCE
Adjust the protection mode accordingly, if necessary.
4. Take the full backup of database.
Post failover, it is highly recommended to take a full backup of database immediately. Because, the “ALTER DATABASE ACTIVATE STANDBY DATABASE” command performs an OPEN RESETLOGS operation which invalidates the previous backups.
5. Build the new standby database.
If flashback enabled on failed primary, reinstate it to the standby database.
OR
Create the standby database by the new RMAN backup or by over the network method using duplicate method.
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
