 |
|
Log Groups and Log
Member Files
Oracle Tips by Burleson
|
The number of redo logs is directly related to
the number, size, and length of transactions that are performed in the
database. Each transaction that alters the database is recorded in the
redo log files. The size of redo logs is governed by the amount of
data a database can afford to lose. If a database supports noncritical
data, where loss of a few hours’ worth of data is not important, then
very large redo logs can be used. In a database where each piece of
data is critical and loss of even minuscule portions of data could be
catastrophic, a very small redo log is in order. If you have larger
redo logs, fewer are needed; if you have small redo logs, many may be
needed. Under Oracle7, Oracle8-8i and Oracle, two groups of at least
one redo log each are required; again, three are suggested. Having
multiple group members allows the shadowing of log files on multiple
drives, thus making redo-log-loss-type failures almost impossible.
Under Oracle7, Oracle8-8i, and Oracle, redo
logs are members of groups, and each group should be located on a
separate drive and can be associated with a single thread of the
multithread server. In addition, Oracle allows redo log mirroring,
where a redo log can be simultaneously copied to multiple disks at the
same time by the LGWR process. This ensures that the loss of a group
of log files will not affect operation. Groups are archived together.
The MAXLOGMEMBERS parameter in the CREATE DATABASE statement
determines the maximum number of redo logs in a group. The MAXLOGFILES
parameter in the CREATE DATABASE statement determines the maximum
number of groups. The reason that the MAXLOGFILES parameter is
MAXLOGFILES and not MAXLOGGROUPS is that it is a carryover from the
days before you could have multiple log members, as well as multiple
log groups. In those days, a single parameter value controlled the
number of files. Rather than change the parameter everywhere it is
used, Oracle simply added a second parameter.
Another factor is whether or not you are using
archive logging. While a redo log (or log group) is being archived, it
cannot be used. If a log switch goes to a redo log (or log group) that
is being archived, the database stops. This is why three is the
minimum number of logs or log groups recommended for an archive
situation: one in use, one waiting to be used, and one archiving.
Generally, it is suggested that several be available for use. In
several installations where the logs were archived to disk, during
heavy-use periods the disk filled, causing archiving to be suspended.
Once the available logs filled, the database stopped. In Oracle8i and
Oracle, multiplexing of archive logs is supported. Multiplexing of
archive logs allows multiple copies to be written to several
locations, thus enhancing recoverability.
With multiple logs or log groups, you can have
time to respond to this type of situation before the database has to
be stopped. This also points out that you should keep a close eye on
disk space usage for your archive destination(s). If the redo logs or
groups are archived to tape, ensure that the log sizes are such that
an equal number will fit on a standard tape to avoid wasting space and
time. For example, if you have redo logs that are 1 MB in size on a
version 7 database, and your tape has 90-MB capacity, then 90 will fit
on the tape (approximately) with little waste. The entire group is
archived as a unit with a size equal to that of one of the members.
After operating for a while, DBAs get a feel
for how often their databases generate logs. This will tell them how
many they will require and what size they will need to be. A check of
the alert<SID>.log file located in the background_dump_dest-specified
location will tell you how often your system is switching logs.
Another location where log history is stored is the v$loghist or
v$loghistory view. Unfortunately, there is no convenient formula for
determining this; each DBA must determine this for his or her own
database(s). To add a redo log, the following command is used:
ALTER DATABASE
database name
ADD LOGFILE
THREAD y GROUP n (file specification, file specification) SIZE x;
or:
ALTER DATABASE database name
ADD LOGFILE
MEMBER 'file specification' REUSE TO GROUP n;
or:
ALTER DATABASE database name
ADD
LOGFILE MEMBER 'file specification' REUSE TO
('file specification', 'file specification');
where:
n is the group number. If the GROUP n clause
is left out, a new group will be added that consists of the specified
log files.
x is the size for all members of the group.
y is the thread number to which the group is
assigned.
file specification is a system-specific full
path filename:
On UNIX, AIX, or Linux:
'/oracle1/oracle/ortest/logs/ora_redo11.rdo' SIZE 1M REUSE
(The SIZE parameter is not with the file
specification.)
On VMS:
'DUA1:[M_ORACLE_1.ORACLE.ORTEST.LOGS]ORA_REDO11.RDO' SIZE 1M
REUSE
NT:
'd:\oracle1\oracle\ortest\logs\ora_redo11.rdo' SIZE 1M REUSE
The SIZE clause specifies the size of the new
log (it should be the same size as that of all of the other redo
logs). M means megabytes; K is kilobytes; and no specification, just a
number, means bytes. REUSE tells Oracle that if the file exists, reuse
it; however, to be reused, the old and new files must be the same
specified size.
Dropping Log Files
The ALTER command is also used to drop redo
logs:
ALTER DATABASE database name
DROP
LOGFILE GROUP n --OR--('filename', 'filename');
or:
ALTER DATABASE database name
DROP
LOGFILE MEMBER 'filename';
where ‘filename’ is just the filename, no SIZE
or REUSE clause.
Addition of Rollback Segments
Another database structure is the ROLLBACK
segment. Under pre-9i versions and in 9i with manually managed undo,
ROLLBACK segments can be placed in any tablespace, but it is suggested
that they be placed in a tablespace that contains only other rollback
segments. This makes administration easier. Rollback segments can be
PUBLIC, which means that for a multi-instance database, any instance
can use the rollback segment, or PRIVATE, which means only the
instance that has the rollback segments named in the ROLLBACK SEGMENTS
clause of its INIT.ORA file can use the rollback segment.
Under Oracle, an UNDO tablespace can be
created (is created by default) and should be used to allow Oracle to
internally manage UNDO (read rollback) operations. The UNDO tablespace
is usually created with the UNDO TABLESPACE clause of the CREATE
DATABASE command or can be added after database creation with the
CREATE UNDO TABLESPACE command and addition of the required
initialization parameters.
See
Code Depot for Full Scripts
 |
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
