Spotlight on Oracle – Log file space/switch waits (SOO63)

Log file space/switch waits

Log file space/switch waits occur when a redo log entry cannot be made. A redo log cannot be written to when:

• there is no free space in the redo log buffer, or
• it is in the process of being switched.

The following error messages may also be included in the alarm log for the database instance:

• CANNOT ALLOCATE NEW LOG CHECKPOINT NOT COMPLETE
• CANNOT ALLOCATE NEW LOG
• ALL ONLINE LOGS NEED ARCHIVING.

The incidence of these events should be negligible in a well-tuned database. These two conditions are defined by the following events:

• Log buffer space. Waiting for free space in the redo log buffer. You can reduce this wait by increasing the size of the log buffer (LOG_BUFFER parameter), or by optimizing the performance of the log writer.
• Log file switch (checkpoint incomplete). The next redo log cannot be used because a checkpoint that commenced when the log was last switched has not completed.
• Log file switch (archiving needed). A redo log cannot be used because an archive operation that commenced when it was last switched has not completed.

These waits may indicate that:

• your redo logs are on slow devices
• your LOG_BUFFER setting is too low
• you have too few, or too small redo logs

If you are getting log file switch (archiving needed) events, consider alternating your redo logs across multiple devices to reduce contention between the log writer and archiver processes.  For more information, see Improving redo log writer performance.