This discussion is archived
8 Replies Latest reply: Sep 26, 2013 5:27 AM by mseberg RSS

Some queries on Data Guard design

Jimbo Explorer
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points