Introduction
In Oracle, a switchover is a role transition operation between the primary and standby databases in a Data Guard environment. A switchover lets you swap roles between the primary and standby databases in a planned, controlled manner, without any data loss. This is typically done for maintenance, patching, hardware testing, or periodic load balancing between data centers, and unlike a failover, it's a graceful operation both databases participate in willingly.
Prerequisites
- Plan for roughly 30 minutes of database downtime during the switchover window.
- Confirm application and database connectivity are ready at the standby site, since it will become the new primary.
- Primary and standby databases must be fully in sync before starting. Switching over with a lag risks losing the unapplied redo.
Environment Used in This Guide
These are the primary and standby details used throughout this walkthrough, following on from our earlier standby database setup guide.
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
Step 1: Check Database Status and Sync
Before attempting a switchover, confirm both databases agree on their current roles and that the standby has applied all available redo. An ARC_DIFF of 0 on both sides means there's no unapplied redo, which is the state you want before proceeding.
At Primary:
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
-------- ---------------------- ----------------- ---------------- --------------------
ORCL ORCLDC PRIMARY READ WRITE TO STANDBY
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, 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 applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name, database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------ ---------------- -------- ---------- ----------- --------------------- ----------
ORCLDC PRIMARY 1 103 103 08-FEB-2025 20:24:32 0
At Standby:
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------- ---------------- ------------------ ------------------------ --------------------
ORCL ORCLDR PHYSICAL STANDBY READ ONLY WITH APPLY NOT ALLOWED
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF,
a.last_app_timestamp,
round((sysdate - a.last_app_timestamp) * 24 * 60, 2) Gap_in_Mins,
round((sysdate - a.last_app_timestamp) * 24 * 60 * 60, 2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp
from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name, database_role from v$database) d
where a.thread# = b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
--------------- ------------------ -------- ---------- ----------- ---------- --------------------- ------------ ---------------
ORCLDR PHYSICAL STANDBY 1 103 103 0 08-FEB-2025 20:24:32 12.85 771
Notice the primary's SWITCHOVER_STATUS shows TO STANDBY, which confirms the primary is in a valid state to switch over. The standby shows NOT ALLOWED, which is expected, the standby can't initiate the switchover itself, that command runs from the primary side.
Step 2: Verify the Switchover
Before actually switching, run a verify-only pass. This checks for problems without making any real changes, giving you a safe way to catch issues in advance.
SQL> alter database switchover to orcldr verify;
Database altered.
If this completes with no errors, you're clear to proceed to the actual switchover. If you see a warning like the one below, check the database alert log before continuing, since it usually points to a specific unresolved issue (a lagging standby, a missing archive log, or a configuration mismatch).
SQL> alter database switchover to orcldr verify
alter database switchover to orcldr verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details
Step 3: Perform the Switchover
With verification clean, run the actual switchover command on the primary. Keep an eye on the alert log during this step, it will show Oracle working through the role transition in real time.
SQL> alter database switchover to orcldr;
Database altered.
Step 4: Start the New Primary and New Standby
After the switchover command completes, both databases are left in a mounted state with their roles swapped. Start the new primary fully open for read/write, and start the new standby in mount mode with managed recovery running.
At new Primary:
SQL> startup
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 905969664 bytes
Database Buffers 536870912 bytes
Redo Buffers 7598080 bytes
Database mounted.
Database opened.
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE LOG_MODE
------- ---------------- --------------- ---------------- ------------
ORCL ORCLDR PRIMARY READ WRITE ARCHIVELOG
At new Standby:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 1157627904 bytes
Database Buffers 285212672 bytes
Redo Buffers 7598080 bytes
Database mounted.
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE, LOG_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE LOG_MODE
------- ---------------- ----------------- ----------------- ------------
ORCL ORCLDC PHYSICAL STANDBY MOUNTED ARCHIVELOG
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Notice the unique names have effectively swapped roles: ORCLDR is now PRIMARY and ORCLDC is now PHYSICAL STANDBY, the reverse of how they started. This confirms the switchover succeeded.
Step 5: Confirm Sync on the New Primary
As a final check, switch a couple of logs on the new primary and confirm the new standby is receiving and applying them, the same verification pattern used during initial setup.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Run the same sync-check queries from Step 1 against both databases to confirm ARC_DIFF is back to 0 on the new standby.
Switching Back
A switchback follows the exact same steps, just in reverse. Run the switchover command from what is now the primary (ORCLDR), pointing at ORCLDC instead, and repeat Steps 2 through 5 exactly as above.
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