Roll Forward Physical Standby Using Service in Oracle Database 18.1 and later (non cdb & non-RAC) oracledna DataGuard March 9, 2026 This post demonstrates rolling forward the physical standby database to bring it back to sync with primary when archivelogs missed or have a huge gap. When archivelogs are missed, rolling forward an Oracle Physical Standby Database entails applying incremental backups from the primary database using RMAN to synchronize it. The automated approach is using “RECOVER STANDBY DATABASE FROM SERVICE” to automate the backup, transfer and apply. This approach is the fastest and reduces manual steps. This method applies to Version 18.1.0.0.0 and later 1. Stop the recovery managed process on standby database.   SQL> alter database recover managed standby database cancel;Database altered. Below is the archivelog sync gap between primary and standby databases.   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#)) APPLWHERE ARCH.THREAD# = APPL.THREAD#; Thread      Last Sequence Received        Last Sequence Applied          Difference———- ———————- ——————— ———-1                          66                                               52                                           14   2. Ensure that Oracle Net connectivity is established between the physical standby database and the primary database. On Primary:=========[oracle@prod ~]$ tnsping stdbyTNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 09-MAR-2026 19:18:36Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:/u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.142)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdby) (UR = A)))OK (0 msec)   [oracle@prod ~]$ sqlplus sys/manager@stdby as sysdbaSQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 9 19:21:54 2026Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – ProductionVersion 19.3.0.0.0   On Standby:=========[oracle@stdby ~]$ tnsping prodTNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 09-MAR-2026 19:19:28Copyright (c) 1997, 2019, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.141)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) (UR = A)))OK (10 msec)   [oracle@stdby ~]$ sqlplus sys/manager@prod as sysdbaSQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 9 19:22:42 2026Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – ProductionVersion 19.3.0.0.0 3. On Standby database, connect to RMAN and recover the standby database.   [oracle@stdby ~]$ rman target /Recovery Manager: Release 19.0.0.0.0 – Production on Mon Mar 9 19:24:40 2026Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.connected to target database: PROD (DBID=608515774, not open)   RMAN> RECOVER STANDBY DATABASE FROM SERVICE prod; Starting recover at 09-MAR-2026 19:25:40using target database control file instead of recovery catalogOracle instance started Total System Global Area 2113927120 bytes Fixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytes contents of Memory Script:{restore standby controlfile from service ‘prod’;alter database mount standby database;}executing Memory Script Starting restore at 09-MAR-2026 19:25:50allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=34 device type=DISK channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: using network backup set from service prodchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/app/oracle/oradata/STDBY/control01.ctloutput file name=/u01/app/oracle/oradata/STDBY/control02.ctlFinished restore at 09-MAR-2026 19:25:53 released channel: ORA_DISK_1Statement processedExecuting: alter system set standby_file_management=manual contents of Memory Script:{recover database from service ‘prod’;}executing Memory Script Starting recover at 09-MAR-2026 19:25:59allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=45 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using network backup set from service proddestination for restore of datafile 00001: /u01/app/oracle/oradata/STDBY/system01.dbfchannel ORA_DISK_1: restore complete, elapsed time: 00:00:07channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using network backup set from service proddestination for restore of datafile 00003: /u01/app/oracle/oradata/STDBY/sysaux01.dbfchannel ORA_DISK_1: restore complete, elapsed time: 00:00:07channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using network backup set from service proddestination for restore of datafile 00004: /u01/app/oracle/oradata/STDBY/undotbs01.dbfchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using network backup set from service proddestination for restore of datafile 00007: /u01/app/oracle/oradata/STDBY/users01.dbfchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:00Finished recover at 09-MAR-2026 19:26:16Executing: alter system set standby_file_management=autoFinished recover at 09-MAR-2026 19:26:16   Standby alert log:=============License high water mark = 82026-03-09T19:25:42.055216+05:30USER(prelim) (ospid: 17531): terminating the instance2026-03-09T19:25:43.098455+05:30Instance terminated by USER(prelim), pid = 175312026-03-09T19:25:46.466196+05:30Starting ORACLE instance (normal) (OS id: 17568)2026-03-09T19:25:46.471862+05:30****************************************************Sys-V shared memory will be used for creating SGA****************************************************2026-03-09T19:25:46.473340+05:30**********************************************************************2026-03-09T19:25:46.473424+05:30Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 2026-03-09T19:25:46.473592+05:30Per process system memlock (soft) limit = 3072M2026-03-09T19:25:46.473668+05:30Expected per process system memlock (soft) limit to lockinstance MAX SHARED GLOBAL AREA (SGA) into memory: 2018M2026-03-09T19:25:46.473893+05:30Available system pagesizes:4K, 2048K2026-03-09T19:25:46.474041+05:30Supported system pagesize(s):2026-03-09T19:25:46.474117+05:30PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)2026-03-09T19:25:46.474193+05:304K Configured 4 516100 NONE2026-03-09T19:25:46.474396+05:302048K 0 1009 0 NONE2026-03-09T19:25:46.474472+05:30RECOMMENDATION:2026-03-09T19:25:46.474545+05:301. For optimal performance, configure system with expected numberof pages for every supported system pagesize prior to the nextinstance restart operation.2026-03-09T19:25:46.492337+05:30**********************************************************************LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Initial number of CPU is 1Number of processor cores in the system is 1Number of processor sockets in the system is 1Capability Type : Networkcapabilities requested : 7 detected : 0 Simulated : 0Capability Type : Runtime Environmentcapabilities requested : 400000FF detected : 40000000 Simulated : 0Capability Type : Engineered Systemscapabilities requested : 7 detected : 0 Simulated : 0Capability Type : Database Testcapabilities requested : 3 detected : 0 Simulated : 0Autotune of undo retention is turned on.IMODE=BRILAT =51LICENSE_MAX_USERS = 0SYS auditing is enabledNOTE: remote asm mode is local (mode 0x1; from cluster type)NOTE: Using default ASM root directory ASMNOTE: remote asm mode is local (mode 0x1; from cluster type)NOTE: Cluster configuration type = NONE [2]Oracle Database 19c

