while large deletes in database following error were observed in alert log file

[Fenil Manek_4]

ARC1: Beginning to archive log# 3 seq# 18165
ARC1: Failed to archive log# 3 seq# 18165
ARC1: Beginning to archive log# 2 seq# 18166
Thu Jun 26 08:24:15 2003
ARC0: Completed archiving log# 3 seq# 18165
Thu Jun 26 08:24:15 2003
ARC2: Beginning to archive log# 2 seq# 18166
ARC2: Failed to archive log# 2 seq# 18166
Thu Jun 26 08:24:22 2003
ARC1: Completed archiving log# 2 seq# 18166

above error were noticed in alert log
oracle 8.1.7.3 on hp ux 11.11

[Massimo Bianchi · Accepted solution]

Hi,
these are not error, are just warning.

They mean that you were generating so many archive that the arc process were not able to save them.

In fact, you can see that after a while you get a "Completed" for each fo them.

Since you were working so much, oracle spawned ARC0 (default), ARC1 and ARC2 for a faster archiving. Until the redolog is saved, and become and archive log, you cannot reuse it.

If you plan to have often such large operation, i suggest you to enlarge your redo log, so you will see these message not often.

HTH,
Massimo

[Fenil Manek_4]

hi

thanks a lot

does log_archive_trace parameter can help in avoiding this messages in alert logfile &
my current logfile size is 104mb and we have 3 logfile in our database

shall i add two more logfile or shall i resize the existing logfile to 150mb

regards

fenil manek

[Kenneth_19]

Adding more log files will lower the chance of having this error, as the archiver will have more time to archive the log before it is reused again. Increasing the size of the log will bring other issues on backup and recovery.

The log_archive_trace parameter will help you understand more about the whole log archive process in the system, but not fix it.

Kenneth

[twang]

The alert log showed that log switches had occured. You may ignore these messages, and you can get more info on archivelog status with
SELECT * FROM V$ARCHIVE_DEST;

[Yogeeraj_1]

hi,

a. there is no command to RESIZE a log file. You must DROP and CREATE them:

alter database drop logfile '.......';

alter database add logfile ....;


b. Check your logs
select group#,members, status from v$log;

Seems like one of yours is STALE!


It should show something like:
GROUP# MEMBERS STATUS
__________ __________ ________________
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE


If you see any STALE, just do 4:
alter system switch logfile;

c.
As for the sizing of redolog files:
I myself start with 3 groups of 2 members of 25meg apiece and work up from there. Either adding more groups or making the members larger until I have enough online redo log to get me through at least 15-20 minutes of work without switching. As with all rules of thumb -- there will be times when 15-20 minutes is too long and too short. You need to pick your time and go from there.


I would recommend that you generate 100 50byte transactions or 100 500k transactions per minute.

You figure out your redo generation rate and use that to help you size it. I generally just add logs until it goes away. Not very scientific but brutally effecient.

hope this helps!

regards
Yogeeraj

[Massimo Bianchi]

Hi,
besides tips from the other, i suugest to have a higher number of redo logs.

Having bigger redo logs is better for big transaction, like a big delete, but if you have many small transaction, increasing the number would be better.

HTH,
Massimo