Oracle8 Administrator's Guide Release 8.0 A58397-01 |
Library | Product | Contents | Index

# 5
Managing the Online Redo Log

This chapter explains how to manage the online redo log, and includes the following topics:

- Planning the Online Redo Log
- Creating Online Redo Log Groups and Members
- Renaming and Relocating Online Redo Log Members
- Dropping Online Redo Log Groups
- Dropping Online Redo Log Members
- Controlling Checkpoints and Log Switches
- Verifying Blocks in Redo Log Files
- Clearing an Online Redo Log File
- Listing Information about the Online Redo Log

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 and Administration.

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 Administrator's Guide and Oracle Server Manager User's Guide.

## Planning the Online Redo Log

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:

- Multiplex the Online Redo Log
- Place Online Redo Log Members on Different Disks
- Set the Size of Online Redo Log Members
- Choose an Appropriate Number of Online Redo Log Files

### Multiplex the Online Redo Log

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

### Place Online Redo Log Members on Different Disks

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.

### Set the Size of Online Redo Log Members

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.

### Choose an Appropriate Number of Online Redo Log Files

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:

- The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of online redo log files per database; group values can range from 1 to MAXLOGFILES. The only way to override this upper limit is to re-create the database or its control file; thus, it is important to consider this limit before creating a database. If MAXLOGFILES is not specified for the CREATE DATABASE statement, Oracle uses an operating system default value.
- The LOG_FILES parameter (in the parameter file) can temporarily decrease the maximum number of groups of online redo log files for the duration of the current instance. However, LOG_FILES cannot override MAXLOGFILES to increase the limit. If LOG_FILES is not set in the database's parameter file, Oracle uses an operating system-specific default value.
- The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members per group. As with MAXLOGFILES, the only way to override this upper limit is to re-create the database or control file; thus, it is important to consider this limit before creating a database. If no MAXLOGMEMBERS parameter is specified for the CREATE DATABASE statement, Oracle uses an operating system default value.

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.

## Creating Online Redo Log Groups and Members

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.

### Creating Online Redo Log 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.

### Creating Online Redo Log Members

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.

## Renaming and Relocating Online Redo Log Members

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.

##### To Rename Online Redo Log Members

1. Back up the database.

   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.

2. Copy the online redo log files to the new location.

   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 by using the Enterprise Manager HOST command.

3. Rename the online redo log members.

   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.

4. Open the database for normal operation.

   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.

5. Back up the control file.

   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 database is currently mounted by, but closed to, the instance.
- The online redo log is duplexed: one group consists of the members LOG1A and LOG1B, and the second group consists of the members LOG2A and LOG2B. The files LOG1A and LOG2A are stored on Disk A, while LOG1B and LOG2B are stored on Disk B.
- The online redo log files located on Disk A must be relocated to Disk C. The new filenames will reflect the new location: LOG1C and LOG2C.

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';

## Dropping Online Redo Log Groups

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:

- An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)
- You can drop an online redo log group only if it is not the active group. If you need to drop the active group, first force a log switch to occur; see "Forcing a Log Switch".
- Make sure an online redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the Enterprise Manager ARCHIVE LOG command with the LIST parameter.

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.

## Dropping Online Redo Log Members

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:

- It is all right to drop online redo log files so that a multiplexed online redo log becomes temporarily asymmetric. For example, if you use duplexed groups of online redo log files, you can drop one member of one group, even though all other groups have two members each. However, you should rectify this situation immediately so that all groups have at least two members, and thereby eliminate the single point of failure possible for the online redo log.
- An instance always requires at least two valid groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.) If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid; to see a redo log file's status, use the V$LOGFILE view. A redo log file becomes INVALID if Oracle cannot access it. It becomes STALE if Oracle suspects that it is not complete or correct; a stale log file becomes valid again the next time its group is made the active group.
- You can drop an online redo log member only if it is not part of an active group. If you want to drop a member of an active group, first force a log switch to occur.
- Make sure the group to which an online redo log member belongs is archived (if archiving is enabled) before dropping the member. To see whether this has happened, use the Enterprise Manager ARCHIVE LOG command with the LIST parameter.

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

## Controlling Checkpoints and Log Switches

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:

- Setting Database Checkpoint Intervals
- Forcing a Log Switch
- Forcing a Fast Database Checkpoint Without a Log Switch

### Setting Database Checkpoint Intervals

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.

#### Setting LOG_CHECK-POINT_INTERVAL

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:

- All online redo log files of the database instance are 512K.
- The operating system block size is 512 bytes.
- Checkpoints should occur when an online redo log file is half full.

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

#### Setting LOG_CHECKPOINT_TIMEOUT

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 Tuning manual.

For more information about the LOG_CHECKPOINT_TIMEOUT parameter when using the Oracle Parallel Server, see the Oracle8 Parallel Server Concepts and Administration.

### Forcing a Log Switch

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;

### Forcing a Fast Database Checkpoint Without a Log Switch

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 and Administration manual.

## Verifying Blocks in Redo Log Files

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

## Clearing an Online Redo Log File

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;

### Restrictions

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 SQL Reference.

## Listing Information about the Online Redo Log

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.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index