RE: Why extra standby redo log group?

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: "fuadar@xxxxxxxxx" <fuadar@xxxxxxxxx>, John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx>
  • Date: Thu, 9 Apr 2009 15:10:49 -0700 (PDT)

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.


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


Other related posts: