Donald K. 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 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.
This is an
excerpt by Mike Ault’s book “Oracle
Administration & Management”. If you want more current Oracle tips
by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or
Ault’s Oracle Scripts Download.
Copyright © 1996 - 2014 by
Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.