How to Failover a database on Physical DR setup.

Scenario: 

We have a DB in NFS storage with 5TB in size. As the DB size grows Application team started complaining poor performance on the queries. Also recently we had issues on NFS array due to which all the NFS storage on that array started giving poor performance. So we decided to move the DB from NFS to SAN storage.

Usually DB failover happens when Primary DB is completely unavailable. After the failover former primary DB is no longer needed.

Solution: 

In order to setup Standby database we have got new host with SAN storage and then we have built physical Standby on the new host and make it SYNC with its primary DB.

At the time of failover, on the Primary DB disable the log shipping to standby DB and stop the listener service then bounce the DB. (this will make sure no changes happened on the DB)

Now switch the log files couple of times and manually transfer those newly generated archive logs to the standby database and recover it.

 
prod_host.mydomain.com {oracle}:PROD_DB>sqldba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 16 22:14:15 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
         28156

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
         28156

SQL> alter system set log_archive_dest_state_2='DEFER';

System altered.

On Standby DB need to check if there is any gap.

 
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
  2  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  3  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
  4  WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  28156                 28156          0

If there is no gap we can then proceed with activating the Standby Database.

Since this is a planned activity, we need to ensure that all the archive logs are applied on the Standby DB before to activate the Standby DB

 
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database finish;

Database altered.

SQL> alter database activate physical standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD_DB     READ WRITE           PRIMARY

Thats it! we have now moved the entire DB to SAN storage also Standby Database is now converted into Primary Database.