OracleFree calculatorPDF, Excel, Word exportOracle Database

Oracle Database 19c Online Redo Log Size Calculator

This calculator sizes Oracle Database 19c online redo logs from your peak redo rate and the number of minutes you want between log switches. It also shows the online redo disk footprint and how much archived redo a day the database produces.

Your numbers

Peak redo generation per thread

MB/hour

AWR 'redo size' per second at peak x 3600 / 1048576, or the busiest hour of archived log volume from V$ARCHIVED_LOG.

Average redo generation per thread

MB/hour

Target minutes between log switches at peak

20 min

15 to 30 minutes is a widely used DBA rule of thumb.

Round size up to

Groups per thread

Members per group

Redo threads (RAC instances)

Candidate log size to test

Used for the what-if chart and the candidate output.

Your results

Recommended redo log size (MB)

2,048

Archived redo per day at average load (GB)

58.5938

Unrounded size for target (MB)

2,000

Switches per hour at peak

2.9297

Switches per hour at average load

1.2207

Total online redo disk (GB)

Switches per hour at peak with candidate size

2.9297

The 15 to 30 minute target is a common DBA rule of thumb, not an Oracle formula. Cross-check with V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE when FAST_START_MTTR_TARGET is set, and with Data Guard or backup requirements.

Save results:

Peak switches per hour vs candidate log size (MB)

Sweeps Candidate log size to test from 256 to 8192, holding your other inputs fixed.

Send us your redo history

Share a week of V$LOG_HISTORY switch counts and an AWR report from batch peak and we will return a reviewed redo and archive sizing.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Get the peak redo rate from an AWR report of your busiest hour (Load Profile, Redo size per second, multiplied by 3600 and divided by 1,048,576 for MB) or by summing BLOCKS x BLOCK_SIZE in V$ARCHIVED_LOG per hour. ERP peaks usually come from batch: MRP, invoice posting, period close or purges. Enter it with an average rate, the target minutes between switches and your group, member and thread counts. The recommended size is the peak MB per minute times the target minutes, rounded up to your chosen step. Use the candidate input and chart to see how a size you are considering behaves at peak.

Size = ceil((peak MB/h / 60 x target min) / step) x step.
Switches per hour = redo MB/h / size.
Online redo disk = size x groups x members x threads.
Archive per day = average MB/h x 24 x threads.

What Oracle actually recommends

Oracle does not publish a fixed switch interval. Its guidance is to size logs so switches are not too frequent, because every switch triggers a checkpoint and, in archivelog mode, an archive. When FAST_START_MTTR_TARGET is set, V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE reports the size in MB that Oracle considers optimal for that recovery target, and Oracle recommends all online logs be at least that size. The 15 to 30 minute target is a widely used DBA rule of thumb. If you need a maximum data loss window on a standby, set ARCHIVE_LAG_TARGET to force switches rather than shrinking the logs.

How to check and change it in the system

Count switches per hour with: SELECT TRUNC(first_time, 'HH24') hr, thread#, COUNT(*) switches FROM v$log_history WHERE first_time > SYSDATE - 7 GROUP BY TRUNC(first_time, 'HH24'), thread# ORDER BY 1. Check current sizes with SELECT group#, thread#, bytes/1024/1024 mb, members, status FROM v$log, and file locations from V$LOGFILE. You cannot resize a log group: add new groups at the new size (with Oracle Managed Files: ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 SIZE 2G; otherwise list the member file names), switch with ALTER SYSTEM SWITCH LOGFILE until the old groups are INACTIVE, force a checkpoint with ALTER SYSTEM CHECKPOINT, then ALTER DATABASE DROP LOGFILE GROUP n for each old group and remove the files if they are not Oracle managed.

Symptoms and common mistakes

'Checkpoint not complete' in the alert log means the database wanted to reuse a log whose checkpoint was not finished: logs are too small or too few. 'Cannot allocate new log, archival required' means the archiver cannot keep up: check archive destination throughput and space. A common mistake is sizing from the daily average, which hides the batch peak. Another is adding members without adding groups; members give redundancy, groups give time. Standby redo logs on Data Guard should be the same size as the online logs, with one more group per thread.

Frequently Asked Questions

How many log switches per hour is normal in Oracle?

Many DBAs aim for roughly 2 to 4 switches per hour at peak, which is one every 15 to 30 minutes. That is a rule of thumb, not an Oracle limit. Frequent switches, such as several per minute during batch, usually cause 'checkpoint not complete' waits and extra archiver load.

What does OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY mean?

It is the redo log size in MB that Oracle considers optimal for the current FAST_START_MTTR_TARGET. It is only meaningful when that parameter is set. Oracle recommends configuring all online redo logs to be at least that size. It is a useful cross-check against the switch interval method.

Can I resize an existing redo log group?

No. Add new groups at the new size, switch logs until the old groups are INACTIVE, checkpoint, then drop the old groups. A group that is CURRENT or ACTIVE cannot be dropped. In RAC do this for every thread, and keep the same size across threads.

How much archive log space do I need per day?

Roughly your average redo per hour times 24, per thread, because archived logs contain the redo generated. Add peak days such as period close, and multiply by the number of days archives stay on disk before RMAN backs them up and deletes them.

If you want your DBA team to ask the alert log, AWR and V$ views questions in plain English on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools