Oracle Log Switches and Checkpoints

When a redo log file fills up it switches to the next file. This is called a log switch.

The log switch always causes a checkpoint to occur. This flushes all dirty buffers from the log cache. (writes modified data to disk)

If archiving is on, the old log file id archived.

The new log file is given a log sequence number which is also given to the archived log file.

 

Controlint the checkpoint

LOG_CHECKPOINT_INTERVAL

set to the number of operating system blocks that can be used before a checkpoint occurs.

LOG_CHECKPOINTS_TO_ALERT

Writes a message to the alert log when a checkpoint occurs.

This can take up a lot of space!

LOG_CHECKPOINT_TIMEOUT

Set the time in seconds bertween checkpoints

 

Force a checkpoint

ALTER SYSTEM CHECKPOINT

You may do this if there is a risk of the system failing.

 

Force a log switch

ALTER SYSTEM SWITCH LOGFILE;

you will rarely need to do this.

Oracle Checkpoints

When the data in a oracle database needs to be changed, it has to be done quickly. Accessing the data files on disk is slow and so, all changes in the Oracle database are carried out in the buffer cache and are only written to files at a later point by the DBWR.

The buffer is of finite size and so will fill up after a while. More data will need to be written in to the buffer meaning that old data will need to be written to disk and cleared out. A Least recently Used (LRU) algorithm is used to determine what data to write to disk and remove from the buffer cache.

Popular data will constantly be moving to the top of the queue and may never reach the bottom of the LRU queue where it would be written to disk.

This would be a problem and so Oracle uses something called checkpoints to ensure that all data is eventually written out to disk, regardless of how often it is accessed in memory.

Checkpoints are signaled by either the LGWR (Log Writer) process or the CKPT (checkpoint) background process.

There are two types of checkpoints:

  • Fast Checkpoints
  • Normal Checkpoints

Normal Checkpoints.

  • The DBWR will write a few extra buffers each time it runs.
  • Normal checkpoints will take much longer to complete but have little effect on system performance while they run.

Fast Checkpoints.

  • At each checkpoint, the DBWR writes a large number of buffers.
  • This type of checkpoint completes much quicker, but has a large impact on system while it is running
  • Fast checkpoints are more efficient overall in terms of I/O.

A checkpoint is automatically triggered on a log switch.

You can reduce the recovery time of the database by generating more frequent checkpoints.