Reinstate Failed Primary Database into Physical Standby (Using Flashback) in Oracle Database oracledna DataGuard March 7, 2026 The 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 mountORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase 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 22780612026-03-08T07:16:17.079417+05:30Flashback Restore StartFlashback Restore CompleteFlashback Media Recovery Start2026-03-08T07:16:17.677157+05:30Serial Media Recovery startedFlashback Media Recovery Log /u01/arch/prod/76_1_1196677442.arcFlashback Media Recovery Log /u01/arch/prod/77_1_1196677442.arcFlashback Media Recovery Log /u01/arch/prod/78_1_1196677442.arcFlashback Media Recovery Log /u01/arch/prod/79_1_1196677442.arcFlashback Media Recovery Log /u01/arch/prod/80_1_1196677442.arcFlashback Media Recovery Log /u01/arch/prod/81_1_1196677442.arcFlashback Media Recovery Log /u01/arch/prod/82_1_1196677442.arc2026-03-08T07:16:18.063264+05:30Incomplete Recovery applied until change 2278062 time 03/08/2026 05:39:16Flashback Media Recovery CompleteCompleted: 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 openDatabase dismounted.ORACLE instance shut down. SQL> STARTUP MOUNT;ORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase mounted.   Alert log of new standby database:=========================ALTER DATABASE CONVERT TO PHYSICAL STANDBY2026-03-08T07:17:51.766425+05:30ALTER DATABASE CONVERT TO PHYSICAL STANDBY (prod)2026-03-08T07:17:51.814339+05:30Clearing 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 logfilesUse the following SQL commands on the standby database to createstandby 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 timePhysical Standby Database mounted.2026-03-08T07:17:51.851687+05:30In-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 solutionsCONVERT TO PHYSICAL STANDBY: Complete – Database mounted as physical standbyCompleted: 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 stringlog_archive_dest_1 string LOCATION=/u01/arch/stdby VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=stdbylog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 string NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 string SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod   On Standby:=========SQL> show parameter LOG_ARCHIVE_DEST NAME TYPE VALUE———————————— ———– ——————————log_archive_dest stringlog_archive_dest_1 string LOCATION=/u01/arch/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prodlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 string NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_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 ModeAutomatic archival EnabledArchive destination /u01/arch/stdbyOldest online log sequence 8Next log sequence to archive 10Current 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#)) APPLWHERE 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. Popular Posts Reinstate Failed Primary Database into Physical Standby (Using Flashback) in Oracle Database Manual Failover of Physical Standby Database (Without resolving GAP) in Oracle 19c Convert Snapshot Standby Database into Production in Oracle 19c (non-cdb & non-RAC) Logical Standby Database Switchover in Oracle 19c Create Logical Standby Database in Oracle 19c Categories ASM (2) DataGuard (10) News (1) Oracle Standalone Database (8) Patching (1) RAC (3)

Manual Failover of Physical Standby Database (Without resolving GAP) in Oracle 19c oracledna DataGuard March 7, 2026 This 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 DATABASE2026-03-08T05:54:18.377869+05:30ALTER 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 archivalRESETLOGS after incomplete recovery UNTIL CHANGE 2278063 time 03/08/2026 05:39:16Resetting resetlogs activation ID 608653432 (0x24475078)Online log /u01/app/oracle/oradata/STDBY/redo01.log: Thread 1 Group 1 was previously clearedOnline log /u01/app/oracle/oradata/STDBY/redo02.log: Thread 1 Group 2 was previously clearedOnline log /u01/app/oracle/oradata/STDBY/redo03.log: Thread 1 Group 3 was previously clearedStandby became primary SCN: 22780612026-03-08T05:54:18.641449+05:30Setting recovery target incarnation to 32026-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 time2026-03-08T05:54:18.854868+05:30…. (PID:3429): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]ACTIVATE STANDBY: Complete – Database mounted as primaryCompleted: ALTER DATABASE ACTIVATE STANDBY DATABASE 3. Shutdown the database, start the database, verify the database role and protection mode.   SQL> shut immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down. SQL> startupORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase 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. Popular Posts Manual Failover of Physical Standby Database (Without resolving GAP) in Oracle 19c Convert Snapshot Standby Database into Production in Oracle 19c (non-cdb & non-RAC) Logical Standby Database Switchover in Oracle 19c Create Logical Standby Database in Oracle 19c Convert the Snapshot Standby Database to the Physical Standby Database Categories ASM (2) DataGuard (9) News (1) Oracle Standalone Database (8) Patching (1) RAC (3)

Convert Snapshot Standby Database into Production in Oracle 19c (non-cdb & non-RAC) oracledna DataGuard March 6, 2026 This post demonstrates converting Snapshot Standby Database into Production in Oracle 19c (non-cdb & non-RAC). The Snapshot Standby Database helps developers for testing. To retain the transactions that occurred on Snapshot standby, they want to convert Snapshot standby into PRODUCTION/PRIMARY database. Note: Both forceful and graceful failover cannot be done on snapshot standby. 1. Mount the snapshot standby database.   SQL> startup mountORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase mounted.   SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED SNAPSHOT STANDBY 2. Take backup of controlfile of snapshot database. SQL> alter database backup controlfile to trace;Database altered. To get the location of newly created controlfile backup, check the alert log SQL> oradebug setmypidStatement processed. SQL> oradebug tracefile_name;/u01/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_ora_14751.trc Alert log of snapshot standby:======================alter database backup controlfile to trace2026-03-06T23:56:40.129646+05:30Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_ora_14751.trcCompleted: alter database backup controlfile to trace 3. Get the current control file location and shutdown the snapshot standby instance. Also, take the backup of controlfile. SQL> select name,open_mode,database_role from v$database;NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED SNAPSHOT STANDBY   SQL> show parameter control_filesNAME TYPE VALUE———————————— ———– ——————————control_files string /u01/app/oracle/oradata/STDBY/control01.ctl, /u01/app/oracle/oradata/STDBY/control02.ctl SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down. Take the backup of controlfile at OS level==[oracle@stdby ~]$ cd /u01/app/oracle/oradata/STDBY/   [oracle@stdby STDBY]$ mv control01.ctl control01.ctl_bkp[oracle@stdby STDBY]$ mv control02.ctl control02.ctl_bkp   [oracle@stdby STDBY]$ ls -lrt control*-rw-r—– 1 oracle oinstall 10895360 Mar 7 00:13 control01.ctl_bkp-rw-r—– 1 oracle oinstall 10895360 Mar 7 00:13 control02.ctl_bkp 4. Create a new control from the trace file. 4.a Edit the trace control file and rename to control.ctl CREATE CONTROLFILE REUSE DATABASE “PROD” NORESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 ‘/u01/app/oracle/oradata/STDBY/redo01.log’ SIZE 200M BLOCKSIZE 512,GROUP 2 ‘/u01/app/oracle/oradata/STDBY/redo02.log’ SIZE 200M BLOCKSIZE 512,GROUP 3 ‘/u01/app/oracle/oradata/STDBY/redo03.log’ SIZE 200M BLOCKSIZE 512DATAFILE‘/u01/app/oracle/oradata/STDBY/system01.dbf’,‘/u01/app/oracle/oradata/STDBY/sysaux01.dbf’,‘/u01/app/oracle/oradata/STDBY/undotbs01.dbf’,‘/u01/app/oracle/oradata/STDBY/users01.dbf’CHARACTER SET AL32UTF8; 4.b Start the snapshot database in nomount state SQL> startup nomountORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytes 4.c Create the controlfile from trace (created on step 2) SQL> @control.ctlControl file created. 4.d check database role and whether mounted or not. SQL> select name,open_mode,database_role from v$database;NAME OPEN_MODE DATABASE_ROLE——— ——————– —————-PROD MOUNTED PRIMARY The database is in mount state. Note: After control file creation, check the temp tablespace and tempfiles and add them accordingly. 5. Open the database. SQL> alter database open;Database altered. SQL> select name,open_mode,database_role from v$database;NAME OPEN_MODE DATABASE_ROLE——— ——————– —————-PROD READ WRITE PRIMARY 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. Popular Posts Convert Snapshot Standby Database into Production in Oracle 19c (non-cdb & non-RAC) Logical Standby Database Switchover in Oracle 19c Create Logical Standby Database in Oracle 19c Convert the Snapshot Standby Database to the Physical Standby Database Convert Oracle Physical Standby Database to the Snapshot Standby Database Categories ASM (2) DataGuard (8) News (1) Oracle Standalone Database (8) Patching (1) RAC (3)

