Primary and physical standby database manual switchover in Oracle 19c Database

Primary and physical standby database manual switchover in Oracle 19c Database

During the switchover process:

-Current Primary Database becomes Physical Standby
-Current Physical Standby Database becomes Primary
-No data loss occurs when both databases are synchronized.
-This is typically performed for planned maintenance, DR testing, OS patching, or data center migration.

Validation Checklist

-Primary and Standby databases are running the same Oracle Database version.
-Primary and Standby databases are on the same Release Update (RU) level.
-Oracle Home patch inventory matches on both servers.
-No pending database upgrade activity exists.
-Data Guard configuration status is healthy.

Environment              	     Primary Database        Standby Database
----------------               -------------------       -------------------------
OS Version                 	 RHEL 8                	       RHEL 8
DB Version                 	 19.17.0.0.0            	       19.17.0.0.0 
Database Role                 Primary               	       Physical Standby
Database SID                  orclprd               	       orclprd
instance name              	 orclprd               	       orclstby 
Database unique name  orclprd               	       orclstby 


Check Oracle Database Version on Both Primary and Physical Standby Databases

Before starting the manual switchover process, it is mandatory to verify that both the Primary and Physical Standby databases are running the same Oracle Database version and patch level. A consistent Oracle software version across both sites helps ensure a smooth role transition during the switchover operation.

On Primary Database
SQL> select instance_name, status, version, open_mode, database_role from V$database, v$instance;

INSTANCE_NAME         STATUS     VERSION         OPEN_MODE       DATABASE_ROLE
-------------------------------------------------------------------------------
orclprd                 OPEN      19.0.0.0.0      READ WRITE         PRIMARY

On Standby Database
SQL> select instance_name, status, version, open_mode, database_role from V$database, v$instance;

INSTANCE_NAME      STATUS      VERSION        OPEN_MODE       DATABASE_ROLE
-----------------------------------------------------------------------------
orclstby         MOUNTED     19.0.0.0.0       MOUNTED      PHYSICAL STANDBY

Verify the Primary & Standby database are sync., Issue the below query.

On Primary Database
SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE dest_id=2 and applied = 'YES' GROUP BY thread#) a,(SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

THREAD#     LAST_SEQ      APPLIED_SEQ     LAST_APP_    ARC_DIFF
----------------------------------------------------------------
1            43020           43020        21-NOV-25       0

On Standby Database

SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

THREAD#      MAX(SEQUENCE#)
------------------------------
1                43020

Check the Status on PRIMARY
SQL> select switchover_status from V$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Check the Status on PRIMARY
SQL> select switchover_status from V$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

Check the flashback retention policy of database, archivelog mode & flashback status
SQL> show parameter db_flashback_retention_target

NAME                                  TYPE          VALUE
---------------------------------------------------------
db_flashback_retention_target       integer         1440

SQL> select log_mode, flashback_on, open_mode from v$database;

LOG_MODE         FLASHBACK_ON         OPEN_MODE
-------------------------------------------------
ARCHIVELOG          YES             READ WRITE

Create a Restore Point as part of fallback plan
SQL> create restore point pridb_restore_21NOV25 guarantee flashback database;

Restore point created.

SQL> col name for a50

SQL> select scn,time,name,storage_size from v$restore_point;

SCN            TIME                                   NAME                                  STORAGE_SIZE
---------------------------------------------------------------------------------------------------------
14362303245   21-NOV-25 10.13.43.000000000 PM     SWITCHOVER_21NOV25_11_10_CDB_PDB          4335017120

On Primary Database
Converted primary into standby database by using below command.

SQL> alter database commit to switchover to standby with session shutdown;
Database Altered.
Shutdown the Database

SQL> shudown immediate;

Startup old primary database with nomount fallowed by mount as standby database or directly started database with mount state.
SQL> startup nomount;
SQL> alter database mount standby database;

Start the recovery process on New Switchover Standby database.
SQL> alter database recover managed standby database using current logfile disconnect from session;

Check the status of Switchover database., it should changed from Primary to Physical Standby.
SQL> select name,open_mode, database_role from V$database;

NAME         OPEN_MODE              DATABASE_ROLE
-------------------------------------------------
ORCLPRD       MOUNTED             PHYSICAL STANDBY

On Standby Database
Conversion of standby to Primary Database
SQL> alter database commit to switchover to primary with session shutdown;
Database Altered.

Check the Swichover DB Status
SQL> select name,open_mode, database_role from V$database;

NAME          OPEN_MODE          DATABASE_ROLE
---------------------------------------------
ORCLPRD       READ WRITE            PRIMARY

Note: Primary database role have been changed to Standby and vice-versa.