Thread 1 cannot allocate new log

Sometimes, you can see in your alert.log file, the following corresponding
messages:


  Thread 1 advanced to log sequence 248
    Current log# 2 seq# 248 mem# 0: /prod1/oradata/logs/redologs02.log

  Thread 1 cannot allocate new log, sequence 249
  Checkpoint not complete



This message indicates that Oracle wants to reuse a redo log file, but
the
corresponding checkpoint associated is not terminated.  In this case,
Oracle
must wait until the checkpoint is completely realized. This situation
may be encountered particularly when the transactional activity is
important.


 This situation may also be checked by tracing two statistics in the
BSTAT/ESTAT report.txt file.  The two statistics are:


  - Background checkpoint started.
  - Background checkpoint completed.


These two statistics must not be different more than once.  If this is

not true, your database hangs on checkpoints.  LGWR is unable to
continue
writing the next transactions until the checkpoints complete.


Three reasons may explain this difference:


- A frequency of checkpoints which is too high.
- A checkpoints are starting but not completing
- A DBWR which writes too slowly.


The number of checkpoints completed and started as indicated by
these statistics should be weighed against the duration of the
bstat/estat
report.  Keep in mind the goal of only one log switch per hour, which
ideally
should equate to one checkpoint per hour as well.


The way to resolve incomplete checkpoints is through tuning
checkpoints and
logs:


1) Give the checkpoint process more time to cycle through the logs
       -  add more redo log groups
       -  increase the size of the redo logs
2) Reduce the frequency of checkpoints
        - increase LOG_CHECKPOINT_INTERVAL
        - increase size of online redo logs
3) Improve the efficiency of checkpoints enabling the CKPT process
with CHECKPOINT_PROCESS=TRUE
4) Set LOG_CHECKPOINT_TIMEOUT = 0.  This disables the checkpointing
based on
     time interval.
5) Another means of solving this error is for DBWR to quickly write
the dirty
buffers on disk.  The parameter linked to this task is:


  DB_BLOCK_CHECKPOINT_BATCH.



DB_BLOCK_CHECKPOINT_BATCH specifies the number of blocks which are
dedicated
inside the batch size for writing checkpoints.  When you want to
accelerate
the checkpoints, it is necessary to increase this value.