Logical Standby Database Switchover in Oracle 19c oracledna DataGuard March 5, 2026 This post demonstrate the switchover of Logical Standby Database to Primary Database in Oracle 19c. 1. Ensure the Logical Standby Database is in sync with Primary Database. Execute below statement to check the sync. On Primary:=========SQL> select thread#,max(sequence#) from v$log_history group by thread#; THREAD# MAX(SEQUENCE#)———- ————–1 91 On Logical Standby Standby:=====================SQL> select thread#,max(sequence#) from dba_logstdby_log where applied=’YES’ group by thread#; THREAD# MAX(SEQUENCE#)———- ————–1 91 2. Check dba_logstdby_skip to inform customer that these tables are not replicated on logical standby and when Logical standby become primary. Application team must be aware before trying to access the skipped tables.   Execute below SQL on Logical Standby Database. set lineszie 300col name for a30col owner for a18col proc for a8col error for a8col statement_opt for a16select * from dba_logstdby_skip; 3. Check for the REDO transport configured from logical standby database to current primary database. Execute below SQL on Logical Standby Database. Ensure the proper redo transport configuration must be in place. SQL> show parameter log_archive_dest NAME TYPE VALUE———————————— ———– ——————————log_archive_dest stringlog_archive_dest_1 string LOCATION=/u01/arch/stdby VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdbylog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 string NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 string SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodlog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 string NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 stringlog_archive_dest_28 stringlog_archive_dest_29 stringlog_archive_dest_3 string LOCATION=/u01/arch/log_stby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stdbylog_archive_dest_30 stringlog_archive_dest_31 string 4. Check the SWITCHOVER_STATUS. On Primary:==========SQL> select database_role,switchover_status from v$database;DATABASE_ROLE SWITCHOVER_STATUS—————- ——————–PRIMARY TO STANDBY On Logical Standby:===============SQL> select database_role,switchover_status from v$database;DATABASE_ROLE SWITCHOVER_STATUS—————— ——————–LOGICAL STANDBY NOT ALLOWED 5. Prepare Primary Database for the switchover.   Execute below SQL on Primary Database. SQL> alter database prepare to switchover to logical standby;Database altered. SQL> select database_role,switchover_status from v$database;DATABASE_ROLE SWITCHOVER_STATUS—————- ——————–PRIMARY PREPARING SWITCHOVER 6. Prepare Logical Standby Database for the switchover. Execute below SQL on Logical Standby Database. SQL> alter database prepare to switchover to primary;Database altered. SQL> select database_role,switchover_status from v$database;DATABASE_ROLE SWITCHOVER_STATUS—————— ——————–LOGICAL STANDBY PREPARING SWITCHOVER   Logical Standby Database alert log:=========================alter database prepare to switchover to primary2026-03-06T00:22:05.735793+05:30ALTER DATABASE SWITCHOVER TO PRIMARY (stdby)2026-03-06T00:22:05.736048+05:30ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY (stdby)2026-03-06T00:22:05.936919+05:30Thread 1 advanced to log sequence 4 (LGWR switch)Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/STDBY/redo01.log2026-03-06T00:22:06.670338+05:30NET (PID:3169): Archived Log entry 3 added for T-1.S-3 ID 0x2c708ca4 LAD:12026-03-06T00:22:06.813880+05:30Fri Mar 06 00:22:06 2026Logminer Bld: Build started2026-03-06T00:22:06.857402+05:30ALTER SYSTEM SWITCH ALL LOGFILE start (stdby)2026-03-06T00:22:06.987911+05:30Thread 1 advanced to log sequence 5 (LGWR switch)Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/STDBY/redo02.log2026-03-06T00:22:06.988467+05:30ALTER SYSTEM SWITCH ALL LOGFILE complete (stdby)2026-03-06T00:22:07.070570+05:30ARC3 (PID:3377): LGWR is scheduled to archive to LAD:2 after log switch2026-03-06T00:22:07.131140+05:30Fri Mar 06 00:22:07 2026Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 2407830 LockdownSCN is 24078302026-03-06T00:22:08.811949+05:30ARC3 (PID:3377): Archived Log entry 4 added for T-1.S-4 ID 0x2c708ca4 LAD:12026-03-06T00:22:15.905241+05:30ALTER SYSTEM ARCHIVE LOG2026-03-06T00:22:15.945972+05:30Thread 1 cannot allocate new log, sequence 6Checkpoint not completeCurrent log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/STDBY/redo02.log2026-03-06T00:22:17.751701+05:30Thread 1 advanced to log sequence 6 (LGWR switch)Current log# 3 seq# 6 mem# 0: /u01/app/oracle/oradata/STDBY/redo03.log2026-03-06T00:22:17.759744+05:30NET (PID:3169): LGWR is scheduled to archive to LAD:2 after log switch2026-03-06T00:22:26.415631+05:30NET (PID:3169): Archived Log entry 6 added for T-1.S-5 ID 0x2c708ca4 LAD:1Fri Mar 06 00:22:26 2026Logminer Bld: Done2026-03-06T00:22:26.523378+05:30Thread 1 cannot allocate new log, sequence 7Checkpoint not completeCurrent log# 3 seq# 6 mem# 0: /u01/app/oracle/oradata/STDBY/redo03.log2026-03-06T00:22:26.759064+05:30Thread 1 advanced to log sequence 7 (LGWR switch)Current log# 1 seq# 7 mem# 0: /u01/app/oracle/oradata/STDBY/redo01.log2026-03-06T00:22:26.760995+05:30NET (PID:3169): LGWR is scheduled to archive to LAD:2 after log switch2026-03-06T00:22:27.319744+05:30NET (PID:3169): Archived Log entry 8 added for T-1.S-6 ID 0x2c708ca4 LAD:12026-03-06T00:22:27.428472+05:30Completed: alter database prepare to switchover to primary 7. Initiate the switchover on the Primary Database. SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS—————- ——————–PRIMARY TO LOGICAL STANDBY SQL> alter database commit to switchover to logical 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 READ WRITE LOGICAL STANDBY Primary alert log:============alter database commit to switchover to logical standby2026-03-06T00:38:58.327222+05:30ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (prod)2026-03-06T00:38:58.327386+05:30LOGSTDBY: Switchover initiated via DDLLOGSTDBY: Ensuring no active hot backups.LOGSTDBY: Archiving current online log files.2026-03-06T00:38:58.385551+05:30Thread 1 advanced to log sequence 93 (LGWR switch)Current log# 1 seq# 93 mem# 0: /u01/app/oracle/oradata/PROD/redo01.log2026-03-06T00:38:58.426476+05:30NET (PID:3318): Archived Log entry 165 added for T-1.S-92 ID 0x24475078 LAD:12026-03-06T00:38:58.445605+05:30LOGSTDBY: Waiting for pending archivals to all destinations.2026-03-06T00:38:58.461268+05:30NET (PID:3318): Waiting for all non-current ORLs to be archived2026-03-06T00:38:58.461317+05:30NET (PID:3318): All non-current ORLs have been archived2026-03-06T00:38:58.461346+05:30NET (PID:3318): Waiting for all FAL entries to be archived2026-03-06T00:38:58.461373+05:30NET (PID:3318): All FAL entries have been archived2026-03-06T00:38:58.461451+05:30NET (PID:3318): Waiting for potential Logical Standby switchover target to become synchronized2026-03-06T00:38:58.725109+05:30TT02 (PID:3334): SRL selected for T-1.S-93 for LAD:22026-03-06T00:38:59.462460+05:30NET (PID:3318): Active, synchronized Logical Standby switchover target has been identified2026-03-06T00:38:59.462547+05:30LOGSTDBY: Enabling database guard.LOGSTDBY: Waiting for completion of transactions started at or before scn 2387195 (0x0000000000246cfb)LOGSTDBY: All transactions started at or before scn 2387195 (0x0000000000246cfb) have completed2026-03-06T00:38:59.572304+05:30LOGSTDBY: Database guard enabled. User transactions are no longer permitted.2026-03-06T00:38:59.572336+05:30LOGSTDBY: Waiting for pending archivals to all destinations.2026-03-06T00:38:59.585152+05:30NET (PID:3318): Waiting for all non-current ORLs to be archived2026-03-06T00:38:59.585205+05:30NET (PID:3318): All non-current ORLs have been archived2026-03-06T00:38:59.585254+05:30NET (PID:3318): Waiting for all FAL entries to be archived2026-03-06T00:38:59.585306+05:30NET (PID:3318): All FAL entries have been archived2026-03-06T00:38:59.585370+05:30NET (PID:3318): Waiting for potential Logical Standby switchover target to become synchronized2026-03-06T00:39:00.586930+05:30NET (PID:3318): Active, synchronized Logical Standby switchover target has been identifiedLOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn 2387198 [0x0000000000246cfe].2026-03-06T00:39:00.652678+05:30Thread 1 advanced to log sequence 94 (LGWR switch)Current log# 2 seq# 94 mem# 0: /u01/app/oracle/oradata/PROD/redo02.log2026-03-06T00:39:01.654020+05:30ARC1 (PID:3327): LGWR is scheduled to archive to LAD:2 after log switchARC1 (PID:3327): SRL selected for T-1.S-93 for LAD:22026-03-06T00:39:01.835322+05:30ARC1 (PID:3327): Archived Log entry 167 added for T-1.S-93 ID 0x24475078 LAD:12026-03-06T00:39:01.916197+05:30NET (PID:3318): LOG_ARCHIVE_DEST_2 is a potential Logical Standby switchover targetNET (PID:3318): Database role changed from PRIMARY to LOGICAL STANDBY [dglc.c:1961]2026-03-06T00:39:01.934712+05:30Thread 1 cannot allocate new log, sequence 95Checkpoint not completeCurrent log# 2 seq# 94 mem# 0: /u01/app/oracle/oradata/PROD/redo02.log2026-03-06T00:39:04.293898+05:30Thread 1 advanced to log sequence 95 (LGWR switch)Current log# 3 seq# 95 mem# 0: /u01/app/oracle/oradata/PROD/redo03.log2026-03-06T00:39:04.303214+05:30NET (PID:3318): Archived Log entry 169 added for T-1.S-94 ID 0x24475078 LAD:12026-03-06T00:39:04.316050+05:30LOGSTDBY: Switchover complete (prod)LOGSTDBY: enabling scheduler job queue processes.2026-03-06T00:39:04.316142+05:30JOBQ: re-enabling CJQ0Completed: alter database commit to switchover to logical standby   Logical Standby alert log:==================2026-03-06T00:38:59.725449+05:30RFS LogMiner: Registered logfile [/u01/arch/log_stby/92_1_1196677442.arc] to LogMiner session id [1]2026-03-06T00:38:59.780851+05:30RFS LogMiner: RFS id [7414] assigned as thread [1] PING handler2026-03-06T00:38:59.782815+05:30RFS LogMiner: RFS id [7414] assigned as thread [1] PING handler2026-03-06T00:38:59.794412+05:30rfs (PID:7416): Primary

