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.
