Re: ARCHIVELOG mode - HP-UX 10 & Oracle 7.1.4.1.10

cyberldy_at_ix.netcom.com (Tracey Harris ) writes:

>We are running an Oracle database in ARCHIVELOG mode. We have three
>redo logs that are 20 MB each. When the redo logs become full they are
>copied to disk. We have allocated 500MB of disk space for the archive
>files.
 

>Is there a way to determine what causes the archive directory to fill
>so rapidly.

The answer is simple, any time you update, insert, or delete from the database you create redo logs which later become archived logs. Your users must be doing a LOT of database manipulations.

(I don't _think_ temporary tables used during select or join operations are included in the redo logs, but it is worth checking on. If you are using 3rd party tools, they may be creating temporary tables which account for your excessive archive log files.)

Another possibility for 'hidden' activity is if you are using the 'temporary table' workaround to the mutating table problem in trigger statements.

If you are auditing things like selects, the audit data is just another set of tables, so that would cause inserts. (I would imagine that Trusted Oracle generates log files like crazy!) If you aren't using auditing, maybe you could do that for a while to collect data about who is accessing what.

There is a package called SQL*Trax from Turnstone Software that can analyze log files, you might be able to use it to determine what tables are being affected. I've not used it personally, and I don't know what it costs, but they're in Woodside, CA, 415-851-9576.

We have similar problems periodically, like around the end of the month, and I have a cron job that runs every hour or two that compresses the archived log files. Compressed archive logs are typically 1/3 the size of the original log files. According to several presenters from Oracle at IOUW, as long as you are using UNIX compress or other reliable lossless compression techniques, this is quite safe. This contradicts what Oracle has written in the manuals in several places.

(One thing you need to do is make sure you don't try to compress an archive log file that is still being created, though.)

--
Mike Nolan, nolan_at_tssi.com
(posting from nolan_at_helios.unl.edu)