FAQ
Oracle recommends one more group for standby redo logs (SRL) on the physical standby than the primary online logfile groups:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#SBYDB00426

"Step 2 Determine the appropriate number of standby redo log file groups.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database....
(maximum number of logfiles for each thread + 1) * maximum number of threads
Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database."

I think it says that if you have groups #1 and #2 on primary and #1, #2 on standby, and if LGWR on primary just finished #1, switched to #2, and now it needs to switch to #1 again because #2 just became full, the standby must catch up, otherwise the primary LGWR cannot reuse #1 because the standby is still archiving the standby's #1. Now, if you have the extra #3 on standby, the standby in this case can start to use #3 while its #1 is being archived. That way, the primary can reuse the primary's #1 without delay.

I did a test on 10.2.0.4 with two groups on primary and three SRL groups on standby. I keep doing "alter system switch logfile" on primary, which changes current log between #1 and #2. However, I see only two of the three SRL groups on standby, #1 and #2, being used, just like on primary; #3 is never used. It makes no difference whether log_archive_max_processes is set to 2 or 10. This seems to contradict what documentation says. However, I barely have any DML or DDL so log switching is not triggered because the file is full, but triggered manually by my command. Does that matter?

Again, my question is, What's exactly the extra SRL group used for on standby?

Yong Huang

