Reinstating the Old Primary Database After Activating Physical Standby

📌 Scenario: Primary Database Site Outage Suppose there is a critical issue or complete outage at the Primary Database site, where the Primary Database is not accessible at all. The root cause could be anything—hardware failure, server crash, network outage, or storage issue. As a result, the entire application becomes unavailable. In such a situation, if a Physical Standby Database is already configured and available, you can activate the physical standby database to restore application services. Once activated, the application can run from the standby site until the Primary Database becomes accessible again. Activating Physical Standby Database in Oracle database will open the standby database in read-write mode. Once you activate the standby database, role reversable is not possible which means that you can not convert the activated database back into physical standby.  ❓ Key Consideration when the Original Primary Database Becomes Available : A common question arises: what happens if the original Primary Database becomes available after some time - whether after a few hours or even several days? This leads to further questions: - Do we need to completely rebuild the old Primary Database by taking a full database backup from the newly activated primary and restoring it on the old primary server to configure it as a new standby? - What if the database size is in terabytes (TBs) or even larger? - Will the customer agree if rebuilding the standby database takes several hours or longer? - Is there a way to reinstate the old Primary Database without rebuilding the entire database from scratch? These are critical concerns for DBAs, especially in large-scale production environments where downtime and data transfer windows are tightly constrained. 🔍 Let’s Simulate the Scenario : To answer these questions and explore the available options, let’s simulate this scenario step by step and understand the best possible approach in such cases.

