8 Replies Latest reply: Sep 26, 2013 7:27 AM by mseberg RSS

    Some queries on Data Guard design

    Jimbo

      Hi, I have some basic questions around Data Guard design

       

      Q1. Looking at the Oracle instructions for creating a logical standby, it firstly advocates creating a physical standby and then converting it to a logical standby. However I thought a logical standby could have a completely different physical structure from the primary. How can this be the case if a logical standby first starts its life as a physical standby ( where structure needs to be identical ) ?

       

      Q2. Is it normal practice to backup your standby database as well – if so why ?

       

      Q3. Can RMAN backup a Standby Database whilst it is in the mounted state ( rather than open ) ?

       

      Q4. What's the point of Cascaded Redo Apply rather than simply getting the Primary to ship to each Standby ?

      I guess you could be trying to reduce node to node latency if some of the standby were quite distant from the primary

       

      Q5. Is it possible to convert a Logical Standby back to a Physical one ?

       

      Q6. What the maximium number of standbys you can have - Oracle suggests 30 but I thought I remember reading somewhere in relation to 11gR2 that this limit has now been increased ?

       

       

      thanks,

      Jim

        • 1. Re: Some queries on Data Guard design
          Anar Godjaev

          Hi,

           

          Q5 . Is it possible to convert a Logical Standby back to a Physical one ?  --- Yes it is possible .

           

          -- Primary                                                                                                        

          select switchover_status from v$database;                                                                         

                                                                                                                            

          SWITCHOVER_STATUS                                                                                                 

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

          SESSIONS ACTIVE                                                                                                   

                                                                                                                            

          2) switch operation                                                                                            

                                                                                                                            

          -- Primary - starat database switch                                                                         

          alter database commit to switchover to physical standby with session shutdown;             

           

                                                                                                                                                                                                                                               

          -- see alertlog.                                                                               

          Switchover: Complete - Database shutdown required                                                                 

          Completed: alter database commit to switchover to physical standby with session shutdown                          

                                                                                                                            

                                                                                                                            

          -- database role ve switchover_status                                                  

          select NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL from v$database;           

                                                                                                                            

          NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL        

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

          TESTCRD   READ WRITE           PHYSICAL STANDBY RECOVERY NEEDED      MAXIMUM AVAILABILITY UNPROTECTED             

                                                                                                                            

          -- Primary                                                                                                        

          shutdown abort                                                                                                    

                                                                                                                            

                                                                                                                            

          -- standby - switch to  primary                                                                           

                                                                                                                            

          alter database commit to switchover to primary with session shutdown;                                             

                                                                                                                            

          -- alert log                                                                                                      

          Completed: alter database commit to switchover to primary with session shutdown                                   

                                                                                                                            

                                                                                                                            

          alter database open                                                                                               

                                                                                                           

          3) Old Primary  open                                                                                                 

                                                                                                                            

          sqlplus / as sysdba                                                                                               

          startup nomount                                                                                                   

          alter database mount standby database                                                                             

                                                                                                                            

          -- database role ve switchover_status                                                  

          select NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL from v$database;           

                                                                                                                            

          NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL        

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

          TESTCRD   READ WRITE           PHYSICAL STANDBY RECOVERY NEEDED      MAXIMUM AVAILABILITY UNPROTECTED             

                                                                                               

          recover managed standby database using current logfile disconnect from session;                                   

                                                                                                                            

                                                                                                                            

          4) Old  standby

                                                                                                                            

                                                                                                                            

          SQL> select NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL from v$database;      

                                                                                                                            

          NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL        

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

          AZKKDB    READ WRITE           PRIMARY          TO STANDBY           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE     

           

          Q2 . . Is it normal practice to backup your standby database as well – if so why ?

           

          Hence you only have to backup one of the both (you can decide to only backup the standby though, if you want to remove load on the primary). But if you are confident with the backup of the primary, there is no need for backing up the standby. (Since you can recreate the standby with the backup from primary).


          Please see link : http://blog.dbvisit.com/rman-backups-on-your-standby-database-yes-it-is-easy/


          Thank you





          • 2. Re: Some queries on Data Guard design
            Jimbo

            I think you are confusing a switch from a primary to a standby with the question I am asking ? i.e. can I convert my standby from being a logical standby to being a physical standby ( I know I can convert physical to logical )

            • 3. Re: Some queries on Data Guard design
              Anar Godjaev

              Hi Jimbo,

              This form has been discussed

              Please check this from : https://forums.oracle.com/thread/1033905

              • 4. Re: Some queries on Data Guard design
                mseberg

                Hello;

                 

                The simple answer is no. You cannot convert  a logical standby back to a physical standby.

                 

                Options

                 

                1. Rebuild with Active duplicate.

                2. Use snapshot standby if possible instead of logical.

                 

                RMAN will work on a Standby and is generally a method to take load off the standby.

                 

                LOG_ARCHIVE_DEST_n will limit the Standbys to 30

                 

                Best Regards

                 

                mseberg

                • 5. Re: Some queries on Data Guard design
                  Jimbo

                  What's the story with the KEEP IDENTITY clause supposedly introduced in 11g which then allows a logical standby to be converted back to a physical standby ?

                   

                  thanks,

                  Jim

                  • 6. Re: Some queries on Data Guard design
                    mseberg

                    Hello again;

                     

                    Its a special case used for rolling upgrades. The key here is " temporarily converted to a transient logical standby" Its not the same as having a logical standby around for while and then trying to convert it back to a physical standby.

                     

                    Never done a rolling upgrade.

                     

                    Best Regards

                     

                    mseberg

                    • 7. Re: Some queries on Data Guard design
                      Jimbo

                      Thanks, very useful.

                       

                      So in your experience does every logical standby first start its life as a physical standby and then is coverted to a logical standby ? This is what the oracle note for creating a logical standby would suggest i.e. you firstly create a physical standby then convert it to a logical standby.

                       

                      It's just that I always thought one of the big selling points of a logical standby was that it did not have to have the same structure as the primary. I am just wondering how that can be the case if the logical standby first starts its life as a physical standby ( which does have to have a structure identical to the primary ) ?

                       

                      thanks,

                      Jim

                      • 8. Re: Some queries on Data Guard design
                        mseberg

                        Hello again;

                         

                        "does every logical standby first start its life as a physical standby?"

                         

                        Its the only way I've ever done it. I think its the simple way to do it.  By having a physical standby first you have already confirmed the network and most of the parameters for Data Guard so you can focus on just the Logical Standby Database configuration.

                         

                        Chapter 3 of this book makes it easy:

                         

                        http://www.visi.com/~mseberg/data_guard/new_dg_book.html

                         

                        Best Regards

                         

                        mseberg