Oracle Redo Logs: Sizing Without Guesswork

Redo log problems rarely start with a clean error message. They usually show up as slow commits, archive destinations filling faster than expected, Data Guard apply lag, or batch jobs that suddenly take longer during month-end. By the time someone checks the alert log, the database may already be switching logs too frequently, checkpointing aggressively, or waiting for archiving to catch up.

A common reaction is to increase redo log size and move on. Sometimes that is required, but it is not always the real fix. Redo log sizing controls how frequently Oracle switches logs. It does not reduce the amount of redo generated by bad SQL, unnecessary indexes, row-by-row commits, or poorly designed batch jobs.

This post looks at Oracle redo log sizing from a production DBA angle. We will check log switch frequency, estimate redo generation rate, review MTTR guidance, discuss common failure scenarios, and compare the idea briefly with PostgreSQL WAL pressure.

Why Redo Log Sizing Matters in Production

Every committed change in Oracle must be protected by redo. LGWR writes redo records to the online redo logs, and in ARCHIVELOG mode, those logs are archived after switching. This is normal database behavior, but it becomes painful when the redo infrastructure is not sized for the workload.

When redo logs are too small, Oracle switches logs more often. Frequent log switches can increase checkpoint activity, put pressure on archiver processes, and expose slow storage or archive destinations. On a Data Guard setup, the same problem can also show up as transport or apply lag.

When redo logs are too large without thought, crash recovery behavior may not match business expectations. So the target is not simply “make redo logs huge”. The target is to size them based on workload, recovery requirement, and operational behavior.

The First Check: How Often Are Logs Switching?

Before touching redo log size, check the current switch pattern. I normally start with hourly switch frequency and then compare it against known business activity. A switch every 10 to 15 minutes during normal OLTP workload is usually comfortable. A switch every minute during regular workload needs investigation. During a heavy batch window, faster switching may be acceptable only if checkpointing, archiving, and standby apply are keeping up.

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

SELECT thread#,
TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS switch_hour,
COUNT(*) AS switch_count FROM v$log_history WHERE first_time >= SYSDATE - 7 GROUP BY thread#, TO_CHAR(first_time, 'YYYY-MM-DD HH24') ORDER BY switch_hour, thread#;

SELECT group#,
type,
member,
status FROM v$logfile ORDER BY group#, member;

In RAC, check each thread separately. If thread 1 switches every few minutes but thread 2 is quiet, the issue may be workload imbalance rather than redo sizing alone. Services, application connection pools, or batch jobs may be pinned heavily to one instance.

Size Redo Logs Based on Peak Redo Rate

Average redo generation is useful for capacity planning, but it is not enough for sizing redo logs. Production incidents usually happen during peak load: month-end processing, large data correction jobs, ETL runs, release deployments, or index maintenance.

The below query uses archived logs to estimate redo generation rate. Run it for the busiest window, not during a quiet period.

SELECT thread#,
       sequence#,
       first_time,
       next_time,
       ROUND(blocks * block_size / 1024 / 1024, 2) AS size_mb,
       ROUND((next_time - first_time) * 86400) AS seconds_taken,
       ROUND(
         (blocks * block_size / 1024 / 1024) /
         NULLIF((next_time - first_time) * 86400, 0),
         2
       ) AS mb_per_sec
