Diagnose Redo/Checkpoint Pressure, Log File Sync, Log Switch Issues, and Recovery Risk

Learning outcomes

ServiceHub commit latency spikes every few minutes. One engineer wants ten-times-larger redo logs; another schedules a checkpoint every minute. Neither has checked LGWR write latency, transaction commit rate, archive destination errors, log-switch waits, or checkpoint/recovery state. The diagnostic rule is simple: identify which mechanism is delaying foreground progress before changing configuration.

1. log file sync is foreground commit waiting

At commit, a foreground session must wait until its required redo is durable. The session posts LGWR and waits on log file sync. LGWR's physical writes appear in events such as log file parallel write. High values in both point toward redo-write I/O/LGWR scheduling; high sync with fast LGWR writes can instead point toward commit frequency, CPU scheduling, or other posting/system effects.

SELECT event,total_waits,time_waited_micro,       CASE WHEN total_waits>0 THEN ROUND(time_waited_micro/1000/total_waits,3) END AS avg_msFROM v$system_eventWHERE event IN ( 'log file sync', 'log file parallel write', 'log file switch (checkpoint incomplete)', 'log file switch (archiving needed)', 'log file switch completion')ORDER BY event;

These counters are cumulative. Take two timestamped snapshots around the incident interval and compare deltas; lifetime averages can hide short spikes.

2. Commit rate and redo rate are different workload dimensions

SELECT name,valueFROM v$sysstatWHERE name IN ( 'redo size','redo entries','redo writes','redo synch writes','redo synch time', 'redo log space requests','user commits','user rollbacks')ORDER BY name;

A workload can generate moderate redo but perform an excessive number of tiny commits. Correct transaction granularity in the application rather than weakening durability semantics to hide commit overhead.

3. checkpoint incomplete means the next log cannot yet be reused

log file switch (checkpoint incomplete) means LGWR wants to wrap into a group whose checkpoint has not completed. Possible contributors include small logs relative to redo rate, high dirty-buffer pressure, slow datafile writes, or checkpoint policy. The wait does not prove that “bigger redo logs” is automatically the right fix.

SELECT group#,sequence#,bytes,members,archived,statusFROM v$log ORDER BY group#;SELECT target_mttr,estimated_mttr,recovery_estimated_ios,       actual_redo_blks,target_redo_blks,log_file_size_redo_blksFROM v$instance_recovery;

4. archiving needed points at archive lifecycle pressure

log file switch (archiving needed) means the group needed for the next switch has not completed archiving. Check destination capacity/errors, ARCn state, FRA/storage throughput, and network/remote destination health where applicable.

SELECT dest_id,status,target,destination,errorFROM v$archive_destWHERE status <> 'INACTIVE'ORDER BY dest_id;SELECT process,status,log_sequenceFROM v$archive_processesORDER BY process;SELECT log_mode FROM v$database;

Data Guard synchronous transport can affect commit latency in a Data Guard deployment, but this single-instance Free lesson does not attribute local waits to an HA topology that is not present.

5. Measure switch cadence and redo rate by interval

SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') AS hour_bucket,       COUNT(*) AS switchesFROM v$log_historyWHERE first_time >= SYSDATE-1GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')ORDER BY hour_bucket;

SELECT SYSTIMESTAMP AS captured_at,       MAX(CASE WHEN name='redo size' THEN value END) AS redo_bytes,       MAX(CASE WHEN name='user commits' THEN value END) AS commitsFROM v$sysstatWHERE name IN ('redo size','user commits');

Subtract snapshots and divide by elapsed time to obtain redo bytes/second and commits/second. There is no universal “correct” number of switches per hour.

6. Deliberately wrong: arbitrary log resizing or forced checkpoints

Very small logs can cause frequent switches and checkpoint pressure. Larger logs reduce switch frequency but change archive file size/cadence and interact with recovery/checkpoint planning. Forced checkpoints can increase DBWR write load. Every change must be justified by measured mechanism and followed by the same before/after capture plus a recovery test.

7. Repeatable no-pack incident capture

SELECT SYSTIMESTAMP AS captured_at FROM dual;SELECT event,total_waits,time_waited_microFROM v$system_eventWHERE event LIKE 'log file%'ORDER BY event;SELECT name,valueFROM v$sysstatWHERE name IN ('redo size','redo entries','redo writes','redo synch writes',               'redo log space requests','user commits')ORDER BY name;SELECT group#,sequence#,bytes,members,archived,status FROM v$log ORDER BY group#;SELECT dest_id,status,destination,error FROM v$archive_dest WHERE status <> 'INACTIVE' ORDER BY dest_id;SELECT target_mttr,estimated_mttr,actual_redo_blks,target_redo_blksFROM v$instance_recovery;

Capture twice around a defined workload interval and preserve application request/commit throughput plus OS/storage latency alongside database evidence. AWR/ASH is unnecessary for this mandatory workflow.

8. Diagnostic decision table

9. Recovery risk belongs in redo tuning

Online redo is required for instance recovery and archived redo is central to many media-recovery designs. Check member failure domains, archive/FRA capacity, backup/archived-log retention, and recovery time after changes. Foreground latency is only one side of the decision.

10. Production judgment and bridge

Diagnose redo from interval deltas. Keep transaction boundaries correct, place redo on reliable low-latency storage, multiplex across real failure domains, monitor archive destinations, and size logs/checkpoints from measured redo rate plus recovery objectives. Re-test both latency and recovery after changes.

The current baseline remains Oracle AI Database 26ai RU 23.26.3. No pack or COMPATIBLE change is required. This chapter now provides the durability foundation for deeper RMAN, Flashback, Data Guard, and operational recovery work.

Authoritative references

• Descriptions of Wait Events — log file sync/parallel write/switch semantics
• Statistics Descriptions — redo statistics
• V$LOG_HISTORY — switch history
• V$INSTANCE_RECOVERY — checkpoint/recovery evidence
• Managing Archived Redo Logs — archive diagnostics