Create Logical Standby Database in Oracle 19c oracledna DataGuard March 4, 2026 This post demonstrates the create logical standby database in Oracle 19c. The logical standby is maintained by applying data changes as SQL statements via SQL Apply, which extracts these changes from the redo data received from the primary.   Pre-requisites:=========== 1. Create a physical standby database. 2. Before converting the physical standby database to logical standby database, see the below document for a complete list of data type and storage type considerations. https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-logical-standby.html#GUID-3666CA35-D993-44B6-8D70-A2B8B9EC8B2E 3. Ensure Table Rows in the Primary Database Can Be Uniquely Identified. This is because, Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. In primary database, run the below query to display a list of tables that SQL Apply may not be able to uniquely identify. SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = ‘Y’;no rows selected 1. Ensure that Physical Standby is in Sync with Primary Database. Run the below query in Physical Standby database to check the archive log sync. SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied” 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#)) APPLWHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;  Thread Last Sequence Received Last Sequence Applied———- ———————- ———————1 78 78 2. Stop Redo Apply (MRP) on the Physical Standby Database.   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered. 3. Set Parameters for Logical Standby in Primary database. 3.1 Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES) Change the VALID_FOR attribute in the original LOG_ARCHIVE_DEST_1 destination to archive redo data only from the online redo log and not from the standby redo log. SQL> show parameter LOG_ARCHIVE_DEST_1NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_1 string LOCATION=/u01/arch/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod SQL> alter system set LOG_ARCHIVE_DEST_1= ‘LOCATION=/u01/arch/prod VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod’ scope=both;System altered. SQL> show parameter LOG_ARCHIVE_DEST_1NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_1 string LOCATION=/u01/arch/prod VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod   3.2 set the LOG_ARCHIVE_DEST_3 destination on the primary database and for logs which will received on Standby from Primary. This parameter will be in effective only when the primary database is transitioned to the logical standby role. SQL> alter system set LOG_ARCHIVE_DEST_3= ‘LOCATION=/u01/arch/log_stby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prod’ scope=both;System altered. SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;System altered. 4. Build a Dictionary in the Redo Data on Primary Database. Building a LogMiner dictionary is mandatory, so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. This will enable the supplemental logging automatically set up to log primary key and unique-constraint/index columns. To build the LogMiner dictionary, execute the below statement. The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. SQL> EXECUTE DBMS_LOGSTDBY.BUILD;PL/SQL procedure successfully completed. From Primary alert log:=================2026-03-05T00:54:51.014293+05:30alter database add supplemental log data (primary key, unique index) columns2026-03-05T00:54:51.065128+05:30SUPLOG: Previous supplemental logging attributes at scn = 2341923SUPLOG: minimal = OFF, primary key = OFFSUPLOG: unique = OFF, foreign key = OFF, all column = OFFSUPLOG: procedural replication = OFF, subset db replication = OFF2026-03-05T00:54:51.163148+05:30SUPLOG: New supplemental logging attributes at scn = 2341923SUPLOG: minimal = ON, primary key = ONSUPLOG: unique = ON, foreign key = OFF, all column = OFFSUPLOG: procedural replication = OFF, subset db replication = OFFSUPPLEMENTAL LOG: Waiting for completion of transactions started at or before scn 2341923 (0x000000000023bc23)SUPPLEMENTAL LOG: All transactions started at or before scn 2341923 (0x000000000023bc23) have completedCompleted: alter database add supplemental log data (primary key, unique index) columnsalter database add supplemental log data for procedural replication2026-03-05T00:54:51.196423+05:30SUPLOG: Previous supplemental logging attributes at scn = 2341934SUPLOG: minimal = ON, primary key = ONSUPLOG: unique = ON, foreign key = OFF, all column = OFFSUPLOG: procedural replication = OFF, subset db replication = OFF2026-03-05T00:54:51.196860+05:30SUPLOG: New supplemental logging attributes at scn = 2341934SUPLOG: minimal = ON, primary key = ONSUPLOG: unique = ON, foreign key = OFF, all column = OFFSUPLOG: procedural replication = ON, subset db replication = OFFCompleted: alter database add supplemental log data for procedural replicationThu Mar 05 00:54:51 2026Logminer Bld: Build started2026-03-05T00:54:51.269958+05:30ALTER SYSTEM SWITCH ALL LOGFILE start (prod)2026-03-05T00:54:51.304773+05:30Thread 1 advanced to log sequence 80 (LGWR switch)Current log# 3 seq# 80 mem# 0: /u01/app/oracle/oradata/PROD/redo03.log2026-03-05T00:54:51.304859+05:30ALTER SYSTEM SWITCH ALL LOGFILE complete (prod)2026-03-05T00:54:51.344150+05:30Thu Mar 05 00:54:51 2026Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 2341945 LockdownSCN is 23419452026-03-05T00:54:51.620756+05:30TT02 (PID:3145): SRL selected for T-1.S-80 for LAD:22026-03-05T00:54:51.827520+05:30ARC1 (PID:3139): Archived Log entry 138 added for T-1.S-79 ID 0x24475078 LAD:12026-03-05T00:54:51.851286+05:30Resize operation completed for file# 1, old size 921600K, new size 931840K2026-03-05T00:54:52.169900+05:30Resize operation completed for file# 1, old size 931840K, new size 942080K2026-03-05T00:54:52.622072+05:30ALTER SYSTEM ARCHIVE LOG2026-03-05T00:54:52.663766+05:30Thread 1 advanced to log sequence 81 (LGWR switch)Current log# 1 seq# 81 mem# 0: /u01/app/oracle/oradata/PROD/redo01.log2026-03-05T00:54:52.712405+05:30NET (PID:4304): Archived Log entry 139 added for T-1.S-80 ID 0x24475078 LAD:1Thu Mar 05 00:54:52 2026Logminer Bld: DoneLOGMINER: Dictionary Build: Waiting for completion of transactions started at or before scn 2342475 (0x000000000023be4b)LOGMINER: Dictionary Build: All transactions started at or before scn 2342475 (0x000000000023be4b) have completed 5. Convert the Physical Standby Database to a Logical Standby Database. In below SQL, specify the new db_name for logical standby database other than the db_name of primary database. If database was opened with spfile, the nelow SQL update the SPFILE with new db_name. SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>; SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY logstdby;Database altered. From Standby alert log:=================2026-03-05T01:07:11.416494+05:30ALTER DATABASE RECOVER TO LOGICAL STANDBY logstdby2026-03-05T01:07:11.416824+05:30Media Recovery Start: Managed Standby Recovery (stdby)2026-03-05T01:07:11.418994+05:30Serial Media Recovery startedNET (PID:4240): Managed Standby Recovery not using Real Time Applystopping change tracking2026-03-05T01:07:11.522669+05:30NET (PID:4240): Media Recovery Log /u01/arch/stdby/79_1_1196677442.arc2026-03-05T01:07:11.669361+05:30NET (PID:4240): Media Recovery Log /u01/arch/stdby/80_1_1196677442.arcResize operation completed for file# 1, old size 921600K, new size 931840KResize operation completed for file# 1, old size 931840K, new size 942080K2026-03-05T01:07:11.917822+05:30NET (PID:4240): Media Recovery Log /u01/arch/stdby/81_1_1196677442.arc2026-03-05T01:07:12.472000+05:30Incomplete Recovery applied until change 2342487 time 03/05/2026 00:54:522026-03-05T01:07:12.483939+05:30Media Recovery Complete (stdby)stopping change trackingNET (PID:4240): Killing 3 processes (PIDS:4287,4300,4291) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 42402026-03-05T01:07:12.545859+05:30Process termination

