The nightly batch used to finish in twenty minutes. Now it takes an hour, and during that hour the application stutters: every few seconds, every session that writes anything (an order, a login timestamp, a session audit row) freezes for a second or two, then carries on. The SQL hasn’t changed. The plans haven’t changed. CPU is fine, the storage team says the disks are fine, and the alert log has the same two lines over and over:

Thread 1 cannot allocate new log, sequence 23
Checkpoint not complete

Nothing is broken, and no query is slow. The database is waiting for itself: the online redo logs are too small for the rate at which the workload changes data, so the log writer keeps running out of logs it is allowed to reuse. This post covers what that message means, why it freezes every writer at once, how to spot it before users do, and how to fix it online. It ends with a lab where the same update takes 14.1 seconds on two 20 MB redo logs, 11.9 of them waiting, and 1.9 seconds after the fix.

How the redo log circle works

Every change in an Oracle database is first written as redo to the online redo logs, a small, fixed set of files (groups) that the log writer (LGWR) fills one after another in a circle. When the current group is full, LGWR switches to the next one, and when it reaches the last group it wraps around and reuses the first.

Reusing a group is only safe once nothing still needs the redo in it for crash recovery. The redo protects changes to data blocks that may still exist only in memory. So before a group can be overwritten, the checkpoint that covers it must finish: the database writer (DBWR) has to write every dirty block whose changes are recorded in that group to the datafiles. Until that happens, V$LOG shows the group as ACTIVE, meaning still needed for recovery, and LGWR cannot touch it.

Normally the circle is big enough that DBWR finishes long before LGWR comes back around, and the next group is always INACTIVE and ready.

Why it freezes everyone

When the logs are small relative to the write rate, LGWR fills the whole circle faster than DBWR can complete the checkpoints. It comes back to a group that is still ACTIVE, and it has nowhere to write. The alert log records it as “cannot allocate new log … Checkpoint not complete”.

LGWR is a single point of serialisation. Until DBWR frees that group, no session in the database can generate redo: no insert, no update, no delete, no commit. They all wait on the same event, log file switch (checkpoint incomplete), and then all resume at once. That’s the stutter. It’s not one slow query; it’s the whole instance pausing every time the log writer laps the checkpoint.

⤢ Enlarge

This is a close cousin of ORA-00257, archiver stuck. There, a log group can’t be reused because the archiver hasn’t copied it yet, and the wait is log file switch (archiving needed). Here it’s because the checkpoint hasn’t finished. Same circle, same freeze, different fix.

Prove it

The lab uses the community Oracle Database Free container image, which trims the redo logs to two groups of 20 MB each to keep the image small: exactly the layout that causes this. It loads a table of 100,000 rows of about 1 KB, then runs one statement that rewrites every row and commits, a little over 200 MB of redo. It measures the elapsed time, the waits from V$SYSTEM_EVENT, the switches from V$LOG_HISTORY, and the Checkpoint not complete lines in the alert log:

redo logs              elapsed   switches   waits (time waited)   alert lines
2 x 20 MB (image)       14.1 s         12        12 (11.9 s)              12
3 x 512 MB               1.9 s          0         0                        0

On the default logs, 84% of the update’s elapsed time was waiting for a redo log to become reusable: 11.9 of 14.1 seconds. The statement itself was never the problem. After the logs were resized, the identical update ran in 1.9 seconds with no switches and no waits.

Elapsed times depend on the hardware and vary from run to run; on my machine the small-log update took between 8 and 14 seconds. The waits and the alert-log lines appear every time, and disappear every time after the resize.

Don’t take my word for it — run it. The checkpoint-incomplete lab runs the burst on the default 2 x 20 MB logs and asserts that log file switch (checkpoint incomplete) waits and Checkpoint not complete alert-log lines appear. It then resizes the logs online and asserts the same burst produces zero of both. If the stall doesn’t reproduce, or the fix doesn’t remove it, the run fails. Proven on every CI push.

How to spot it

The alert log is the giveaway. Search it for Checkpoint not complete; one occurrence during a once-a-year data load is noise, but dozens a day mean the logs are undersized for your real workload.

