Oracle redo log switch frequency: undersized logs and checkpoint pressure

Redo log switch frequency is a direct proxy for redo throughput pressure. When the database cycles through online redo log groups faster than DBWn can flush dirty buffers or ARCn can archive filled logs, every committing session starts waiting. The database does not crash, but transactions stall.

This article covers what switch frequency measures, the thresholds that separate normal operation from pressure, the cascade from undersized logs into checkpoint not complete waits, and the operational levers for fixing it. See the Oracle Database mental model if you need background on Oracle’s redo mechanism and background processes.

What redo log switch frequency measures

Each time LGWR fills one online redo log group and moves to the next, Oracle records a switch in V$LOG_HISTORY. Counting switches per hour gives a workload-normalized view of redo throughput: at the same redo generation rate, smaller logs switch more often than larger ones.

The metric is relative, not absolute. The same 20 switches per hour can mean undersized logs on one database or a genuinely high redo rate on another. Interpretation always pairs frequency with redo log size and redo generation rate.

-- Switches per hour for the last 24 hours
SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24') AS hour, 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;

-- Current redo log group sizes and status
SELECT GROUP#, BYTES/1024/1024 AS size_mb, MEMBERS, STATUS, ARCHIVED
FROM V$LOG
ORDER BY GROUP#;

Thresholds: when frequency becomes pressure

General Oracle field guidance is that log switches should occur no more frequently than every 15-30 minutes under normal OLTP load, which corresponds to 2-4 switches per hour at the upper end of healthy operation.

Frequency alone is not the diagnosis. A spike during a batch load with 4GB logs may be entirely benign. The same frequency on 200MB logs during sustained OLTP is a problem waiting to happen.

How undersized logs cascade into checkpoint pressure

Oracle cannot reuse an online redo log group until two conditions are met: the checkpoint for that group has completed (DBWn has flushed all dirty buffers protected by that redo), and in ARCHIVELOG mode ARCn has copied the group to the archive destination.

When logs are undersized for the redo rate, LGWR cycles through groups faster than DBWn and ARCn can release them. The buffer of available groups collapses, and LGWR has nowhere to write. Sessions that need to generate redo stall.

flowchart TD
    A[High redo generation rate] --> B[Online redo logs fill quickly]
    B --> C[Frequent log switches]
    C --> D{DBWn checkpoint keeping up?}
    D -- No --> E[log file switch checkpoint incomplete]
    D -- Yes --> F{ARCn archiving keeping up?}
    F -- No --> G[log file switch archiving needed]
    F -- Yes --> H[Normal operation]
    E --> I[Sessions stall on COMMIT]
    G --> I
    I --> J[TPS drops, app hangs]

The two wait events that emerge from this cascade are distinct and require different responses:

• log file switch (checkpoint incomplete) means DBWn has not finished writing dirty buffers protected by the redo in the log LGWR wants to reuse.
• log file switch (archiving needed) means ARCn has not finished copying the filled log to its destination.

Both produce the same visible symptom: every session that needs redo waits, and TPS collapses. Both can coexist. The first is a sizing and DBWn throughput problem. The second is an archive destination or archiver throughput problem. Thread N cannot allocate new log, sequence N in the alert log is the hard-outage version: LGWR has no group available and the database is hung.

Distinguishing checkpoint pressure from archive pressure

The alert log and V$SYSTEM_EVENT distinguish the two:

-- Log file switch wait events with average wait 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%';

