Checkpoint intervals are more than LOG_CHECKPOINT_TIMEOUT
There is activity on the database, but the intervals between subsequent
archive logs being written is noticeably longer than the interval specified
It is a misconception that setting
to a given value will initiate a log switch at that interval (possibly enabling
a recovery window used for a stand-by database configuration). Log switches
cause a checkpoint, but a checkpoint does not cause a log switch.
To check that checkpoints are happening at the specified frequency:
- Check that
LOG_CHECKPOINT_TIMEOUThas the value expected. Verify its value by:
Show parameter LOG_CHECKPOINT_TIMEOUT
- Verify that the database is running in archive log mode:
Archive log list
- According to the Oracle documentation: “A checkpoint scheduled to
occur because of this parameter [
LOG_CHECKPOINT_TIMEOUT] is delayed until the completion of the previous checkpoint if the previous checkpoint has not yet completed.” Set the initialisation parameter:
- Then close and restart the database. This will allow monitoring of when each checkpoint started and when it actually finished. Be sure to set this back to False (the default) once the problem is resolved.
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.