Scops
Docs Download

Oracle problems

log file switch (checkpoint incomplete): Redo Logs Too Small

3 November 2026

Every few minutes the whole database stops for a second or two, every active session on the same wait, and the alert log says Checkpoint not complete. Oracle's reference describes log file switch (checkpoint incomplete) as a session waiting for a log switch because it cannot wrap into the next log: the checkpoint for that log has not completed. The log writer has gone round all the redo log groups and come back to one whose dirty blocks the database writer has not yet written to the datafiles, and until it has, no redo can be written and no transaction can commit. The cause is redo logs that are too small for the rate, too few of them, or a database writer that cannot keep up, and the fix is one of three.

The waits, and which one it is

SELECT event, total_waits,
       ROUND(time_waited_micro/1e6)                       AS seconds,
       ROUND(time_waited_micro/NULLIF(total_waits,0)/1000) AS avg_ms
FROM   v$system_event
WHERE  event IN ('log file switch (checkpoint incomplete)',
                 'log file switch (archiving needed)',
                 'log file switch completion',
                 'log buffer space')
ORDER  BY time_waited_micro DESC;

The four look alike from the application and have different fixes. Checkpoint incomplete is the database writer behind the log writer. Archiving needed is the archiver behind: Oracle's reference says the log the writer would switch into has not been archived yet, and points to the alert log, more archive processes and faster archive disks. Log file switch completion is the switch itself, and is normal in small amounts. Log buffer space is the sessions ahead of the log writer, a small log buffer or slow redo disks, and belongs with log file sync.

The groups and their state

SELECT group#, thread#, sequence#, ROUND(bytes/1024/1024) AS mb,
       members, archived, status, first_time
FROM   v$log
ORDER  BY thread#, group#;

Oracle's reference defines the states: CURRENT is the log being written; ACTIVE is a log that is not current but is needed for crash recovery, which means its checkpoint has not completed; INACTIVE is no longer needed for instance recovery. Two or three groups ACTIVE behind the current one, most of the time, is the picture of this problem: the log writer will reach them before the database writer has finished with them. Different sizes in the MB column are a problem of their own, because the smallest group sets the pace.

How often it switches

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,
       COUNT(*)                                  AS switches,
       ROUND(SUM(blocks * block_size)/1024/1024) AS redo_mb
FROM   v$archived_log
WHERE  first_time > SYSDATE - 2
AND    dest_id = 1
GROUP  BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER  BY 1;

Switches an hour, with the redo behind them, by hour of the day; in NOARCHIVELOG mode V$LOG_HISTORY has the same times. Twenty switches an hour at the batch peak is a switch every three minutes, and a checkpoint of a 1 GB log has to finish in that time or the wait begins. The usual target is a switch every 15 to 20 minutes at the peak, which the next query turns into a size.

What Oracle says the size should be

SELECT target_mttr, estimated_mttr, optimal_logfile_size,
       writes_mttr, writes_logfile_size, log_file_size_redo_blks
FROM   v$instance_recovery;

OPTIMAL_LOGFILE_SIZE is the redo log file size in megabytes that Oracle considers optimal for the current FAST_START_MTTR_TARGET, and the reference recommends that all online redo logs be at least this value; it is filled in once the target is set. WRITES_LOGFILE_SIZE counts the database writer's writes that were driven by the smallest redo log file size, against WRITES_MTTR driven by the recovery target: when the first is most of the writing, the logs are what forces the checkpoints. LOG_FILE_SIZE_REDO_BLKS is the number of redo blocks that guarantees a log switch does not occur before the checkpoint completes.

The fix, in order

  1. Bigger logs. A redo log cannot be resized in place: add groups of the new size, switch until the old groups are INACTIVE, and drop them. Oracle's administrator's guide says to make all groups the same size, so that checkpoints come at regular intervals, and that the minimum is 4 MB. Size for a switch every 15 to 20 minutes at the peak hour, or at least OPTIMAL_LOGFILE_SIZE, whichever is larger; 2 to 4 GB is ordinary for a busy database, and 50 MB, the size many databases were created with, is the usual finding.
  2. More groups. The guide says to add groups when the log writer frequently has to wait for a group because a checkpoint has not completed or a group has not been archived. Five or six groups give the database writer two or three logs of time to finish; they are cheap, and a short burst that would have waited now does not.
  3. Let the database writer keep up. FAST_START_MTTR_TARGET makes checkpointing continuous and incremental, so dirty blocks are written steadily instead of in a rush when a log fills; Oracle's reference says it is overridden by LOG_CHECKPOINT_INTERVAL, so an old setting of that parameter has to go. Beyond that: more database writer processes, asynchronous I/O, and datafile storage that writes as fast as the redo arrives, because this wait is the datafiles' write speed, not the redo disks'.
  4. If it is archiving needed, fix the archiver. More LOG_ARCHIVE_MAX_PROCESSES, a faster archive destination, and a recovery area that is not full; ARCHIVE_LAG_TARGET, 1800 seconds being a typical value, switches on time for a standby without making the logs small.
  5. Reduce the redo, where it is honest to. Fewer and larger commits, indexes that are used, TRUNCATE where a DELETE of everything was, and direct-path loads. NOLOGGING is not on this list for a database with a standby.

See it coming

Radar Engine, the detection layer of Scops, watches these four waits live in every database, by the number of active sessions on each, so a redo configuration that was fine until this month's load is a finding at the first stall, with the switches per hour and the log sizes in it:

DetectorClockWhen it fires
Redo log switches wait for a checkpoint, Redo log switches wait for archiving, Sessions wait for free buffers, Sessions wait for writes to completeLive. log file switch (archiving needed), log file switch (checkpoint incomplete), free buffer waits, write complete waits.From 0.3 active sessions (archiving) or 1 (the others): Warning. From 1, 3 or 4 sessions, still rising: Critical.
Configuration waits surgingLive. Twice the hour before, well above its usual spread.Watch. With a second sign, such as 5 blocked sessions or commits three times slower: Warning. Above max(4, 4 × usual) active sessions, with an effect: Critical.

Scops samples every session every 5 seconds, so the stall shows in the Activity chart as a bar of Configuration waits with every session in it, and the redo log sizes and switch rate are on the database page, on any edition, with nothing installed on the server.

Questions

What does log file switch (checkpoint incomplete) mean?
The log writer needs to reuse a redo log group whose checkpoint has not completed, so the dirty blocks that log protects are still unwritten, and no redo can be written until the database writer finishes. Every session that needs redo waits, and the alert log reports Checkpoint not complete.

How big should Oracle redo logs be?
Big enough that a switch happens every 15 to 20 minutes at the busiest hour, all groups the same size, and at least OPTIMAL_LOGFILE_SIZE from V$INSTANCE_RECOVERY once FAST_START_MTTR_TARGET is set. For a busy database that is gigabytes, not the 50 MB the database may have been created with.

Does adding redo log groups fix it?
It gives the database writer more time before a group is needed again, which is enough for short bursts. A steady rate that fills small logs every few minutes needs bigger logs or faster datafile writes as well.

Is "Checkpoint not complete" in the alert log the same problem?
Yes. The alert log line is what the log writer records when it cannot allocate the next log; the wait event is the same moment seen from the sessions that are waiting for it.

Related problems

Sources