RE: formula for # of redo groups

The time to open and close the files should be pretty insignificant unless you reach a number of files which results in suboptimal performance of the directory structure locating the files. Even with an overloaded directory structure the time to open/close should be very small compared to processing 50M of redo. I would be more concerned about sheer file clutter and possibly overloading a tape library directory structure capacity than this as a performance issue.

As regards point in time recovery, there is a tradeoff. The risk of this tradeoff is mitigated by multiplex image of disk drives and the near universal adoption of robust power supplies, but it still exists to the extent is it still possible to lose your online redo log media through misadventure. This includes a site disaster, but that is only relevant to the extent you ship your archived redo logs offsite quickly, whether part of dataguard or just to have them safe at two remote locations, together with some variety of recoverable image of the database. This tradeoff is the larger your archives are the more time elapses between archives if letting them fill is how you drive switches. So then in a recovery where online redo that was not archived is corrupt or no longer exists, the point to which you can recover is further in the past. Now if you know of an impending potential problem you can further mitigate this risk by manual switching and archiving, but you cannot really eliminate it. The other side of the tradeoff is that excessive log switching drives a lot of otherwise unneeded overhead.

Another issue on the original topic I did not see discussed (though I confess to less than exhaustive reading on this thread) is whether there is a significant difference between the speed of your media for the online redo logs and the speed of your archive destination, especially as regards reads and potentially cached reads. In an instance recovery, this can make a significant difference. Even if the online logs have aged out of cache at the beginning of recovery, if your number of cpus, i/o characteristics, and some level of cache in your disk farm system (not including the SGA in this case) fits the bill, then you can use any old utility to run ahead of recovery reading the online logs into cache. If your archived destination is slower or has little or no cache, those possibilities are limited to things like the file system cache that are not part of the disk farm hardware.

If, like most my clients, you have an excess of space on the online redo log devices in order to get enough i/o bandwidth there, then a lot of online groups gives you a potential gain at little or no cost. If you're in an environment where ping ponging the online logs make be needed to mitigate competition for the files by "ARCH" and "LGWR" then choose a number that is an even multiple of your number of independently operating ping pong sets.

I find that 12 fits the bill pretty much everywhere, being nicely divisible by 2, 3, and 4. And of course you can adjust your on line log configuration later, but be sure to understand the order of the round robin use as you add logs. I find it inconvenient to have the order of use differ from the numbers in the naming convention, so I usually gratuitously switch them around so that new ones added are in the position implied by the naming convention. This also helps inadvertently adding a group such that it is used immediately after a group on the same devices when you had intended to separate simultaneous activity on the devices by ARCH and LGWR.

Sorry I went on so long, I didn't have time to write it shorter.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Doug Gernaat
Sent: Thursday, October 12, 2006 10:08 AM To: Oracle Discussion List
Subject: RE: formula for # of redo groups

thanks for the replies... i'll bump it up to 3 groups with 3 members. they're at 50M now... probably will leave them at that and see how many switches i get. this does lead me to a question about # of archive files generated. for recovery... is it better/worse to apply more/less archives? how does this affect point-in-time recovery?

thanks
-doug-

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Doug Gernaat Sent: Wednesday, October 11, 2006 1:50 PM To: oracle-l_at_freelists.org
Subject: formula for # of redo groups

is there a tried and true formula for deciding on the number or redo groups and members? is the decision solely based on LGWR activity? what about archiving? for example... a database with 2 groups and 2 members in each group that switches about 10 times a day producing 10 archive files. what is optimal in a relative world?

thanks
-doug-
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 12 2006 - 10:22:08 CDT