sequence of events when a log switch happens

Sequence of events when a log switch happens

This makes sense if you think about where the various v$ dynamic performance views get their info from, and which Oracle background process is responsible for each task. First, note that:

1. v$log.status gets its redo log info from the *control file*
2. v$datafile_header.checkpoint_change# and checkpoint_time get their info
from the *datafile headers*.
3. v$datafile.checkpoint change# gets info from control file.

Here's the sequence of events when a log switch happens:

1. LGWR switches to the next redo log file, changes the status of the previous redo log file from CURRENT to ACTIVE in the control file, and signals DBWR to do a checkpoint on the previous redo log file.
2. When DBWR finishes with the checkpoint, it signals CKPT to update datafile headers and update checkpoint info (only) in the control file. This is the info read by v$datafile_header.checkpoint_change# and checkpoint_time.
Note that CKPT does not update redo log info in the control file. It only deals with checkpoint info, as its name implies.
3. When CKPT is done, it signals LGWR to update the redo log status in the control file from ACTIVE to INACTIVE. This is the info read by v$log.status. This update task is a low priority item for LGWR because the only process that cares about whether the redo log status is active or not is LGWR itself. The redo log status tells LGWR whether it can reuse a redo log file or not (i.e. whether checkpoint has completed on that redo log file.) That is, by delaying this operation, LGWR is not blocking the work
of any other process.

LGWR will update the redo log status in the control file when any of these occurs (and others too, that I don't know of):

1. when LGWR periodically checks for compliance with the
LOG_CHECKPOINT_TIMEOUT parameter, which says that the checkpoint position should not lag behind the latest redo record by this amount of time.

2. when you issue a "alter system checkpoint" which is what you did.

So if you want the redo log status to be updated more quickly to inactive after a checkpoint, one way to do it is to decrease the value of LOG_CHECKPOINT_TIMEOUT in init.ora.