FAQ
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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 9 of 9 | next ›
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