Re: Frequency of log switches

If any of your tablespaces are in HOT backup mode, you will be generating a *LOT* of redo. Make sure that is not the case in your setup.

-V

on 10/8/2003 2:10 AM Howard J. Rogers said the following:

>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
>
Received on Wed Oct 08 2003 - 13:33:49 CDT