Convert the Snapshot Standby Database to the Physical Standby Database oracledna DataGuard March 2, 2026 This post demonstrate the Convert the Snapshot Standby Database to the Physical Standby Database. 1. Shutdown the snapshot standby database. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD READ WRITE SNAPSHOT STANDBY   SQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut down. 2. Start the database to the mount stage. SQL> startup mountORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase mounted. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED SNAPSHOT STANDBY 3. Convert the Snapshot Standby Database to the Physical Standby Database.   SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;Database altered. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED PHYSICAL STANDBY   Alert Log===========2026-03-03T01:53:59.955179+05:30ALTER DATABASE CONVERT TO PHYSICAL STANDBY2026-03-03T01:53:59.955319+05:30ALTER DATABASE CONVERT TO PHYSICAL STANDBY (stdby)2026-03-03T01:53:59.955838+05:30…. (PID:4767): Killing 2 processes (PIDS:4800,4802) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 47672026-03-03T01:53:59.956015+05:30Process termination requested for pid 4800 [source = rdbms], [info = 2] [request issued by pid: 4767, uid: 543232]2026-03-03T01:53:59.956121+05:30Process termination requested for pid 4802 [source = rdbms], [info = 2] [request issued by pid: 4767, uid: 543232]2026-03-03T01:54:01.984469+05:30Flashback Restore StartFlashback Restore CompleteDrop guaranteed restore pointStopping background process RVWR2026-03-03T01:54:03.834186+05:30Deleted Oracle managed file /u01/arch/stdby/STDBY/flashback/o1_mf_ntcshyfj_.flbDeleted Oracle managed file /u01/arch/stdby/STDBY/flashback/o1_mf_ntcsj1ow_.flbGuaranteed restore point dropped2026-03-03T01:54:03.934045+05:30…. (PID:4767): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8837]Clearing standby activation ID 638690225 (0x2611a3b1)The primary database controlfile was created using the‘MAXLOGFILES 16’ clause.There is space for up to 13 standby redo logfilesUse the following SQL commands on the standby database to createstandby 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:4767): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8842]…. (PID:4767): RT: Role transition work is not done…. (PID:4767): Redo network throttle feature is disabled at mount timePhysical Standby Database mounted.2026-03-03T01:54:03.986541+05:30In-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 solutionsCONVERT TO PHYSICAL STANDBY: Complete – Database mounted as physical standbyCompleted: ALTER DATABASE CONVERT TO PHYSICAL STANDBY2026-03-03T01:54:04.202028+05:30rfs (PID:5525): Primary database is in MAXIMUM PERFORMANCE mode2026-03-03T01:54:04.288359+05:30rfs (PID:5525): Selected LNO:4 for T-1.S-73 dbid 608515774 branch 1196677442 4. Shutdown the physical standby database and mount it. SQL> shut immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down. SQL> startup mountORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase mounted.   SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED PHYSICAL STANDBY 5. Start the media recovery process. SQL> alter database recover managed standby database disconnect from session;Database altered. Alert Log===========2026-03-03T01:57:57.181240+05:30alter database recover managed standby database disconnect from session2026-03-03T01:57:57.184921+05:30Attempt to start background Managed Standby Recovery process (stdby)Starting background process MRP02026-03-03T01:57:57.210656+05:30MRP0 started with pid=43, OS id=73732026-03-03T01:57:57.211904+05:30Background Managed Standby Recovery process started (stdby)2026-03-03T01:58:02.216540+05:30Serial Media Recovery startedMRP0 (PID:7373): Managed Standby Recovery starting Real Time Applystopping change tracking2026-03-03T01:58:02.349934+05:30MRP0 (PID:7373): Media Recovery Log /u01/arch/stdby/71_1_1196677442.arc2026-03-03T01:58:02.350798+05:30TT02 (PID:7441): Waiting for all non-current ORLs to be archived2026-03-03T01:58:02.350882+05:30TT02 (PID:7441): All non-current ORLs have been archivedTT02 (PID:7441): Clearing ORL LNO:1 /u01/app/oracle/oradata/STDBY/redo01.logClearing online log 1 of thread 1 sequence number 12026-03-03T01:58:02.428799+05:30MRP0 (PID:7373): Media Recovery Log /u01/arch/stdby/72_1_1196677442.arcMRP0 (PID:7373): Media Recovery Waiting for T-1.S-73 (in transit)2026-03-03T01:58:02.454530+05:30Recovery of Online Redo Log: Thread 1 Group 4 Seq 73 Reading mem 0Mem# 0: /u01/app/oracle/oradata/STDBY/redo04.log2026-03-03T01:58:02.940762+05:30TT02 (PID:7441): Clearing ORL LNO:1 complete2026-03-03T01:58:03.216073+05:30Completed: alter database recover managed standby database disconnect from session2026-03-03T01:58:08.008019+05:30TT02 (PID:7441): Waiting for all non-current ORLs to be archived2026-03-03T01:58:08.008081+05:30TT02 (PID:7441): All non-current ORLs have been archived 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. Popular Posts Convert the Snapshot Standby Database to the Physical Standby Database Oracle Database 19c (non-cdb) Switchover using SQL*Plus Clone Oracle Grid Infrastructure Home Using Golden Images Enabling Flash Recovery Area In Oracle RAC 19c Database Restore OCR from backup in Oracle RAC 19c Categories ASM (2) DataGuard (4) News (1) Oracle Standalone Database (8) Patching (1) RAC (3)

