Oracle Checkpoint Process in Oracle AI Database 26ai

Dirty Buffers, Redo, and Durable Changes

When a session changes a cached data block, the corresponding buffer becomes dirty: its contents differ from the block currently stored in the datafile. Oracle also generates redo describing the change. Datafile writing and redo writing serve different purposes and do not have to happen simultaneously.

Under normal synchronous commit behavior, Oracle acknowledges a commit after the required redo, including the commit record, has been written to the online redo log. The changed table or index block may still be in memory. If the instance then fails, recovery can reconstruct the committed change from redo. A commit therefore does not require an immediate write of every affected data block.

The reverse is also possible: a datafile block may contain changes belonging to an uncommitted transaction. Writing the block does not commit that transaction. Oracle retains the information needed to undo uncommitted work. Checkpoint processing must be understood within this combination of redo, undo, and transaction state.

The Roles of CKPT, DBWn, and LGWR

Incremental, Thread, and File Checkpoints

Incremental Checkpoints

Incremental checkpointing advances the recovery position as database writers write dirty buffers during normal operation. Spreading this work over time helps avoid a large concentration of writes when an online redo log switch occurs. The database continues serving requests while this background activity proceeds.

For an incremental checkpoint, CKPT records the advancing checkpoint position in the control file, not in the datafile headers on every advance. Consequently, a datafile-header checkpoint value is not a live display of every increment in instance checkpoint progress. A DBA comparing views must account for the different information they expose.

Incremental checkpointing is normal database behavior. It should not be described as a feature that exists only after a DBA sets a nonzero recovery-time target. Configuration, workload, and other limits influence the writing performed, but a database does not wait for an administrator to schedule periodic cache flushes.

Thread and Database Checkpoints

A thread checkpoint concerns buffers modified by redo in a particular redo thread before a target position. A database checkpoint encompasses the relevant thread checkpoints. This distinction becomes especially useful in RAC, where different instances generate redo in different threads.

Checkpoint activity occurs during a consistent database shutdown, an explicit ALTER SYSTEM CHECKPOINT, and an online redo log switch. A switch does not mean that all associated checkpoint writing finishes instantaneously. Oracle can begin using another available group while the work needed to release an earlier group continues.

An explicit checkpoint requests work; it is not a general-purpose performance improvement. Repeatedly forcing checkpoints can increase writing without addressing the actual reason that recovery estimates or application latency are high. Ordinary operation should rely on an appropriate configuration supported by measurements.

Tablespace and Datafile Checkpoints

Some operations require checkpoint work for a smaller set of files. Examples include making a tablespace read-only, taking it offline normally, and beginning a user-managed tablespace backup. The required scope follows the operation instead of automatically covering every datafile in the CDB.

This distinction also prevents confusion about backups. User-managed backup mode and RMAN backups are different mechanisms. An ordinary RMAN backup does not require the DBA to surround it with manual BEGIN BACKUP and END BACKUP commands. Checkpoint behavior should be explained in the context of the operation actually being performed.

Checkpoint SCNs and Instance Recovery

SCN means System Change Number. It is a logical ordering value used throughout Oracle, including transaction processing and recovery. It is not simply a unique transaction identifier. A checkpoint SCN describes a recovery boundary; it does not necessarily equal the SCN of the latest committed transaction.

After an inconsistent shutdown, instance recovery uses the recorded checkpoint position to determine where redo processing must begin. Earlier changes covered by that position do not have to be reconstructed for this recovery. Oracle applies the required redo and resolves uncommitted work using undo. Checkpoint progress reduces the work, but does not eliminate the need to check transaction state.

Consider a session that commits an update whose redo is durable while the updated block remains in memory. If the instance fails before that block reaches its datafile, redo supplies the change during recovery. If the block has already been written and the checkpoint has advanced sufficiently, recovery can start later. In neither case does the checkpoint itself commit the update.

A completed SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL closes the database consistently, ordinarily avoiding instance recovery at the next startup. SHUTDOWN ABORT does not perform that clean close. When the necessary files remain available, reopening involves ordinary instance recovery. Missing or damaged files introduce a different problem: media recovery may be required.

Checkpoint Progress and Online Redo Reuse

Online redo groups are reused in a cycle. Before an earlier group can be reused normally, Oracle must no longer need its contents for instance recovery. In ARCHIVELOG mode, the applicable archiving requirements must also be satisfied. Checkpoint progress and archiving are therefore separate conditions.

For example, a group can show ARCHIVED=YES and remain ACTIVE. Its redo has been copied, but checkpoint progress has not yet removed its instance-recovery role. Conversely, an inactive group may still await archiving. Either condition can delay reuse when the database needs another group.

An inactive status is not permission to delete archived logs. Those logs may still be required to recover an older backup or support a standby. ARCn copies redo before the online space is reused; checkpointing does not replace archive retention or backup policy. Similarly, one failed redo member does not establish loss of the whole group when a valid multiplexed member survives.

Interpreting Datafile Headers Correctly

