DATAGAURD SWITCHOVER IN ORACLE:-

Preparing for a Role Transition:-
=>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.
=>Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

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


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 name,open_mode,log_mode,protection_mode,switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY

NOTE:-
=>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 sdbto log file on primary database and verify that it has been appleid
SQL>alter system switch logfile ;


Step  4 : Connect with primary database and initiate the switchover
[www.sakthidbtech.com@stby ]sqlplus sys/xxxx@sakthi 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 ;
NOTE:-
=>The above statement first terminates all active sessions by closing the primary database.
=>Then any non-archived sdbto log files are transmitted and applied to standby database.
=>Apart from that an end-of-sdbto 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(sdbt).
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(sakthi)
SQL> select name,open_mode,db_unique_name from v$database;
NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------       -----------          --------------------         ----------------------
Ravis   MOUNTED                Ravis                     TO PRIMARY

Step 7:On old standby database (sdbt)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database;
NAME        OPEN_MODE         DB_UNIQUE_NAME     SWITCHOVER_STATUS
------         ------------         -----------------            ---------------------
Ravis     MOUNTED               sdbt                          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 sdbto 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
sdbto Buffers                          6410240 bytes
Database mounted.
Database opened.

Step  9  : Check the new primary database(sdbt) 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(sakthi) 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
------      -------------      ------------------        -----------------------
Ravis   READ ONLY        Ravis                      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         
---------          ---------------------------------
Ravis          READ ONLY WITH APPLY