# Alert log: checkpoint pressure and archive errors
# Path assumes default DIAGNOSTIC_DEST ($ORACLE_BASE). Adjust if overridden.
grep -iE "checkpoint not complete|cannot allocate new log|ORA-16014|ORA-16038|ORA-00257" \
  "$ORACLE_BASE/diag/rdbms/"*/*/trace/alert_*.log | tail -30

If log file switch (checkpoint incomplete) dominates, the lever is redo log size and group count, plus verifying DBWn is not bottlenecked on data file I/O. If log file switch (archiving needed) dominates, the lever is the archive destination: free space, ARCn process count, and network bandwidth to remote destinations. Adding more online redo log groups buys time in both cases but does not fix the underlying throughput limit.

Quick checks

All read-only and safe to run during an incident. Switch frequency and log config queries appear above; the following complement them.

-- Redo generation rate (sample twice, compute delta over interval)
SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'redo size';

-- Redo log space requests: non-zero means LGWR cannot write redo fast enough
-- to keep the redo log buffer available, often a redo log I/O bottleneck
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME IN ('redo log space requests', 'redo log space wait time');

-- Archive destination status and errors
SELECT DEST_ID, STATUS, DESTINATION, ERROR
FROM V$ARCHIVE_DEST_STATUS WHERE STATUS != 'INACTIVE';

Sizing redo logs correctly

The goal is to size logs so that under peak redo generation, switches happen no more than every 15-30 minutes. The sizing procedure is workload-driven:

1. Measure peak redo generation rate during a representative busy period by sampling redo size from V$SYSSTAT twice.
2. Compute redo MB per minute: (redo_size_t2 - redo_size_t1) / interval_seconds * 60 / 1024 / 1024.
3. Multiply by the target switch interval in minutes to get the target log size.
4. Round up to a sensible size and deploy to all groups.

Example: peak redo rate of 500 MB/min with a 20-minute target switch interval gives a 10GB target redo log size. Production databases should have at least three redo log groups; three to five is typical. The minimum redo log file size Oracle supports is 4MB, but production logs are typically hundreds of MB to multiple GB.

Sizing is not a one-time exercise. Redo rate changes when workloads change: batch jobs added, supplemental logging enabled for GoldenGate, force logging turned on, data loads shifted to peak hours. Re-check sizing after any significant workload change.

ARCHIVE_LAG_TARGET imposes a forced switch after a configurable interval (default 0, disabled; typical production value 1800 seconds). If you set it lower than your natural switch interval, it will dominate switch frequency and can mask undersizing:

SELECT NAME, VALUE, ISDEFAULT FROM V$PARAMETER WHERE NAME = 'archive_lag_target';

Adding and resizing redo log groups

Two operations matter: adding groups (gives DBWn and ARCn more time) and resizing groups (changes the switch frequency directly).

To add a group, non-disruptive:

-- Add a new redo log group
ALTER DATABASE ADD LOGFILE GROUP <n> ('<member_path>') SIZE <size>M;

Multiplex members across separate disks or ASM disk groups for redundancy. A group with a single member is a recovery risk: losing the only member of the CURRENT group means unrecoverable data loss unless you can ALTER DATABASE CLEAR UNARCHIVED LOGFILE, which sacrifices recoverability.

To resize a group, you cannot resize in place. You must add new groups at the desired size, switch logs until the old groups become INACTIVE, and drop the old groups:

-- Force a switch (can briefly stall sessions under heavy load)
ALTER SYSTEM SWITCH LOGFILE;
-- Repeat until target group shows STATUS = INACTIVE
SELECT GROUP#, STATUS FROM V$LOG;
-- Drop the old group (never drop a CURRENT or ACTIVE group)
ALTER DATABASE DROP LOGFILE GROUP <n>;

Oracle blocks a drop that would leave fewer than two log files for the thread (ORA-01567), so add the replacement groups before removing old ones.

Forcing switches under load can itself trigger the checkpoint pressure you are trying to fix. Do resizing during a maintenance window or low-load period, and verify each old group is INACTIVE before dropping it.

FAST_START_MTTR_TARGET controls checkpoint aggressiveness. A lower target means more aggressive DBWn writes, which helps avoid checkpoint incomplete at the cost of runtime I/O overhead. When FAST_START_MTTR_TARGET is set, Oracle recommends setting LOG_CHECKPOINT_TIMEOUT to 0 to avoid conflicting checkpoint drivers.

Signals to monitor

Prevention

• Establish a switch-frequency baseline by hour of day. Alert on deviation, not absolute thresholds alone. A workload change is usually the cause when frequency jumps.
• Re-check sizing after every workload change. New batch jobs, supplemental logging, force logging, and large data loads all move the redo rate.
• Keep at least three redo log groups, multiplexed. Single-member groups are a recovery risk. Two groups is never enough; LGWR can stall waiting on ARCn.
• Treat redo log storage as the most latency-sensitive I/O path. Do not co-locate it with data files. Slow redo storage shows up first as log file sync degradation, then as switch pressure.
• Monitor the archive destination independently of tablespace monitoring. Archive pressure converts to a hard hang faster than any other Oracle failure mode.
• Review FAST_START_MTTR_TARGET and ARCHIVE_LAG_TARGET in change control. Both interact with switch frequency and can mask or amplify sizing problems.

How Netdata helps

Netdata correlates redo log switch frequency with the rest of the write path so you can see a stall forming before the alert log fills:

• Per-second redo generation rate from V$SYSSTAT shows the workload change driving switch frequency in real time.
• Log file switch wait events (checkpoint incomplete, archiving needed) plotted alongside switch frequency make the cause visible without an incident query.
• Active session count and dominant wait class correlate switch frequency with user impact: a spike during a batch window with no rise in active sessions is different from one during peak OLTP.
• Archive destination utilization trends pair with switch frequency to show whether pressure is heading toward a hard hang.
• Anomaly detection on switch frequency and redo rate surfaces deviations from the workload baseline before absolute thresholds trip.

See Oracle Database monitoring with Netdata for the full metric set.

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