Release 8.0
A54641_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Server Administrator's Guide Release 8.0 A54641_01 |
|
This chapter explains how to manage the online redo log, and includes the following topics:
See Also: For more information about managing the online redo logs of the instances when using Oracle Parallel Server, see the Oracle8 Parallel Server Concepts & Administrationl.
For more information archiving the redo log, see Chapter 23, "Archiving Redo Information".
This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager User's Guide.
Every instance of an Oracle database has an associated online redo log, which is a set of two or more online log files that record all committed changes made to the database. Online redo logs serve to protect the database in the event of an instance failure. Whenever a transaction is committed, the corresponding redo entries temporarily stored in redo log buffers of the system global area are written to an online redo log file by the background process LGWR.
Online redo log files are used in a cyclical fashion; for example, if two files constitute the online redo log, the first file is filled, the second file is filled, the first file is reused and filled, the second file is reused and filled, and so on. Each time a file is filled, it is assigned a log sequence number to identify the set of redo entries.
This section describes guidelines you should consider when configuring a database instance's online redo log, and includes the following topics:
The online redo log of a database instance should consist of multiplexed groups of online redo log files. Furthermore, members in the same group should be stored on separate disks so that no single disk failure can cause LGWR and the database instance to fail.
To avoid losing a database due to a single point of failure, Oracle can maintain multiple sets of on-line redo log files. A multiplex online redo log consists of copies of online redo log files physically located on separate disks; changes made to one member of the group are made to all members. If a disk that contains an online redo log file fails, other copies are still intact and available to Oracle. System operation is not interrupted and the lost online redo log files can be easily recovered.
Warning: Although the Oracle Server allows multiplexed groups to contain different numbers of members, this state should only be the temporary result of an abnormal situation such as a disk failure damaging a member of a group. If any group contains only one member, the failure of the disk containing that member could cause Oracle to halt.
While multiplexed groups require extra storage space, the cost of this space is usually insignificant compared to the potential cost of lost data (if a disk failure destroys a non-multiplexed online redo log).
When setting up a multiplex online redo log, place members of a group on different disks. This way, if a single disk fails, 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 online redo log members across disks to eliminate contention between the LGWR and ARCH background processes. For example, if you have two groups of duplexed online redo log members, place each member on a different disk and set your archiving destination to a fifth disk. This way, there is never contention between LGWR (writing to the members) and ARCH (reading the members).
Datafiles and online redo log files should also be on different disks to reduce contention in writing data blocks and redo entries.
When setting the size of online redo log files, consider whether you will be archiving the redo log. Online 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 online redo log group can fit on a tape and 49% of the tape's storage capacity remains unused. In this case, it would be better to decrease the size of the online redo log files slightly, so that two log groups could be archived per tape.
With multiplex groups of online redo logs, all members of the same 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.
See Also: The default size of online redo log files is operating system -dependent; for more details see your operating system-specific Oracle documentation.
The best way to determine the appropriate number of online redo log files for a database instance is to test different configurations. The optimum configuration has the fewest groups possible without hampering LGWR's 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 if the current online redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database's ALERT file. 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 online redo log files before setting up or altering the configuration of an instance's online redo log. The following three parameters limit the number of online redo log files that you can add to a database:
See Also: For the default and legal values of the MAXLOGFILES and MAXLOGMEMBERS parameters, and the LOG_FILES initialization parameter, see your operating system-specific Oracle documentation.
You can create groups and members of online redo log files during or after database creation. If you can, plan the online redo log of a database and create all required groups and members of online redo log files during database creation. To create new online redo log groups and members, you must have the ALTER DATABASE system privilege.
In some cases, you might need to create additional groups or members of online redo log files. For example, adding groups to an online redo log can correct redo log group availability problems. A database can have up to MAXLOGFILES groups.
To create a new group of online redo log files, use either the Add Logfile Group property sheet of Enterprise Manager, or the SQL command ALTER DATABASE with the ADD LOGFILE parameter.
The following statement adds a new group of redo logs to the database:
ALTER DATABASE ADD LOGFILE ('log1c', 'log2c') SIZE 500K;
Note: Fully specify filenames of new log members to indicate where the operating system file should be created; otherwise, the file is created in the default directory of the database server, which is operating system-dependent. If you want to reuse an existing operating system file, you do not have to indicate the file size.
Using the ALTER DATABASE statement with the ADD LOGFILE option, you can specify the number that identifies the group with the GROUP option:
ALTER DATABASE ADD LOGFILE GROUP 10 ('log1c', 'log2c') SIZE 500K;
Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES; do not skip redo log file group numbers (that is, do not number your groups 10, 20, 30, and so on), or you will consume unnecessary space in the control files of the database.
In some cases, you might not need to create a complete group of online redo log files; the group may already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.
To create new online redo log members for an existing group, use the Add Logfile Member property sheet of Enterprise Manager, or the SQL command ALTER DATABASE with the ADD LOG MEMBER parameter.
The following statement adds a new redo log member to redo log group number 2:
ALTER DATABASE ADD LOGFILE MEMBER 'log2b' TO GROUP 2;
Notice that filenames must be specified, but sizes need not be; the size of the new members is determined from the size of the existing members of the group.
When using the ALTER DATABASE command, you can alternatively identify the target group by specifying all of the other members of the group in the TO parameter, as shown in the following example:
ALTER DATABASE ADD LOGFILE MEMBER 'log2c' TO ('log2a', 'log2b');
Note: Fully specify the filenames of new log members to indicate where the operating system files should be created; otherwise, the files will be created in the default directory of the database server.
You can rename online redo log members to change their locations. This procedure is necessary, for example, if the disk currently used for some online redo log files is going to be removed, or if datafiles and a number of online redo log files are stored on the same disk and should be separated to reduce contention.
To rename online redo log members, you must have the ALTER DATABASE system privilege. Additionally, you might also need operating system privileges to copy files to the desired location and privileges to open and back up the database.
Before renaming any online redo log members, ensure that the new online redo log files already exist.
Warning: The following steps only modify the internal file pointers in a database's control files; they do not physically rename or create any operating system files. Use your computer's operating system to copy the existing online redo log files to the new location.
Rename online redo log members with the Rename Logfile Member property sheet of Enterprise Manager, or the SQL command ALTER DATABASE with the RENAME FILE parameter.
Before making any structural changes to a database, such as renaming or relocating online redo log members, completely back up the database (including the control file) in case you experience any problems while performing this operation.
Operating system files, such as online redo log members, must be copied using the appropriate operating system commands. See your operating system manual for more information about copying files.
Suggestion: You can execute an operating system command to copy a file without exiting Enterprise Manager. Use the Enterprise Manager HOST command.
Use the Rename Online Redo Log Member dialog box, or the ALTER DATABASE command with the RENAME FILE clause to rename the database's online redo log files.
The online redo log alterations take effect the next time that the database is opened. Opening the database may require shutting down the current instance (if the database was previously opened by the current instance) or just opening the database using the current instance.
As a precaution, after renaming or relocating a set of online redo log files, immediately back up the database's control file.
The following example renames the online redo log members. However, first assume that:
The files LOG1A and LOG2A on Disk A must be copied to the new files LOG1C and LOG2C on Disk C.
ALTER DATABASE RENAME FILE 'log1a', 'log2a' TO 'log1c', 'log2c';
In some cases, you might want to drop an entire group of online redo log members. For example, you might want to reduce the number of groups in an instance's online redo log.
To drop an online redo log group, you must have the ALTER DATABASE system privilege.
Before dropping an online redo log group, consider the following restrictions and precautions:
Drop an online redo log group with either the Drop Logfile Group menu item of Enterprise Manager, or the SQL command ALTER DATABASE with the DROP LOGFILE clause.
The following statement drops redo log group number 3:
ALTER DATABASE DROP LOGFILE GROUP 3;
When an online redo log group is dropped from the database, the operating system files are not deleted from disk. Rather, the control files of the associated database are updated to drop the members of the group from the database structure. After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files.
In some cases, you might want to drop one or more specific online redo log members. For example, if a disk failure occurs, you might need to drop all the online redo log files on the failed disk so that Oracle does not try to write to the inaccessible files. In other situations, particular online redo log files become unnecessary; for example, a file might be stored in an inappropriate location.
To drop an online redo log member, you must have the ALTER DATABASE system privilege.
Consider the following restrictions and precautions before dropping individual online redo log members:
To drop specific inactive online redo log members, use either the Drop Logfile Member menu item of Enterprise Manager, or the SQL command ALTER DATABASE command with the DROP LOGFILE MEMBER clause.
The following statement drops the redo log LOG3C:
ALTER DATABASE DROP LOGFILE MEMBER 'log3c';
When an online redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping an online redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log file.
See Also: For information on dropping a member of an active group, see "Forcing a Log Switch" on page 5-12.
A checkpoint is the event during which the Database Writer process (DBWR) writes all modified database buffers in the SGA to the appropriate datafiles. A log switch is the event during which LGWR stops writing to one online redo log group and starts writing to another. The two events are often connected: an instance takes a checkpoint at each log switch by default. A log switch, by default, takes place automatically when the current online redo log file group fills.
However, you can designate that checkpoints are taken more often than when you have log switches, or you can have a checkpoint take place ahead of schedule, without a log switch. You can also have a log switch and checkpoint occur ahead of schedule, or without an accompanying checkpoint.
This section includes the following checkpoint and log switch topics:
When your database uses large online redo log files, you can set additional database checkpoints to take place automatically at predetermined intervals, between the checkpoints that automatically occur at log switches. The time necessary to recover from an instance failure decreases when more database checkpoints are set. However, there may be a performance impact on the Oracle Server due to the extra I/O necessary for the checkpoint to complete.
Generally, unless your database consistently requires instance recovery on startup, set database checkpoint intervals so that checkpoints occur only at log switches. If you use small online redo log files, checkpoints already occur at frequent intervals (at each log switch).
You can control the frequency of automatic database checkpoints via the values set in the LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters.
To have database checkpoints only occur at log switches (the default), set the value for the LOG_CHECKPOINT_INTERVAL parameter higher than the size of the online redo log files in use. Alternatively, to force additional checkpoints to occur at intervals between two log switches, set the value for the LOG_CHECKPOINT_INTERVAL parameter lower than the size of the online redo log files in use.
The value of the LOG_CHECKPOINT_INTERVAL is a number of operating system blocks, not Oracle data blocks. Therefore, you must know the size, in bytes, of your operating system's blocks. Once you know this, calculate the number of operating system blocks per online redo log file.
As an example, assume the following conditions:
Using this information, you can compute the number of blocks per redo log file as follows:
Now that the approximate number of blocks per online redo log file (1000) is known, the LOG_CHECKPOINT_INTERVAL parameter can be set accordingly in the instance's parameter file:
LOG_CHECKPOINT_INTERVAL=500
To have database checkpoints only occur at log switches (the default), set the value for the LOG_CHECKPOINT_TIMEOUT parameter to zero. Alternatively, to force additional checkpoints to occur at intervals between two log switches, set the value for the LOG_CHECKPOINT_TIMEOUT parameter to a time interval (in seconds) less than the average time it takes to fill an online redo log file. To determine the average time it takes to fill online redo log files, examine the LGWR trace file for messages that indicate the times of log switches.
See Also: For information on how to determine operating system block size, see your operating system-specific Oracle documentation.
For more information about tuning Oracle regarding checkpoints, see the Oracle8 Server Tuning manual.
For more information about the LOG_CHECKPOINT_TIMEOUT parameter when using the Oracle Parallel Server, see the Oracle8 Parallel Server Concepts & Administration.
You can force a log switch to make the currently active group inactive and available for online redo log maintenance operations. For example, you want to drop the currently active group, but are not able to do so until the group is inactive. You may also wish to force a log switch if the currently active group needs to be archived at a specific time before the members of the group are completely filled; this option is often useful in configurations with large online redo log files that take a long time to fill.
To force a log switch, you must have the Alter System privilege.To force a log switch, use either the Switch Logfile menu item of Enterprise Manager or the SQL command ALTER SYSTEM with the SWITCH LOGFILE option.
The following statement forces a log switch:
ALTER SYSTEM SWITCH LOGFILE;
In some cases, you might want to force a fast database checkpoint. A fast checkpoint is one which does not involve a log switch; LGWR continues to write to the current online redo log file. A fast checkpoint allows DBWR to write more modified database buffers to disk per I/O on behalf of a checkpoint. Therefore, you need fewer I/Os (thus less time) to complete a fast checkpoint.
To force a database checkpoint, you must have the ALTER SYSTEM system privilege. Force a fast database checkpoint with either the Force Checkpoint menu item of Enterprise Manager, or the SQL command ALTER SYSTEM with the CHECKPOINT option.
The following statement forces a checkpoint:
ALTER SYSTEM CHECKPOINT;
Omitting the GLOBAL option allows you to force a checkpoint for only the connected instance, while including it forces a checkpoint for all instances of the database. Forcing a checkpoint for only the local instance is useful only with the Oracle Parallel Server. In a non-parallel server configuration, global and local checkpoints are identical.
See Also: For more information on forcing checkpoints with the Oracle Parallel Server, see the Oracle8 Parallel Server Concepts & Administration manual.
You can configure Oracle to use checksums to verify blocks in the redo log files. Set the initialization parameter LOG_BLOCK_CHECKSUM to TRUE to enable redo log block checking. The default value of LOG_BLOCK_CHECKSUM is FALSE.
If you enable redo log block checking, Oracle computes a checksum for each redo log block written to the current log. The checksums are written in the header of the block.
Oracle uses the checksum to detect corruption in a redo log block. Oracle tries to verify the redo log block when it writes the block to an archive log file and when the block is read from an archived log during recovery.
If Oracle detects a corruption in a redo log block while trying to archive it, Oracle tries to read the block from another member in the group. If the block is corrupted in all members the redo log group, then archiving cannot proceed.
See Also: For information about archiving redo log files, see Chapter 23, "Archiving Redo Information".
If you have enabled redo log block checking, Oracle verifies each block before archiving it. If a particular redo log block is corrupted in all members of a group, archiving stops. Eventually all the redo logs become filled and database activity is halted, until archiving can resume.
In this situation, you can use the SQL command ALTER DATABASE... CLEAR LOGFILE to clear the corrupted redo logs and avoid archiving them. The cleared redo logs are available for use even though they were not archived.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE
GROUP 3;
You can clear a redo log file whether it is archived or not. However, when it is not archived, you must include the keyword UNARCHIVED.
If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. Oracle writes a message in the alert log describing the backups from which you cannot recover.
Attention: If you clear an unarchived redo log file, you should take another backup of the database.
If you want to clear an unarchived redo log that is needed to bring an offline tablespace online, you must use the clause UNRECOVERABLE DATAFILE in the ALTER DATABASE command.
If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform an incomplete recovery.
See Also: For a complete description of the ALTER DATABASE command, see the Oracle8 Server SQL Reference.
Use the V$LOG, V$LOGFILE, and V$THREAD views to see information about the online redo log of a database; the V$THREAD view is of particular interest for Parallel Server administrators.
The following query returns information about the online redo log of a database used without the Parallel Server:
SELECT group#, bytes, members FROM sys.v$log; GROUP# BYTES MEMBERS ---------- ---------- ---------- 1 81920 2 2 81920 2
To see the names of all of the member of a group, use a query similar to the following:
SELECT * FROM sys.v$logfile WHERE group# = 2; GROUP# BYTES MEMBERS ---------- ---------- --------- 2 LOG2A 2 STALE LOG2B 2 LOG2C
If STATUS is blank for a member, the file is in use.