Datafile headers contain checkpoint and recovery information, but comparing their SCNs is not a complete consistency test. Incremental progress is recorded differently from file-header checkpoints, and files can have different states. A read-only or offline file must be interpreted in that context. Different numbers alone do not establish corruption or prove that recovery is required.

The V$DATAFILE_HEADER view exposes several separate indicators. CHECKPOINT_CHANGE# is the file checkpoint value; FUZZY identifies a fuzzy file; RECOVER indicates whether media recovery is needed; and ERROR reports a header-read or validation problem. Do not infer one of these conditions simply from another column.

Matching header values also do not prove that every block in a datafile is valid. Header inspection and full block validation answer different questions. A useful diagnosis combines file state, error messages, and recovery metadata instead of relying on a single comparison between SCNs.

Read-Only Queries for Checkpoint Monitoring

Run these examples as an authorized administrator connected to the intended CDB root. Privileges and container visibility affect the rows returned. In RAC, identify the relevant instance and redo thread; use the corresponding GV$ views and instance identifiers when comparing instance-local measurements.

Inspect the Current Configuration

SELECT name, value
FROM v$parameter
WHERE name IN ('fast_start_mttr_target',
               'log_checkpoints_to_alert',
               'log_checkpoint_interval',
               'log_checkpoint_timeout')
ORDER BY name;

Start by reading the existing settings. Changing several checkpoint-related parameters together makes it harder to understand their effects. Record the configuration alongside workload observations so that comparisons between different periods remain meaningful.

Inspect Datafile-Header Evidence

SELECT con_id, file#, tablespace_name, status,
       checkpoint_change#, checkpoint_time,
       fuzzy, recover, error
FROM v$datafile_header
ORDER BY con_id, file#;

Use the container and file identifiers to locate the affected file before interpreting its checkpoint value. If the header cannot be read successfully, other values may be unavailable or unreliable. Correlate a reported problem with the alert log and storage condition rather than immediately assuming that every file needs restoration.

Inspect Redo Groups

SELECT thread#, group#, sequence#, status, archived
FROM v$log
ORDER BY thread#, group#;

Compare observations across log switches. A group remaining active briefly is normal; persistent inability to reuse groups requires investigation. Distinguish a checkpoint-related delay from an archiving delay using the actual messages and state. The query observes the database and does not clear, drop, or otherwise modify a group.

Inspect Estimated Recovery Work

SELECT recovery_estimated_ios, actual_redo_blks,
       target_mttr, estimated_mttr, ckpt_block_writes,
       writes_mttr, writes_autotune, writes_full_thread_ckpt
FROM v$instance_recovery;

TARGET_MTTR is the effective recovery-time target in seconds. ESTIMATED_MTTR estimates recovery time for the current workload state. Despite its name, RECOVERY_ESTIMATED_IOS is documented as a count of dirty buffers. ACTUAL_REDO_BLKS describes redo blocks currently required for recovery.

The write counters help distinguish checkpoint activity associated with MTTR control, automatic tuning, and full thread checkpoints. Compare their changes over a measured interval; a cumulative total alone is not an activity rate. Take account of an instance restart when comparing counters, and retain timestamps for each observation.

Choosing a Recovery-Time Objective

FAST_START_MTTR_TARGET expresses a target for single-instance crash recovery in seconds. It is not a timer that orders a checkpoint every specified number of seconds. Its documented default is zero, and it is not modifiable in a PDB. Other settings, including existing checkpoint limits, can influence the effective behavior.

The target also differs from the complete application recovery objective. Storage availability, database startup, service registration, connection retries, and application checks can contribute additional downtime. Meeting an estimated database recovery target does not guarantee that every application will be available within the same interval.

Select a target in the context of acceptable downtime and the capacity of the storage system. More aggressive checkpoint writing can reduce outstanding recovery work while increasing normal write activity. Evaluate the balance under representative load. There is no universal reason that every OLTP database should checkpoint at a fixed interval of 20–30 minutes.

A growing recovery estimate is a reason to examine trends, not a diagnosis by itself. Consider changes in workload, datafile write latency, redo-group sizing, and archiving conditions. Several active groups may reflect a temporary burst. A repeated reuse stall, supported by alert-log evidence, warrants closer investigation of the limiting resource.

Checkpoint Messages in the Alert Log

LOG_CHECKPOINTS_TO_ALERT controls checkpoint logging in the alert log. Its default is false. Enabling logging can make checkpoint activity easier to observe, but the parameter does not set checkpoint frequency or establish an appropriate recovery target. It is also not PDB-modifiable.

Review checkpoint messages with their timestamps and surrounding errors. A message becomes more useful when matched to a log switch, a workload change, or a recovery estimate collected at the same time. The absence of a message under a particular logging configuration is not proof that checkpointing has stopped.

What a DBA Should Remember

Oracle 26ai References

• Process architecture: CKPT, DBW, and LGWR
• Checkpoints and instance recovery
• Managing online redo logs
• V$DATAFILE_HEADER and V$INSTANCE_RECOVERY
• FAST_START_MTTR_TARGET and LOG_CHECKPOINTS_TO_ALERT

Archive Log File - Quiz