Re: Redo Log Files Too Big? Hidden gotchas?

From: Don Seiler <>
Date: Mon, 9 Dec 2013 11:48:36 -0600
Message-ID: <>

Our redo logs are 20 Gb. We have a LOT of change during the day. The only downside that I've encountered is the time to ship an archived log when I need to. But we haven't had any regular problems with it, we have dataguard with real-time transport/apply working across our datacenters in the midwest and southeast.

On Mon, Dec 9, 2013 at 11:36 AM, Chris Taylor <> wrote:

> My client has a production database that is hitting 150-160 redo log
> switches PER HOUR during the hours of 1-3 am. This is database that is
> loaded thru an nightly ETL process where tables are truncated and reloaded
> from the source. Average redolog switches during nightly processing is 50+
> but during the day we see reasonable switches of 0,2,4 or 6 per hour.
> Performance is a concern and as expected we are seeing logfile switch
> completion in the top 5 wait events during those periods.
> Current RedoLog sizes are 300MB. If I take Oracle's recommended 4 per
> hour then we are at approximately 38.75x above the recommended value. I'd
> like to get the redo log switches down to 4-6 per hour but that means
> resizing my redologs to about 10GB per log member.
> I'm going to set archive_lag_target to a 15 minute interval, but I'm
> concerned that 10GB might be "too big". I can't think of a technical
> reason why but I've got a nagging feeling that I might be overlooking
> something.
> I was thinking there might be a negative impact to backups, but logically
> the amount of archive log data being backed up for the same period should
> be similar (whether its many ~300 MB archived logs or few ~10GB archived
> logs).
> The filesystems in question reside on a NetApp appliance and the
> filesystems for database files and the backup location are NFS mounted.
> Is there any obvious thing I'm missing here? I'm going to reduce the
> count of redo log files while increasing the size (that's the plan anyway).
> I plan to have a couple of additional groups to take into account any slow
> archiving so that I should have a "spare" redolog group in case Oracle
> tries to wrap around to a group that is being archived.
> Thoughts?
> Chris Taylor

Don Seiler

Received on Mon Dec 09 2013 - 18:48:36 CET

Original text of this message