Cry about...
Oracle Troubleshooting

---

Checkpoint intervals are more than LOG_CHECKPOINT_TIMEOUT

---

Symptom:

There is activity on the database, but the intervals between subsequent archive logs being written is noticeably longer than the interval specified by LOG_CHECKPOINT_TIMEOUT.

Cause:

It is a misconception that setting LOG_CHECKPOINT_TIMEOUT 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.

Advice:

To check that checkpoints are happening at the specified frequency:

1. Check that LOG_CHECKPOINT_TIMEOUT has the value expected. Verify its value by:

   Connect internal
Show parameter LOG_CHECKPOINT_TIMEOUT

2. Verify that the database is running in archive log mode:

   Archive log list

3. 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:

   LOG_CHECKPOINTS_TO_ALERT=TRUE

4. 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.