What causes a CKPT checkpoint in Oracle

Before you can trust the data in your database, you need a way to know that the instance and the files on disk actually agree with each other. That is the job a checkpoint does. A checkpoint is the mechanism Oracle AI Database uses to prove, at a given moment, that everything committed up to a certain point in the redo stream is safely written to the data files. Without that proof, every instance failure would force a full replay of the entire redo history, which could take hours on a busy database. With checkpoints happening continuously in the background, recovery only has to replay what happened since the last one, which is normally a matter of seconds.
This lesson covers three things: who does the work of a checkpoint, what actually triggers one, and how the checkpoint mechanism connects to instance recovery and to the different ways you can shut a database down.

The CKPT process is a coordinator, not a writer. CKPT updates the headers of the control files and the data files with checkpoint information: the checkpoint position, the system change number (SCN), and the point in the redo stream where recovery would need to begin if the instance failed right now. To do that, CKPT signals DBWn to write the corresponding dirty buffers from the database buffer cache to disk. CKPT itself never writes a data block to a data file or a redo entry to an online redo log; that work belongs to DBWn and LGWR respectively. This division of labor is diagrammed below.

The checkpoint position itself is worth understanding on its own, because it is what makes recovery predictable. At any moment, the checkpoint position is determined by the oldest dirty buffer still sitting in the database buffer cache, that is, the oldest change that has not yet made it to disk. Every committed change with an SCN lower than the checkpoint SCN is guaranteed to already be on disk in the data files. Everything after that point is what instance recovery would need to reapply from the online redo logs. The older the checkpoint position is allowed to drift from the current SCN, the more redo there is to replay after a failure, which is exactly why the database keeps advancing it continuously rather than waiting for a single large event.

You can force a checkpoint at any time from SQL*Plus or Oracle SQL Developer:

> ALTER SYSTEM CHECKPOINT;

In a Real Application Clusters configuration, you can scope the checkpoint to every redo thread in the cluster rather than just the local instance:

> ALTER SYSTEM CHECKPOINT GLOBAL;

A manual checkpoint is handy before planned maintenance, such as taking a tablespace offline or preparing for a controlled shutdown, since it lets you push the checkpoint position forward on your own schedule instead of waiting for an automatic trigger.

Most of the time you never issue a checkpoint yourself. Oracle AI Database triggers several distinct kinds automatically, and it helps to think of them as three separate categories rather than one flat list.

The first category is the thread checkpoint. A thread checkpoint writes to disk every buffer modified by redo in a given thread, up to a target point. The set of thread checkpoints across every instance in a database is, collectively, a database checkpoint. Thread checkpoints happen in four situations:

1. A consistent database shutdown, using the normal, immediate, or transactional options
2. An explicit ALTER SYSTEM CHECKPOINT statement, like the one shown above
3. An online redo log switch, which happens automatically once the current log fills
4. An ALTER DATABASE BEGIN BACKUP statement, which needs a consistent checkpoint before an old-style hot backup can begin

The second category is the tablespace and data file checkpoint, which is scoped to the data files of one tablespace rather than the whole database. A tablespace checkpoint is really a set of individual data file checkpoints, one per data file in that tablespace. These occur when you:

1. Make a tablespace read-only
2. Take a tablespace offline using the normal option
3. Shrink a data file
4. Issue an ALTER TABLESPACE BEGIN BACKUP statement

This is also why an older, offline tablespace does not need to participate in every database-wide checkpoint going forward: its own checkpoint already captured everything it needed to.

The third category, and the one doing most of the real work on a running production database, is the incremental checkpoint. Rather than waiting for a redo log switch and then writing a large batch of blocks all at once, which is how conventional, event-driven checkpointing used to behave, DBWn checks at least every three seconds to see whether it has work to do. Each time it writes buffers, it writes the oldest modified blocks first, which lets the checkpoint position advance a little at a time. CKPT then records that new position in the control file, though not in the data file headers, since a full header update only happens at the thread and tablespace checkpoints described above. Incremental checkpointing is what eliminates the large I/O spikes that used to come with bulk, event-driven checkpoints, at the cost of doing small amounts of writing almost all the time instead.

Incremental checkpointing is governed by the FAST_START_MTTR_TARGET initialization parameter. It lets you set a target mean time to recover, in seconds, for how long cache recovery should take after an instance failure. Once it is set, the database manages its incremental checkpoint writes to try to hit that target:

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=30;

