RE: Redo Log Files Too Big? Hidden gotchas?
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>, "christopherdtaylor1994@xxxxxxxxx" <christopherdtaylor1994@xxxxxxxxx>
- Date: Mon, 9 Dec 2013 18:00:07 +0000
Are you using Direct NFS or Kernel NFS? One of our mission critical systems is
on NAS. The redo log size is 1GB and there are times during a day when it
switches 10-12 times an hour. But, we have not experienced the issue that you
have described. Also, since you seem to run ETL on nightly basis, can you turn
off logging on those tables that do not require point-in-time recovery and
could be loaded easily in case you need recovery?
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Andrew Kerber
Sent: Monday, December 09, 2013 12:42 PM
To: christopherdtaylor1994@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Redo Log Files Too Big? Hidden gotchas?
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<mailto: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:
