Oracle Dataguard Switch Over and Failover

Role Transitions/Switch-over  Involving Physical Standby Databases
------------------------------------------------------------

To check the switchover status of database issue the following commands on primary database

SQL>select switchover_status from v$database;

A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be
switched to the standby role.

SQL>alter database commit to switchover to physical standby with session shutdown;
then
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;

Above commands with create a new marker archived log which be applied on standby database a signal that the
standby is ready to work as primary database. you can confirm it by following commands on standby database.

SQL>select switchover_status from v$database;

If you received the switchover status of commands as "TO_PRIMARY" then its mean marker archived log has been received and  your database
is ready to change the status to primary.

Now issue the following command  on standby to change the status of standby database to primary.

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL>shutdown immediate;
SQL>startup;

Now enjoy your new primary database.

following command on current standby DB .

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

select sequence#,applied from v$archived_log order by 1;


Performing a Failover to a Physical Standby Database
=====================================================
1-check V$ARCHIVE_GAP  view for any gap. If there is gap manually copy the archives and register in standby.

 select * from v$archive_gap;

2- ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
               repeat for all missing archived files until gap resloved.

3- Change the physical standby status step by step by following commands.

Stop Redo Apply
---------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Incase of error execute below statement otherwise skip it
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
------


-----------check status and switchover to primary
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE ;


SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;