Re: Redo Log Files Too Big? Hidden gotchas?
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: christopherdtaylor1994@xxxxxxxxx
- Date: Mon, 9 Dec 2013 11:42:16 -0600
The only place I can think of redo log size being an issue is if you have a
standby. If you are transferring 10g files across the network to a
standby, I could see the network getting overloaded. Cant think of
anyplace else where that may be a problem. just remember to create
sufficient redo log groups that they can be copied off to archive logs
before the groups wrap around.
On Mon, Dec 9, 2013 at 11:36 AM, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> 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
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
