Checkpoint Monitoring (Oracle)
A checkpoint is an operation that Oracle performs to ensure data file consistency. When a checkpoint occurs, Oracle ensures all modified buffers are written from the data buffer to disk files. Frequent checkpoints decrease the time necessary for recovery should the database crash, but may decrease overall database performance.
Checkpoints also lead to the updating of data file headers. If the Oracle background processCKPT is not available for your system or is not started, the Oracle log writer (LGWR) will have to perform this task. The background process CKPT is active if the init<SID>.ora parameter log_checkpoint_process is set to true .
A checkpoint is automatically performed by Oracle each time an on-line redo log fills and the LGWR writes the redo entries from the redo log buffer in the next log group. Theinit<SID>.ora parameter log_checkpoint_interval ( LOG_CHECKPOINT_INTERVAL (Oracle)) determines how many checkpoints are performed between these redo log switches. If this parameter is set to a value larger than the size of the largest on-line redo log file, Oracle will not perform additional checkpoints in between the redo log file switches. The SAP default setting is usually sufficient for most productive systems.
When Oracle performs a switch of the on-line redo log files from one group to the next, archiving of the file (that has just been filled) in the archiving directory is started. This work is normally carried out by the background process ARCH. If the archiving process is not yet complete by the time the background process LGWR want to write this log again, Oracle will have to wait for the log to become available again. Only then can the LGWR process write the next redo entries from the buffer to the online redo log files. When this happens, all processes performing updates on the database may stop, since no changes can be recorded in the redo log buffer.
First check why the ARCH process cannot archive the online redo log files. Perhaps the archiving directory is full or may be the process is no longer active.
Then check whether you can avoid this problem in the future, for example, by increasing the size of the online redo log files. The LGWR will then be able to write considerably more redo entries from the buffer to the online redo log files and will not come into conflict with the ARCH process so quickly. The default size for SAP redo log files is 20 MB. You should only change this size if recommended by SAP or Oracle.