Control Log Switches and Checkpoints

As a DBA, it is important for you to know how to control log switches and checkpoint frequency. This section provides information on how to do this.

Log Switches

Log writer writes to the online redo log files sequentially. When the current active online redo log group is filled, log writer begins writing to the next group. This is called a log switch. When the last online redo log available is filled, LGWR returns to the first redo log group and starts the process all over again.

Checkpoints

During a checkpoint, a number of dirty database buffers (buffers whose contents haven't yet been written out to disk) covered by the redo log file that is being checkpointed are written to the data files by DBWn. This number of dirty database buffers is determined by the value set to the FAST_START_MTTR_TARGET initialization parameter. This initialization parameter is covered in greater detail in preparation for the Oracle 9i DBA Fundamentals II test.

The checkpoint is handled by the CKPT background process that updates all the headers on all the data files and control files to reflect that the checkpoint has completed successfully.

A checkpoint occurs at the following times:

• At every log switch

• Whenever the instance shuts down normally, transactionally, or immediately

• Whenever forced by the setting of the FAST_START_MTTR_TARGET

• When manually requested by the DBA

• Whenever an ALTER TABLESPACE command is run with any one of the following options: OFFLINE NORMAL, READ ONLY, or BEGIN BACKUP

Information concerning every checkpoint is recorded in the alert_SID.log file in the BACKGROUND_DUMP_DEST location if the LOG_CHECKPOINTS_TO_ALERT initialization parameter is set to trUE. The default is FALSE for this parameter because it could make the alert log prohibitively large if every checkpoint were to be written out. This default behavior can be changed if you have a reason to do so.

Force Log Switch

Log switches are done automatically periodically, but as a DBA you can force a log switch to occur with the ALTER SYSTEM command as follows:

 ALTER SYSTEM SWITCH LOGFILE; 

Force Checkpoint

Checkpoints are also done automatically periodically by the database but can be forced using either the FAST_START_MTTR_TARGET parameter or the ALTER SYSTEM command as follows:

 ALTER SYSTEM CHECKPOINT; 

FAST_START_MTTR_TARGET is a centralized means of controlling the deprecated parameters FAST_START_IO_TARGET and LOG_CHECKPOINT_TIMEOUT.

FAST_START_MTTR_TARGET=500 means that instance recovery should not take more than 500 total seconds. The database adjusts other parameters behind the scenes to accomplish this goal.

FAST_START_IO_TARGET and LOG_CHECKPOINT_TIMEOUT must not be used if you are using the FAST_START_MTTR_TARGET parameter.

Now that you understand how to control log switches and checkpoints, we will look at multiplexing and maintaining the redo log files.