8 Replies Latest reply: Apr 1, 2012 6:19 PM by Seberg RSS

    Switch Over & Fail Over Scenario's in physical Standby Database

    446335
      Hi ,

      Can some one please share the steps to do Switch Over & Fail Over Scenario's in physical Standby Database. Also could you please help me on these queries as i am an new to oracle.

      - Is Fail Over is automatic in an physical Standby Database. If the answer is yes do we need to do any additional steps for it.

      - Can an physical standby DB be opened in an read write mode , either in 10g / 11g

      - If we want to keep the physical Standby Database in read only mode , how are the dictionary tables updated and Is there any chance to do an DML on the database objects when in read only mode.

      - Is there any possibility to keep the logical standby database in read write mode.

      - How can we test & show to the business that the above Scenario's are fulfilled in the physical Standby Database.

      Thanks in advance.

      Rakesh
        • 1. Re: Switch Over & Fail Over Scenario's in physical Standby Database
          mseberg
          Hello


          Failover ( My test document )

          http://www.visi.com/~mseberg/Data_Guard_Failover_Test_using_SQL.pdf


          Switchover ( More of a how to using SQL )

          http://www.visi.com/~mseberg/Data_Guard_switchover.html

          Q : Is Fail Over is automatic in an physical Standby Database. You have to set this up. Extra steps.


          Q : Can an physical standby DB be opened in an read write mode. No. Read in Oracle 11 Only

          Q: How can we test & show to the business that the above Scenario's are fulfilled in the physical Standby Database.

          Would perform my own tests and document the details


          Q : Is there any possibility to keep the logical standby database in read write mode. Just Read as the default

          Q: How are the dictionary tables updated and Is there any chance to do an DML on the database objects when in read only mode.

          The dictionary tables are updated as part of redo apply

          DML, just Select and only under some conditions


          At Standby database can be a "Reader" database, or switch roles.

          Having a good Data Guard Test System is key.


          Best Regards

          mseberg
          • 2. Re: Switch Over & Fail Over Scenario's in physical Standby Database
            Shivananda Rao
            Pre-switchover checks:

            1. Verify whether MRP is running on the standby database and make sure that the standby is in sync with the primary database.
            2. Verify primary and standby tempfiles match
            3. Verify that all datafiles are online on both primary and standby databases.
            4. Check there are no jobs running on the primary database.
            4. Block further job submission by setting job_queue_processes to 0. Before doing so, note down its current value.

            Post-switchover
            1. Reset the job_queue_process parameter to its previous value.
            2. Check whether the archives generated in the new primary database are shipped and applied on the new standby database.
            3. Update the tnsnames.ora file appropriately.
            You can refer MOS document 10.2 Data Guard Physical Standby Switchover [ID 751600.1]. It might be helpful

            Switchover:
            Primary database:
            alter database commit to switchover to standby with session shutdown;
            Standby database:
            alter database commit to switchover to primary;
            Failover:
            Primary database: It is down/crashed
            Standby:
            alter database recover managed standby database finish;
            alter database commit to switchover to primary;
            - Is Fail Over is automatic in an physical Standby Database. If the answer is yes do we need to do any additional steps for it.
            You need to recreate your standby database once the failover is done.
            -Can an physical standby DB be opened in an read write mode , either in 10g / 11g
            No. Physical standby database can be opened in read only mode. In 10g, no option of active dataguard, but in 11g there is option of active dataguard where you can have the MRP in progress even when the database is in read only mode.
            • 3. Re: Switch Over & Fail Over Scenario's in physical Standby Database
              mseberg
              Hello;

              If you have FRA you can FLASHBACK to Reinstate a Standby after a FAILOVER.

              You don't have to recreate the database.

              You start this by using this :
              SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FAILOVER_SCN FROM
              V$DATABASE;
              Or did I misunderstand your answer?


              Best Regards

              mseberg
              • 4. Re: Switch Over & Fail Over Scenario's in physical Standby Database
                Shivananda Rao
                Yes, I forgot to mention that in my post...(if flashback was enabled)

                There was no misunderstanding from your end... Thanks for reminding me...

                A disciple of you and CKPT... :)
                • 5. Re: Switch Over & Fail Over Scenario's in physical Standby Database
                  446335
                  Hi ,

                  I am working on Failover to setup for EBS. Sorry for the delay in replying back to this mail thread as the requirement from the client didn't was in pending for a while ...

                  mseberg,

                  Q : Is Fail Over is automatic in an physical Standby Database. You have to set this up. Extra steps. --> Could you please add more steps

                  If you have FRA you can FLASHBACK to Reinstate a Standby after a FAILOVER. --> Could you please add more steps

                  Also could you please explain or share your knowledge in detail what are the best role initialization parameters for switchover & failover ?

                  Thanks in advance.

                  Rakesh
                  • 6. Re: Switch Over & Fail Over Scenario's in physical Standby Database
                    CKPT
                    Q : Is Fail Over is automatic in an physical Standby Database. You have to set this up. Extra steps. --> Could you please add more steps
                    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-faststartfailoverbestp-131997.pdf
                    http://www.databasejournal.com/features/oracle/article.php/3849106/Fast-Start-Failover-in-Oracle-11g-Data-Guard.htm

                    If you have FRA you can FLASHBACK to Reinstate a Standby after a FAILOVER. --> Could you please add more steps
                    http://uhesse.com/2012/01/16/after-test-failover-make-new-primary-standby-again/?like=1
                    Also could you please explain or share your knowledge in detail what are the best role initialization parameters for switchover & failover ?
                    This question is not clear enough, Whatever initialization parameters exist, even they will work after switchover. Parameters at instance level. If you perform switchover, you just take care of remote destination parameter(ex: log_archive_dest_2) whether it points properly or not.
                    • 7. Re: Switch Over & Fail Over Scenario's in physical Standby Database
                      Shivananda Rao
                      Mseberg already has posted the link which contains all the steps for the failover.

                      Failover is a one way process (say your primary P1 is down/crashed due to some reason and you have the standby S1). Then you can perform failover where S1 is turned into P1. Now you got back your primary database, but what about the standby S1 (it is now P1) ? So, you would not be having a standby once you failover. If you would have enabled Flashback (which requires you to set FRA) on the old primary database, you can use it back as a standby database. For this you can refer the link provided by Mseberg (http://www.visi.com/~mseberg/Data_Guard_Failover_Test_using_SQL.pdf) .

                      I do not think there are any extra steps involved apart from what is mentioned in the link. Only thing is setting up Flashback which you can refer http://shivanandarao.wordpress.com/2012/03/02/enabling-flashback-database-in-oracle/
                      • 8. Re: Switch Over & Fail Over Scenario's in physical Standby Database
                        Seberg
                        Glad to see you back.
                        Q : Is Fail Over is automatic in an physical Standby Database. You have to set this up. Extra steps. --> Could you please add more steps
                        No, at least not by default. You choose between manual or automatic.

                        You can setup fast start failover however. Its an advanced topic that if setup correctly would require a thrid server and "Observer".

                        Some people may try to do this will scripts but its a bad idea because you could "failover" at the wrong time.

                        And some people may disagree its an advanced topic but search E10700-02 and it hardly comes up at all.

                        I might look to an MAA white paper.
                        If you have FRA you can FLASHBACK to Reinstate a Standby after a FAILOVER. --> Could you please add more steps
                        Every detail of this is in mseberg's links and in PDF format for you.

                        Also could you please explain or share your knowledge in detail what are the best role initialization parameters for switchover & failover ?
                        There are no role initialization parameters for switchover & failover, only Data Guard parameters.

                        The details are listed in E10700-02 under "Set Primary Database Initialization Parameters" and "Create a Parameter File for the Standby Database"


                        Cooper