8 Replies Latest reply: Nov 20, 2013 2:33 AM by Mahir M. Quluzade RSS

    How to setup Multiple Standby Databases

    Neeraj Goel

      I have to setup multiple standby databases using data guard. Following are my questions:

       

      1. Whats are the key parameters to take care of?
      2. As we add redo logs for standby on primary, Do we need to add two sets of redo logs for 2 standby's databases?
      3. How data guard identify which redo log is add for which standby?
      4. Is there any parameter value to set for standby redo logs in case of multiple standby's?

       

      Regards,

      Neeraj G.

        • 1. Re: How to setup Multiple Standby Databases
          Mahir M. Quluzade

          NeerajGoel wrote:

           

          I have to setup multiple standby databases using data guard. Following are my questions:

          1. Whats are the key parameters to take care of?

           

          LOG_ARCHIVE_DEST_n parameters is using archive redo logs for standby service.

           

          2.  As we add redo logs for standby on primary, Do we need to add two sets of redo logs for 2 standby's databases?


          Standby  redo logs using when database role is standby.
          Best practice, add standby  only 1 standby logfile to each standby  log group.
          If you will use Real Time Apply, or Max availability or Max Protection modes, then SRLs require add  to standby database.

          You  can use standby database without SRLs only Max Performance mode


          3. How data guard identify which redo log is add for which standby?


          Standby  redo logs using when database role is standby. It means added SRLs to primary is using when role changed to standby.

           

          4. Is there any parameter value to set for standby redo logs in case of multiple standby's?

           

          I think you must read about  standby  redologs : Creating a Physical Standby Database

           

          3.1.3 Configure a Standby Redo Log

           

          Regards

          Mahir M. Quluzade

          • 2. Re: How to setup Multiple Standby Databases
            Neeraj Goel

            Thanks Mahir for your response,

             

            As I have to add two standby's , one in Max Availability and other in Max Protection. I have 3 redo log group on Primary and accordingly added standby logfile to 3 standby log group.

             

            This is enough for both the standby databases or I need to add another set of standby log group for 2nd standby database.

             

            Regards,

            Neeraj G.

            • 3. Re: How to setup Multiple Standby Databases
              Mahir M. Quluzade

              NeerajGoel wrote:

               

              Thanks Mahir for your response,

              You are welcome!

               

              As I have to add two standby's , one in Max Availability and other in Max Protection.

              I have 3 redo log group on Primary and accordingly added standby logfile to 3 standby log group.

               

              Best practice SRL group count eques ORL group Count + 1, if you  have 3 ORL group then add 4 SRL group.

               

               

              This is enough for both the standby databases or I need to add another set of standby log group for 2nd standby database.

               

              You must add SRLs to standby database.

               

              Standby  redo logs using when database role is standby. It means added SRLs to primary is using when role changed to standby.

               

               

              Please read my before reply carefully.

               

               

              Regards,

              Mahir M. Quluzade

              • 4. Re: How to setup Multiple Standby Databases
                Neeraj Goel

                Mahir,

                 

                You are correct, But according to the section 3.1.3 Configure a Standby Redo Log, for Example 3-2 Adding a Standby Redo Log File Group to a Specific Group Number

                 

                Note:

                Although the standby redo log is only used when the database is running in the standby role, Oracle recommends that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention. Consider using Oracle Enterprise Manager to automatically configure standby redo log on both your primary and standby databases.


                Highlighted lines in above confusing me. I have added standby redo log on primary which is correct as per above. But Is that work in multiple standby case also?


                Regards,

                Neeraj G.

                • 5. Re: How to setup Multiple Standby Databases
                  Mahir M. Quluzade

                  NeerajGoel wrote:

                   

                  Mahir,

                   

                  You are correct, But according to the section 3.1.3 Configure a Standby Redo Log, for Example 3-2 Adding a Standby Redo Log File Group to a Specific Group Number

                   

                  Note:

                  Although the standby redo log is only used when the database is running in the standby role, Oracle recommends that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention. Consider using Oracle Enterprise Manager to automatically configure standby redo log on both your primary and standby databases.


                  Highlighted lines in above confusing me. I have added standby redo log on primary which is correct as per above. But Is that work in multiple standby case also?


                  Regards,

                  Neeraj G.


                  Oracle recommends that you create a standby redo log on the primary database so that the primary database

                  can switch over quickly to the standby role without the need for additional DBA intervention.


                  As you read, primary database can switch over quickly to standby role without the  need additional DBA intervention.

                  It means, recommended SRLs must be add to primary database, when role change to standby on primary  after swithover, then

                  DBA not need add standby redo logs again to new standby (old primary).


                  Regards

                  Mahir M. Quluzade

                  • 6. Re: How to setup Multiple Standby Databases
                    Mahir M. Quluzade

                    NeerajGoel wrote:

                     

                    Mahir,

                     

                    You are correct ....

                     

                    It is correct answer for your thread

                     

                    Regards

                    Mahir M. Quluzade

                    • 7. Re: How to setup Multiple Standby Databases
                      Neeraj Goel

                      I understood that:

                      Oracle recommends that you create a standby redo log on the primary database so that the primary database

                      can switch over quickly to the standby role without the need for additional DBA intervention.


                      But My Question is still not answered: As I have to setup two standby databases and on Primary I have created a set of SRL's as per ORL's.

                      Is that work for both the standby databases?

                       

                      Following are my configuration:

                       

                      Original Redo Logs (On Primary):

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

                      SQL> select GROUP#,THREAD#,MEMBERS,BYTES FROM V$LOG;

                       

                       

                          GROUP#    THREAD#    MEMBERS      BYTES

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

                               4          1                  1                    4294967296

                               5          1                  1                    4294967296

                               6          1                  1                    4294967296

                       

                      I have created following standby redo logs on Primary:

                       

                      SQL> select GROUP#,THREAD#,BYTES FROM V$STANDBY_LOG;

                       

                       

                          GROUP#    THREAD#       BYTES

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

                              11          1                   4294967296

                              12          1                   4294967296

                              13          0                   4294967296

                       

                      In case of Single standby database above configuration works.

                       

                      Now I have to add one more standby (Max Performance mode). Is the above configuration same for 2nd standby also? Or I need to create another set of SRL's for 2nd standby?

                       

                      Regards,

                      Neeraj G.

                      • 8. Re: How to setup Multiple Standby Databases
                        Mahir M. Quluzade

                        You didn't understood.

                         

                        You added SRL to  primary  for using when primary  role will change to standby.

                        When database role primary , then SRLs is not using by database.

                         

                        It means SRL's must be added to standby database.

                        Standby database using SRLs. You must add SRL's to every  standby database.

                         

                        SRLs group = ORLs group + 1

                         

                         

                         

                        Regards

                        Mahir M. Quluzade