Why does an online redolog keeps active after checkpoint completion?
Anton Buijs
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).
quarkman
> Well, if your database is in Archivelog mode, after the log switch the
> online redo log is still active until its archiving process is finished.
>
> Regards,
>
> Yago
>
Incorrect. A log file has a status of active until its been checkpointed.
That's one thing. Then it has a status of archived or unarchived. That's
another thing. You can see that quite clearly in v$log... there's a column
called STATUS ('active', 'inactive' or 'current') and another column
entirely called 'ARCHIVED' (yes or no). Two different causes, two
different scenarios. And your suggestion that the one affects the other is
quite wrong.
The source of confusion for the original poster is simply that in 8i (if
not in 8.0??) Oracle made a change: a log isn't inactive until it's been
switched away from, been checkpointed *and the next log's been switched
away from, too*. Which means our friend needs to switch twice before the
original log appears as inactive.
It's been documented for a while, too.
~QM
> "Anton Buijs" <remove_a...@xs4all.nl> wrote in message
> news:3f2ad39d$0$49098$e4fe...@news.xs4all.nl...
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
Dave Hau
news:3f2ad39d$0$49098$e4fe...@news.xs4all.nl...
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
Anton Buijs
Dave Hau <davehau-n...@no-spam.netscape.net> schreef in berichtnieuws
OoSWa.122$6d2...@newssvr22.news.prodigy.com...
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.
quarkman
wrote:
> |
>
> 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.
It should be pointed out, however, that setting L_C_TIMEOUT is not a
particularly good idea. In 8i, you have L_C_INTERVAL and
FAST_START_IO_TARGET to work with to make instance recovery time extremely
predictable. And in 9i, you have FAST_START_MTTR_TARGET to use, which
internally gets converted into settings for _IO_TARGET and _INTERVAL -but
not for _TIMEOUT. Which gives the game away: _TIMEOUT is not particularly
useful in limiting instance recovery times in a predictable fashion.
In the absence of _TIMEOUT, behaviour is as I described earlier: another
log switch will cause the original log's status to be updated.
~QM
Anton Buijs
quarkman <quar...@myrealbox.com> schreef in berichtnieuws
oprta1dwt1zkogxn@haydn...
You are right. I know how to set these parameters. But for the purpose of
testing and insight in this behaviour I used an absolutely wrong value for
log_checkpoint_timeout of 10 and 30 sec. Just to observe the behaviour.
But thanks for your concerns.
Dave Hau
news:oprta1dwt1zkogxn@haydn...
If L_C_INTERVAL is set to a value less than the redo log file size (by
either directly setting the parameter, or indirectly setting it via setting
FAST_START_MTTR_TARGET as you mentioned), you'll get another checkpoint
before the next log switch, in which case the original log's status in the
control file might get updated before the next log switch.
Cheers,
Dave