Multiplexing and Maintaining Online Redo Log Files

It is often desirable to add redo log file groups to the database to make sure that the first group is ready to be overwritten before the last group is full. This can often happen if a great deal of activity occurs on the database, and the database is in ARCHIVELOG mode.

To add a group to a database that has two redo log file groups, you would issue an ALTER DATABASE command as follows:

 ALTER DATABASE ADD LOGFILE GROUP 3 ('/mydatabases/mydb1/log3a.dbf', '/mydatabases2/mybd1/log3b.dbf') SIZE 1M; 

Or in Windows

 ALTER DATABASE ADD LOGFILE GROUP 3 ('c:\mydatabases\mydb1\log3a.dbf', 'd:\mydatabases2\mybd1\log3b.dbf') SIZE 1M; 

The generic format of the command is as follows:

 ALTER DATABASE [database name] ADD LOGFILE [GROUP integer] filespec, filespec; 

All the information in the brackets is optional, and you would specify the name, location, and size with the filespec variable. The value required for use as the GROUP number can be selected and provided for each redo log file group that you want to add. If omitted, Oracle generates the value automatically.

Adding a Log File Member to a Group

Oracle, similar to its suggestions on control files, suggests that redo log files be multiplexed. To do this, Oracle allows you to use the ALTER DATABASE command to add the LOGFILE MEMBER. This is accomplished in Unix as follows:

 ALTER DATABASE ADD LOGFILE MEMBER '/mydatabases2/mybd1/log1b.dbf' TO GROUP 1 '/mydatabases2/mybd1/log2b.dbf' TO GROUP 2 '/mydatabases2/mybd1/log3b.dbf' TO GROUP 3; 

It is important to fully specify the filename for the new members; otherwise, Oracle places the files in the default directory of the database server. If a file by the name that you have specified already exists, it must have the same size as the existing file for the GROUP specified, and you must specify the REUSE option; otherwise, the command fails.

But how do you determine how many redo log files you need for your database situation?

Optimum Number of Online Redo Log Files

Determining the appropriate number of online redo log groups for your given database instance can be an exercise in creativity. You simply need to test different configurations to determine what is needed for your database.

In some cases, a database instance may indeed only require two groups. Other situations may mean that additional groups are needed to guarantee that the groups will always be available to LGWR to use. You will start to see messages in the LGWR trace files and in the alert file that indicate that LGWR has started to have to wait. It will wait for available groups because checkpoints have not completed, or archival of the log file has not completed. At this point, the log groups need LGWR processing that can't occur until there are freed other groups. If this happens, it is time to add additional redo log groups to the database.

Although it is possible, with the Oracle server, to have multiplexed redo log file groups with different numbers of members in each group, it is better to try to maintain a symmetric configuration. Having different numbers of files in different groups should be only maintained as a temporary result of some unintended event (such as disk failure or other accidental situation).

Location, Location, Location

When you multiplex the online redo log files, you need to be sure to place the members on different disks or different groups of disks. By splitting up members like this, you are less likely to have a database crash because an entire group is unavailable in case of a loss of a disk.

Also, separating archive log files from the location of the online redo log files and placing these on different disks or disk groups reduces contention between the ARCn process and the LGWR process.

Ideally, redo log files should also be placed on different disks or disk groups than data files to reduce the possibility of contention between LGWR and DBWn, as well as to reduce the chances of losing both redo log files and data files in case of a loss of a disk or disk group.

Sizing the Redo Log Files

Although the minimum size for online redo log files is 50KB, the maximum size is operating system specific. Members of different GROUPS can have different sizes, although there is no real benefit to having different sized groups. Each member of a given group has to have the same size as the other members of that group.

The following situations will help to guide you in determining whether you have the right number of online redo log files:

• If you have many log switches and checkpoints during a given time period, you may want larger redo log members.

• If you have frequent log switches, you may want larger redo log members.

• If you only see log switches and checkpoints when the database is shut down, you may want smaller redo log members.

• If you see several points where the Archiver or Checkpoint has to wait to perform its activities until the log file is available, you may want to add one or more redo log groups.

• You are starting to see many "checkpoint not complete" messages in your alert logs.

Dropping Redo Log Groups

If you need to resize (either increase or decrease the size of) redo log groups, or you want to replace the existing redo log groups with redo log groups of a different size, you need to have the ability to drop the existing redo log groups.

Before you can drop the existing log groups (at least before you can drop the last two), you have to have replacements built for them.

To drop a log file, or a redo log group, you use the ALTER DATABASE command as follows:

 ALTER DATABASE [database name] DROP LOGFILE {GROUP integer| ('filename', 'filename'...)} 

The restrictions associated with dropping redo log groups are as follows:

• An instance always requires at least two active groups of online redo log files.

• An active or current group cannot be dropped (ALTER DATABASE SWITCH LOGFILE can allow the group in question to no longer be active or current).

• When the online redo log group is dropped, the associated operating system files are not deleted.

You can drop a log member, as well as an entire group. It is appropriate to do this when the online redo log member is corrupt or unusable:

 ALTER DATABASE DROP LOGFILE MEMBER <logfile name>; 

There are, of course, the following restrictions to this, as well:

• The log member to drop cannot be the last valid member of the group.

• If the group to which the member is associated is the active or current group, you have to manually execute a log switch to drop the offending member.

• If the database is in ARCHIVELOG mode, and the log file group to which the log member is associated has not yet been archived, the member cannot be dropped.

• If the online redo log member is dropped, you still need to drop the operating system file to which it was associated.

Clearing Online Redo Logs

Occasionally, but fortunately not often, a redo log group will become corrupt, and all the members will be corrupt. As DBA, it falls to you to solve the problem by reinitializing the log group associated with the corruption.

Reinitialization can be accomplished through an ALTER DATBASE command as in the following example:

 ALTER DATABASE CLEAR LOGFILE GROUP 2; 

The generic form of the command follows:

 ALTER DATABASE [database name] CLEAR [UNARCHIVED] LOGFILE {GROUP | (FILENAME, FILENAME)} 

If your database is in ARCHIVELOG mode, and the corrupt redo log group hasn't yet been archived, you will need to use the UNARCHVIED parameter.

Using this command has the net effect similar to having added an online redo log group and dropped an offending online redo log group but can be issued even if only two groups are associated with the database.

You can clear an online redo log file whether or not it has already been archived. If it has not been archived, you must include the keyword UNARCHIVED in the ALTER DATABASE CLEAR LOGFILE command. This renders your backups unusable if the redo log file is needed for recovery. For this reason, you should take a backup of the database as soon as possible.

Relocating and Renaming Online Redo Logs

You can change the location and name of the online redo log files by adding new log files and dropping the old log files. Alternatively, you can use the ALTER DATABASE RENAME FILE command. Using this alternative method requires the database to be STARTUP MOUNT but not opened and is therefore impractical for a database that is running. It is usually much easier to add new redo log files and drop the old ones.

We have dealt with the management and maintenance of redo log files in a non-OMF database, but now we will look at their maintenance in a database that is managed using Oracle Managed Files (OMF).