Analyzing space problems due to excessive Oracle archive logs

Description

One of the main causes of space problems in the /exlibris filesystem in Aleph is the Oracle archive log directory -- typically at /exlibris/oradata/aleph2n/arch, but could be in a different location.  

Resolution

[For a discussion of Aleph space problems in general, see the article " /exlibris filesystem 100% full; locating files to delete **MASTER RECORD** " .]

The size of the archive logs has increased greatly in recent years as libraries have been loading more bibliographic records with large 5xx fields with many words in them.  This causes much more updating of the z980, z98, and z97 tables (by the ue_01_word_parallel process) than had been the case the previously.

At one site it was noted that loading 40,000 bib records resulted in 84 gig of archive logs being produced (in the  /exlibris/oradata/aleph23/arch directory).   

It typically takes 3-5 days for Oracle to free up the ./arch space.  (This occurs when Oracle has determined that it will no longer need these archive log files for recovery purposes.)  Note:  The ./arch files should *not* be deleted manually.   You need to let Oracle handle it, although, it may be possible to do a cold backup and have Oracle (or your DBA) delete the no-longer-necessary archive files.

This site had been doing file-96 loads of 20,000 records each night.  Because /arch was filling up too fast, in order to allow the Oracle archive-log-cleanup to keep pace, it was determined that only 10,000 of these large records could be loaded each night.

These are tools you can use to determine the state of things at any particular time:

> s+ xxx01
SQL> select count(*) from z07;

(The above shows the number of indexing requests waiting to be processed.)

> s+ xxx01
SQL> select count(*) from z07a;

(The above shows the number of word-indexing requests -- generated by ue_01 -- waiting to be processed.)

The following shows the how much space the archive logs are currently taking:

> cd /exlibris/oradata/aleph23/arch
> du -sh

It may be that a run of manage-01 (to completely regenerate the Word indexes) could improve the ue_01_word indexing speed, but this is uncertain.