Convert Oracle Physical Standby Database to the Snapshot Standby Database oracledna DataGuard March 2, 2026 This post demonstrates the convert the Oracle Physical Standby Database to the Snapshot Standby Database. The snapshot standby database allows write operations and developers can perform their testing activities. The Snapshot standby database will accept incoming archive redo logs, but does not apply the redo data . This redo data applied automatically after reverting the snapshot standby database to the physical standby database. 1. Configure flash recovery area in physical standby database, if not already configured. SQL> @dbNAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————PROD stdby MOUNTED PHYSICAL STANDBY stdby stdby 03-MAR-2026 00:17   SQL> show parameter db_recoveryNAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0 SQL> alter system set db_recovery_file_dest_size=10G scope=both;System altered. SQL> alter system set db_recovery_file_dest=’/u01/arch/stdby’ scope=both;System altered. SQL> show parameter db_recovery NAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest string /u01/arch/stdbydb_recovery_file_dest_size big integer 10G   2. Stop the managed recovery (MRP), if it is active. SQL> alter database recover managed standby database cancel;Database altered. 3. Convert physical standby database to snapshot standby database. SQL>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED PHYSICAL STANDBY   SQL> alter database convert to snapshot standby;Database altered.   SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED SNAPSHOT STANDBY   SQL> alter database open;Database altered.   SQL> select name,open_mode,database_role from v$database;NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD READ WRITE SNAPSHOT STANDBY       From Standby alert log====================alter database convert to snapshot standbyStarting background process RVWR2026-03-03T01:03:18.416253+05:30RVWR started with pid=26, OS id=165232026-03-03T01:03:19.626142+05:30Allocating 4194304 bytes in shared pool for flashback generation buffer.Allocated 4194304 bytes in shared pool for flashback generation bufferCreated guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/03/2026 01:03:18…. (PID:13893): Killing 3 processes (PIDS:3310,3382,3314) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 138932026-03-03T01:03:19.764938+05:30Process termination requested for pid 3310 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]2026-03-03T01:03:19.765139+05:30Process termination requested for pid 3382 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]2026-03-03T01:03:19.765285+05:30Process termination requested for pid 3314 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]2026-03-03T01:03:21.790486+05:30…. (PID:13893): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3111]…. (PID:13893): Begin: SRL archival…. (PID:13893): End: SRL archivalRESETLOGS after incomplete recovery UNTIL CHANGE 2275208 time 03/03/2026 00:59:45Resetting resetlogs activation ID 608653432 (0x24475078)Online log /u01/app/oracle/oradata/STDBY/redo01.log: Thread 1 Group 1 was previously clearedOnline log /u01/app/oracle/oradata/STDBY/redo02.log: Thread 1 Group 2 was previously clearedOnline log /u01/app/oracle/oradata/STDBY/redo03.log: Thread 1 Group 3 was previously clearedStandby became primary SCN: 22752062026-03-03T01:03:22.088801+05:30Setting recovery target incarnation to 32026-03-03T01:03:22.343169+05:30…. (PID:13893): Redo network throttle feature is disabled at mount timeCONVERT TO SNAPSHOT STANDBY: Complete – Database mounted as snapshot standbyCompleted: alter database convert to snapshot standby 4. Perform testing. SQL> create user ram identified by ram123 default tablespace users temporary tablespace temp;User created. SQL> grant create session to ram;Grant succeeded. SQL> grant create table to ram;Grant succeeded. SQL> conn ram/ram123Connected. SQL> create table test (id number(2), name varchar(20));Table created. SQL> insert into test (id,name) values(1,’RAM’);1 row created. SQL> commit;Commit complete. SQL> select * from test; ID NAME———- ———-1 RAM 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. Popular Posts Convert the Snapshot Standby Database to the Physical Standby Database Convert Oracle Physical Standby Database to the Snapshot Standby Database Oracle Database 19c (non-cdb) Switchover using SQL*Plus Clone Oracle Grid Infrastructure Home Using Golden Images Enabling Flash Recovery Area In Oracle RAC 19c Database Categories ASM (2) DataGuard (5) News (1) Oracle Standalone Database (8) Patching (1) RAC (3)

