||Oracle Tips by Burleson
Number and Placement of Redo
Oracle requires at least two groups of one
redo log. If you are archiving, three are suggested. In a number of
installations, up to six or more have been defined. If you do a lot
of update activity and have numerous users, more than six may be
required. When a log fills, the next one in the queue is opened, and
the previously active log is marked for archive (if you have
archiving enabled). The logs are archived on a first-in, first-out
basis, so, depending on the speed that the log groups can be written
to disk or tape, more than one log group may be waiting to be
archived. One redo log group is used at a time, with multiple users
writing into it at the same time. The size of the redo logs in a
group depends on one critical piece of data: How much data can you
afford to lose on a system crash?
You see, the smaller the log group size, the
more often it is written to disk and the less data (time-wise) is
lost. The larger the log group size, the less often it is written to
disk and the more data (time-wise) is lost. For instance, if your
log groups are filling every 10 minutes, then you may lose 10
minutes’ worth of data should the disk(s) crash that holds that redo
log group’s files. It has been demonstrated on an active system that
a 100-MB redo log group may only last a few seconds. In an inactive
or read-only-type situation, a 100-MB redo log may last for hours.
It is all dependent on how the database is being used and the size
of the redo log group. Remember, a group of three 100-MB redo logs
is actually treated as only a single 100-MB redo log (the other two
files are mirrors). If you mirror redo logs by placing the group
members on separate disks (not just on separate file systems; be
sure it is separate physical disks), then your ability to recover
from a disk array or controller crash increases manyfold.
You have to balance the needs for
restoration and minimal data loss against time to recover data.
Obviously, if you have archiving happening every minute and your
normal workday is eight hours, you will have 480 logs written to
disk daily. Over a five-day workweek, this turns into 2,400 files.
If you have to restore from a crash or other disaster, you may have
to apply all of these to your last backup to bring the database
current to the time of the crash. In one case, a Remote DBA had to apply
9,000-plus files to recover his system because he hadn’t looked at
how often his redo logs were archiving. Needless to say, he pays
more attention now. The minimum size for redo log groups is 50 KB.
By default the Oracle9i DBCA creates 100 megabyte redo log.
See Code Depot