Explain how database writers and the checkpoint process cooperate, why completed writes can shorten redo replay after an instance failure, and how to interpret recovery-time targets and estimates. The detailed redo-group lifecycle belongs to the following lesson.
Oracle DBA Lesson 12 Part 1 — Why Checkpoint Progress Reduces Recovery Work
Recovery has a starting position
A changed database block can remain in the buffer cache in RAM while its data-file copy on disk contains an older value. Redo records describe the changes and are persisted in physical online redo log files. After an instance failure with those files intact, Oracle uses redo to reconstruct changes that the data files still need.
The checkpoint position identifies where instance recovery must begin in the redo stream. Its progress depends on the oldest dirty buffer: a changed working copy whose required write is still outstanding. As database writers complete the relevant earlier writes, the recovery starting position can advance. The database continues to accumulate newer changes, so other buffers can remain dirty.
The video follows three blocks:
| Block | Working value in RAM | Older value in the data file | Initial situation |
|---|---|---|---|
| A | 11 | 10 | Oldest outstanding changed block |
| B | 21 | 20 | A later changed block |
| C | 31 | 30 | A newer changed block |
The corresponding redo changes have already reached disk. DBWn copies A's changed contents to its data file. A's buffer can stay in RAM for later use. Once the earlier write requirements are satisfied, B becomes the oldest outstanding dirty buffer and the recovery position advances to its relevant redo. The range from that position to the end of redo is shorter.
This is a simplified three-block case with one outstanding change interval per block and no intervening updates. Real checkpoint positions are redo locations associated with system change numbers (SCNs). They are not literal block names. Oracle tracks the oldest outstanding work across the instance. A block may be changed again after being written. Completing an arbitrary newer block's write does not by itself establish that every earlier write is complete.
During recovery, redo can reconstruct both table and undo changes. Transaction recovery uses undo to roll back uncommitted work. This lesson concentrates on checkpoint progress and the redo replay range. The earlier redo and undo lesson explains transaction outcomes. See checkpoints and instance recovery.
DBWn writes blocks; CKPT records progress
DBWn identifies the database-writer processes. They write modified database buffers to physical data files. Writer activity has several triggers, including checkpoint requirements and the need for reusable buffers.
CKPT is the checkpoint process. It signals database writers and records checkpoint information. The control file is a physical file on disk containing information needed to identify recovery progress. Data-file headers also carry checkpoint information.
The kind of checkpoint matters. During incremental checkpointing, completed writer work advances the checkpoint position and CKPT updates that position in the control file. It does not update data-file headers for every incremental advance. Other checkpoint events update the applicable headers. A thread checkpoint completes the writes required before a particular target. Newer changes can still be dirty.
A log switch starts checkpoint work associated with the earlier redo. Switching log groups and completing all relevant checkpoint writes are separate events. Very frequent switches can increase checkpoint activity. Investigate workload, log-group sizing, and write performance before planning changes. See process architecture, checkpoint types, and managing the redo log.
Read the recovery objective and current estimate
FAST_START_MTTR_TARGET expresses a recovery-time objective in seconds. Oracle manages incremental checkpoint writes in an attempt to meet a practical target. A tighter objective can require more writing during normal activity, consuming I/O capacity that the workload also needs.
The setting is an instance parameter, with a documented default of zero and a range of 0–3600 seconds. It is not modifiable within a pluggable database. Other checkpoint settings can interfere with target-based management. Establish the current configuration and the system's practical range before considering tuning. See FAST_START_MTTR_TARGET.
Use this query and SQL*Plus command:
SELECT target_mttr, estimated_mttr,
recovery_estimated_ios, writes_mttr
FROM v$instance_recovery;
SHOW PARAMETER fast_start_mttr_targetThe SELECT runs in the database. SHOW PARAMETER is a SQL*Plus command that displays the parameter name, type, and value.
| Field | Meaning and interpretation |
|---|---|
TARGET_MTTR | Effective recovery-time target in seconds, calculated from the parameter and system limitations. Compare it with the configured value. |
ESTIMATED_MTTR | Current recovery-time estimate in seconds, based on dirty buffers and redo blocks. It can change as workload and writing progress change. |
RECOVERY_ESTIMATED_IOS | The documented field reports the number of dirty buffers in the buffer cache. Use it as recovery-work context. The value is not an exact count of every physical I/O a future restart will perform. |
WRITES_MTTR | Counter of writes driven by FAST_START_MTTR_TARGET. Compare consecutive samples from the same instance startup to determine additional writes. |
If the target is unspecified, TARGET_MTTR can be zero. The estimate is still calculated. Establish that context before interpreting zero or unchanged values. See V$INSTANCE_RECOVERY and monitoring and tuning recovery performance.
Recent updates increase the estimate
Two samples from the same instance startup show:
| Sample | TARGET_MTTR | ESTIMATED_MTTR | RECOVERY_ESTIMATED_IOS | WRITES_MTTR |
|---|---|---|---|---|
| After heavy updates | 30 | 45 | 120 | 1,200 |
| Later, as writes progress | 30 | 24 | 80 | 1,450 |
The effective objective stayed at 30 seconds. The estimated time initially rose with outstanding work, then fell as writes progressed. The dirty-buffer count also fell in this case. The write-counter difference is 1,450 − 1,200 = 250 additional target-driven writes. There is no one-to-one relationship between this difference and the net reduction in dirty buffers. Other writes, new changes, and repeated changes can occur between samples.
The samples are not evidence that changing a parameter caused an improvement. No parameter was changed.
Recovery estimates describe expected behavior under current conditions. Workload, storage response, instance startup, and the application's service requirements affect actual availability. Controlled restart tests establish measured recovery and service availability. Evaluate that result alongside normal writing cost when choosing an objective. Oracle's recovery-performance guidance explains practical targets and calibration.
Recap
DBWn makes changed blocks persistent. CKPT records checkpoint progress. Advancing the starting position reduces redo replay after a failure. Compare the effective target, current estimate, and write-count changes, then use controlled recovery measurements to judge the practical balance.
No comments:
Post a Comment