Dataguard SWITCHOVER on Oracle 11gr2 RAC Database (Using Physical Standby)

A primary RAC database that is running normally, the switchover operation must start from the primary site. Below are the step of switchover operation:

a. Shutdown all primary instances except one.

b. Shutdown all standby instances except one.

c. On the Primary site

    i. SQL> select switchover_status from V$database;

    ii. If switchover_status shows ‘sessions active’ go to step iii. If switchover_status shows      

        ‘TO_STANDBY’ go to step v.

    iii.SQL> select sid, process, program from v$session where type='user' and sid <>(select distinct 

         sid from V$mystat);

    iv. if any rows/process found then kill those process or wait until logout. 

    v. SQL> alter database commit to switchover to physical standby with session shutdown;

    vi. SQL>shutdown immediate;

    vii. SQL>exit;

    viii. Restart the instance and mount it as standby

                        SQL> startup nomount;

                        SQL> alter database mount standby database;

    ix.   Open database in read only mode

                        SQL> alter database mount standby database;

    x.   Start recover process

          SQL> alter database recover managed standby database using current logfile disconnect from 

                     session;

d. On the Standby site.

     i. SQL>alter database recover managed standby database cancel;

    ii. SQL>alter database recover managed standby database nodelay disconnect from session          

         through last switchover;

          Note: Above (ii)  is need only when a time lag used.

   iii. SQL> alter database commit to switchover to primary with session shutdown;

   iv.  SQL> alter database open;

e. On the original primary site mount & open the other instances as standby.

f. On the original DR site start up and open the other instances (as primary).

g. Verify the new data guard configuration

     i. On new Primary

        SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

    ii. On new standby

         make sure the log file is copied over and applied or will be applied (if time lag in apply is used).

IMPLEMENTATION

Primary server : 172.xx.xxx.60 

Standby Server : 172.xx.xxx.45

Step-1: On Primary Server (172.xx.xxx.60) 

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

SESSIONS ACTIVE

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PRIMARY          READ WRITE

SQL> select sid,process,program from v$session where type='user';

no rows selected

Step-2: On Standby Server (172.xx.xxx.45)

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

NOT ALLOWED

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PHYSICAL STANDBY READ ONLY WITH APPLY

Step-3: On Primary Server (172.xx.xxx.60)

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

Database altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

oracle@testbk [/home/oracle]$sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 5 16:59:02 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 5127602176 bytes

Fixed Size                  2214432 bytes

Variable Size            4580181472 bytes

Database Buffers          536870912 bytes

Redo Buffers                8335360 bytes

SQL>

Step-4: On Standby (172.xx.xxx.45)

SQL>  select switchover_status from v$database;

SWITCHOVER_STATUS

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

SESSIONS ACTIVE

Step-6: On Primary (172.xx.xxx.60)

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

Step-7: On Standby (172.xx.xxx.45)

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PHYSICAL STANDBY READ ONLY

SQL> alter database recover managed standby database nodelay disconnect from session through last switchover;

alter database recover managed standby database nodelay disconnect from session through last switchover

*

ERROR at line 1:

ORA-16177: media recovery is not required

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PRIMARY          MOUNTED

SQL>  alter database open;

Database altered.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PRIMARY          READ WRITE

Step-8: On New Primary (172.xx.xxx.45)

SQL>  alter system archive log current;

System altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     196

Next log sequence to archive   199

Current log sequence           199

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     196

Next log sequence to archive   200

Current log sequence           200

SQL>

Step-9: On New standby (172.xx.xxx.60)

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     0

Next log sequence to archive   0

Current log sequence           199

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     0

Next log sequence to archive   0

Current log sequence           200