The wait event. log file switch (checkpoint incomplete) shows up in V$SYSTEM_EVENT, in AWR’s Top Events and in ASH. In an AWR report it’s often near the top during batch windows, and it’s easy to misread as an I/O problem, because the time is spent waiting on DBWR’s writes.

How often the logs switch. V$LOG_HISTORY has one row per switch:

SELECT TO_CHAR(TRUNC(first_time, 'HH24'), 'YYYY-MM-DD HH24') AS hour,
       COUNT(*)                                             AS switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 7
GROUP  BY TRUNC(first_time, 'HH24')
ORDER  BY 1;

A common rule of thumb is a few switches per hour at peak, roughly one every 15 to 20 minutes. The lab’s update switched 12 times in 14 seconds. If your busiest hours show switches per minute, the logs are far too small for that workload, whether or not you’ve seen the alert yet.

Oracle’s own sizing advice. V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE suggests a redo log size in megabytes, but only when FAST_START_MTTR_TARGET is set. With it at 0, as on the lab database, the column is empty. In the lab, with a 60-second target, it suggested 827 MB. Treat it as a starting point, not a law: it’s derived from the recovery target, not from your peak write rate.

The fix

You can’t resize a redo log file in place. Instead, you add new groups at the size you want, move onto them, and drop the old ones, all online:

-- 1. Add bigger groups (use your own paths, and two members each on separate disks in production)
ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/oradata/PROD/redo03.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oradata/PROD/redo04.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/oradata/PROD/redo05.log') SIZE 2G;

-- 2. Switch until one of the new groups is CURRENT, then checkpoint
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- 3. Wait until the old groups show INACTIVE in V$LOG, then drop them
SELECT group#, bytes / 1024 / 1024 AS mb, status FROM v$log;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;

• Order matters. A CURRENT group can’t be dropped, and neither can an ACTIVE one, because it’s still needed for recovery. Switch off it, checkpoint, and wait for INACTIVE.
• Dropping a group doesn’t delete its file. Remove the old files at the operating-system level afterwards (with Oracle Managed Files, the database does it for you).
• Size for the peak, not the average. The goal is that, at your busiest, a log lasts long enough for DBWR to finish the checkpoint long before LGWR needs it again. In the lab, 512 MB groups hold the entire burst, so there is no switch at all. In production, aim for that rule of thumb of a few switches an hour at peak.
• More groups help too. A third or fourth group gives DBWR more time before LGWR comes back around. Two groups is the bare minimum Oracle allows, and it leaves no slack.
• All groups the same size. Mixed sizes make switch timing unpredictable and complicate standby redo logs.

The other lever is DBWR itself: if it can’t keep up because the storage is slow or asynchronous I/O is off, checkpoints take longer at any log size. (The lab container runs with FILESYSTEMIO_OPTIONS=none, which doesn’t help DBWR either.) But undersized logs are far more common, and resizing them is a five-minute, fully online change.

What teams get wrong

• Treating it as a storage problem. The waits look like I/O, so the first instinct is faster disks. Faster writes help DBWR a little; logs sized for the workload remove the wait entirely.
• Never changing the default. Many databases still run on the redo logs they were created with, sized for the workload of the day they were built, or trimmed for a small footprint the way the lab’s container image trims them to two 20 MB groups. Fine for a laptop, not for a batch window. Check yours.
• Forgetting the standby. With Data Guard, standby redo logs should be the same size as the online redo logs, with one more group per thread. Resize the online logs and leave the standby redo logs small, and the standby can’t use them for real-time apply; it falls back to applying from archived logs. Resize both.
• Confusing it with the archiver. log file switch (archiving needed) is ORA-00257’s wait: the archive destination is full or slow. Bigger logs don’t fix that one; freeing the destination does.
• Fearing longer recovery, or ignoring it. With FAST_START_MTTR_TARGET at 0, how far the checkpoint lags is tied to the size of the smallest log, so bigger logs can mean more redo to replay after a crash. When you enlarge the logs, set FAST_START_MTTR_TARGET (say, 60 to 300 seconds) and the database paces its checkpoint writes to keep recovery time bounded. Undersized logs cost you every busy day; properly sized ones cost a little disk.
• Dropping the wrong group. Run SELECT group#, status FROM v$log before every DROP LOGFILE. A CURRENT or ACTIVE group can’t be dropped, and in a hurry it’s easy to type the wrong number.