The maximum value is 3600 seconds, one hour; anything higher is rounded down to 3600. In principle the minimum is one second, though what is actually achievable depends on factors like instance startup time, not just checkpoint frequency. You can check what your database can really deliver at its current setting by querying the TARGET_MTTR column of V$INSTANCE_RECOVERY. If daily throughput matters more to you than a fast recovery, setting FAST_START_MTTR_TARGET to the maximum of 3600 minimizes checkpointing overhead while still keeping Fast-Start Fault Recovery active. Sizing your online redo log files appropriately also matters here: aim to switch logs no more often than about once every twenty minutes, and keep every redo log file the same size, since undersized logs force more frequent checkpoints than you actually want.

Three older, static parameters, FAST_START_IO_TARGET, LOG_CHECKPOINT_INTERVAL, and LOG_CHECKPOINT_TIMEOUT, predate FAST_START_MTTR_TARGET and should be disabled or removed once you are using it, since leaving them set interferes with how the database manages recovery time. All of these are set through the instance's parameter file, either the older, editable PFILE, still fully supported, or the modern SPFILE, which lets ALTER SYSTEM changes persist across a restart without hand-editing a text file.

Tuning FAST_START_MTTR_TARGET is not a set-it-and-forget-it exercise, and the V$INSTANCE_RECOVERY view is how you check whether your setting is actually doing what you think it is. Two columns matter most:

SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR
  2  FROM V$INSTANCE_RECOVERY;

TARGET_MTTR reports the effective MTTR target in seconds, and reads 0 if FAST_START_MTTR_TARGET was never set. ESTIMATED_MTTR is always calculated, whether or not you have set a target, and reflects the current estimated recovery time based on the number of dirty buffers and log blocks right now. The two values are not the same kind of number, one is a goal and the other is a live estimate, but they should generally track each other once the system settles down. If ESTIMATED_MTTR stays noticeably longer than TARGET_MTTR over time, raise FAST_START_MTTR_TARGET to something no lower than what the database can realistically sustain. If it stays consistently shorter, you likely have room to relax FAST_START_MTTR_TARGET and let the database checkpoint less aggressively, trading a bit of recovery time for better run-time throughput. Because ESTIMATED_MTTR reacts quickly to recent activity, a single reading right after a burst of updates is not a reliable basis for a tuning decision; check it again after the workload settles before changing anything.

Checkpoints also decide when a filled online redo log file becomes available for reuse, and the rule depends on whether the database is in ARCHIVELOG mode or NOARCHIVELOG mode. In NOARCHIVELOG mode, a filled redo log file is available for reuse as soon as the changes recorded in it have been checkpointed, that is, written to the data files by DBWn. There is nothing else waiting on that log file once the checkpoint has caught up to it. In ARCHIVELOG mode, that same log file has to clear two hurdles instead of one: the changes it holds must be checkpointed to the data files, and the file itself must have been archived to an archive log destination before LGWR can reuse it. This is one of the reasons an ARCHIVELOG database that falls behind on archiving can stall: LGWR eventually runs out of redo log files it is allowed to overwrite, and every checkpoint in the world will not free one up until archiving catches up.

The checkpoint SCN is also what separates a consistent backup from an inconsistent one, a distinction this course will come back to in later lessons on RMAN. In a consistent backup, every read/write data file and control file shares the same checkpoint SCN, which guarantees the backup already contains every change up to that point and needs no recovery after it is restored. A consistent whole database backup is only possible right after a consistent shutdown, and in NOARCHIVELOG mode it is the only backup option you have, since there is no archived redo available to bring an inconsistent backup forward to a usable state.

An inconsistent backup is different: the data files and control file are not guaranteed to share a checkpoint SCN, so some changes may be missing from individual files relative to others. Every online backup, meaning one taken while the database stays open, is necessarily inconsistent for this reason. That is fine as long as the database is in ARCHIVELOG mode, because the archived redo generated during and after the backup lets RMAN roll every file forward to the same point during recovery. Without ARCHIVELOG mode, an inconsistent backup has no way to become consistent again, which is exactly why production databases that need online backups have to run in ARCHIVELOG mode.

The checkpoint position is also what decides whether a restarting instance needs recovery at all, and that is where the different SHUTDOWN modes matter. A SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL all perform a checkpoint and close the open files as part of shutting down, so the next startup does not require instance recovery. SHUTDOWN ABORT is different: it is the fastest of the four precisely because it skips that checkpoint step entirely.

Note: a manual checkpoint issued right before a SHUTDOWN ABORT can reduce how much redo needs to be replayed afterward, but it cannot remove the need for instance recovery. Because ABORT never checkpoints the open data files, no matter what happened beforehand, instance recovery is required the next time the database opens. If you can afford to wait even briefly, a SHUTDOWN IMMEDIATE gets you a clean shutdown with no recovery step at all. Reach for ABORT only when nothing else is working.

Click the Quiz link below to test your understanding of LGWR, ARCH, and DBWR.
lgwr ckpt - Quiz
The next lesson explores database physical files.