Oracle Adding/Dropping Logfile Threads/Groups/Members

Adding Logfiles

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 tread number to which the group is assigned.

file specification is a system specific full path file name:

UNIX:

'/etc/usr/ora_redo1.rdo'  SIZE 1M REUSE
(the size parameter is not with the file specification)

VMS:

'DUA1:[M_ORACLE_1.DB_EXAMPLE]ORA_REDO1.RDO' SIZE 1M REUSE 

The SIZE clause specifies the size of the new log (it should be the same size as all of the other redo logs). M means megabytes, K kilobytes and no specification, just a number, means bytes. REUSE tells oracle if the file exists, reuse it.

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 file name, no SIZE or REUSE clause.

Logfile insights

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 non-critical 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, then 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. Two groups of at least one redo log each are required; three are suggested. Having multiple group members allows shadowing of log files on multiple drives, thus making redo log loss type failures almost impossible.

Redo logs are members of groups. Each group should be located on a separate drive and each group can be associated with a single thread of the multi-thread server. In addition, Oracle allows redo log mirroring, were a redo log can be simultaneously copied to two disks at the same time by the LGWR process. This ensures that loss of a group of log files will not effect 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.

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 were 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.

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. If the redo logs or groups are archived to tape, ensure the log sizes are such that an equal number will fit on a standard tape to avoid space, and time, wastage.

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. Unfortunately there is no convenient formula for determining this, each DBA must determine this for their own database(s).