Search Discussions

  • Fuad Arshad at Apr 4, 2009 at 2:44 am
    Well in my case I've seem standby redo logs used to cover for backlogs I.e Log switching is faster than the standby instance can perform of also depends if you are using realtime or not in realtime I've seen less use of extra standby redo Log than in non realtime I.e apply immediate vs just apply

    Fuad

    On Apr 3, 2009, at 20:53, Yong Huang wrote:

    Oracle recommends one more group for standby redo logs (SRL) on the physical standby than the primary online logfile groups:

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#SBYDB00426

    "Step 2 Determine the appropriate number of standby redo log file groups.
    Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database....
    (maximum number of logfiles for each thread + 1) * maximum number of threads
    Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database."

    I think it says that if you have groups #1 and #2 on primary and #1, #2 on standby, and if LGWR on primary just finished #1, switched to #2, and now it needs to switch to #1 again because #2 just became full, the standby must catch up, otherwise the primary LGWR cannot reuse #1 because the standby is still archiving the standby's #1. Now, if you have the extra #3 on standby, the standby in this case can start to use #3 while its #1 is being archived. That way, the primary can reuse the primary's #1 without delay.

    I did a test on 10.2.0.4 with two groups on primary and three SRL groups on standby. I keep doing "alter system switch logfile" on primary, which changes current log between #1 and #2. However, I see only two of the three SRL groups on standby, #1 and #2, being used, just like on primary; #3 is never used. It makes no difference whether log_archive_max_processes is set to 2 or 10. This seems to contradict what documentation says. However, I barely have any DML or DDL so log switching is not triggered because the file is full, but triggered manually by my command. Does that matter?

    Again, my question is, What's exactly the extra SRL group used for on standby?

    Yong Huang
  • John Hallas at Apr 5, 2009 at 1:33 pm
    I would agree with what Fuad says, it is to ensure that the standby can keep up with the primary. It is only a recommendation though and not mandatory

    John

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Fuad Arshad
    Sent: 04 April 2009 03:44
    To: yong321_at_yahoo.com
    Cc: oracle-l@freelists.org
    Subject: Re: Why extra standby redo log group?

    Well in my case I've seem standby redo logs used to cover for backlogs I.e Log switching is faster than the standby instance can perform

    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    Wm Morrison Supermarkets PLC accepts no liability or responsibility for anything said in the email or its attachments and gives no warranty as to accuracy. It is the policy of Wm Morrison Supermarkets PLC not to enter into any contractual or other obligations by email.

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.

    --
    http://www.freelists.org/webpage/oracle-l
  • Martin Brown at Apr 5, 2009 at 1:46 pm
    Totally agree. Our configuration has 8 primary nodes and (of course) only 1 standby node. For those that don't run DataGuard 10g, you can only have 1 active standby node. Normal log switches happen about 3 per hour. During peak times, our log switchs pick up speed and this configuration keeps up quite nicely.

    From: John.Hallas_at_morrisonsplc.co.uk
    To: fuadar_at_yahoo.com; yong321_at_yahoo.com
    CC: oracle-l@freelists.org
    Date: Sun, 5 Apr 2009 14:33:16 +0100
    Subject: RE: Why extra standby redo log group?

    I would agree with what Fuad says, it is to ensure that the standby can keep up with the primary. It is only a recommendation though and not mandatory

    John

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Fuad Arshad
    Sent: 04 April 2009 03:44
    To: yong321_at_yahoo.com
    Cc: oracle-l@freelists.org
    Subject: Re: Why extra standby redo log group?


    Well in my case I've seem standby redo logs used to cover for backlogs I.e Log switching is faster than the standby instance can perform

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    Wm Morrison Supermarkets PLC accepts no liability or responsibility for anything said in the email or its attachments and gives no warranty as to accuracy. It is the policy of Wm Morrison Supermarkets PLC not to enter into any contractual or other obligations by email.

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l
    Rediscover Hotmail®: Get quick friend updates right in your inbox.
    http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Updates1_042009
  • Yong Huang at Apr 5, 2009 at 2:39 pm
    Martin, John, Fuad,

    Thank you. When you have heavy transaction for a period of time, if you have n log groups on primary and n+1 SRL groups on standby, do you see all n+1 SRL groups used? That is, on the standby, do you see all, not just n, rows in v$standby_log under status column, alternately showing 'ACTIVE'?

    I know if I manually switch logfile on primary, only n SRL groups will be 'ACTIVE' (the extra stays unused). I'll test by building a small data guard where primary is on a node with fast storage and standby with slow storage and create lots of redo.

    Yong Huang

    On Sun, 4/5/09, Martin Brown wrote:

    From: Martin Brown
    Subject: RE: Why extra standby redo log group?
    To: john.hallas_at_morrisonsplc.co.uk, fuadar_at_yahoo.com, yong321_at_yahoo.com
    Cc: oracle-l@freelists.org
    Date: Sunday, April 5, 2009, 8:46 AM

    Totally agree. Our configuration has 8 primary nodes and (of
    course) only 1 standby node. For those that don't run
    DataGuard 10g, you can only have 1 active standby node.
    Normal log switches happen about 3 per hour. During peak
    times, our log switchs pick up speed and this configuration
    keeps up quite nicely.

    From: John.Hallas_at_morrisonsplc.co.uk
    To: fuadar_at_yahoo.com; yong321_at_yahoo.com
    CC: oracle-l@freelists.org
    Date: Sun, 5 Apr 2009 14:33:16 +0100
    Subject: RE: Why extra standby redo log group?

    I would agree with what Fuad says, it is to ensure
    that the standby can keep up with the primary. It is only a
    recommendation though and not mandatory
    John

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Fuad
    Arshad
    Sent: 04 April 2009 03:44
    To: yong321_at_yahoo.com
    Cc: oracle-l@freelists.org
    Subject: Re: Why extra standby redo log group?


    Well in my case I've seem standby redo logs used
    to cover for backlogs I.e Log switching is faster than the
    standby instance can perform
  • Fuad Arshad at Apr 5, 2009 at 4:19 pm
    Here is an example form one of my standby databases
    GROUP#,DBID,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,LAST_CHANGE#,LAST_TIME

    5,2993939164,1,539739,1258291200,512,YES,ACTIVE,124541122419,4/5/2009 11:15:20 AM,124541122522,4/5/2009 11:15:20 AM
    6,2993939164,1,539738,1258291200,294810112,NO,ACTIVE,124540991837,4/5/2009 11:03:59 AM,124541122419,4/5/2009 11:15:20 AM
    7,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,
    8,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,
    9,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,

    As you can see both are active but one is archived=yes the other is not sicne it is a realtime recieve . in a very busy environment you will see all unassigned as Active with one being used as realtime till time that the backlog is more than the standby redo logs.

    Original Message ----
    From: Yong Huang
    To: john.hallas_at_morrisonsplc.co.uk; fuadar_at_yahoo.com; Martin Brown
    Cc: oracle-l@freelists.org
    Sent: Sunday, April 5, 2009 9:39:09 AM
    Subject: RE: Why extra standby redo log group?

    Martin, John, Fuad,

    Thank you. When you have heavy transaction for a period of time, if you have n log groups on primary and n+1 SRL groups on standby, do you see all n+1 SRL groups used? That is, on the standby, do you see all, not just n, rows in v$standby_log under status column, alternately showing 'ACTIVE'?

    I know if I manually switch logfile on primary, only n SRL groups will be 'ACTIVE' (the extra stays unused). I'll test by building a small data guard where primary is on a node with fast storage and standby with slow storage and create lots of redo.

    Yong Huang

    On Sun, 4/5/09, Martin Brown wrote:

    From: Martin Brown
    Subject: RE: Why extra standby redo log group?
    To: john.hallas_at_morrisonsplc.co.uk, fuadar_at_yahoo.com, yong321_at_yahoo.com
    Cc: oracle-l@freelists.org
    Date: Sunday, April 5, 2009, 8:46 AM

    Totally agree. Our configuration has 8 primary nodes and (of
    course) only 1 standby node. For those that don't run
    DataGuard 10g, you can only have 1 active standby node.
    Normal log switches happen about 3 per hour. During peak
    times, our log switchs pick up speed and this configuration
    keeps up quite nicely.
    From: John.Hallas_at_morrisonsplc.co.uk
    To: fuadar_at_yahoo.com; yong321_at_yahoo.com
    CC: oracle-l@freelists.org
    Date: Sun, 5 Apr 2009 14:33:16 +0100
    Subject: RE: Why extra standby redo log group?

    I would agree with what Fuad says, it is to ensure
    that the standby can keep up with the primary. It is only a
    recommendation though and not mandatory
    John

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Fuad
    Arshad
    Sent: 04 April 2009 03:44
    To: yong321_at_yahoo.com
    Cc: oracle-l@freelists.org
    Subject: Re: Why extra standby redo log group?


    Well in my case I've seem standby redo logs used
    to cover for backlogs I.e Log switching is faster than the
    standby instance can perform
  • Yong Huang at Apr 9, 2009 at 10:10 pm
    Fuad,

    I created a small 10.2.0.4 database with 2 log groups on a server with fast storage, and created its physical standby with 3 standby redo log groups on a server with slow storage. On the primary, I have log_archive_dest_2 set to 'service=toysb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=toysb', and I ran

    begin
    for i in 1..100 loop
    delete from t;
    rollback;
    end loop;
    end;
    /

    and on the standby:

    SQL> select group#, thread#, sequence#, used, archived, status, first_change#, first_time from v$standby_log;
    ...
    SQL> /

    GROUP# THREAD# SEQUENCE# USED ARC STATUS FIRST_CHANGE# FIRST_TIME

    ---------- ---------- ---------- ---------- --- ---------- ------------- -----------------
    4 1 256 0 YES ACTIVE 578452 20090409 16:51:46
    5 1 255 8436224 NO ACTIVE 577577 20090409 16:51:44
    6 1 0 512 NO UNASSIGNED 0

    Many times I do see groups 4 and 5 both showing 'ACTIVE' although most of the time only one of them is 'ACTIVE'. But group 6, the extra SRL group, is always 'UNASSIGNED'. So my question remains, When is the extra SRL group used?

    On primary I increased log_archive_max_processes from 2 to 5. Then opened another sqlplus session and ran the same PL/SQL block except on another big size table (so two sessions doing delete and rollback). On standby, there's no difference.

    Yong Huang

    On Sun, 4/5/09, Fuad Arshad wrote:

    Here is an example form one of my standby databases
    GROUP#,DBID,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,LAST_CHANGE#,LAST_TIME

    5,2993939164,1,539739,1258291200,512,YES,ACTIVE,124541122419,4/5/2009 11:15:20

    AM,124541122522,4/5/2009 11:15:20 AM
    6,2993939164,1,539738,1258291200,294810112,NO,ACTIVE,124540991837,4/5/2009

    11:03:59 AM,124541122419,4/5/2009 11:15:20 AM

    7,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,
    8,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,
    9,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,

    As you can see both are active but one is archived=yes the other is not sicne
    it is a realtime recieve . in a very busy environment you will see all
    unassigned as Active with one being used as realtime till time that the backlog
    is more than the standby redo logs.
  • Fuad Arshad at Apr 10, 2009 at 1:27 pm
    The best way to test this would be to stop sql apply and generate redo on the primary so while the sql apply is not happening redo is being generated and standby redo logs are being filled.

    Original Message ----
    From: Yong Huang
    To: "fuadar_at_yahoo.com"; John Hallas
    Cc: "oracle-l@freelists.org"
    Sent: Thursday, April 9, 2009 5:10:49 PM
    Subject: RE: Why extra standby redo log group?

    Fuad,

    I created a small 10.2.0.4 database with 2 log groups on a server with fast storage, and created its physical standby with 3 standby redo log groups on a server with slow storage. On the primary, I have log_archive_dest_2 set to 'service=toysb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=toysb', and I ran

    begin
    for i in 1..100 loop
    delete from t;
    rollback;
    end loop;
    end;
    /

    and on the standby:

    SQL> select group#, thread#, sequence#, used, archived, status, first_change#, first_time from v$standby_log;
    ...
    SQL> /

    GROUP# THREAD# SEQUENCE# USED ARC STATUS FIRST_CHANGE# FIRST_TIME

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

    4 1 256 0 YES ACTIVE 578452 20090409 16:51:46
    5 1 255 8436224 NO ACTIVE 577577 20090409 16:51:44
    6 1 0 512 NO UNASSIGNED 0

    Many times I do see groups 4 and 5 both showing 'ACTIVE' although most of the time only one of them is 'ACTIVE'. But group 6, the extra SRL group, is always 'UNASSIGNED'. So my question remains, When is the extra SRL group used?

    On primary I increased log_archive_max_processes from 2 to 5. Then opened another sqlplus session and ran the same PL/SQL block except on another big size table (so two sessions doing delete and rollback). On standby, there's no difference.

    Yong Huang

    On Sun, 4/5/09, Fuad Arshad wrote:

    Here is an example form one of my standby databases
    GROUP#,DBID,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,LAST_CHANGE#,LAST_TIME

    5,2993939164,1,539739,1258291200,512,YES,ACTIVE,124541122419,4/5/2009 11:15:20

    AM,124541122522,4/5/2009 11:15:20 AM
    6,2993939164,1,539738,1258291200,294810112,NO,ACTIVE,124540991837,4/5/2009

    11:03:59 AM,124541122419,4/5/2009 11:15:20 AM

    7,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,
    8,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,
    9,UNASSIGNED,1,0,1258291200,512,NO,UNASSIGNED,0,,0,

    As you can see both are active but one is archived=yes the other is not sicne
    it is a realtime recieve . in a very busy environment you will see all
    unassigned as Active with one being used as realtime till time that the backlog
    is more than the standby redo logs.
  • Yong Huang at Apr 10, 2009 at 6:12 pm
    I have some interesting finding. I opened an SR. Oracle support immediately called back and told me that the recommendation for an extra SRL group is for maximum protection (even though documentation doesn't say so). Fine. But he went on and said even if we have >2 groups on primary, only 2 SRL groups on standby will be used. Skeptical about that, I added one more group to primary so it has 3 now, and added one more SRL group to standby so it has 4 now. I opened 3 sqlplus sessions doing massive delete and rollback in a tight loop on 3 different tables. I'm very excited to find that SRL status check once every one to two seconds does occasionally show more than 3 groups active. In fact, in my test, of all 112 samplings, the first group becomes active with a probability of 82/(40+82)=67%, the second group 64/(58+64)=52%, the third 14/(108+14)=11%, and the fourth, i.e. the extra, group 4/(118+4)=3%. StandbyRedoLogStatus.txt, which is summarized below,
    records the repeated queries of v$standby_log.

    C:\>grep "^ 4" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
    40
    C:\>grep "^ 4" StandbyRedoLogStatus.txt | grep -c ACTIVE
    82
    C:\>grep "^ 5" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
    58
    C:\>grep "^ 5" StandbyRedoLogStatus.txt | grep -c ACTIVE
    64
    C:\>grep "^ 6" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
    108
    C:\>grep "^ 6" StandbyRedoLogStatus.txt | grep -c ACTIVE
    14
    C:\>grep "^ 7" StandbyRedoLogStatus.txt | grep -c UNASSIGNED
    118
    C:\>grep "^ 7" StandbyRedoLogStatus.txt | grep -c ACTIVE
    4

    The extra group being ACTIVE is such a rare event that I want to share my joy with you:

    SQL> /

    GROUP# THREAD# SEQUENCE# USED ARC STATUS FIRST_CHANGE# FIRST_TIME

    ---------- ---------- ---------- ---------- --- ---------- ------------- -----------------
    4 1 682 10130432 NO ACTIVE 954336 20090410 11:21:49
    5 1 684 512 YES ACTIVE 954466 20090410 11:21:50
    6 1 683 10350592 NO ACTIVE 954347 20090410 11:21:50
    7 1 685 0 YES ACTIVE 954756 20090410 11:21:52

    That particular sampling is so rare (and precious!) in that all 4 groups are ACTIVE at the same time. By the way, I find that if the file system on the standby server where archive logs are stored becomes full, the all SRL showing ACTIVE phenomenon will also show up and persist. But in the above case, that was not due to space full condition and only appeared in one sampling.

    The above test is done with recovery still going. Actually, recovery or not doesn't make difference. I guess my earlier attempt to see the extra SRL active failed is because I still didn't have enough redo per second. I can't think of any other explanation.

    Thank you all.

    Yong Huang
    On Thu, 4/9/09, Yong Huang wrote:

    From: Yong Huang
    Subject: RE: Why extra standby redo log group?
    To: "fuadar_at_yahoo.com", "John Hallas"
    Cc: "oracle-l@freelists.org"
    Date: Thursday, April 9, 2009, 5:10 PM
    Fuad,

    I created a small 10.2.0.4 database with 2 log groups on a
    server with fast storage, and created its physical standby
    with 3 standby redo log groups on a server with slow
    storage. On the primary, I have log_archive_dest_2 set to
    'service=toysb lgwr async
    valid_for=(online_logfiles,primary_role)
    db_unique_name=toysb', and I ran

    begin
    for i in 1..100 loop
    delete from t;
    rollback;
    end loop;
    end;
    /

    and on the standby:

    SQL> select group#, thread#, sequence#, used, archived,
    status, first_change#, first_time from v$standby_log;
    ...
    SQL> /
    GROUP# THREAD# SEQUENCE# USED ARC STATUS
    FIRST_CHANGE# FIRST_TIME
    ---------- ---------- ---------- ---------- --- ----------
    ------------- -----------------
    4 1 256 0 YES ACTIVE
    578452 20090409 16:51:46
    5 1 255 8436224 NO ACTIVE
    577577 20090409 16:51:44
    6 1 0 512 NO UNASSIGNED

    Many times I do see groups 4 and 5 both showing
    'ACTIVE' although most of the time only one of them
    is 'ACTIVE'. But group 6, the extra SRL group, is
    always 'UNASSIGNED'. So my question remains, When is
    the extra SRL group used?

    On primary I increased log_archive_max_processes from 2 to
    5. Then opened another sqlplus session and ran the same
    PL/SQL block except on another big size table (so two
    sessions doing delete and rollback). On standby, there's
    no difference.

    Yong Huang


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 4, '09 at 1:53a
activeApr 10, '09 at 6:12p
posts9
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase