David Hau explained this much better and in much more clear
fashion then me. Here is the most important part from the usenet
thread he was referring you to:
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:
- v$log.status gets its redo log info from the *control file*
- 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:
- 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.
- 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.
- 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):
- 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.
- 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
On 01/30/2004 03:44:29 PM, A Joshi wrote:
> Mladen,
> Thanks for info. So all the dirty blocks need to be written to disk
> after each checkpoint. After that is done the status becomes
> 'INACTIVE'. Just that sometimes this is very unpredictable.
> My question : If a log switch always causes a implicit checkpoint
> then
> what is the need for this explicit checkpoint to be given? Thank you.
>
> Mladen Gogala <mladen_at_wangtrading.com> wrote:
> On 01/30/2004 01:24:26 PM, A Joshi wrote:
> > Hi,
> > In view v$log there is a column status. This changes from current
> > (if the redo log is in use) to atcive then to inactive.
> Documentation
> > says :
> >
> >
> > ACTIVE: The log is active but is not the current log. It is needed
> > for
> > crash recovery. It may be in use for block recovery. It might or
> > might
> > not be archived.
> >
> > INACTIVE: The log is no longer needed for instance recovery. It may
> > be
> > in use for media recovery. It might or might not be archived.
> >
> > So if it is in 'ACTIVE' status : it means it is needed for
> > crash/instance recovery? So what does it mean? That changes in this
> > log are still not written to data files? What else? Is this related
> > to
> > delayed block cleanout etc or no connection?
> >
> > What factor affects how long it will be in ACTIVE state before
> going
> > to INACTIVE? What can be changed to control how long it takes. Any
> > trade off?
> >
> > Any detailed explanation on this will be greatly appreciated. You
> can
> > mail me direct or to the list. Thank you.
> >
> Status of active means that log has been switched, but not all of the
> changes are written to the disk. It is, essentially, saying that DBWR
> has some more work to do to catch up. The only log that is open by
> the
> instance is the one marked "CURRENT". Here is an example:
> SQL> select group#,status from v$log;
>
> GROUP# STATUS
> ---------- ----------------
> 1 CURRENT
> 2 INACTIVE
> 3 INACTIVE
>
> SQL> alter system switch logfile;
>
> System altered.
>
> SQL> select group#,status from v$log;
>
> GROUP# STATUS
> ---------- ----------------
> 1 ACTIVE
> 2 CURRENT
> 3 INACTIVE
>
> So, after the checkpoint, the CKPT has updated log file headers,
> corresponding markers and termination records are written to the
> file,
> data file headers have been updated and so has been the control file.
> DBWR still has some work to do because there are still unwritten
> blocks modified by transactions whose log records are in the "ACTIVE"
> file. DBWR will take its time to write them down.
> The only log file open by the instance is redo02:
>
> [root_at_tux925 root]# fuser /data/db/OraHome1/oradata/compldb/*.log
> /data/db/OraHome1/oradata/compldb/redo02.log: 6019
> [root_at_tux925 root]#
>
> After another checkpoint, everyhing is back to normal:
>
> SQL> alter system checkpoint
> 2 /
>
> System altered.
>
> SQL> select group#,status from v$log;
>
> GROUP# STATUS
> ---------- ----------------
> 1 INACTIVE
> 2 CURRENT
> 3 INACTIVE
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free web site building tool. Try it!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mladen_at_wangtrading.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 30 2004 - 15:24:26 CST