Oracle Database 19c (non-cdb) Switchover using SQL*Plus – Copy oracledna DataGuard March 1, 2026 This blog demonstrate the Oracle Database 19c (non-cdb) Switchover using SQL*Plus. 1. Verify redo log apply is good and there are no archve log sync gap between primary and standby databases.   SQL> select thread#, max(sequence#) “Last Standby Seq Applied” from gv$archived_log val, gv$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in (‘YES’,’IN-MEMORY’) group by thread# order by 1;     THREAD# Last Standby Seq Applied———- ————————     1                         67 2. Check the MRP(Managed Recovery Process) process status (it should be started, running and applying the logs). SQL> col status for a14col process for a10select process,thread#,sequence#,status,pid,delay_mins from V$managed_standby where process like ‘%MRP%’; PROCESS THREAD# SEQUENCE# STATUS PID DELAY_MINS———- ———- ———- ————– ———————— ———-MRP0 1 68 APPLYING_LOG 4676 0 3. If standby database recovery (MRP) started with delay, cancel the MRP and start the MRP with nodelay. This will minimize the duration of switchover.   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY; 4. Check the datafiles & Tempfiles status All the datafiles should be online in primary and standby, incase if there are files in offline or not in online status, then restore the file and recover. So standby database datafiles are same as primary database datafiles. SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=’OFFLINE’;no rows selected The temp files should be in equal number in primary and standby databases. Run below SQL in both primary and standby databases. After comparing, add temp files accordingly. SQL> col filename for a60select tf.name filename, bytes, ts.name tablespace from v$tempfile tf, v$tablespace ts where tf.ts#=ts.ts#;SQL> FILENAME                                                             BYTES                                   TABLESPACE———————————————————— ———- ——————————/u01/app/oracle/oradata/PROD/temp01.dbf      33554432                                     TEMP   SQL> col filename for a60select tf.name filename, bytes, ts.name tablespace from v$tempfile tf, v$tablespace ts where tf.ts#=ts.ts#;SQL> FILENAME                                                                 BYTES                                     TABLESPACE———————————————————— ———- ——————————/u01/app/oracle/oradata/STDBY/temp01.dbf       33554432                                      TEMP 5. Check the Online and standby redo logfile configuration Run the below SQL for Online redo logfile status in primary database. we may get a.status in (INACTIVE,ACTIVE,CURRENT). SQL> set lines 150col member for a50select a.thread#,a.group#,a.bytes,a.blocksize,b.type,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;SQL> SQL> THREAD# GROUP# BYTES BLOCKSIZE TYPE STATUS MEMBER———- ———- ———- ———- ——- —————- —————————————1 3 209715200 512 ONLINE CURRENT /u01/app/oracle/oradata/PROD/redo03.log1 2 209715200 512 ONLINE INACTIVE /u01/app/oracle/oradata/PROD/redo02.log1 1 209715200 512 ONLINE INACTIVE /u01/app/oracle/oradata/PROD/redo01.log   Also, execute above SQl in Standby database. Expected a.status from Standby is UNUSED, CLEARING or CLEARING_CURRENT, if output has different result, then manually clear redo logfiles. SQL> set lines 150col member for a50select a.thread#,a.group#,a.bytes,a.blocksize,b.type,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;SQL> SQL> THREAD# GROUP# BYTES BLOCKSIZE TYPE STATUS MEMBER———- ———- ———- ———- ——- ————– ——————————————1 3 209715200 512 ONLINE UNUSED /u01/app/oracle/oradata/STDBY/redo03.log1 2 209715200 512 ONLINE UNUSED /u01/app/oracle/oradata/STDBY/redo02.log1 1 209715200 512 ONLINE UNUSED /u01/app/oracle/oradata/STDBY/redo01.log   For Standby redo logfile(SRL) status, run below SQL in standby database. The status would be in UNASSIGNED OR ACTIVE. SQL> select s.thread#,s.group#,s.status,s.bytes,l.type,l.member from v$logfile l,v$standby_log s where s.group#=l.group#; THREAD# GROUP# STATUS BYTES TYPE MEMBER———- ———- ————– ———- ——- ————————————————–1 4 UNASSIGNED 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo04.log1 5 ACTIVE 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo05.log1 6 UNASSIGNED 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo06.log0 7 UNASSIGNED 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo07.log 6. Check the alert logs. Check the alert logs of primary and physical standby databases. If any errors found, resolve them. 7. Check Archive log GAP & Redo Delay apply.   Execute below SQL in primary database to get the archive log sync gap status. Expected result for GAP_STATUS is “NO GAP” and status should be “VALID”.   SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; STATUS    GAP_STATUS——— ————————VALID           NO GAP If result is other than above, the switchover should not be started. 8. Start the switchover. 8.a Verify the switchover by running below SQL in primary database. The expected result is “Database altered.” . If result is other than this or any errors reported in alert log, resolve them and ruin the verify again. SQL> ALTER DATABASE SWITCHOVER TO stdby VERIFY;Database altered. — 8.b If switchover verify is successful, then execute the below command in current primary database to switchover the database. SQL> ALTER DATABASE SWITCHOVER TO stdby;Database altered. — 8.c If 8.b step is successful, open new primary database in open mode. SQL> ALTER DATABASE OPEN;Database altered. NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————-PROD stdby READ WRITE PRIMARY stdby stdby 02-MAR-2026 06:22 — 8.d Mount the new standby database in mount state and start the MRP SQL> STARTUP MOUNT;ORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase mounted. SQL> @db NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————-PROD prod MOUNTED PHYSICAL STANDBY prod prod 02-MAR-2026 06:32 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered. 9. Post switchover, check whether archivelogs are shipped from primary and applying in standby standbydatabse or not. Run below SQL in Primary database: SQL> alter system switch logfile;System altered. SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/arch/stdbyOldest online log sequence 70Next log sequence to archive 72Current log sequence 72 Run below SQL in standby database:SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE#

