How to Perform Manual Switchover in Dataguard

How to Perform Manual Switchover in Oracle 

PRIMARY DATABASE OPERATIONS

1.       Check if Switchover can take place or not. The SWITCHOVER_STATUS should have the value of TO STANDBY or SESSIONS ACTIVE. If not then redo transport is not functioning properly and you will not be able to perform the switchover. If you received SESSIONS ACTIVE in the prior step you might have connections that may prevent the switchover. Verify the active connections by querying V$SESSION.

2.       Below Select statement SHOULD (Mandatory) RETURN "TO_STANDBY", If not FIX ISSUES first, WARNING !!!!

select name, open_mode, database_role, switchover_status from v$database;

3.       Trace File Settings - Change Date-Time to current time

alter session set tracefile_identifier='SWITCHOVER_<Date-Time>';

4.       Execute to convert primary DB to Standby Database

alter database commit to switchover to physical standby with session shutdown;

5.       Check Alert logfile for below text :

"Switchover: Primary controlfile converted to standby controlfile succesfully.

“Switchover: Complete - Database shutdown required"

6.       Start Primary Database as standby by executing below commands

shutdown immediate;

startup nomount;

alter database mount standby database;

7.       Defer the remote archive destination on the old primary( new standby)

alter system set log_archive_dest_state_2=defer;

8.       Check Database Role

select DATABASE_ROLE from v$database;

STANDBY DATABASE OPERATIONS

1.       WARNING !!!! Below Select statement SHOULD (Mandatory) RETURN "TO_PRIMARY", if not FIX ISSUES first.

select name, open_mode, database_role, switchover_status from v$database;

2.       Trace File Settings - Change Date-Time to current time

alter session set tracefile_identifier='SWITCHOVERTEST_STBY_<Date-Time>';

alter database commit to switchover to primary with session shutdown;

alter database open;

3.       Check alert.log file of Standby Database for text:

Switchover: Complete - Database mounted as primary"

4.       Enable Dest-2, again.

alter system set log_archive_dest_state_2=enable;

5.       Execute on both Primary and Standby Databases

select instance_name,status, database_status, instance_role,active_state from v$instance;

6.       Check Database Role

select DATABASE_ROLE from v$database;

7.       Run on NEW standby Database (Former primary)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE;

8.       Applying Logs without opening the Database (only in Mount State)

alter database recover managed standby database disconnect from session; 

9.       Switch logfile on New Primary DB 

ALTER SYSTEM SWITCH LOGFILE;

10. Match sequence to know last applied log file on standby database with one of below Select statements.  If sequence number is not same, then redo log is not functioning properly and switchover will not be successful. FIX REDO ISSUES FIRST, WARNING!!!!!!!!

select max(sequence#) from v$archived_log;

select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb 

where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb 

where val.resetlogs_change# = vdb.resetlogs_change# and val.applied='YES' group by thread# order by 1;