Oracle 19c-Step by Step Manual Data Guard Switchover

Switchover Operation Procedure: 

After configuring data guard, databases would be either primary and standby database role, and we can altered these roles without loss of data or without resetting logs  called Switchover.

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

Data guard Configuration Details:-

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
--------- -------------------- ----------------
DBWR         READ WRITE           PRIMARY

Standby database:-

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

NAME        OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
DBWR        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-MAR-2019 18:59:20 13-MAR-2019 23:55:09   NO
4          13-MAR-2019 23:55:09 14-MAR-2019 20:04:18   NO
5          14-MAR-2019 20:04:18 14-MAR-2019 20:43:17   NO
6          14-MAR-2019 20:43:17 15-MAR-2019 01:35:09   NO
7          15-MAR-2019 01:35:09 15-MAR-2019 22:57:54   NO
8          15-MAR-2019 22:57:54 16-MAR-2019 00:11:18   NO
8          15-MAR-2019 22:57:54 16-MAR-2019 00:11:18   YES
9          16-MAR-2019 00:11:18 16-MAR-2019 00:47:08   NO
9          16-MAR-2019 00:11:18 16-MAR-2019 00:47:08   YES
10         16-MAR-2019 00:47:08 16-MAR-2019 01:54:56   NO
10         16-MAR-2019 00:47:08 16-MAR-2019 01:54:56   YES

11 rows selected.

Step 3:- 

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Note: This command will give you appropriate message about the data guard current status.

After getting confirmation on data guard smooth activity, we can instantiate switchover activity by issuing following sort of commands:

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@ram dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 02:20:17 2019
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.

Step 6:-

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

NAME         OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
DBWR         READ WRITE          PRIMARY

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
--------- -------------------- ----------------
DBWR          MOUNTED            PRIMARY

Switch over activity have completed successfully, our old primary database has become standby and old standby database has become primary database.

Note:-To test above switchover activity, generate multiple archive logs on primary database and verify those archive logs being transferred on standby database.

After switchover activity,

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Leave a Reply

Your email address will not be published. Required fields are marked *