Oracle 12c-Step by Step Manual Data Guard Switchover

💡 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

Step 1:- Check database role and database name

Primary database:-

select name,open_mode,database_role from v$database;

Standby database:-

select name,open_mode,database_role from v$database

Step 2:

ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

Step 3:

select switchover_status from v$database;

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 databaseTo 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:-

Step4:

alter database commit to switchover to standby;

Step 5:

shutdown immediate

startup nomount

alter database mount standby database;

Step 6:

select name,open_mode,database_role from v$database;

alter database recover managed standby database disconnect from session;

On Standby database:

Step 7:

alter database commit to switchover to primary;

Step 8:

select name,open_mode,database_role from v$database;

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,