Introduction
A switchover is a role reversal between the primary database and one of its standby databases. In this article we will see how to perform a switchover activity in a Logical Standby setup.
Prerequisites
- Logical standby should be configured and in sync.
- 30 mins downtime at database end.
Environment Used in this Guide
| Server | Primary | Standby |
|---|---|---|
| Hostname | dcdb.oraeasy.com | drdb.oraeasy.com |
| OS | OEL 9 | OEL 9 |
| SID | ORADBDC | ORADBDR |
| Service Name | ORADBDC | ORADBDR |
Switchover Activity Step by Step
Step 1: Check the Sync Between Primary and Standby
Confirm the logical standby is fully caught up before starting a switchover. Attempting one against a lagging standby risks data loss or an extended outage.
Primary:
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORADB oradbdc PRIMARY READ WRITE
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
73
Standby:
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
DRDB oradbdr LOGICAL STANDBY READ WRITE
SQL> SELECT NAME, VALUE, UNIT FROM V$DATAGUARD_STATS;
NAME VALUE UNIT
-------------------------------- ---------------------------------------------------------------- ------------------------------
transport lag +00 00:00:05 day(2) to second(0) interval
apply lag +00 00:00:05 day(2) to second(0) interval
apply finish time +00 00:00:00.001 day(2) to second(3) interval
estimated startup time 26 second
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
48 2359387 2379644 18-DEC-25 YES 141188 512
49 2379644 2410748 18-DEC-25 YES 182159 512
50 2410748 2412399 18-DEC-25 YES 1924 512
51 2412399 2412440 18-DEC-25 YES 118 512
52 2412440 2414499 18-DEC-25 YES 1178 512
53 2414499 2416312 18-DEC-25 YES 1147 512
54 2416312 2416320 18-DEC-25 YES 14 512
55 2416320 2416401 18-DEC-25 YES 52 512
56 2416401 2417392 18-DEC-25 YES 568 512
57 2417392 2419821 18-DEC-25 YES 15093 512
58 2419821 2419827 18-DEC-25 YES 13 512
59 2419827 2422503 18-DEC-25 YES 2086 512
60 2422503 2424463 18-DEC-25 YES 1083 512
61 2424463 2425309 18-DEC-25 YES 474 512
68 2438855 2440179 20-DEC-25 YES 1165 512
62 2425309 2426595 18-DEC-25 YES 703 512
39 2355105 2355125 17-DEC-25 YES 40 512
63 2426595 2429603 18-DEC-25 YES 39291 512
64 2429603 2432435 18-DEC-25 YES 2273 512
65 2432435 2434380 18-DEC-25 YES 1082 512
66 2434380 2436356 18-DEC-25 YES 1116 512
67 2436356 2438855 18-DEC-25 YES 10530 512
38 2354571 2355105 17-DEC-25 YES 38359 512
41 2358171 2358214 17-DEC-25 YES 22 512
42 2358214 2358226 17-DEC-25 YES 20 512
43 2358226 2358460 17-DEC-25 YES 120 512
44 2358460 2358471 17-DEC-25 YES 16 512
45 2358471 2358481 17-DEC-25 YES 16 512
46 2358481 2359121 17-DEC-25 YES 350 512
70 2459307 2462128 20-DEC-25 YES 2515 512
47 2359121 2359387 18-DEC-25 YES 218 512
71 2462128 2464114 20-DEC-25 YES 1246 512
72 2464114 2464739 20-DEC-25 YES 327 512
73 2464739 2464749 20-DEC-25 CURRENT 15 512
40 2355125 2358171 17-DEC-25 YES 2685 512
69 2440179 2459307 20-DEC-25 YES 144626 512
36 rows selected.
Zero transport lag and apply lag, together with the standby's dba_logstdby_log entries all showing APPLIED, confirm it's safe to begin the switchover.
Step 2: Prepare to Switchover on the Primary
The switchover process starts on the primary by signaling its intent to become the new standby.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> alter database prepare to switchover to logical standby;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
SWITCHOVER_STATUS moving from TO STANDBY to PREPARING SWITCHOVER confirms the primary has begun the handoff and is flushing final redo to the standby.
Step 3: Prepare to Switchover on the Standby
The standby must independently prepare for its new role as primary.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> alter database prepare to switchover to primary;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
SWITCHOVER_STATUS moving to PREPARING SWITCHOVER on the standby confirms both databases are now aligned and ready for the actual role switch.
Step 4: Commit to Switchover on the Primary
Check the switchover status on the primary first, and only run the commit command once it reads TO LOGICAL STANDBY. This is the go/no-go check, since committing before this status appears can fail the switchover.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY
SQL> alter database commit to switchover to logical standby;
Database altered.
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORADB oradbdc LOGICAL STANDBY READ WRITE
DATABASE_ROLE now reporting LOGICAL STANDBY confirms the former primary (oradbdc) has completed its role change.
Step 5: Commit to Switchover on the Standby
Check the switchover status on the standby, and only run the commit command once it reads TO PRIMARY.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
DRDB oradbdr PRIMARY READ WRITE
DATABASE_ROLE now reporting PRIMARY confirms the former standby (oradbdr) is now the primary database, completing the role reversal.
Step 6: Switch Logs on the New Primary and Check Sync
With roles reversed, confirm the new configuration is replicating correctly. Logical standby apply has to be explicitly restarted on the new standby after a switchover, since it doesn't resume automatically the way physical standby recovery does.
Primary:
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
31
Standby:
SQL> alter database start logical standby apply immediate;
Database altered.
SQL> SELECT NAME, VALUE, UNIT FROM V$DATAGUARD_STATS;
NAME VALUE UNIT
-------------------------------- ------------------- ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval
apply lag +00 00:00:00 day(2) to second(0) interval
apply finish time +00 00:00:00.000 day(2) to second(3) interval
estimated startup time 28 second
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
27 2497644 2497970 20-DEC-25 YES 38333 512
28 2497970 2497978 20-DEC-25 YES 7 512
29 2497978 2501570 20-DEC-25 YES 16436 512
30 2501570 2503802 20-DEC-25 YES 10108 512
31 2503802 2503810 20-DEC-25 YES 14 512
SQL>
Zero lag on all V$DATAGUARD_STATS metrics confirms the new standby (the former primary, oradbdc) is fully caught up under its new role.
Step 7: Create a User on the New Primary and Check on the New Standby
Final verification: create a user on the new primary and confirm it replicates to the new standby, proving the switched-over configuration works end to end.
Primary:
SQL> def
DEFINE _DATE = "20-DEC-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oradbdr" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1928000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1928000000" (CHAR)
SQL> select username from dba_users where username='TEST2';
no rows selected
SQL> create user TEST2 identified by Test##123;
User created.
SQL> select username from dba_users where username='TEST2';
USERNAME
-------------------------------------
TEST2
SQL> alter system switch logfile;
System altered.
Standby:
SQL> def
DEFINE _DATE = "20-DEC-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oradbdc" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1928000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1928000000" (CHAR)
SQL>
SQL> select username from dba_users where username='TEST2';
USERNAME
------------------------------
TEST2
The TEST2 user appearing on the new standby (oradbdc) confirms the switchover is complete and replication is functioning correctly in the reversed direction.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment