||Oracle Tips by Burleson
Oracle Number and Placement of Redo Logs?
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
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
In Chapter 12 on database tuning, we will discuss how to determine
if you have a sufficient number of redo log groups and how to
optimize your archive process.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.