- 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 do Switchover activity in Logical Standby setup.
- Prerequisites:
- Logical standby should be configured and in sync.
- 30 mins downtime at database end.
- Environment:
- Now proceed to Switchover activity step by step:
| Server | Primary | Standby |
|---|---|---|
| Hostname | dcdb.oraeasy.com | drdb.oraeasy.com |
| OS | OEL 9 | OEL 9 |
| SID | ORADBDC | ORADBDR |
| Service Name | ORADBDC | ORADBDR |
1. Check the sync between Primary and Standby.
==> 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.
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
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
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
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
==> 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>
==> 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>
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
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
Comments
Post a Comment