oraclednaThe process of restoring an old primary database as a standby database following a failover without starting from scratch is known as reinstating a failed primary database into a physical standby in Oracle Data Guard. This necessitates that the old primary had the Flashback Database enabled prior to its failure.
Prerequisites for Reinstate Failed Primary Database:
1. Before the failover, the database had to have Flashback Database enabled.
2. The failed primary database must have enough flashback logs in that to flashback up to the SCN when standby becomes the primary.
3. The failed primary must be a part of Dataguard configuration.
1. Find the SCN at which the old standby database became the primary database.
Execute the below SQL in new primary database (old standby database)
SQL> SELECT NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
———- ————— ———- ——————
PROD stdby READ WRITE PRIMARY
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
—————————————-
2278061
2. Flashback the Failed Primary Database to SCN standby_became_primary_scn.
Mount the failed primary database.
SQL> startup mount
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.
SQL> SELECT NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
——— —————————— ——————– —————-
PROD prod MOUNTED PRIMARY
3. Flashback the Failed Primary Database to SCN STANDBY_BECAME_PRIMARY_SCN of step 1.
SQL> FLASHBACK DATABASE TO SCN 2278061;
Flashback complete.
Alert log of failed primary database:
==========================
FLASHBACK DATABASE TO SCN 2278061
2026-03-08T07:16:17.079417+05:30
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
2026-03-08T07:16:17.677157+05:30
Serial Media Recovery started
Flashback Media Recovery Log /u01/arch/prod/76_1_1196677442.arc
Flashback Media Recovery Log /u01/arch/prod/77_1_1196677442.arc
Flashback Media Recovery Log /u01/arch/prod/78_1_1196677442.arc
Flashback Media Recovery Log /u01/arch/prod/79_1_1196677442.arc
Flashback Media Recovery Log /u01/arch/prod/80_1_1196677442.arc
Flashback Media Recovery Log /u01/arch/prod/81_1_1196677442.arc
Flashback Media Recovery Log /u01/arch/prod/82_1_1196677442.arc
2026-03-08T07:16:18.063264+05:30
Incomplete Recovery applied until change 2278062 time 03/08/2026 05:39:16
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 2278061
4. Convert the failed primary database to a physical standby database and restart database in mount stage.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> SELECT NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
——— —————————— ——————– —————-
PROD prod MOUNTED PHYSICAL STANDBY
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
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.
Alert log of new standby database:
=========================
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
2026-03-08T07:17:51.766425+05:30
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (prod)
2026-03-08T07:17:51.814339+05:30
Clearing standby activation ID 608653432 (0x24475078)
The primary database controlfile was created using the
‘MAXLOGFILES 16’ clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 209715200;
…. (PID:6099): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8842]
…. (PID:6099): RT: Role transition work is not done
…. (PID:6099): Redo network throttle feature is disabled at mount time
Physical Standby Database mounted.
2026-03-08T07:17:51.851687+05:30
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete – Database mounted as physical standby
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY
5. Verify the redo transport configuration and correct the configuration if necessary
On Primary:
==========
SQL> ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH;
System altered.
SQL> show parameter LOG_ARCHIVE_DEST
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest string
log_archive_dest_1 string LOCATION=/u01/arch/stdby VALID
_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=stdby
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=PROD LGWR ASYNC VALID_
FOR=(ONLINE_LOGFILES,PRIMARY_R
OLE) DB_UNIQUE_NAME=prod
On Standby:
=========
SQL> show parameter LOG_ARCHIVE_DEST
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest string
log_archive_dest_1 string LOCATION=/u01/arch/prod VALID_
FOR=(ALL_LOGFILES,ALL_ROLES) D
B_UNIQUE_NAME=prod
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=STDBY LGWR ASYNC VALID
_FOR=(ONLINE_LOGFILES,PRIMARY_
ROLE) DB_UNIQUE_NAME=stdby
6. Check whether redo transport is started to standby database or not.
Execute below SQL in primary database.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
DEST_ID STATUS ERROR
———- ——— —————————————————————–
2 VALID
7. Start the managed recovery process in standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
8. Check the archive log applied status on Standby database.
ON Primary:
=========
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch/stdby
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
On Standby:
=========
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 9 9 0
The above result showing that the primary and standby databases are in sync.
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.
