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_at_yahoo.com> wrote:

> From: Yong Huang <yong321_at_yahoo.com>
> Subject: RE: Why extra standby redo log group?
> To: "fuadar_at_yahoo.com" <fuadar_at_yahoo.com>, "John Hallas" <John.Hallas_at_morrisonsplc.co.uk>
> Cc: "oracle-l_at_freelists.org" <oracle-l_at_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
> 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