How to do Quick Switchover with Physical Standby Database

SwitchOver is a feature of Oracle Data Guard in which a primary database is allowed to switch roles with its standby database. In process of Switch , there is no data loss. we can move back to original primary database by making another switch over called as 'SWITCH BACK' .

By performing Switch over , Down time can be reduced to a great extent. In Switch over , standby database becomes primary database in open and read write mode and Original Primary becomes standby in mounted mode. Once our work with Original Primary completed we can switch back their roles. There is no data loss in this process.

Prerequisites for SwitchOver :-

1. Original Primary has to be in Open mode and Standby to be in Mounted mode.
2. There are no active users to be there in Primary database.
3. Perform a logswitch in Primary database before applying switchover.

Switchover steps :-

1. Initiate the Switch over process in primary database :-

SQL> alter system switch logfile;

System altered.

2. Check on both primary and standby that last redo has been applied.

Primary :-
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#; 

 SEQUENCE# FIRST_TIME                    NEXT_TIME
---------- ----------------------------- -----------------------------
       21 25-JUL-2013 12:23:36          25-JUL-2013 12:25:59
        22 25-JUL-2013 12:25:59          25-JUL-2013 12:48:09
        22 25-JUL-2013 12:25:59          25-JUL-2013 12:48:09

Standby :-

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

       20 25-JUL-2013 12:23:02 25-JUL-2013 12:23:36 YES

        21 25-JUL-2013 12:23:36 25-JUL-2013 12:25:59 YES

        22 25-JUL-2013 12:25:59 25-JUL-2013 12:48:09 YES

3. Change Primary database to standby mode :-

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

Database altered.

SQL> SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP NOMOUNT;

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2213384 bytes

Variable Size             817891832 bytes

Database Buffers          213909504 bytes

Redo Buffers                9871360 bytes

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

you will get below mentioned output in your alert log :-

All non-current ORLs have been archived.

Clearing online redo logfile 1 /pacs/oracle/TEST/TEST/redo01.log

Clearing online log 1 of thread 1 sequence number 25

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /pacs/oracle/TEST/TEST/redo02.log

Clearing online log 2 of thread 1 sequence number 23

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /pacs/oracle/TEST/TEST/redo03.log

Clearing online log 3 of thread 1 sequence number 24

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Clearing online redo logfile 3 complete

Media Recovery Waiting for thread 1 sequence 26

4. Change standby database to Primary :-

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> SHUTDOWN IMMEDIATE;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2213384 bytes

Variable Size             666896888 bytes

Database Buffers          364904448 bytes

Redo Buffers                9871360 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

5. Check Log Transport from primary to Standby :-

SQL> alter system switch logfile;

System altered.

Output in Primary database alert log PFB :-

Thread 1 advanced to log sequence 30 (LGWR switch)

  Current log# 5 seq# 30 mem# 0: /disk1/STBY/STBY/online_redo02.log

Thu Jul 25 14:07:17 2013

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

Thu Jul 25 14:07:17 2013

Archived Log entry 29 added for thread 1 sequence 29 ID 0x7e5a4c15 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 30 for destination LOG_ARCHIVE_DEST_2

Output in Standby database alert log :-

Thu Jul 25 13:06:34 2013

RFS[14]: Assigned to RFS process 61014246

RFS[14]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 60030994

Primary database is in MAXIMUM PERFORMANCE mode

RFS[14]: Selected log 4 for thread 1 sequence 30 dbid 2119844559 branch 821698130

Thu Jul 25 13:06:35 2013

Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_25/o1_mf_1_29_8z1od2dz_.arc

Media Recovery Waiting for thread 1 sequence 30 (in transit)

Once redo test completed , switchover has been successfully completed. We can move back to original server by doing another switchover called SWITCHBACK.

I hope this article helped you.

Regards,

Amit Rath