Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

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

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 Oracle7 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 Oracle7 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:

512K/redo log file
__________________ = approximately 1000 blocks/redo log file
512 bytes/OS block

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_CHECK-POINT_ 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 Oracle7 regarding checkpoints, see the Oracle7 Server Tuning manual.

For more information about the LOG_CHECKPOINT_TIMEOUT parameter when using the Oracle7 Parallel Server, see the Oracle7 Parallel Server Concepts & Administration manual.

For more information about setting LOG_CHECKPOINT_TIMEOUT when using Trusted Oracle7 in OS MAC mode, see the Trusted Oracle7 Server Administrator's Guide.

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 Server 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/O's (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 Server 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 Oracle7 Parallel Server. In a non-parallel server configuration, global and local checkpoints are identical.

See Also: For more information on forcing checkpoints with the Oracle7 Parallel Server, see the Oracle7 Parallel Server Concepts & Administration manual.

Contents Index Home Previous Next