Re: Why extra standby redo log group?

The best way to test this would be to stop sql apply and generate redo on the primary so while the sql apply is not happening redo is being generated and standby redo logs are being filled.

• Original Message ---- From: Yong Huang <yong321_at_yahoo.com> To: "fuadar_at_yahoo.com" <fuadar_at_yahoo.com>; John Hallas <John.Hallas_at_morrisonsplc.co.uk> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Thursday, April 9, 2009 5:10:49 PM Subject: RE: Why extra standby redo log group?

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
--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 10 2009 - 08:27:44 CDT