Need help improving redo arch speed

From: Vince Laurent <vlaurent_at_NOSPAM.networkusa.net>
Date: Tue, 20 Aug 2002 16:31:35 -0500
Message-ID: <v3d5mucd5m4vu9ni82e1r6csk65ieaj4lk@4ax.com>

We have HP OpenView monitoring our Oracle Database. I get the following message every so often:

Message Text : DBSPI-0060.1: Redo logs unarchived count (2.00) is too high for PRD (>=1.50).
Original Text: DBSPI-0060: 2.00 (Threshold 1.50 exceeded)

The instructions say:

The number of redo logs not yet archived is higher than the ITO set threshold (usually 1).

Probable cause(s): Archive process stalled or set to manual, archive file system full or archival too slow to keep pace with database activity.

Potential impact: Failure

Suggested action(s): Check archive destination file system space. Check alert log for errors. Verify archiving is automatic.

So...
1. The archive file system has plenty of space 2. Here is the alert log for that time:

Tue Aug 20 11:06:21 2002
Completed checkpoint up to RBA [0x4070.2.10], SCN: 0x0000.024b1af0 Tue Aug 20 11:07:25 2002
Beginning log switch checkpoint up to RBA [0x4071.2.10], SCN: 0x0000.024b24c9
Thread 1 advanced to log sequence 16497
Tue Aug 20 11:07:25 2002
ARC0: Beginning to archive log# 18 seq# 16496 Tue Aug 20 11:07:25 2002
Current log# 15 seq# 16497 mem# 0: /oracle/PRD/origlogA/log_g15m1.dbf Current log# 15 seq# 16497 mem# 1: /oracle/PRD/mirrlogA/log_g15m2.dbf ARC0: Completed archiving log# 18 seq# 16496 Tue Aug 20 11:07:45 2002
Beginning log switch checkpoint up to RBA [0x4072.2.10], SCN: 0x0000.024b2622
Thread 1 advanced to log sequence 16498
Current log# 11 seq# 16498 mem# 0: /oracle/PRD/origlogA/log_g11m1.dbf Current log# 11 seq# 16498 mem# 1: /oracle/PRD/mirrlogA/log_g11m2.dbf Tue Aug 20 11:07:45 2002
ARC0: Beginning to archive log# 15 seq# 16497 Tue Aug 20 11:07:56 2002
ARC0: Completed archiving log# 15 seq# 16497 Tue Aug 20 11:07:57 2002
Completed checkpoint up to RBA [0x4071.2.10], SCN: 0x0000.024b24c9 Tue Aug 20 11:08:04 2002
Beginning log switch checkpoint up to RBA [0x4073.2.10], SCN: 0x0000.024b27cc
Tue Aug 20 11:08:04 2002
ARC0: Beginning to archive log# 11 seq# 16498 Tue Aug 20 11:08:04 2002
Tue Aug 20 11:08:04 2002
Thread 1 advanced to log sequence 16499
Current log# 12 seq# 16499 mem# 0: /oracle/PRD/origlogB/log_g12m1.dbf Current log# 12 seq# 16499 mem# 1: /oracle/PRD/mirrlogB/log_g12m2.dbf Tue Aug 20 11:08:12 2002
Completed checkpoint up to RBA [0x4072.2.10], SCN: 0x0000.024b2622 Tue Aug 20 11:08:14 2002
ARC0: Completed archiving log# 11 seq# 16498

3. And archiving is automatic.

Looking at the redo history I see:

2002/08/20-16:20:10 orastat | oraver=8.1.7 oraver3=8.1 sqlcmd=sp 2002/08/20-16:20:10 orastat | ORACLE_SID=PRD ORACLE_HOME=/oracle/PRD/817_64
2002/08/20-16:20:10 orastat | Version=2.33 Host=sapprd 2002/08/20-16:20:10 orastat | -rh: Archive Log History

Thread ArcLog First Chg. Num Last Chg. Num Timestamp

---- -------- --------------- --------------- ------------------------
     1    16493        38462460        38468765 08/20/2002-10:26:04
     1    16494        38468765        38474037 08/20/2002-10:43:22
     1    16495        38474037        38476528 08/20/2002-10:53:14
     1    16496        38476528        38479049 08/20/2002-10:58:47
     1    16497        38479049        38479394 08/20/2002-11:07:25
     1    16498        38479394        38479820 08/20/2002-11:07:45
     1    16499        38479820        38483954 08/20/2002-11:08:04
     1    16500        38483954        38488713 08/20/2002-11:17:50
     1    16501        38488713        38492566 08/20/2002-11:34:08
     1    16502        38492566        38494906 08/20/2002-11:41:36
     1    16503        38494906        38496182 08/20/2002-11:46:04

There were 2 log files , 16497 and 16498, within 20 seconds at 11:07am. (I highly recommend orastat if you don't have it!)

I was looking at some params (log_archive_buffer_size) and such but most are obsolete on the version of Oracle we are on (8.1.7.3). We are also on HPUX 11

We currently have 8 pairs of 50M redo files. How can I speed up the archiving process?

Thanks! And sorry for such a long post but I figured more info the better.

Vince Received on Tue Aug 20 2002 - 16:31:35 CDT