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
|
