|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks. Even if all copies of the redo log are on the same disk, however, the redundancy can help protect against I/O errors, file corruption, and so on. When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure.
Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.
Figure 12-2 Multiplexed Redo Log Files
In Figure 12-2,
B_LOG1 are both members of Group 1,
B_LOG2 are both members of Group 2, and so forth. Each member in a group must be exactly the same size.
Each member of a log file group is concurrently active—that is, concurrently written to by LGWR—as indicated by the identical log sequence numbers assigned by LGWR. In Figure 12-2, first LGWR writes concurrently to both
B_LOG1. Then it writes concurrently to both
B_LOG2, and so on. LGWR never writes concurrently to members of different groups (for example, to
Note:Oracle recommends that you multiplex your redo log files. The loss of the log file data can be catastrophic if recovery is required. Note that when you multiplex the redo log, the database must increase the amount of I/O that it performs. Depending on your configuration, this may impact overall database performance.
Whenever LGWR cannot write to a member of a group, the database marks that member as
INVALID and writes an error message to the LGWR trace file and to the database alert log to indicate the problem with the inaccessible files. The specific reaction of LGWR when a redo log member is unavailable depends on the reason for the lack of availability, as summarized in the table that follows.
|LGWR can successfully write to at least one member in a group||Writing proceeds as normal. LGWR writes to the available members of a group and ignores the unavailable members.|
|LGWR cannot access the next group at a log switch because the group needs to be archived||Database operation temporarily halts until the group becomes available or until the group is archived.|
|All members of the next group are inaccessible to LGWR at a log switch because of media failure||Oracle Database returns an error, and the database instance shuts down. In this case, you may need to perform media recovery on the database from the loss of a redo log file.
If the database checkpoint has moved beyond the lost redo log, media recovery is not necessary, because the database has saved the data recorded in the redo log to the datafiles. You need only drop the inaccessible redo log group. If the database did not archive the bad log, use
|All members of a group suddenly become inaccessible to LGWR while it is writing to them||Oracle Database returns an error and the database instance immediately shuts down. In this case, you may need to perform media recovery. If the media containing the log is not actually lost--for example, if the drive for the log was inadvertently turned off--media recovery may not be needed. In this case, you need only turn the drive back on and let the database perform automatic instance recovery.|
In most cases, a multiplexed redo log should be symmetrical: all groups of the redo log should have the same number of members. However, the database does not require that a multiplexed redo log be symmetrical. For example, one group can have only one member, and other groups can have two members. This configuration protects against disk failures that temporarily affect some redo log members but leave others intact.
The only requirement for an instance redo log is that it have at least two groups. Figure 12-3 shows legal and illegal multiplexed redo log configurations. The second configuration is illegal because it has only one group.
Figure 12-3 Legal and Illegal Multiplexed Redo Log Configuration
When setting up a multiplexed redo log, place members of a group on different physical disks. If a single disk fails, then only one member of a group becomes unavailable to LGWR and other members remain accessible to LGWR, so the instance can continue to function.
If you archive the redo log, spread redo log members across disks to eliminate contention between the LGWR and ARCn background processes. For example, if you have two groups of multiplexed redo log members (a duplexed redo log), place each member on a different disk and set your archiving destination to a fifth disk. Doing so will avoid contention between LGWR (writing to the members) and ARCn (reading the members).
Datafiles should also be placed on different disks from redo log files to reduce contention in writing data blocks and redo records.
When setting the size of redo log files, consider whether you will be archiving the redo log. Redo log files should be sized so that a filled group can be archived to a single unit of offline storage media (such as a tape or disk), with the least amount of space on the medium left unused. For example, suppose only one filled redo log group can fit on a tape and 49% of the tape storage capacity remains unused. In this case, it is better to decrease the size of the redo log files slightly, so that two log groups could be archived on each tape.
All members of the same multiplexed redo log group must be the same size. Members of different groups can have different sizes. However, there is no advantage in varying file size between groups. If checkpoints are not set to occur between log switches, make all groups the same size to guarantee that checkpoints occur at regular intervals.
The minimum size permitted for a redo log file is 4 MB.
See Also:Your operating system–specific Oracle documentation. The default size of redo log files is operating system dependent.
Unlike the database block size, which can be between 2K and 32K, redo log files always default to a block size that is equal to the physical sector size of the disk. Historically, this has typically been 512 bytes (512B).
Some newer high-capacity disk drives offer 4K byte (4K) sector sizes for both increased ECC capability and improved format efficiency. Most Oracle Database platforms are able to detect this larger sector size. The database then automatically creates redo log files with a 4K block size on those disks.
However, with a block size of 4K, there is increased redo wastage. In fact, the amount of redo wastage in 4K blocks versus 512B blocks is significant. You can determine the amount of redo wastage by viewing the statistics stored in the
SQL> SELECT name, value FROM v$sysstat WHERE name = 'redo wastage'; NAME VALUE -------------------------------- ---------- redo wastage 17941684
To avoid the additional redo wastage, if you are using emulation-mode disks—4K sector size disk drives that emulate a 512B sector size at the disk interface—you can override the default 4K block size for redo logs by specifying a 512B block size or, for some platforms, a 1K block size. However, you will incur a significant performance degradation when a redo log write is not aligned with the beginning of the 4K physical sector. Because seven out of eight 512B slots in a 4K physical sector are not aligned, performance degradation typically does occur. Thus, you must evaluate the trade-off between performance and disk wastage when planning the redo log block size on 4K sector size emulation-mode disks.
Beginning with Oracle Database 11g Release 2, you can specify the block size of online redo log files with the
BLOCKSIZE keyword in the
CONTROLFILE statements. The permissible block sizes are 512, 1024, and 4096.
The following statement adds a redo log file group with a block size of 512B. The
512 clause is valid but not required for 512B sector size disks. For 4K sector size emulation-mode disks, the
512 clause overrides the default 4K size.
ALTER DATABASE orcl ADD LOGFILE GROUP 4 ('/u01/logs/orcl/redo04a.log','/u01/logs/orcl/redo04b.log') SIZE 100M BLOCKSIZE 512 REUSE;
To ascertain the redo log file block size, run the following query:
SQL> SELECT BLOCKSIZE FROM V$LOG; BLOCKSIZE --------- 512
The best way to determine the appropriate number of redo log files for a database instance is to test different configurations. The optimum configuration has the fewest groups possible without hampering LGWR from writing redo log information.
In some cases, a database instance may require only two groups. In other situations, a database instance may require additional groups to guarantee that a recycled group is always available to LGWR. During testing, the easiest way to determine whether the current redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database alert log. If messages indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups.
Consider the parameters that can limit the number of redo log files before setting up or altering the configuration of an instance redo log. The following parameters limit the number of redo log files that you can add to a database:
MAXLOGFILES parameter used in the
DATABASE statement determines the maximum number of groups of redo log files for each database. Group values can range from 1 to
MAXLOGFILES. When the compatibility level is set earlier than 10.2.0, the only way to override this upper limit is to re-create the database or its control file. Therefore, it is important to consider this limit before creating a database. When compatibility is set to 10.2.0 or later, you can exceed the
MAXLOGFILES limit, and the control files expand as needed. If
MAXLOGFILES is not specified for the
DATABASE statement, then the database uses an operating system specific default value.
MAXLOGMEMBERS parameter used in the
DATABASE statement determines the maximum number of members for each group. As with
MAXLOGFILES, the only way to override this upper limit is to re-create the database or control file. Therefore, it is important to consider this limit before creating a database. If no
MAXLOGMEMBERS parameter is specified for the
DATABASE statement, then the database uses an operating system default value.
You can force all enabled redo log threads to switch their current logs at regular time intervals. In a primary/standby database configuration, changes are made available to the standby database by archiving redo logs at the primary site and then shipping them to the standby database. The changes that are being applied by the standby database can lag behind the changes that are occurring on the primary database, because the standby database must wait for the changes in the primary database redo log to be archived (into the archived redo log) and then shipped to it. To limit this lag, you can set the
ARCHIVE_LAG_TARGET initialization parameter. Setting this parameter lets you specify in seconds how long that lag can be.
When you set the
ARCHIVE_LAG_TARGET initialization parameter, you cause the database to examine the current redo log of the instance periodically. If the following conditions are met, then the instance will switch the log:
The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + m exceeds the value of the
ARCHIVE_LAG_TARGET initialization parameter.
The current log contains redo records.
In an Oracle Real Application Clusters environment, the instance also causes other threads to switch and archive their logs if they are falling behind. This can be particularly useful when one instance in the cluster is more idle than the other instances (as when you are running a 2-node primary/secondary configuration of Oracle Real Application Clusters).
ARCHIVE_LAG_TARGET initialization parameter specifies the target of how many seconds of redo the standby could lose in the event of a primary shutdown or failure if the Oracle Data Guard environment is not configured in a no-data-loss mode. It also provides an upper limit of how long (in seconds) the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time.
The following initialization parameter setting sets the log switch interval to 30 minutes (a typical value).
ARCHIVE_LAG_TARGET = 1800
A value of 0 disables this time-based log switching functionality. This is the default setting.
You can set the
ARCHIVE_LAG_TARGET initialization parameter even if there is no standby database. For example, the
ARCHIVE_LAG_TARGET parameter can be set specifically to force logs to be switched and archived.
ARCHIVE_LAG_TARGET is a dynamic parameter and can be set with the
ALTER SYSTEM SET statement.
ARCHIVE_LAG_TARGETparameter must be set to the same value in all instances of an Oracle Real Application Clusters environment. Failing to do so results in unpredictable behavior.
Consider the following factors when determining if you want to set the
ARCHIVE_LAG_TARGET parameter and in determining the value for this parameter.
Overhead of switching (as well as archiving) logs
How frequently normal log switches occur as a result of log full conditions
How much redo loss is tolerated in the standby database
ARCHIVE_LAG_TARGET may not be very useful if natural log switches already occur more frequently than the interval specified. However, in the case of irregularities of redo generation speed, the interval does provide an upper limit for the time range each current log covers.
ARCHIVE_LAG_TARGET initialization parameter is set to a very low value, there can be a negative impact on performance. This can force frequent log switches. Set the parameter to a reasonable value so as not to degrade the performance of the primary database.