oraclednaThis 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 300
col name for a30
col owner for a18
col proc for a8
col error for a8
col statement_opt for a16
select * 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 string
log_archive_dest_1 string LOCATION=/u01/arch/stdby VALID
_FOR=(ONLINE_LOGFILES,ALL_ROLE
S) 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
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string LOCATION=/u01/arch/log_stby/ V
ALID_FOR=(STANDBY_LOGFILES,STA
NDBY_ROLE) DB_UNIQUE_NAME=stdby
log_archive_dest_30 string
log_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 primary
2026-03-06T00:22:05.735793+05:30
ALTER DATABASE SWITCHOVER TO PRIMARY (stdby)
2026-03-06T00:22:05.736048+05:30
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY (stdby)
2026-03-06T00:22:05.936919+05:30
Thread 1 advanced to log sequence 4 (LGWR switch)
Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/STDBY/redo01.log
2026-03-06T00:22:06.670338+05:30
NET (PID:3169): Archived Log entry 3 added for T-1.S-3 ID 0x2c708ca4 LAD:1
2026-03-06T00:22:06.813880+05:30
Fri Mar 06 00:22:06 2026
Logminer Bld: Build started
2026-03-06T00:22:06.857402+05:30
ALTER SYSTEM SWITCH ALL LOGFILE start (stdby)
2026-03-06T00:22:06.987911+05:30
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/STDBY/redo02.log
2026-03-06T00:22:06.988467+05:30
ALTER SYSTEM SWITCH ALL LOGFILE complete (stdby)
2026-03-06T00:22:07.070570+05:30
ARC3 (PID:3377): LGWR is scheduled to archive to LAD:2 after log switch
2026-03-06T00:22:07.131140+05:30
Fri Mar 06 00:22:07 2026
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 2407830 LockdownSCN is 2407830
2026-03-06T00:22:08.811949+05:30
ARC3 (PID:3377): Archived Log entry 4 added for T-1.S-4 ID 0x2c708ca4 LAD:1
2026-03-06T00:22:15.905241+05:30
ALTER SYSTEM ARCHIVE LOG
2026-03-06T00:22:15.945972+05:30
Thread 1 cannot allocate new log, sequence 6
Checkpoint not complete
Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/STDBY/redo02.log
2026-03-06T00:22:17.751701+05:30
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /u01/app/oracle/oradata/STDBY/redo03.log
2026-03-06T00:22:17.759744+05:30
NET (PID:3169): LGWR is scheduled to archive to LAD:2 after log switch
2026-03-06T00:22:26.415631+05:30
NET (PID:3169): Archived Log entry 6 added for T-1.S-5 ID 0x2c708ca4 LAD:1
Fri Mar 06 00:22:26 2026
Logminer Bld: Done
2026-03-06T00:22:26.523378+05:30
Thread 1 cannot allocate new log, sequence 7
Checkpoint not complete
Current log# 3 seq# 6 mem# 0: /u01/app/oracle/oradata/STDBY/redo03.log
2026-03-06T00:22:26.759064+05:30
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /u01/app/oracle/oradata/STDBY/redo01.log
2026-03-06T00:22:26.760995+05:30
NET (PID:3169): LGWR is scheduled to archive to LAD:2 after log switch
2026-03-06T00:22:27.319744+05:30
NET (PID:3169): Archived Log entry 8 added for T-1.S-6 ID 0x2c708ca4 LAD:1
2026-03-06T00:22:27.428472+05:30
Completed: 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 standby
2026-03-06T00:38:58.327222+05:30
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (prod)
2026-03-06T00:38:58.327386+05:30
LOGSTDBY: Switchover initiated via DDL
LOGSTDBY: Ensuring no active hot backups.
LOGSTDBY: Archiving current online log files.
2026-03-06T00:38:58.385551+05:30
Thread 1 advanced to log sequence 93 (LGWR switch)
Current log# 1 seq# 93 mem# 0: /u01/app/oracle/oradata/PROD/redo01.log
2026-03-06T00:38:58.426476+05:30
NET (PID:3318): Archived Log entry 165 added for T-1.S-92 ID 0x24475078 LAD:1
2026-03-06T00:38:58.445605+05:30
LOGSTDBY: Waiting for pending archivals to all destinations.
2026-03-06T00:38:58.461268+05:30
NET (PID:3318): Waiting for all non-current ORLs to be archived
2026-03-06T00:38:58.461317+05:30
NET (PID:3318): All non-current ORLs have been archived
2026-03-06T00:38:58.461346+05:30
NET (PID:3318): Waiting for all FAL entries to be archived
2026-03-06T00:38:58.461373+05:30
NET (PID:3318): All FAL entries have been archived
2026-03-06T00:38:58.461451+05:30
NET (PID:3318): Waiting for potential Logical Standby switchover target to become synchronized
2026-03-06T00:38:58.725109+05:30
TT02 (PID:3334): SRL selected for T-1.S-93 for LAD:2
2026-03-06T00:38:59.462460+05:30
NET (PID:3318): Active, synchronized Logical Standby switchover target has been identified
2026-03-06T00:38:59.462547+05:30
LOGSTDBY: 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 completed
2026-03-06T00:38:59.572304+05:30
LOGSTDBY: Database guard enabled. User transactions are no longer permitted.
2026-03-06T00:38:59.572336+05:30
LOGSTDBY: Waiting for pending archivals to all destinations.
2026-03-06T00:38:59.585152+05:30
NET (PID:3318): Waiting for all non-current ORLs to be archived
2026-03-06T00:38:59.585205+05:30
NET (PID:3318): All non-current ORLs have been archived
2026-03-06T00:38:59.585254+05:30
NET (PID:3318): Waiting for all FAL entries to be archived
2026-03-06T00:38:59.585306+05:30
NET (PID:3318): All FAL entries have been archived
2026-03-06T00:38:59.585370+05:30
NET (PID:3318): Waiting for potential Logical Standby switchover target to become synchronized
2026-03-06T00:39:00.586930+05:30
NET (PID:3318): Active, synchronized Logical Standby switchover target has been identified
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn 2387198 [0x0000000000246cfe].
2026-03-06T00:39:00.652678+05:30
Thread 1 advanced to log sequence 94 (LGWR switch)
Current log# 2 seq# 94 mem# 0: /u01/app/oracle/oradata/PROD/redo02.log
2026-03-06T00:39:01.654020+05:30
ARC1 (PID:3327): LGWR is scheduled to archive to LAD:2 after log switch
ARC1 (PID:3327): SRL selected for T-1.S-93 for LAD:2
2026-03-06T00:39:01.835322+05:30
ARC1 (PID:3327): Archived Log entry 167 added for T-1.S-93 ID 0x24475078 LAD:1
2026-03-06T00:39:01.916197+05:30
NET (PID:3318): LOG_ARCHIVE_DEST_2 is a potential Logical Standby switchover target
NET (PID:3318): Database role changed from PRIMARY to LOGICAL STANDBY [dglc.c:1961]
2026-03-06T00:39:01.934712+05:30
Thread 1 cannot allocate new log, sequence 95
Checkpoint not complete
Current log# 2 seq# 94 mem# 0: /u01/app/oracle/oradata/PROD/redo02.log
2026-03-06T00:39:04.293898+05:30
Thread 1 advanced to log sequence 95 (LGWR switch)
Current log# 3 seq# 95 mem# 0: /u01/app/oracle/oradata/PROD/redo03.log
2026-03-06T00:39:04.303214+05:30
NET (PID:3318): Archived Log entry 169 added for T-1.S-94 ID 0x24475078 LAD:1
2026-03-06T00:39:04.316050+05:30
LOGSTDBY: Switchover complete (prod)
LOGSTDBY: enabling scheduler job queue processes.
2026-03-06T00:39:04.316142+05:30
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to logical standby
Logical Standby alert log:
==================
2026-03-06T00:38:59.725449+05:30
RFS LogMiner: Registered logfile [/u01/arch/log_stby/92_1_1196677442.arc] to LogMiner session id [1]
2026-03-06T00:38:59.780851+05:30
RFS LogMiner: RFS id [7414] assigned as thread [1] PING handler
2026-03-06T00:38:59.782815+05:30
RFS LogMiner: RFS id [7414] assigned as thread [1] PING handler
2026-03-06T00:38:59.794412+05:30
rfs (PID:7416): Primary database is in MAXIMUM PERFORMANCE mode
2026-03-06T00:38:59.879457+05:30
rfs (PID:7416): Selected LNO:4 for T-1.S-93 dbid 608515774 branch 1196677442
2026-03-06T00:39:00.234621+05:30
LOGMINER: Alternate logfile found. Transition to mining archived logfile for session 1 thread 1 sequence 92, /u01/arch/log_stby/92_1_1196677442.arc
2026-03-06T00:39:00.237497+05:30
LOGMINER: End mining logfile for session 1 thread 1 sequence 92, /u01/arch/log_stby/92_1_1196677442.arc
2026-03-06T00:39:00.242974+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 93, /u01/app/oracle/oradata/STDBY/redo04.log
2026-03-06T00:39:00.790007+05:30
RFS LogMiner: RFS id [7419] assigned as thread [1] PING handler
2026-03-06T00:39:00.791820+05:30
RFS LogMiner: RFS id [7419] assigned as thread [1] PING handler
2026-03-06T00:39:02.854967+05:30
LOGSTDBY Analyzer process AS00 server id=0 pid=59 OS id=25189 stopped
2026-03-06T00:39:02.855792+05:30
LOGSTDBY Apply process AS01 server id=1 pid=60 OS id=25197 stopped
2026-03-06T00:39:02.856289+05:30
LOGSTDBY Apply process AS02 server id=2 pid=61 OS id=25214 stopped
2026-03-06T00:39:02.856911+05:30
LOGSTDBY Apply process AS04 server id=4 pid=63 OS id=25223 stopped
2026-03-06T00:39:02.857373+05:30
LOGSTDBY Apply process AS05 server id=5 pid=64 OS id=25225 stopped
2026-03-06T00:39:02.861191+05:30
LOGSTDBY Apply process AS03 server id=3 pid=62 OS id=25221 stopped
2026-03-06T00:39:02.939295+05:30
rfs (PID:7422): Selected LNO:4 for T-1.S-93 dbid 608515774 branch 1196677442
2026-03-06T00:39:03.014582+05:30
RFS LogMiner: Registered logfile [/u01/arch/log_stby/93_1_1196677442.arc] to LogMiner session id [1]
2026-03-06T00:39:03.067162+05:30
RFS LogMiner: RFS id [7425] assigned as thread [1] PING handler
2026-03-06T00:39:03.067698+05:30
RFS LogMiner: RFS id [7425] assigned as thread [1] PING handler
2026-03-06T00:39:03.522766+05:30
LOGMINER: Alternate logfile found. Transition to mining archived logfile for session 1 thread 1 sequence 93, /u01/arch/log_stby/93_1_1196677442.arc
2026-03-06T00:39:03.523867+05:30
LOGMINER: End mining logfile for session 1 thread 1 sequence 93, /u01/arch/log_stby/93_1_1196677442.arc
2026-03-06T00:39:03.527992+05:30
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=58 OS id=25186 sid=70 stopped
2026-03-06T00:39:03.531049+05:30
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=56 OS id=25182 sid=74 stopped
2026-03-06T00:39:03.543734+05:30
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=57 OS id=25184 sid=77 stopped
2026-03-06T00:39:03.563372+05:30
LOGSTDBY status: ORA-16257: Switchover initiated stop apply successfully completed
…. (PID:25174): Current role is logical and force mode is not set, not clearing SRLs
8. Perform the switchover on Logical Standby Database.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
—————— ——————–
LOGICAL STANDBY TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select name,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
———- ————— ———- ——————
LOGSTDBY stdby READ WRITE PRIMARY
Logical Standby alert log:
===================
alter database commit to switchover to primary
2026-03-06T00:44:44.978184+05:30
ALTER DATABASE SWITCHOVER TO PRIMARY (stdby)
2026-03-06T00:44:44.978274+05:30
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (stdby)
2026-03-06T00:44:44.978513+05:30
LOGSTDBY: Switchover initiated via DDL
LOGSTDBY: Starting SCN of new stream from seeded lockdown [0x000000000024bd96]
2026-03-06T00:44:45.017484+05:30
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn [2416699].
LOGSTDBY: Successful close of the current log stream:
LOGSTDBY: primary: [608515774]
LOGSTDBY: first scn: [0x0000000000000000]
LOGSTDBY: end scn: [0x0000000000246d04]
LOGSTDBY: processed scn: [0x0000000000246d05]
2026-03-06T00:44:45.046732+05:30
LOGSTDBY: terminating active RFS connections for role change
LOGSTDBY: terminated RFS process [7419]
LOGSTDBY: terminated RFS process [7422]
LOGSTDBY: terminated RFS process [24345]
LOGSTDBY: (dglcccsp) Archiving standby redo logfiles.
LOGSTDBY: (dglcccsp) Not using surrogate archiving mode
LOGSTDBY: (dglcccsp) Complete. No standby redo logfiles needed archiving.
2026-03-06T00:44:45.049167+05:30
Process termination requested for pid 7419 [source = rdbms], [info = 0] [request issued by pid: 3169, uid: 543232]
2026-03-06T00:44:45.049298+05:30
Process termination requested for pid 7422 [source = rdbms], [info = 0] [request issued by pid: 3169, uid: 543232]
2026-03-06T00:44:45.049415+05:30
Process termination requested for pid 24345 [source = rdbms], [info = 0] [request issued by pid: 3169, uid: 543232]
2026-03-06T00:44:45.066519+05:30
NET (PID:3169): Database role cleared from LOGICAL STANDBY [dglc.c:1953]
Starting background process LSP1
2026-03-06T00:44:45.081073+05:30
LSP1 started with pid=58, OS id=8469
2026-03-06T00:44:45.081661+05:30
LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Created
2026-03-06T00:44:45.084642+05:30
LOGSTDBY: (LSP1) Archiving online logs as a primary database
2026-03-06T00:44:45.085827+05:30
LOGSTDBY: Disabling database guard.
2026-03-06T00:44:45.113564+05:30
TT03 (PID:8471): Switchover in progress, stop clearing SRLs
2026-03-06T00:44:45.159971+05:30
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0: /u01/app/oracle/oradata/STDBY/redo02.log
2026-03-06T00:44:45.169439+05:30
LOGSTDBY: Database guard disabled. User transactions are now permitted.
LOGSTDBY: enabling scheduler job queue processes.
2026-03-06T00:44:45.171881+05:30
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to primary
2026-03-06T00:44:45.259658+05:30
LSP1 (PID:8469): Archived Log entry 10 added for T-1.S-7 ID 0x2c708ca4 LAD:1
LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Completed
2026-03-06T00:44:45.383821+05:30
ARC1 (PID:3373): SRL selected for T-1.S-7 for LAD:2
9. Start the SQL Apply process on new Logical Standby Database.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
10. Check the sync.
On Primary:
=========
SQL> select thread#,max(sequence#) from v$log_history group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 10
On Logical Standby Standby:
=====================
SQL> select thread#,max(sequence#) from dba_logstdby_log where applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 10
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.
