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

Dave Hau <davehau-no-spam-123_at_no-spam.netscape.net> schreef in berichtnieuws OoSWa.122$6d2.89_at_newssvr22.news.prodigy.com...
| "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
|

You are right. I varied the value of log_checkpoint_timeout in my playground database and the status active becomes inactive after a period of time that is related to the timeout period. Lgwr is choosing priorities as you described. I get the impression it also depends on the load on the db. My tests where the only active sessions. When I do some (update) work the active status disappears also much quicker. Thanks. Received on Sat Aug 02 2003 - 13:37:42 CDT