Re: Frequency of log switches

Kurt-Erich Finger wrote:

> Oracle 8.1.7 on Linux 2.4
>
> Hello,
>
> I am trying to implement a back-up/recovery procedure in my company.
> To test it, I took a copy of the production database, added
>
> log_checkpoint_interval = 10000
> log_archive_dest=/data/arc
> log_archive_format='arc%s.log'
> log_archive_start=True
>
> to the init-file and started the instance with
> ALTER DATABASE ARCHIVELOG
>
> The archived logs are generated, however, at a high frequency.
> Before I switched on archiving there were only some log switches
> per day. Switching on archivelog I got new logs (and archived logs) every
> minute when the the size of the log-files was 10mb and log switches
> every 20 minutes when the size of the log-files was 50mb.
> The frequencies seem to be very high taking into account that there is
> no (user) activity on the instance.
>
> How can I reduce the log switches to reasonable numbers?
> (increasing log_checkpoint_interval did not help)
>
> Cheers,
> Kurt-Erich

It wouldn't have helped, because log_checkpoint_interval has nothing to do with generating redo, and it's only the generation of redo which causes log switches.

Neither does switching on archiving in itself generate extra redo. It merely means that a copy of a log is taken once it has been switched away from. The switching away from it in the first place still only happens because the log gets full... and that's only possible if redo is being generated.

Something must be generating redo on your instance/database. Logs don't just switch for no reason. So you have to track down what it is. Why not have a go with dbms_logmnr and find out what is in a recent log. It could be a lot of recursive SQL stuff, but it could equally well be that something is being done on the instance that you're not aware of.

Anyway, check out log miner, and see what's happening on your database.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------