Re: Frequency of log switches

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3f83d530$0$29410$afc38c87_at_news.optusnet.com.au>...
> 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
Howard,

yesterday evening I stopped archiving for my test database. The log switches were back to 'normal' frequency. Which means one log switch per
12hrs. Since nobody is using this instance that's what I would exspect.
I used log miner as described in the Administarator Guide and found 7000 records in $logmnr_contents when archiving was on, but 22000 records when archiving was off.
In both cases I only found entries for user SYS (SYS.SNAP,SYS.JOB etc.) and INTERNAL operations.

What could be the reason for this strange behavior? (I also searched metalink but couldn't find anything)

Cheers
Kurt-Erich Received on Thu Oct 09 2003 - 06:35:14 CDT