Re: Why does an online redolog keeps active after checkpoint completion?

"Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message news:3f2ad39d$0$49098$e4fe514c_at_news.xs4all.nl...
> I found this behaviour that I don't understand
> Oracle V8.1.7.3 EE 64-bit on HP-UX 11i. (and in may playground Windows 98,
> V8.1.5 Personal Edition).
>
> When a log switch occurs the redolog status goes from current to active
> (v$log. status) and a checkpoint occurs.
> When I check v$datafile_header.checkpoint_change# and checkpoint_time I
can
> see the checkpoint has completed.
> But the redolog status keeps active, even minutes later.
> After 'alter system checkpoint' it is immediately inactive.
>
> Why does the status of the redolog keeps active so long?
> I thought it was no longer needed (status becomes inactive) when the
> checkpoint completes?
> The impact can be that I still need that redo in the unfortunate event I
> loose this redolog group completely (which ofcourse should never happen,
but
> if all that never should happen really never happended we would not have
so
> much fun in this newsgroup).

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

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.

Cheers,
Dave Received on Sat Aug 02 2003 - 12:19:42 CDT