Frequently asked questions

What does 'Checkpoint not complete' mean in the Oracle alert log?

It means the log writer (LGWR) filled the current online redo log and wanted to switch to the next group in the circle, but that group was still ACTIVE: the checkpoint covering it had not finished, so the database writer (DBWR) had not yet written all the dirty blocks whose changes are recorded in that log. The log cannot be overwritten until that happens, so LGWR waits, and while it waits no session can generate redo. The usual cause is online redo logs that are too small for the database's write rate, so LGWR laps the checkpoint during busy periods.

What is the 'log file switch (checkpoint incomplete)' wait event?

It is the wait that sessions experience when they need to write redo but the log writer cannot switch to the next online redo log, because that log is still needed for crash recovery until a checkpoint completes. Every session that inserts, updates, deletes or commits waits on it at the same time, which is why it shows up as the whole database stalling rather than one slow query. In the companion lab, a 100,000-row update spent 11.9 of its 14.1 seconds on this wait with two 20 MB redo logs, and zero seconds after resizing them.

How do I resize Oracle online redo logs?

Online redo log files cannot be resized in place. Add new log groups at the desired size with ALTER DATABASE ADD LOGFILE GROUP n ... SIZE, run ALTER SYSTEM SWITCH LOGFILE until one of the new groups is CURRENT, run ALTER SYSTEM CHECKPOINT, wait until V$LOG shows the old groups as INACTIVE, then drop them with ALTER DATABASE DROP LOGFILE GROUP n and delete their files at the operating-system level (Oracle Managed Files are removed automatically). The whole procedure is online. Keep all groups the same size, and in a Data Guard configuration resize the standby redo logs to match.

How big should Oracle redo logs be?

Big enough that, at peak load, a log lasts long enough for the checkpoint covering the previous logs to finish well before the log writer needs them again. A common rule of thumb is a few log switches per hour at the busiest time, roughly one every 15 to 20 minutes. Check V$LOG_HISTORY for switches per hour; switches per minute mean the logs are far too small. V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE also suggests a size in megabytes, but only when FAST_START_MTTR_TARGET is set. Use at least three groups, all the same size.

Is 'Checkpoint not complete' the same as ORA-00257 archiver error?

No, although both freeze every session that writes. With 'Checkpoint not complete', the next online redo log cannot be reused because its checkpoint has not finished, and sessions wait on log file switch (checkpoint incomplete); the fix is usually larger or more redo log groups. With ORA-00257, the database is in ARCHIVELOG mode and the next log cannot be reused because the archiver has not been able to copy it, typically because the archive destination or Fast Recovery Area is full; sessions wait on log file switch (archiving needed), and the fix is to free or enlarge the archive destination.

Do larger redo logs make crash recovery slower?

Not in a way that should stop you from sizing them properly. Crash recovery time depends on how much redo must be applied since the last checkpoint position, and modern Oracle uses incremental checkpointing to write dirty blocks continuously rather than only at log switches. With FAST_START_MTTR_TARGET at 0, how far that checkpoint may lag is tied to the size of the smallest redo log, so larger logs can mean more redo to replay. Setting FAST_START_MTTR_TARGET makes the database pace its checkpoint writes to keep the estimated recovery time within the target, so set it when you enlarge the logs. Undersized logs, on the other hand, stall every writer on every busy day.

Redo sizing is one of those settings that is decided once, at database creation, and then forgotten until the workload outgrows it. The symptoms point everywhere else: the wait events look like I/O, the batch looks slow, and the SQL looks guilty. Check the alert log for Checkpoint not complete, count your log switches per hour, and if the logs are lapping the checkpoint, give them room: add bigger groups, switch, checkpoint, drop the old ones, and resize the standby redo logs to match. Then prove it the way that ends the argument — with the checkpoint-incomplete lab, where the same update drops from 14.1 seconds, 11.9 of them waiting, to 1.9.