Thursday, November 27, 2014

How to Failover a database on Physical DR setup.


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.


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. {oracle}:PROD_DB>sqldba

SQL*Plus: Release 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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


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


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

    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;

--------- -------------------- ----------------

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


  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

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

  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 dashboards

  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

  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

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

  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

  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

  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!

  10. This comment has been removed by the author.

  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

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

    CEH Training In Hyderbad

  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

  14. Your info is really amazing with impressive content..Excellent blog with informative concept. Really I feel happy to see this useful blog, Thanks for sharing such a nice blog..
    If you are looking for any Big data Hadoop Related information please visit our website Big Data Training In Bangalore page!

  15. I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
    Cyber Security Projects for Final Year

    JavaScript Training in Chennai

    Project Centers in Chennai

    JavaScript Training in Chennai


  17. keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore |

  18. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

  19. This comment has been removed by the author.


  20. You write this post very carefully I think, which is easily understandable to me. Not only this, but another post is also good. As a newbie, this info is really helpful for me. Thanks to you.
    Tally ERP 9 Training
    tally classes
    Tally Training institute in Chennai
    Tally course in Chennai

  21. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    Digital Marketing Course In Kolkata
    Web Design Course In Kolkata

  22. Top technologies to learn

    I am glad that I have visited this blog. Really helpful, eagerly waiting for more updates.

  23. Nice! you are sharing such helpful and easy to understandable blog. i have no words for say i just say thanks because it is helpful for me

    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery

  24. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.

    Web Designing Course Training in Chennai | Web Designing Course Training in annanagar | Web Designing Course Training in omr | Web Designing Course Training in porur | Web Designing Course Training in tambaram | Web Designing Course Training in velachery

  25. I am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place

  26. selenium interview questions and answers for experienced

    Get here for Selenium Interview Questions and Answers for freshers and experienced person those who want to explore more on testing selenium interview questions and answers for experienced

  27. devops interview questions and answers for freshers

    Important DevOps Interview Questions and Answers for freshers and experienced to start your career in DevOps! 101 DevOps Basic Interview Questions for Freshers.devops interview questions and answers for experienced

  28. Thank you for sharing the article. The data that you provided in the blog is informative and effective.

    Tableau Training in Hyderabad

  29. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also studio de personal trainer asa sul

  30. This is a fabulous article, please try to give more useful information.
    python program for fibonacci series
    inheritance in python example

  31. I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up

    Devops Training in Hyderabad

    Hadoop Training in Hyderabad

    Python Training in Hyderabad

    Tableau Training in Hyderabad

    Selenium Training in Hyderabad

  32. Tableau training institute in Hyderabad can be a ticket to your fortunate tableau career.Dashboards, tableau desktop, Tableau online,charts,and many other key features of tableau this is best for you have interest visit my website link