RE: Why extra standby redo log group?

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 9 Apr 2009 15:10:49 -0700 (PDT)
Message-ID: <289771.34906.qm_at_web80607.mail.mud.yahoo.com>


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.       

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 09 2009 - 17:10:49 CDT

Original text of this message