Oracle 12c-Step by Step Manual Switchover | Data Guard

Overview

Oracle Database Switchover is a planned role transition between primary and standby databases with zero data loss. This guide provides a step-by-step procedure for performing a manual switchover in Oracle 12c.

What You'll Learn:

• ✓ How to verify database readiness for switchover
• ✓ Converting primary to standby database
• ✓ Converting standby to primary database
• ✓ Validating successful role transition

Prerequisites:

• Functioning Oracle Data Guard configuration
• Oracle 12c Database
• SYSDBA privileges
• Proper network connectivity between hosts

⏱️ Estimated Time: 20-30 minutes

Oracle 12c Step-by-Step Manual Switchover Guide

A database switchover is a planned operation where you exchange the roles of a primary database and its standby database. This is typically performed during maintenance windows to minimize downtime. Below is a detailed walkthrough of a successful switchover operation.

What is a Switchover?

A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-enable either database.

Step 1: Verify the Current Configuration

First, check the current database role and status on the primary:

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- --------------- --------------- --------------- ORADB PRIMARY READ WRITE ARCHIVELOG

Step 2: Check Archive Log Status

SQL> col destination for a70
SQL> select b.destination,a.dest_id,b.ERROR,max(a.FIRST_TIME),max(a.sequence#) from gvarchived_log a, gvarchive_dest_status b where (a.dest_id='1' or a.applied ='YES') and a.dest_id=b.dest_id group by b.destination,a.dest_id,b.ERROR order by a.dest_id;
DESTINATION DEST_ID ERROR MAX(A.FIR MAX(A.SEQUENCE#)
---------------------------------------------------- ------- -------- --------- ---------------
/u01/app/oracle/product/12.1.0/db_1/dbs 1 04-AUG-24 68
oradbdr 2 04-AUG-24 68

Step 3: Convert Primary to Standby

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Step 4: Monitor the Switchover Process

The alert log will show the progress of the switchover:

Alter LogFile ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY Sun Aug 04 19:45:20 2024 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 8103] (oradb) Sun Aug 04 19:45:20 2024 Waiting for target standby to receive all redo Sun Aug 04 19:45:20 2024 Waiting for all non-current ORLs to be archived... Sun Aug 04 19:45:20 2024 All non-current ORLs have been archived. Sun Aug 04 19:45:20 2024 Waiting for all FAL entries to be archived... Sun Aug 04 19:45:20 2024 All FAL entries have been archived. Sun Aug 04 19:45:20 2024 Waiting for potential Physical Standby switchover target to become synchronized... Sun Aug 04 19:45:21 2024 Active, synchronized Physical Standby switchover target has been identified Preventing updates and queries at the Primary Generating and shipping final logs to target standby Switchover End-Of-Redo Log thread 1 sequence 69 has been fixed Switchover: Primary highest seen SCN set to 0x0.0x26a747 ARCH: Noswitch archival of thread 1, sequence 69 ARCH: End-Of-Redo Branch archival of thread 1 sequence 69 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received all redo Waiting for target standby to apply all redo Final check for a synchronized target standby. Check will be made once. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target Active, synchronized target has been identified Target has also received all redo Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_8103.trc Converting the primary database to a new standby database Clearing standby activation ID 2826499478 (0xa878f596) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Archivelog for thread 1 sequence 69 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required USER (ospid: 8103): terminating the instance Sun Aug 04 19:45:24 2024 Instance terminated by USER, pid = 8103 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY Shutting down instance (abort) License high water mark = 5 Sun Aug 04 19:45:24 2024 Instance shutdown complete Sun Aug 04 19:45:43 2024

Step 5: Start the New Standby Database

[oracle@srv1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 4 19:45:37 2024 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance.
SQL> def
DEFINE _DATE = "04-AUG-2024 19:45:40" (CHAR) >DEFINE _CONNECT_IDENTIFIER = "oradb" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "" (CHAR) DEFINE _O_RELEASE = "" (CHAR)
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1761607680 bytes Fixed Size 2925360 bytes Variable Size 587205840 bytes Database Buffers 1157627904 bytes Redo Buffers 13848576 bytes Database mounted. Database opened.

Step 6: Verify the New Standby Status

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- -------------------- --------------- ---------------
ORADB PHYSICAL STANDBY READ ONLY ARCHIVELOG

Step 7: Start Managed Recovery

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- -------------------- -------------------------- ---------------
ORADB PHYSICAL STANDBY READ ONLY WITH APPLY ARCHIVELOG

Step 8: Convert the Standby to Primary

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_PROCESS SEQUENCE# STATUS
---------- ---------------- ------------ ---------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS LGWR 69 IDLE
RFS ARCH 0 IDLE
MRP0 N/A 69 WAIT_FOR_LOG
7 rows selected.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.

Step 9: Open the New Primary Database

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- --------------- ------------ ---------------
ORADB PRIMARY MOUNTED ARCHIVELOG

SQL> alter database open;
Database altered.

Step 10: Verify the Final Configuration

SQL> select INST_ID,name,open_mode,database_role,log_mode,controlfile_type from gv$database;
INST_ID NAME OPEN_MODE DATABASE_ROLE LOG_MODE CONTROL
------- --------- ------------ --------------- ------------- ---------
1 ORADB READ WRITE PRIMARY ARCHIVELOG CURRENT

Note: The switchover is now complete! The original standby database is now the primary, and the original primary is now the standby.

Key Takeaways

• Always verify database roles and synchronization before initiating a switchover
• Monitor the alert logs during the process to track progress
• Remember to start managed recovery on the new standby after switchover
• Perform a final verification to ensure both databases are in their intended roles
• Document the entire process for future reference and knowledge sharing

Have you performed database switchovers in your environment? What challenges did you face? Share your experiences in the comments below!

Please feel free to ask. thank you 🙂
Toufique Khan