RE: formula for # of redo groups
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <dag@xxxxxxxxxxxxxxx>, "'Oracle Discussion List'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Oct 2006 11:22:08 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Doug Gernaat
Sent: Wednesday, October 11, 2006 1:50 PM
To: oracle-l@xxxxxxxxxxxxx
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-
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
