Thursday, November 27, 2014

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.

21 comments:

  1. Hi, I am really happy to found such a helpful and fascinating post that is written in well manner. Thanks for sharing such an informative post. keep update your blog. R Programming Online Training

    ReplyDelete
  2. great post you can heck it out for information for PEGA Online Training

    ReplyDelete
  3. There will be a list of users that are authorized to access the data on the database and when this access is restricted, the data is all the more secured.oracle dashboards

    ReplyDelete
  4. Thanks a lot very much for the high your blog post quality and results-
    oriented help. I won’t think twice to endorse to anybody who wants
    and needs support about this area.
    Oracle Training in chennai

    ReplyDelete
  5. Appreciation for really being thoughtful and also for deciding on certain marvelous guides most people really want to be aware of.
    digital marketing training in chennai

    ReplyDelete
  6. Needed to compose one little word thanks for the suggestion that you are contributed here.
    Best Online Software Training Institute | Oracle DBA Training

    ReplyDelete
  7. I just needed to record a speedy word to express profound gratitude to you for those magnificent tips and clues you are appearing on this site.

    Oracle DBA training in Chennai
    Oracle DBA Training

    ReplyDelete
  8. Large-scale manufacturing, sewage treatment and power generation facilities are required to leverage increased process visibility which provides better and faster decisions, increased productivity and reduced costs for a sustainable competitive advantage.Database Diagram Tool

    ReplyDelete
  9. Probably, of all the preferences, this casino is my favorite. superb bonus slot machines I like everything in the casino, the roulette is excellent, I am glad that during the game you can also talk to people who also play at the same table! Muzychka great. With the conclusion there were no difficulties at all. Easily raised from $ 30 to $ 600, withdraw. Played, played, will play!

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. In the beginning, I would like to thank you much about this great post. Its very useful and helpful for anyone looking for tips. I like your writing style and I hope you will keep doing this good working.
    Data Science Course in Chennai
    Data Science Training in Chennai
    Blue Prism Training Chennai
    AWS Training in Chennai
    Digital Marketing Course in Chennai
    Data Science Training in Porur
    Data Science Training in Adyar
    Data Science Training in Velachery

    ReplyDelete
  12. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time.

    CEH Training In Hyderbad

    ReplyDelete
  13. Thank you so much for the basic details and code in oracle in detailed way.Very clear to understand even for the unknowns of the language.
    oneplus mobile service centre in chennai
    oneplus mobile service centre
    oneplus service center near me
    oneplus service

    ReplyDelete