oraclednaThis 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 a14
col process for a10
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 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 a60
select 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 a60
select 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 150
col member for a50
select 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.log
1 2 209715200 512 ONLINE INACTIVE /u01/app/oracle/oradata/PROD/redo02.log
1 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 150
col member for a50
select 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.log
1 2 209715200 512 ONLINE UNUSED /u01/app/oracle/oradata/STDBY/redo02.log
1 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.log
1 5 ACTIVE 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo05.log
1 6 UNASSIGNED 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo06.log
0 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 bytes
Fixed Size 8898512 bytes
Variable Size 486539264 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
Database 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 Mode
Automatic archival Enabled
Archive destination /u01/arch/stdby
Oldest online log sequence 70
Next log sequence to archive 72
Current log sequence 72
Run below SQL in standby database:
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#; 2 3 4
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 71 71 0
The archive logs shipping is happening from primary database server to standby database server and applying in physical standby database. The archive log sync gap is Zero.
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.
