Re: alert_SID.log -> cannot allocate new log, sequence x

In article <3l8r9k$n00_at_nic.tip.net>, Jonas Heyman <jonas_at_sth.ifsab.se> says:

>
>Hi,
>
>I'd experienced the following error in alert_SID.log:
>
>Thread 1 cannot allocate new log, sequence 3161
>Checkpoint not complete
>
>It happens while there are a lot of jobs and transactions going on.
>Also the database seems to hang and does a lot of internal job.
>
>1. Should I add more logfiles ?

  Absolutely. The error message indicates that it is still archiving the logfiles it has, and now has to WAIT because they are all full and in need of archiving. Database transactions are kept in these files, so the users are now waiting too.

>2. Should the logfiles be larger of size ?

  There is a tradeoff to decide on here. Larger logfiles mean more data in the files and hence longer instance recovery time. On the other hand, smaller logfiles keep the archiver active, stealing cycles from the rdbms and hence users. So - faster machine! or faster recovery a slower one?

>3. Should I do a checkpoint more often or should
> they occour at logswitch ?

  The DBA Guide indicates that the LOG_CHECKPOINT_INTERVAL "should be set similarly to the LOG_ALLOCATION parameter, so that it equals or exceeds the number of bytes in the database's redo log files" to improve server performance. How often to you have to recover? Will an extra 5? minutes recovery time matter?

					Good Luck!
					Brian
--
Brian Graham                    .
Programmer / DBA        ._______|_______. Tel: 613-560-5470
Public Service Alliance       \(*)/       Fax: 613-563-3492
of Canada                     o/ \o       Email: grahamb_at_psac.com