Re: Too many Archive log files

Johnny A <johnny_at_blacktower.com.au> wrote in message news:01bff142$02a31140$2faa350a_at_digws8...
> Hi all,
>
> I am running Oracle 8.0.6 in archive log mode. I have been getting the
> situation where at certain times I get Archive log files being produced
> every two minutes. Each file size is 16.7 Mb. Is there a way of knowing
> what is causing the archive log files to be produced so rapidly. Is there
> any utility that actually shows you the contents of the log file?
>
> Greatly apreciate any ideas
> Johnny

Archives are generated each time your database switches logfiles. So if you are getting archives every two minutes, that's because you are log switching every two minutes. You can check that by inspecting your alert log -it will have references to "Thread 1 advanced to log sequence xxx' with a time stamp for each 'advance' (ie, log switch).

As a general rule of thumb, you should be log switching around once an hour, or maybe every half hour (though it's difficult to be precise, because it depends so heavily on the nature of your database).

The cure for too-frequent log switches is to make your log files bigger. Since checkpointing will now take longer for each file, you may also want to check that there are enough log groups.

If performance is absolutely essential, you may wish to make your logfiles so big that they take an age to fill up -so you never get log switches unless you schedule them yourself at the dead of night. Makes recovering a database after Instance failure a pain in the neck, but stops all that intensive I/O related to the checkpoints associated with each log switch.

There is no utility that shows you the contents of the log files until you get into Oracle 8.1.5 (ie, "Oracle 8i"). This introduced Log Miner, which shouws you precisely what SQL statements were issued, by whom, and at precisely what times. It's a lovely utility (though clunkily implemented), but not available to you until you upgrade.

Sorry about that!

Regards
HJR Received on Wed Jul 19 2000 - 00:00:00 CDT