LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT Parameter in Oracle
What is checkpoint?
A checkpoint is when the database synchronizes the dirty blocks in the buffer cache with the datafiles. That is, it writes changed data to disk.
LOG_CHECKPOINT_INTERVAL parameter
LOG_CHECKPOINT_INTERVAL parameter specifies the maximum amount of redo blocks which can exist between checkpoints.
In other way, Its specifies (in blocks) the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log
It also specifies the maximum number of redo blocks the incremental checkpoint target should lag the current log tail.
Note:
Value defined in number of operating system block in the redo log between the checkpoint position and the end of the redo log.
Value 0 Means infinity or ignored the parameter.
ALTER SYSTEM SET LOG_CHECKPOINT_INTERVAL = 600;
LOG_CHECKPOINT_TIMEOUT Parameter
LOG_CHECKPOINT_TIMEOUT Parameter specifies the maximum number of seconds between checkpoints. It means how long a dirty buffer in buffer cache can remain dirty.
In other way, specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log.
Note:
value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.
ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT = 1800;