Oracle 'Checkpoint not complete': redo log sizing, DBWn, and log-switch stalls

The alert log says Checkpoint not complete followed by Current log# N seq# N mem# N: <path>. Foreground sessions stall on log file switch (checkpoint incomplete) at every log switch. Commits hesitate for tens of milliseconds to seconds, and the stall repeats each time LGWR wraps to the next redo log group. The wrong fix (enlarging redo logs when the real bottleneck is DBWn I/O) only buys minutes.

This guide assumes you understand the Oracle write path from how Oracle Database works in production.

What this means

LGWR cycles through online redo log groups. Before it can reuse a log group, two conditions must hold for that group: every redo byte in it must have been flushed by LGWR (trivially true once the log fills), and the dirty buffers protected by that redo must have been written to datafiles by DBWn. The second condition is the one that fails. The thread checkpoint SCN cannot advance past group N until DBWn has flushed everything covered by group N’s redo.

When DBWn has not caught up, CKPT cannot mark the checkpoint complete, and LGWR cannot wrap. Every session that issues COMMIT or any DML waits on log file switch (checkpoint incomplete). The wait is system-wide, not per-SQL. TPS drops uniformly because every write transaction is blocked at the same point.

Alert-log diagnostics vary by release, so check the release-specific strings for your patch level. The traditional Checkpoint not complete text is the canonical signal to grep.

This is not the same failure as the archiver hang. log file switch (archiving needed) and Thread N cannot allocate new log, sequence N in the alert log mean ARCn cannot copy redo to the archive destination. That requires freeing archive space, fixing the FRA, or repairing ARCn. This guide covers the DBWn path. The two have similar symptoms but different fixes.

flowchart TD
    A[LGWR fills current redo log] --> B{Checkpoint
advanced past it?}
    B -->|Yes, wrap| A
    B -->|No| C[All committing sessions wait
log file switch checkpoint incomplete]
    C --> D[LGWR sleeps and polls controlfile
control file sequential read]
    D --> E[CKPT updates datafile headers
holds controlfile enqueue]
    E --> F[DBWn flushes dirty buffers
to datafiles]
    F -->|Slow I/O or too few writers| G[Stall persists
commits hesitate]
    F -->|Catches up| B

LGWR does not synchronously block on DBWn. It goes into an idle sleep and polls the controlfile (producing control file sequential read waits) while waiting for checkpoint progress. CKPT, holding the controlfile enqueue while updating datafile headers during the switch, is what stalls LGWR. The wait chain to look for, if you have Diagnostics Pack, is foreground to LGWR to CKPT (controlfile enqueue) to DBWn (slow I/O).

Common causes

Quick checks

-- Confirm the wait event and recent stall time
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO / NULLIF(TOTAL_WAITS,0) / 1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'log file switch%';

-- Log switch frequency by hour
SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24') AS hr, COUNT(*) AS switches
FROM V$LOG_HISTORY
WHERE FIRST_TIME > SYSDATE - 1
GROUP BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24')
ORDER BY 1;

-- Redo log group count and size
SELECT GROUP#, BYTES/1048576 AS mb, MEMBERS, STATUS, ARCHIVED
FROM V$LOG ORDER BY GROUP#;

-- Oracle's recommended redo size; OPTIMAL_LOGFILE_SIZE populates only when FAST_START_MTTR_TARGET is non-zero
SELECT ESTIMATED_MTTR, TARGET_MTTR, OPTIMAL_LOGFILE_SIZE
FROM V$INSTANCE_RECOVERY;

-- DBWn write latency (the DBWn process I/O wait, not the session view)
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO / NULLIF(TOTAL_WAITS,0) / 1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT
WHERE EVENT IN ('db file parallel write', 'free buffer waits');

-- Per-datafile average I/O time in ms (expect <10ms SSD, <20ms SAN)
SELECT f.FILE#, d.NAME, f.PHYRDS, f.PHYWRTS, f.AVGIOTIM
FROM V$FILESTAT f JOIN V$DATAFILE d ON f.FILE# = d.FILE#
ORDER BY f.AVGIOTIM DESC;

