Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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;


ALTER DATABASE database name

ADD LOGFILE  MEMBER 'file specification'  REUSE TO GROUP n;


ALTER DATABASE database name

     ADD LOGFILE  MEMBER 'file specification'  REUSE TO
          ('file specification', 'file specification');

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:

'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');


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.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter