Switchover to Physical Standby Database in Oracle 11g

Once the standby database is configured and works fine then we can switchover to standby database for testing purpose to reduce the primary database downtime .Primary database may need down-time for many  reasons like OS upgradation, Hardwares upgradation  and for many other issues .

Whenever we switchover the primary database to standby database , there is no loss of data during the switchover. Once the maintainance of the primary database is over , then we can again switchover to standby database.

In this scenario , the Primary database is "NOIDA" and standby database is "RED". Here i will switchover the primary database to standby database i.e, from "noida" to "red". Before switching, we should check some prerequisites .

Step 1 :  Verify whether it is possible to perform a switchover 

On the current primary database, query the "switchover_status" column of the V$DATABASE fixed view on the primary database to verify it is possible to perform switchover.

SQL> select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY

The TO STANDBY value in the "switchover_status" column indicates that it is possible to switch the primary database to the standby role. If the TO STANDBY value is not displayed, then verify the configuration is functioning correctly .  (for example, verify all  "log_archive_dest_n"  parameter values are specified correctly). If the value in the switchover_status column is SESSIONS ACTIVE or FAILED DESTINATION then click here .

Step  2  : Check that there is no active users connected to the databases.
SQL> select distinct osuser,username from v$session;

Step  3 : Switch the current online redo log file on primary database and verify that it has been appleid 

SQL>alter system switch logfile ;
System altered.

Step  4 : Connect with primary database and initiate the switchover 
C:\>sqlplus sys/xxxx@noida as sysdba
SQL> alter database commit to switchover to physical standby;
Database altered.

Now, the primary database is converted into standby database.The controlfile is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file,if necessary.
If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to standby ;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances

In order to perform a switchover, run below command on the primary database.

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

The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log file that was archived.A backup of current control file is created and the current control file is converted into a standby control file.

Step  5 : Shut down and restart the  primary instance(RED).
SQL>shutdown immediate; 

SQL> startup mount ;

Step  6 : Verify the switchover status in the v$database view.

After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "switchover_status"  column of the v$database fixed view on the target standby database.

On old Primary database(noida)
SQL> select name,open_mode,db_unique_name from v$database;
NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------       -----------          --------------------         ----------------------
NOIDA     MOUNTED                noida                       TO PRIMARY

On old standby database (RED)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database;
NAME        OPEN_MODE         DB_UNIQUE_NAME     SWITCHOVER_STATUS
------         ------------         -----------------            ---------------------
NOIDA       MOUNTED               red                          TO PRIMARY

Step 8 : Switch the target physical standby database role to the primary role

We can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following sql statement on the physical standby database that we want to change to the primary role:

SQL>alter database commit to switchover to primary ;
Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area        263639040 bytes
Fixed Size                               1373964 bytes
Variable Size                           213911796 bytes
Database Buffers                     41943040 bytes
Redo Buffers                          6410240 bytes
Database mounted.
Database opened.

Step  9  : Check the new primary database(RED) and switch logfile : 

SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE

Note :  it's a good idea to perform a log switch on the primary .


SQL> alter system switch logfile;
System altered.

Step 10  :  Open new standby database(Noida) in read-write 

SQL> alter database open;
Database altered

SQL> select name,open_mode ,db_unique_name,switchover_status  from v$database;

NAME      OPEN_MODE    DB_UNIQUE_NAME     SWITCHOVER_STATUS
------      -------------      ------------------        -----------------------
NOIDA     READ ONLY        noida                        RECOVERY NEEDED

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select  name,open_mode  from  v$database;
NAME              OPEN_MODE          
---------          ---------------------------------
NOIDA            READ ONLY WITH APPLY

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

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

Enjoy   :-)