FROM v$archived_log
WHERE first_time >= TO_DATE('2026-08-08 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND   first_time <  TO_DATE('2026-08-09 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND   dest_id = 1
AND   blocks > 0
ORDER BY first_time, thread#;

A simple sizing formula is:

Redo log size = peak redo MB/sec x desired switch interval in seconds

For example, if peak redo generation is 40 MB/sec and you want roughly 10-minute switches, the estimated log size is:

40 x 600 = 24000 MB

That does not automatically mean you should create 24 GB logs everywhere. It means the workload is heavy enough that you must validate redo size, archive throughput, Data Guard capacity, and recovery expectations together.

Use MTTR Advice, But Keep DBA Judgement

Oracle provides recovery-related advice through V$INSTANCE_RECOVERY. The column OPTIMAL_LOGFILE_SIZE can help you understand whether the current redo log size fits the configured recovery target.

This is useful, but it should not be treated as the only answer. MTTR is mainly about crash recovery time. Runtime behavior also depends on workload, checkpoint activity, I/O, archive speed, and standby apply capacity.

SHOW PARAMETER fast_start_mttr_target
SHOW PARAMETER statistics_level

SELECT target_mttr,
estimated_mttr,
ckpt_block_writes,
optimal_logfile_size FROM v$instance_recovery;

ALTER SYSTEM SET statistics_level = TYPICAL SCOPE=BOTH;

ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=BOTH;

If OPTIMAL_LOGFILE_SIZE is consistently higher than your current redo log size, that is a strong signal to review redo sizing. Still, test the change during a controlled window and confirm that recovery objectives are acceptable.

Redo Pressure Often Comes from Application Design

DBAs often get asked to fix redo growth from the database side, but many redo problems are created by application behavior. Oracle is doing what it is supposed to do: protecting changes. The question is whether the application is asking Oracle to protect too many unnecessary changes.

Too many indexes on write-heavy tables

Every insert, update, or delete may also modify indexes. A table with many indexes can generate much more redo than expected. Before dropping anything, review index usage carefully and consider invisible indexes for controlled testing.

Delete and insert instead of merge

Some batch jobs delete old rows and insert fresh rows because it is easy to code. From a redo and undo perspective, that can be expensive. If most rows already exist, a controlled MERGE or targeted update may generate less pressure.

Updating unchanged columns

ORM-generated SQL sometimes updates every column even when only one value changed. This can increase redo, undo, row locking, buffer activity, and standby apply workload. For critical batch jobs, review the generated SQL instead of assuming the database parameter is wrong.

SELECT owner,
       table_name,
       COUNT(*) AS index_count
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner, table_name
HAVING COUNT(*) > 10
ORDER BY index_count DESC;

ALTER INDEX app_owner.idx_orders_old INVISIBLE;

MERGE INTO customer_stage t USING customer_stage_new s ON (t.customer_id = s.customer_id) WHEN MATCHED THEN UPDATE SET t.status = s.status,
t.updated_at = SYSTIMESTAMP WHERE NVL(t.status, 'X') <> NVL(s.status, 'X') WHEN NOT MATCHED THEN INSERT (customer_id, status, updated_at) VALUES (s.customer_id, s.status, SYSTIMESTAMP);

Commit Frequency Is Not Just a Coding Style

Row-by-row commits are still common in old batch scripts. They look safe because each row is committed quickly, but they can create unnecessary LGWR activity and poor throughput. On the other hand, one huge commit may create restartability and rollback problems. The right commit size depends on business logic, restart design, and workload volume.

For bulk processing, commit in controlled batches where the business process allows it. Do not change commit logic only for performance without confirming functional requirements.

DECLARE
  l_count NUMBER := 0;
BEGIN
  FOR r IN (SELECT id, status FROM source_table) LOOP

```
UPDATE target_table
   SET status = r.status
 WHERE id = r.id;

l_count := l_count + 1;

IF l_count >= 5000 THEN
  COMMIT;
  l_count := 0;
END IF;
```

END LOOP;

COMMIT;
END;
/

Resizing Redo Logs Safely

Oracle does not simply resize an existing redo log group. The usual production approach is to add new larger groups, switch logs until old groups become inactive, and then drop the old groups. In RAC, do this per thread.

Before dropping any group, confirm it is not current and not required for recovery. Also remember Data Guard: standby redo logs should normally be sized consistently with online redo logs.

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

ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 4G; ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 4G; ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 4G;

ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM CHECKPOINT;

DROP -- Do not run until the group is inactive and safe ALTER DATABASE DROP LOGFILE GROUP 3;

SELECT group#,
thread#,
ROUND(bytes/1024/1024) AS size_mb,
status FROM v$standby_log ORDER BY thread#, group#;

Common Failure Scenarios

• Frequent log switches during normal workload: redo logs may be too small, or redo generation may have increased due to application changes.
• Archive destination fills quickly: check redo generation rate, archive backup frequency, FRA size, and retention policy.
• Data Guard lag during batch jobs: check primary redo rate, standby redo log size, network throughput, and MRP apply speed.
• High log file sync: investigate commit frequency, LGWR latency, and storage performance.
• High log file switch completion: check checkpoint pressure, archiving speed, and redo log size.

SELECT event,
       total_waits,
       ROUND(time_waited_micro/1000000, 2) AS time_waited_sec
FROM v$system_event
WHERE event IN (
       'log file sync',
       'log file parallel write',
       'log file switch completion',
       'log file switch (checkpoint incomplete)',
       'log file switch (archiving needed)'
)
ORDER BY time_waited_sec DESC;

SELECT dest_id,
status,
destination,
error FROM v$archive_dest WHERE status <> 'INACTIVE' ORDER BY dest_id;

SELECT name,
value,
unit,
time_computed FROM v$dataguard_stats WHERE name IN ('transport lag', 'apply lag', 'apply finish time');

Oracle Redo and PostgreSQL WAL: Same Pain, Different Knobs

Oracle redo and PostgreSQL WAL both protect database changes for recovery, but DBAs manage them differently. Oracle DBAs think in terms of redo log groups, members, threads, archiving, and standby redo logs. PostgreSQL DBAs deal with WAL segments, checkpoints, archiving, replication slots, and pg_wal growth.

In Oracle, redo log size directly affects switch frequency. In PostgreSQL, WAL segment size is normally fixed at cluster initialization, so day-to-day tuning is usually around checkpoint settings, archiving, replication slots, and workload behavior.

-- PostgreSQL WAL checks

SHOW wal_segment_size;
SHOW checkpoint_timeout;
SHOW max_wal_size;
SHOW min_wal_size;
SHOW archive_mode;
SHOW archive_command;

SELECT wal_records,
wal_fpi,
pg_size_pretty(wal_bytes) AS wal_generated FROM pg_stat_wal;

SELECT slot_name,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;

DBA Lessons from Production

• If redo logs are resized on primary, review standby redo logs too.
• Frequent log switches are not always a storage issue. Check SQL design and commit behaviour.
• Unused indexes on write-heavy tables can silently increase redo volume.
• Archive log backup performance matters. Bigger redo logs create bigger archived logs.
• For Data Guard, monitor both transport lag and apply lag. They are not the same problem.

Mini Case Study: Data Guard Lag After a Batch Load

A production database started showing 40 to 50 minutes of Data Guard apply lag during a monthly batch. The primary database was still running, so the first assumption was network slowness between primary and standby.

The actual issue was a mix of small redo logs and a batch job that deleted and reinserted large data sets. The target table also had several indexes that were no longer useful for current reporting. During the batch, one RAC thread was switching logs almost every minute.

The fix was not one single change. Redo logs and standby redo logs were resized after checking peak redo rate. The batch logic was changed from delete/insert to a controlled merge for most rows. Two unused indexes were tested as invisible before being dropped. After that, standby lag still appeared during the peak, but it recovered in minutes instead of staying behind for almost an hour.

FAQs

How do I know if redo logs are too small?

Check log switch frequency using V$LOG_HISTORY. If switches are happening every minute during normal workload, review redo size, checkpoint waits, archive speed, and recent workload changes.

Does increasing redo log size reduce redo generation?

No. It reduces log switch frequency. Redo generation depends on DML volume, indexes, commit pattern, SQL design, supplemental logging, and application behavior.

Should all redo log groups be the same size?

Yes, keeping redo log groups the same size per thread makes behavior predictable and troubleshooting easier.

What should I check when Data Guard lag increases?

Check redo generation rate, transport lag, apply lag, archive gaps, standby redo log size, MRP status, network throughput, and standby I/O performance.

Is PostgreSQL WAL tuning the same as Oracle redo tuning?

The recovery concept is similar, but the controls are different. Oracle DBAs manage redo log groups and switch frequency. PostgreSQL DBAs usually manage WAL pressure through checkpoints, archiving, replication slots, and workload design.

Conclusion

Redo log sizing is not a cosmetic database setting. It affects commit performance, checkpoint behaviour, archiving, recovery, and Data Guard stability. If redo logs are too small, the database may spend too much time switching and checkpointing. If they are sized without considering recovery objectives, the design may not match operational expectations.

The best approach is simple: measure first, change later. Start with log switch frequency, then calculate redo generation rate during peak workload. Review V$INSTANCE_RECOVERY, wait events, archive destinations, and Data Guard lag. After that, look at the application side. Index count, batch logic, commit frequency, and unnecessary updates often explain more than redo log size alone.

For Oracle DBAs, redo tuning is a mix of database configuration and workload discipline. For PostgreSQL DBAs, WAL pressure tells a similar story with different knobs. In both cases, the write path exposes the truth. Review it before the next batch window, migration, or month-end run exposes it for you.

Sources : Oracle Docs  / AskTom