Description:
After configuring Data Guard, databases operate in either the primary or standby role, which can be switched dynamically without data loss or resetting redo logs.
This process, known as a Switchover, can be executed using specific commands.
Configuration Details:
| Environment detail | Primary | Standby |
| SID | SOURCE | SOURCE |
| DB Role | Primary | Physical standby |
| Host | prime.localdomain.com | stand.localdomain.com |
| DB Version | 19.3.0.0 | 19.3.0.0 |
| OS | RHEL 7.9 | RHEL 7.9 |
Step 1:- Check database role and database name
Primary database:-
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
SOURCE READ WRITE PRIMARY
Standby database:-
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
SOURCE MOUNTED PHYSICAL STANDBY
Precheck for Switchover:- (PRIMARY SIDE)
Before performing switchover, kindly verify the state of data guard on both the instances by following SQL queries:
Step 2:-
SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
———- ——————– ——————– ———
3 13-DEC-2024 18:59:20 13-DEC-2024 23:55:09 NO
4 13-DEC-2024 23:55:09 14-DEC-2024 20:04:18 NO
5 14-DEC-2024 20:04:18 14-DEC-2024 20:43:17 NO
6 14-DEC-2024 20:43:17 15-DEC-2024 01:35:09 NO
7 15-DEC-2024 01:35:09 15-DEC-2024 22:57:54 NO
8 15-DEC-2024 22:57:54 16-DEC-2024 00:11:18 NO
8 15-DEC-2024 22:57:54 16-DEC-2024 00:11:18 YES
9 16-DEC-2024 00:11:18 16-DEC-2024 00:47:08 NO
9 16-DEC-2024 00:11:18 16-DEC-2024 00:47:08 YES
10 16-DEC-2024 00:47:08 16-DEC-2024 01:54:56 NO
10 16-DEC-2024 00:47:08 16-DEC-2024 01:54:56 YES
11 rows selected.
Step 3:-
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
TO STANDBY
The switchover_status column of v$database can have the following values:
Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request
On Primary database:-
Step 4:
SQL> alter database commit to switchover to standby;
Database altered.
Step 5:-
shutdown immediate
startup nomount
alter database mount standby database
[oracle@localhost dbs]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 – Production on WED Dec 08 14:13:07 2024
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
SOURCE MOUNTED PHYSICAL STANDBY
On Standby database:
Step 7:-alter database commit to switchover to primary;
SQL> alter database commit to switchover to primary;
Database altered.
Step 8:-
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
SOURCE MOUNTED PRIMARY
Switch over activity have completed successfully, our old primary database has become standby and old standby database has become primary database.
Conclusion:
The manual switchover process in Oracle 19c successfully transitions the roles of the primary and standby databases without data loss or resetting redo logs. By following the detailed steps, the primary database is converted into a standby database, while the standby assumes the role of the primary. This ensures minimal downtime and maintains data consistency during role transitions, enhancing the reliability and flexibility of the Data Guard configuration.