Creating multitenant physical standby database in oracle 19c oracledna DataGuard May 1, 2025 This post is to demonstrate the creating multitenant physical standby for the multitenant primary database in Oracle 19c. Below are the environment details: Primary Server: prim, 192.168.56.144 DB Name: Prod PDB Name: prodpdb1 Primary db_unique_name: prod1   Standby Server: stdby, 192.168.56.145 DB Name: Prod PDB Name: prodpdb1 Standby db_unique_name: prod2 1. Enable archive log and force logging in Primary DB SQL> startup mount ORACLE instance started. Total System Global Area 2063596016 bytes Fixed Size                  9136624 bytes Variable Size             469762048 bytes Database Buffers         1577058304 bytes Redo Buffers                7639040 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /patch/prod/arch Oldest online log sequence     6 Next log sequence to archive   8 Current log sequence           8 SQL> SQL> alter database force logging; Database altered. SQL> select name,force_logging,log_mode,flashback_on from v$database; NAME           FORCE_LOGGING             LOG_MODE     FLASHBACK_ON ————– ————————————— ———— —————— PROD           YES                                     ARCHIVELOG             NO 2. Create standby redo logs in Primary database. Note: Make sure to create standby redo log files of same size of online redo logs of Primary DB. Also, create one additional standby redo logs i.e number of online redo logs +1 SQL> select group#,thread#,sequence#,bytes/1024/1024 as MB,members,status from v$log;     GROUP#    THREAD#  SEQUENCE#         MB    MEMBERS STATUS ———- ———- ———- ———- ———- —————-          1                 1                   7                      200          1           INACTIVE          2                1                   8                      200          1           CURRENT          3                1                   6                      200          1           INACTIVE   SQL> Set lines 1234 pages 1234 Col member for a50 select * from v$logfile;     GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID ———- ——- ——- ————————————————– — ———-          3         ONLINE  /u01/app/oracle/oradata/PROD/redo03.log            NO           0          2         ONLINE  /u01/app/oracle/oradata/PROD/redo02.log            NO           0          1         ONLINE  /u01/app/oracle/oradata/PROD/redo01.log            NO           0   SQL> alter database add standby logfile group 4 (‘/u01/app/oracle/oradata/PROD/redo04.log’) size 200m; Database altered. SQL> alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/PROD/redo05.log’) size 200m; Database altered. SQL> alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/PROD/redo06.log’) size 200m; Database altered. SQL> alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/PROD/redo07.log’) size 200m; Database altered. SQL> set lines 1234 pages 1234 SQL> col member for a50 SQL> select * from v$logfile;     GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID———- ——- ——- ————————————————– — ———-         3         ONLINE  /u01/app/oracle/oradata/PROD/redo03.log            NO           0         2         ONLINE  /u01/app/oracle/oradata/PROD/redo02.log            NO           0         1         ONLINE  /u01/app/oracle/oradata/PROD/redo01.log            NO           0         4         STANDBY /u01/app/oracle/oradata/PROD/redo04.log            NO           0         5         STANDBY /u01/app/oracle/oradata/PROD/redo05.log            NO           0         6         STANDBY /u01/app/oracle/oradata/PROD/redo06.log            NO           0         7         STANDBY /u01/app/oracle/oradata/PROD/redo07.log            NO           07 rows selected. 3. Update the init file of primary DB below parameters SQL> alter system set db_unique_name=’PROD1′ scope=spfile; System altered. SQL> alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prod1,prod2)’ scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=/patch/prod/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod1′ scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=prod2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=prod2′ scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; System altered. SQL> alter system SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; System altered. SQL> alter system set FAL_SERVER=prod2 scope=both; System altered. SQL> alter system set FAL_CLIENT=prod1 scope=both; System altered. SQL> alter system set DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/PROD/’,’/u01/app/oracle/oradata /PROD/’,’/u01/app/oracle/oradata/PROD/pdbseed’,’/u01/app/oracle/oradata/PROD/pd bseed’,’/u01/app/oracle/oradata/PROD/prodpdb1′,’/u01/app/oracle/oradata/PROD/pro dpdb1′ scope=spfile; System altered. SQL> alter system set LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/PROD’,’/u01/app/oracle/oradat a/PROD’ scope=spfile; System altered. SQL> alter system set standby_file_management=AUTO scope=both; System altered. Bounce the Database== SQL> shut immediate SQL> startup 4. Update the TNS entries for both Primary and Standby databases. In Primary Server= Listener.ora LISTENER_PRIM =                (DESCRIPTION_LIST =                (DESCRIPTION =                (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.144)(PORT = 1521))               (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))               )