Database Switchover and Switchback

Prerequisites:
1. Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

Query to check if the managed recovery process is running on the standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CLOSING             157
ARCH      CLOSING             155
ARCH      CONNECTED             0
ARCH      CLOSING             156
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                158
MRP0      WAIT_FOR_LOG        158

The above fig. shows that the Managed Recovery Process (MRP0) is running on the standby database. If MRP is not running, then start the process with real time enabled using the below query in the standby database.

SQL>alter database recover managed standby disconnect from session;

Once when the MRP has started on the standby database, make sure that the archive logs generated at the primary end are shipped and getting applied to the standby database.

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

 THREAD# MAX(SEQUENCE#)
---------- --------------
     1            163

In the above case, sequence# 163 is the maximum sequence generated at the primary database and the same has been applied to the standby database.

2. Verify primary and standby tempfiles match

For each temporary tablespace on the standby, verify that temporary files associated with that tablespace on the primary database also exist on the standby database. Tempfiles added after initial standby creation are not propagated to the standby. Run this query on both the primary and target physical standby databases and verify that they match.

 SQL>select tmp.name filename,bytes,ts.name tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;

3.  Verify that all datafiles are online on both primary and standby databases

Check whether all the datafiles are online prior to the switchover on both primary and standby databases

SQL>select name from v$datafile where status=’OFFLINE’;no rows selected

4. Check if there are any jobs running on the primary database using the below query.

SQL> select * from dba_jobs_running;

no rows selected

If there are any jobs running on the primary database and if its execution is not very important, then terminate the job to continue further.

Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no jobs running during switchover.

SQL> sho parameter job_que

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

In the above fig. the job_queue_processes parameter is set to 1000. Set this parameter to the value 0.

SQL>alter system set job_queue_processes=0 scope=spfile;

System altered

5. Query the switchover_status column of the v$database view on the primary database to determine whether the primary database can be switched over to the standby.

SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

--------------------
TO STANDBY

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.

6.  Ensure standby log files exist on the primary database.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

7. Verify that there are no redo transport errors or redo gaps at the standby database by querying the V$ARCHIVE_DEST_STATUS view on the primary database.

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS GAP_STATUS
---------    ------------------------
VALID    NO GAP

Do not proceed until the value of the STATUS column is VALID and the value of the GAP_STATUS column is NOGAP, for the row that corresponds to the standby database.

8.  Ensure both tns listeners are pinging and registered with the database.

$tnsping  tnsstby

9. Make sure all datagaurd parameters for switchover configured. Like fall_server, log_archive_dest and log_archive_dest_state

Procedure:

primary name: db1
standby name: stby

on primary (db1)

sql> alter system set log_archive_dest_state_2='defer';
sql> select max(sequence#) from v$log_history;
sql> atler database commit to switchover to physical standby;
sql> shut immediate;

sql> startup mount;
sql> select name, open_mode, database_role from v$database;
sql> select max(sequence#) from v$log_history;

on standby (stby)

sql> alter database recover managed standby database cancel;
sql> select max(sequence#) from v$log_history;
sql> atler database commit to switchover to primary;
sql> shut immediate;

sql> startup 
sql> select name, open_mode, database_role from v$database;
sql> select max(sequence#) from v$log_history;
sql> alter system switch logfile;
sql> alter system switch logfile;
sql> select max(sequence#) from v$log_history;

on primary (db1)
sql> alter database recover managed standby database disconnect from session;
sql> select max(sequence#) from v$log_history;


switching back

on standby (db1)

sql> alter database recover managed standby database cancel;
sql> shut immediate

sql> startup 
sql> alter database commit to switchover to primary;
sql> shut immediate
sql> startup

on primary (stby)

sql> select name, open_mode, database_role from v$database;
sql> atler database commit to switchover to physical standby;
sql> shut immediate;

sql> startup mount;
sql> startup nomount
sql> alter database mount standby database;

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

sql> alter database recover managed standby database disconnect from session;
sql> select max(sequence#) from v$log_history;

on standby (db1)

sql> alter system set log_archive_dest_state_2='enable';
sql> select max(sequence#) from v$log_history;
sql> alter system switch logfile;
sql> /
sql>/
sql> select max(sequence#) from v$log_history;

on primary (stby)

sql> select max(sequence#) from v$log_history;