Environment Details: Database : Oracle Database 19.0.0.0 Operating System : Red Hat Enterprise Linux release 8.10 #Let's check the sync status between Primary and Physical Standby Databases. On Primary: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,controlfile_type from v$database; NAME  DB_UNIQUE_NAME   OPEN_MODE    DATABASE_ROLE    CONTROL ----- ---------------- ------------ ---------------- ------- PR    PR               READ WRITE   PRIMARY          CURRENT SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) --------------            460 On Standby: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,controlfile_type from v$database; NAME  DB_UNIQUE_NAME   OPEN_MODE   DATABASE_ROLE    CONTROL ----- ---------------- ----------- ---------------- ------- PR    DR               MOUNTED     PHYSICAL STANDBY STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) --------------            460 You can see that both Primary and Physical Standby databases are in sync currently. #Let's abort the Primary database instance due test the scenario. On Primary: SQL> shut abort ORACLE instance shut down. SQL> #Now stop MRP on Physical Standby database before activating it. On Standby: SQL> recover managed standby database cancel; Media recovery complete. #Now Activate the Physical Standby database. On Standby: SQL> alter database activate standby database; Database altered. Standby Alert Logfile: 2025-12-25T15:59:52.699160+05:30 alter database activate standby database 2025-12-25T15:59:52.699239+05:30 ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE [Process Id: 10698] (DR) 2025-12-25T15:59:52.716700+05:30 .... (PID:10698): Begin: SRL archival .... (PID:10698): End: SRL archival 2025-12-25T15:59:52.749114+05:30 DBWPreopen slv 0 open list size=1 successful=0 DBWPreopen slv 1 open list size=1 successful=0 DBWPreopen slv 2 open list size=1 successful=0 DBWPreopen slv 3 open list size=1 successful=0 DBWPreopen total open count=4 shared=0 BG-shared=0 at t=0s 2025-12-25T15:59:52.752163+05:30 RESETLOGS after incomplete recovery UNTIL CHANGE 395533 time 12/25/2025 15:53:06 Resetting resetlogs activation ID 2915317892 (0xadc43884) Online log +RECO1/DR/ONLINELOG/group_1.11872.1220801623: Thread 1 Group 1 was previously cleared Online log +RECO2/DR/ONLINELOG/group_1.2232.1220801623: Thread 1 Group 1 was previously cleared Online log +RECO1/DR/ONLINELOG/group_2.11876.1220801623: Thread 1 Group 2 was previously cleared Online log +RECO2/DR/ONLINELOG/group_2.2231.1220801623: Thread 1 Group 2 was previously cleared Standby became primary SCN: 395531 2025-12-25T15:59:52.789367+05:30 Setting recovery target incarnation to 2 2025-12-25T15:59:52.799651+05:30 .... (PID:10698): RT_PMODE: PHYSICAL           mounting with mode MAXIMUM PERFORMANCE  level NO PROTECTION .... (PID:10698): RT: Role transition work is not done .... (PID:10698): The Time Management Interface (TMI) is being enabled for role transition .... (PID:10698): information.  This will result in messages beingoutput to the alert log .... (PID:10698): file with the prefix 'TMI: '.  This is being enabled to make the timing of .... (PID:10698): the various stages of the role transition available for diagnostic purposes. .... (PID:10698): This output will end when the role transition is complete. .... (PID:10698): Redo network throttle feature is disabled at mount time 2025-12-25T15:59:52.840111+05:30 .... (PID:10698): DBROLE: Activated    as a PRIMARY    at [kcvs.c:1168] ACTIVATE STANDBY: Complete - Database mounted as primary Completed: alter database activate standby database #Now open the activated Physical Standby database. On Standby: SQL> alter database open; Database altered. SQL> select name,db_unique_name,open_mode,database_role,controlfile_type from v$database; NAME  DB_UNIQUE_NAME   OPEN_MODE   DATABASE_ROLE  CONTROL ----- ---------------- ----------- -------------- ------- PR    DR               READ WRITE  PRIMARY        CURRENT SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            +RECO2 Oldest online log sequence     5 Next log sequence to archive   6 Current log sequence           6 Now your new Pirmary Dataase is activated standby DB. Let's switch few logs on newly Primary DB(activated database). SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. ...... SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) --------------            461 In this scenario, if you query the maximum SEQUENCE# from V$ARCHIVED_LOG, the result will display the old maximum sequence number, which is expected behavior. This occurs because the database has undergone a new incarnation after the standby database was activated. To retrieve the correct and current archive log sequence number for the newly activated standby (Now acting as Primary), you must check the database incarnation details using RMAN and include the current incarnation in your query against V$ARCHIVED_LOG. Connect to RMAN and check the incarnation. [oracle@testdbnode1 ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu Dec 25 16:03:00 2025 Version 19.26.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. connected to target database: PR (DBID=2915316868) RMAN> list incarnation; list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1       1       PR       2915316868       PARENT  1          25-DEC-25 2       2       PR       2915316868       CURRENT 395534     25-DEC-25 SQL> select max(sequence#) from v$archived_log where resetlogs_change#=395534; MAX(SEQUENCE#) --------------             20 Now, let’s assume that the original Primary Database has become available now after the outage. The next logical question is: - Is it possible to reinstate the old Primary Database? - If yes, how do we reinstate the old Primary Database without rebuilding it from scratch? Step 1: Create standby controlfile in newly activated database(New Primary). On New Primary: SQL> alter database create standby controlfile as '/home/oracle/stand.ctl'; Database altered.   Copy the above standby controlfile to old Primary DB server. Step 2: Start the database in nomount state (Old Primary). On Old Primary: [oracle@testdbnode1 ~]$ sqlplus / as sysdba SQL> def DEFINE _DATE           = "20251225" (CHAR) DEFINE _CONNECT_IDENTIFIER = "PR" (CHAR) DEFINE _USER           = "SYS" (CHAR) DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1926000000" (CHAR) DEFINE _EDITOR         = "vi" (CHAR) DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0" (CHAR) DEFINE _O_RELEASE      = "1926000000" (CHAR) DEFINE _RC             = "0" (CHAR) SQL> startup nomount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 5.3624E+10 bytes Fixed Size                 37352816 bytes Variable Size            1.0066E+10 bytes Database Buffers         4.2413E+10 bytes Redo Buffers             1107378176 bytes SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0 Connect to RMAN and restore the standby controlfile, mount the database. [oracle@testdbnode1 ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu Dec 25 16:13:12 2025 Version 19.26.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. connected to target database: PR (not mounted) RMAN> restore standby controlfile from '/home/oracle/stand.ctl'; restore standby controlfile from '/home/oracle/stand.ctl'; Starting restore at 25-DEC-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=649 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DATAC1/PR/CONTROLFILE/current.15123.1220800261 output file name=+RECO2/PR/CONTROLFILE/current.484.1220800261 Finished restore at 25-DEC-25 RMAN> RMAN> alter database mount standby database; alter database mount standby database; released channel: ORA_DISK_1 Statement processed Connect to DB and check the DB instance status after restoring standby controlfile. [oracle@testdbnode1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 25 16:14:31 2025 Version 19.26.0.0.0 Copyright (c) 1982, 2024, Oracle.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0 SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,controlfile_type from v$database; NAME  DB_UNIQUE_NAME  OPEN_MODE  DATABASE_ROLE    CONTROL ----- --------------- ---------- ---------------- ------- PR    PR              MOUNTED    PHYSICAL STANDBY STANDBY Now your old Primary role is changed to Physical Standby with standby controlfile.            Step 2: Now enable the log shipping destination from New Primary to Old Primary DB servers. New Primary(Activated): SQL> select max(sequence#) from v$archived_log where resetlogs_change#=395534; MAX(SEQUENCE#) --------------             25 Old Primary(Current Standby):   SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) --------------             25 Old Primary (Current Standby) Alert Log File: 2025-12-25T16:18:14.613595+05:30  rfs (PID:345778): krsr_rfs_atc: Identified database type as 'PHYSICAL': Client is ASYNC (PID:392075)  rfs (PID:345778): Enable RFS client [krsr.c:5795]  rfs (PID:345778): Disable RFS client [kcrlc.c:1531] 2025-12-25T16:19:12.632547+05:30 ALTER SYSTEM SET log_archive_config='dg_config=(PR,DR)' SCOPE=MEMORY; 2025-12-25T16:19:16.302367+05:30 ALTER SYSTEM SET fal_server='DR' SCOPE=MEMORY; 2025-12-25T16:19:23.819961+05:30  rfs (PID:362354): krsr_rfs_atc: Identified database type as 'PHYSICAL': Client is ASYNC (PID:392075) 2025-12-25T16:19:23.824887+05:30  rfs (PID:362356): krsr_rfs_atc: Identified database type as 'PHYSICAL': Client is Foreground (PID:371708) 2025-12-25T16:19:23.905884+05:30  rfs (PID:362354): No SRLs created 2025-12-25T16:19:23.968981+05:30  rfs (PID:362369): krsr_rfs_atc: Identified database type as 'PHYSICAL': Client is FAL (PID:371712) 2025-12-25T16:19:23.980995+05:30  rfs (PID:362354): Opened log for T-1.S-22 dbid 2915316868 branch 1220803192 2025-12-25T16:19:24.010200+05:30  rfs (PID:362369): Opened log for T-1.S-21 dbid 2915316868 branch 1220803192 2025-12-25T16:19:24.019630+05:30  rfs (PID:362369): Archived Log entry 1 added for B-1220803192.T-1.S-21 LOS:0x0000000000061038 NXS:0x00000000000611d2 NAB:118 ID 0xadc3d6c7 LAD:1 2025-12-25T16:20:27.977280+05:30  rfs (PID:362354): Archived Log entry 2 added for B-1220803192.T-1.S-22 LOS:0x00000000000611d2 NXS:0x00000000000612eb NAB:220 ID 0xadc3d6c7 LAD:1 2025-12-25T16:20:28.100735+05:30  rfs (PID:373856): krsr_rfs_atc: Identified database type as 'PHYSICAL': Client is ASYNC (PID:392073)  rfs (PID:373856): No SRLs created 2025-12-25T16:20:28.158283+05:30  rfs (PID:373856): Opened log for T-1.S-23 dbid 2915316868 branch 1220803192 2025-12-25T16:20:29.157258+05:30  rfs (PID:373856): Archived Log entry 3 added for B-1220803192.T-1.S-23 LOS:0x00000000000612eb NXS:0x0000000000061301 NAB:20 ID 0xadc3d6c7 LAD:1  rfs (PID:373856): No SRLs created 2025-12-25T16:20:29.173734+05:30  rfs (PID:373856): Opened log for T-1.S-24 dbid 2915316868 branch 1220803192 2025-12-25T16:20:29.872164+05:30  rfs (PID:373856): Archived Log entry 4 added for B-1220803192.T-1.S-24 LOS:0x0000000000061301 NXS:0x000000000006130a NAB:8 ID 0xadc3d6c7 LAD:1  rfs (PID:373856): No SRLs created 2025-12-25T16:20:29.884144+05:30  rfs (PID:373856): Opened log for T-1.S-25 dbid 2915316868 branch 1220803192 2025-12-25T16:20:32.693273+05:30  rfs (PID:373856): Archived Log entry 5 added for B-1220803192.T-1.S-25 LOS:0x000000000006130a NXS:0x0000000000061315 NAB:8 ID 0xadc3d6c7 LAD:1  rfs (PID:373856): No SRLs created 2025-12-25T16:20:32.709398+05:30  rfs (PID:373856): Opened log for T-1.S-26 dbid 2915316868 branch 1220803192 Step 3: Let's start the MRP process on Old Primary(Current Standby) database. Old Primary(Current Standby):   SQL> recover managed standby database disconnect from session; Media recovery complete. SQL> Old Primary (Current Standby) Alert Log File:  rfs (PID:373856): No SRLs created 2025-12-25T16:20:29.884144+05:30  rfs (PID:373856): Opened log for T-1.S-25 dbid 2915316868 branch 1220803192 2025-12-25T16:20:32.693273+05:30  rfs (PID:373856): Archived Log entry 5 added for B-1220803192.T-1.S-25 LOS:0x000000000006130a NXS:0x0000000000061315 NAB:8 ID 0xadc3d6c7 LAD:1  rfs (PID:373856): No SRLs created 2025-12-25T16:20:32.709398+05:30  rfs (PID:373856): Opened log for T-1.S-26 dbid 2915316868 branch 1220803192 2025-12-25T16:21:43.826259+05:30 ALTER DATABASE RECOVER  managed standby database disconnect from session 2025-12-25T16:21:43.829016+05:30 WARNING: There are no standby redo logs. Standby redo logs should be configured for real time apply. Real time apply will be ignored. 2025-12-25T16:21:43.829975+05:30 Attempt to start background Managed Standby Recovery process (PR) Starting background process MRP0 2025-12-25T16:21:43.851451+05:30 MRP0 started with pid=107, OS id=384390 2025-12-25T16:21:43.851937+05:30 Background Managed Standby Recovery process started (PR) 2025-12-25T16:21:48.878987+05:30  Started logmerger process 2025-12-25T16:21:48.922042+05:30 PR00 (PID:384525): Managed Standby Recovery not using Real Time Apply 2025-12-25T16:21:48.979028+05:30 DBWPreopen slv 0 open list size=1 successful=0 DBWPreopen slv 1 open list size=1 successful=0 DBWPreopen slv 2 open list size=1 successful=0 DBWPreopen slv 3 open list size=1 successful=0 DBWPreopen total open count=5 shared=0 BG-shared=0 at t=0s 2025-12-25T16:21:51.026777+05:30 Only allocated 127 recovery slaves (requested 128) 2025-12-25T16:21:51.026831+05:30 Parallel Media Recovery started with 127 slaves 2025-12-25T16:21:51.093401+05:30 Stopping change tracking 2025-12-25T16:21:51.148452+05:30 TT02 (PID:384893): Waiting for all non-current ORLs to be archived 2025-12-25T16:21:51.148492+05:30 TT02 (PID:384893): All non-current ORLs have been archived TT02 (PID:384893): Clearing ORL LNO:1 +RECO1/DR/ONLINELOG/group_1.11872.1220801623 Clearing online log 1 of thread 1 sequence number 21 2025-12-25T16:21:51.214200+05:30 ERROR: failed to establish dependency between database PR and diskgroup resource ora.RECO1.dg 2025-12-25T16:21:51.286268+05:30 PR00 (PID:384525): Media Recovery Log +RECO2/PR/ARCHIVELOG/2025_12_25/thread_1_seq_21.2290.1220804365 2025-12-25T16:21:51.330345+05:30 TT02 (PID:384893): Clearing ORL LNO:1 complete TT02 (PID:384893): Clearing ORL LNO:2 +RECO1/DR/ONLINELOG/group_2.11876.1220801623 Clearing online log 2 of thread 1 sequence number 20 2025-12-25T16:21:51.411495+05:30 PR00 (PID:384525): Media Recovery Log +RECO2/PR/ARCHIVELOG/2025_12_25/thread_1_seq_22.2289.1220804363 2025-12-25T16:21:51.487206+05:30 PR00 (PID:384525): Media Recovery Log +RECO2/PR/ARCHIVELOG/2025_12_25/thread_1_seq_23.2292.1220804429 2025-12-25T16:21:51.506963+05:30 TT02 (PID:384893): Clearing ORL LNO:2 complete 2025-12-25T16:21:51.562132+05:30 PR00 (PID:384525): Media Recovery Log +RECO2/PR/ARCHIVELOG/2025_12_25/thread_1_seq_24.2294.1220804429 2025-12-25T16:21:51.637434+05:30 PR00 (PID:384525): Media Recovery Log +RECO2/PR/ARCHIVELOG/2025_12_25/thread_1_seq_25.2296.1220804429 PR00 (PID:384525): Media Recovery Waiting for T-1.S-26 (in transit) 2025-12-25T16:21:51.859814+05:30 Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session 2025-12-25T16:21:52.508622+05:30 TT02 (PID:384893): Waiting for all non-current ORLs to be archived 2025-12-25T16:21:52.508675+05:30 TT02 (PID:384893): All non-current ORLs have been archived ✅ Old Primary Database Successfully Reinstated: As you can see, the old Primary Database has been successfully reinstated without any issues. Oracle has automatically picked up the new archive logs from the new incarnation and applied the latest redo sequences, ensuring full synchronization between the databases. ❓ Common Questions After Reinstatement: After reinstating the old Primary Database, a few important questions typically arise: - Is the newly configured standby database valid and reliable? - Can we perform switchover or switchback operations between the New Primary and the New Standby databases? ✔️ Answers: - Yes, the New Standby database is fully valid and operational. - Yes, you can safely perform regular switchover and switchback operations between the New Primary and the New Standby databases without any issues. 📝 Stay tuned for a detailed blog post on this case !!!

Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !