RE: Why extra standby redo log group?
- From: Yong Huang <yong321@xxxxxxxxx>
- To: "fuadar@xxxxxxxxx" <fuadar@xxxxxxxxx>, John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 10 Apr 2009 11:12:51 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
