REDO Log Reasonable Max Size?

Our current DB is Oracle 10.2.0.4 running on AIX. We are upgrading to an IBM System x3850X5 with 32 CPU cores and 512GB of RAM. The OS is RHEL. Our current REDO (on 10g) at 6gb per log is switching every 4 minutes when loading batch files, which is off and on all day. If I extrapolate, then REDO might need to be around 30gb to only switch every 20 minutes. I am familiar with the parameter FAST_START_MTTR_TARGET and have tested a couple values on our Test instance. My question is: What is a reasonable max size of REDO Logs. I realize larger logs will mean longer crash recovery, losing more data if a file gets corrupted, etc. I should also state our DB is on SAN RAID and REDO Logs are multiplexed on different mount points. Are there issues with larger redo logs that I am not realizing? Thanks.

Some things to think about:
- maybe there are a lot of indexes on the tables getting the rows from the
ETL that could be re-created after the ETL load
- remember, even a SELECT creates some redo (see ask tom for that)
- maybe after a 20,000 ft review you may find there are processes that can
be rescheduled

I have reviewed v$log_history. As I stated, logs are switching every 4 minutes. My question relates to hazards of having very large log files in the 30gb range as a means of following Oracle guidelines of switching approximately every 20 minutes. Also, I cannot control batch loads as we get external files and load them as they arrive.

Those recommendation made for an "average size" database more than 10 years back and they proven to be quite inaccurate today.
Switching in 1 to 5 minutes is totally workable numbers for the peek load during the batch processing.
Instead of bluntly following recommendations you have to compromise between the log file size and switch time. Your current 6gb per log which switching every 4 minutes could work much better than 30gb to only switch every 20 minutes. The paramount - is the speed of the loads. If you will do benchmarking of the speed of the loads you will have complete proof of that. I've done such benchmarking on my box 10.2.0.4 on AIX Unix and end up with "best combination" - 4 gig files switching each 1-2 minutes during the batch processing.

Hi,
In a normal (ish) OLTP you would normally get a recommendation of switching maybe 10 times an hour. For batch work it all depends on what work is involved. But then if the batch work is causing a slow down in performance then maybe the root cause of the issues is batch and you perhaps need to focus on tuning this.

Why are you upgrading? Is this due to a massive work load increase? You talk about extrapolation so I assume this is the case? You of course will be using faster disks in a larger SAN. I guess multiple fibre channel disk controllers and suitable RAID for your redo and archive.

Is it possible on your existing DB to add this extra load to the database and see how the existing size copes.

Hi Alan,
I have large batch loads. One load is daily, other loads are every 15 minutes or so. In an upcoming release we will have a new set of loads every 5 minutes. All these loads will get larger as we expand our system to additional regions. The database will grow to over 3TB and will retain data for 7 days. We are upgrading because 10g will become obsolete fairly soon and 11g is now our company standard. I am planning our new database at this time and forecast redo log issues with these large frequent loads. I have never used larger logs and was looking for someone with experience with them. Currently one of our DBs with 1GB logs is switching up to 50 times an hour during peak times. Another DB switches 15 times an hour at peak with 6GB logs. However, as we start loading larger volumes of data I am forecasting this DB to start switching once a minute. I believe I will need logs between 20GB to 30GB in size to have a comfortable level of log switching.
Regarding faster SAN, we already have very fast disks on our existing SAN. I tried to get ITO to dedicate a disk to REDO and suggested only formatting the outer 1/3 of the disk. We are a very large corporation and our ITO has their "standards". It is extremely hard to get any deviation from their standards.
Regarding test hardware, I have a very small underpowered Dev box, a medium sized Test box and Prod. I do not have the luxury of having a "prod like" test bed.

Thanks for your input. Any additional comments would be appreciated.
ShooterII

I don't there is much you can do except set your MTTR settings to help with
the redo sizes.
Try to post on Oracle forums and check Metalink to see if any help can be
assisted.
I think you have pretty much done what you can.

Hi ShooterII
A colleague of mine is currently working on a 17TB database with batch loads, mainly overnight though. I will ask about his REDO sizing and fast_start_mttr parameters and get back to you. However he is on holiday until 2nd week in Jan.

I have spoken to my colleague and he says they have 24GB redo logs in 2 groups because they have large overnight batch runs, they see switching at less than 5 min intervals, during the day they have a script which forces a switch because the day is normal OLTP and a redo of 24GB would maybe only switch once or twice. Their system is different in that there is a standby in max availability which means that as redo is generated it is copied over to the standby. FAST_START_MTTR is not set which means that the old method of recovery is used i.e. LOG_CHECKPOINT_INTERVAL.

I will try and find out more information for you but it would seems sensible in your environment to increase the size of you redo logs. If you are going to be running batch loads for a large part of the day then it seems sensible to ensure that they do not switch too frequently as this does cause an IO overhead but also look at the disk layout of where your redo and archive files are located.

Thanks for remembering to respond after the holidays. Your response helps me a lot. I now think I should go with the 20GB+ file size but keep the FAST_START_MTTR parameter since I will be loading batch all day with virtually no OLTP except for some daily reports. My loads are only getting larger. Unfortunately, I do not have the control I would like over file placements. I have requested a separate disk for redo logs only but our ITO "standard" is to place redo and control files on the same mount point -- but both are multilplexed.

I was mostly concerned about having such large redo logs and was wondering if there were some unknown downsides to having them so large.

Hi, I too faced a lot of issues with redo logs.
Our prod is at RAID 5 , oracle support asked to change the redologs RAiD 5 TO RAID 0+1,
I just made a small RAID 0+1 partition for redos and moved them to new mount point and renamed it database lavel, by doing this really I got 30% improvement in database performance.