-- Edition and MTTR state (FAST_START_MTTR_TARGET is EE-only)
SELECT BANNER FROM V$VERSION WHERE BANNER LIKE 'Oracle%';
SHOW PARAMETER fast_start_mttr_target
SHOW PARAMETER archive_lag_target

# Grep the alert log for the canonical strings; add release-specific strings as needed
adrci exec="show alert -tail 1000" | grep -iE "checkpoint not complete|cannot allocate new log"

How to diagnose it

1. Confirm the symptom class. If the alert log only shows Checkpoint not complete (not cannot allocate new log), you are on the DBWn/checkpoint path. If both appear, fix archive space first. The archive hang is the more dangerous failure: existing sessions freeze silently and new non-SYSDBA connections get ORA-00257.

2. Compute the log switch rate from V$LOG_HISTORY. Compare to current redo log size. A widely used operational target is to switch no more often than every 15 to 30 minutes at peak; Oracle requires each online redo log file to be at least 4 MB, but the right floor is the measured redo-generation rate, not a fixed 1 GB. Anything switching more than once per minute at peak is undersized for the current write rate.

3. Separate redo-rate problems from DBWn-throughput problems. Sample redo size from V$SYSSTAT over 60 seconds to get MB/sec. Then check db file parallel write average latency. If DBWn write latency is high (>10ms on SSD, >20ms on SAN), the bottleneck is datafile I/O, not redo log size. Adding redo log groups buys time but does not fix the underlying write-path issue.

4. Check OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY. It is only populated when FAST_START_MTTR_TARGET is non-zero. If it is more than 2x your current redo log file size, redo log sizing is your primary lever.

5. If you are on Standard Edition, you cannot set FAST_START_MTTR_TARGET. Attempting it raises ORA-00439: feature not enabled: Fast-Start Fault Recovery. Check archive_lag_target instead. When archive_lag_target forces a switch at a fixed interval shorter than the natural fill rate, checkpointing becomes passive and the checkpoint position does not advance fast enough. Adding more redo groups does not help in this scenario; raise or remove archive_lag_target.

6. Walk the wait chain if you have a Diagnostics Pack license. Look at V$ACTIVE_SESSION_HISTORY or an ASH wait-chain report. The typical chain during a stall is foreground to LGWR to CKPT (controlfile enqueue) to DBWn (slow I/O). The presence of control file sequential read for LGWR is the polling signature.

7. Confirm the fix path before changing anything. If db file parallel write is the bottleneck, the highest-leverage fix is storage and DBWn configuration, not redo size. If redo logs are clearly undersized and DBWn I/O is healthy, resize the redo logs. Often both are needed: bigger logs as immediate relief, plus a DBWn I/O investigation for the root cause.

Metrics and signals to monitor

Fixes

Add or enlarge redo log groups

The most common immediate relief. Add groups first (less disruptive than resizing existing members), then add larger members and drop the old ones once the new ones have cycled through. Oracle recommends at least three groups per thread with multiplexed members. Target a switch every 15 to 30 minutes at peak redo rate; Oracle requires online redo logs to be at least 4 MB, while the production size must follow measured redo generation.

-- Add a new group with multiplexed members
ALTER DATABASE ADD LOGFILE GROUP 4 ('/redo/redo04a.log', '/redo/redo04b.log') SIZE 1G;

-- WARNING: Only drop groups whose STATUS is INACTIVE and ARCHIVED is YES.
-- Dropping the CURRENT or ACTIVE group stalls the database.
-- Verify first: SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;
-- Oracle does not delete physical files at the OS level after the drop.
ALTER DATABASE DROP LOGFILE GROUP 1;

Tradeoff: bigger redo logs lengthen crash recovery because more redo must be applied on instance startup. This is exactly the tradeoff FAST_START_MTTR_TARGET exists to manage. Increasing redo log size without bound to mask a DBWn problem will eventually push recovery time past your RTO.

Tune FAST_START_MTTR_TARGET (Enterprise Edition only)

Set a non-zero target so Oracle self-tunes checkpoint aggressiveness. After setting it, V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE populates and you can size redo logs to match. Setting too aggressive a target increases write I/O during normal operation. Setting it too lenient lengthens recovery time.

-- Takes effect immediately; checkpoint behavior changes on the next log switch
ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=BOTH;
-- Then re-check V$INSTANCE_RECOVERY for the optimal logfile size hint

On Standard Edition, attempting this raises ORA-00439. There is no MTTR knob on SE. Use redo log sizing and archive_lag_target discipline instead.

In 19c, LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT are not deprecated, but Oracle recommends disabling/removing them when FAST_START_MTTR_TARGET is set. A nonzero LOG_CHECKPOINT_INTERVAL overrides FAST_START_MTTR_TARGET. FAST_START_IO_TARGET is obsolete and should not be used.

Fix DBWn write throughput

If db file parallel write is the actual bottleneck, redo log sizing is a bandage. The root cause is datafile write I/O. Checks:

• Confirm asynchronous I/O is enabled: SHOW PARAMETER disk_asynch_io (should be TRUE) and SHOW PARAMETER filesystemio_options (should be SETALL or ASYNCH on filesystem-backed datafiles).
• Review DB_WRITER_PROCESSES. One DBWn may not be enough on busy systems with many datafiles spread across LUNs.
• Look at OS-level latency on the datafile LUN with iostat -x 1 5. High await on the datafile device while redo is on a separate, healthy device confirms the diagnosis.
• Consider separating redo logs from datafiles if they currently share a LUN. Redo and datafile I/O contention is a classic misconfiguration that produces exactly this symptom.

When async I/O is misconfigured, enabling it alone (disk_asynch_io=true, filesystemio_options=setall) can eliminate the stalls without redo log changes. Check these settings first on any system where DBWn latency is unexpectedly high.

Review archive_lag_target (especially on Standard Edition)

archive_lag_target forces a log switch at a fixed interval. When it forces switches more often than redo naturally fills the logs, Oracle’s checkpointing becomes passive. The checkpoint position does not advance fast enough, and LGWR stalls when it cycles back. This is a documented failure mode on SE where MTTR tuning is unavailable. Adding redo groups does not help; raise or remove archive_lag_target and re-baseline.

Prevention

• Track log switches per hour as a baseline metric. Set a planning alert at >6 per hour sustained and a ticket at >1 per minute. Sustained high switch frequency is the leading indicator for this failure.
• On EE, set FAST_START_MTTR_TARGET to a non-zero value. Monitor ESTIMATED_MTTR against TARGET_MTTR. A rising gap is an early warning that DBWn cannot keep up with the current checkpoint goal.
• Re-evaluate redo log sizing whenever redo generation rate grows. A 50% increase in redo size per second without a redo log resize will roughly halve the time to switch.
• Monitor db file parallel write average latency as a standing DBWn health signal, not just during incidents. It should be steady week-over-week on the same storage.
• After any storage change (LUN migration, ASM rebalance, SAN firmware update), recheck V$FILESTAT.AVGIOTIM on datafiles. Slow datafile I/O after a storage change is a common root cause for stalls that appear weeks later.
• On SE, audit archive_lag_target against the natural redo fill rate. They need to be in the same order of magnitude. A 2-minute archive_lag_target with 30-minute natural fill is a stall waiting to happen.

How Netdata helps

Per-second collection matters here because checkpoint stalls are bursty. A 20-second stall recurring every 5 minutes can fall between 30-minute AWR snapshots.

• Redo log switch rate and log file switch (checkpoint incomplete) wait counts are collected per second, surfacing stalls within seconds rather than at the next snapshot.
• db file parallel write latency correlated with checkpoint-incomplete events separates a redo-sizing problem from a DBWn I/O problem without stitching together separate tools.
• Alert log strings (Checkpoint not complete, Checkpoint lag detected, cannot allocate new log) produce distinct alerts, so the DBWn path and archiver path page different responders with different runbooks.
• Anomaly detection on log switch frequency catches gradual drift toward undersized redo logs before the first stall.
• ESTIMATED_MTTR vs TARGET_MTTR tracked over time shows whether DBWn can hit its checkpoint goal before the alert log complains.

For the full setup, see Oracle Database monitoring with Netdata.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert