RE: Why extra standby redo log group?

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 <yong321@xxxxxxxxx> wrote:

> From: Yong Huang <yong321@xxxxxxxxx>
> Subject: RE: Why extra standby redo log group?
> To: "fuadar@xxxxxxxxx" <fuadar@xxxxxxxxx>, "John Hallas" 
> <John.Hallas@xxxxxxxxxxxxxxxxxx>
> Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
> 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 
>            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


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


Other related posts: