Re: Redo Log Files Too Big? Hidden gotchas?
Date: Mon, 9 Dec 2013 11:48:36 -0600
Message-ID: <CAHJZqBA=HxGPgtpdWcWzA7n+RpoH3_9N3mzsAyVqwmFGgmUWVA_at_mail.gmail.com>
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 < christopherdtaylor1994_at_gmail.com> 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 http://www.seiler.us -- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 09 2013 - 18:48:36 CET