| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Fwd: Re: ** v$log.status
>Date: Sat, 31 Jan 2004 10:57:25 +0800
>To: ORACLE-L_at_fatcity.com
>From: Hemant K Chitale <hkchital_at_singnet.com.sg>
>Subject: Re: ** v$log.status
>
>
>LGWR will always write to the redo-logs in a "round-robin" fashion. So,
>on completing Group2, it would wait
>for Group3 to be INACTIVE. {One exception is when you ADD a new Log
>Group. If you added a new log
>file without specifying a Group#, it would be allocated Group#4, would be
>set to status UNUSED and would
>get used before Group3 if LGWR is still currently in Group2}
>
>In terms of disk space "many smaller log groups" is the same requirement
>as "few large log group".
>Larger log groups are preferred by many DBAs so as to reduce the frequency
>of the Log Switch Checkpoint.
>{provided, of course, that LOG_CHECKPOINT_INTERVAL is set to higher than
>the size of the log files}.
>In scenarios where you have Standby Database and wish to propagate Archive
>Logs frequently to
>the Standby Site, you would have to accept frequent Log Switches {however,
>this does not mean that
>the Log Files have to be small, they could still be large}
>
>Hemant
>
>At 03:59 PM 30-01-04 -0800, you wrote:
>>Thanks Mladen and David
>>
>> It is great to get such detailed info.
>>If we take your example of three redo logs then you could have a
>>situation where one is is 'CURRENT' status and two are in 'ACTIVE'
>>status. As follows :
>>
>>GROUP# STATUS
>> ---------- ----------------
>> 1 ACTIVE
>> 2 CURRENT
>> 3 ACTIVE
>> Now if group 2 ('CURRENT') fills up or if 'alter system switch
>> logfile' command is given then it will try to go to the next one. Since
>> the next one (in fact both) is 'ACTIVE' it cannot be allocated and made
>> current. Right? So in that case will the database hang? Will it wait for
>> the next sequence redo log which is group 3 to get 'INACTIVE' or will it
>> allocate
>>group 1 if it becomes 'INACTIVE' before group 3.
>>
>>Basically given limited space is it advisable to have many smaller redo
>>log groups instead of Oracle suggested bigger redo logs which switch once
>>an hour.
>>
>>I agree it is unlikely and might as well just get extra space but the
>>point is to put in the best possible setup and be ready for crazy
>>activity instead of just going by the 'thumb rule' of having one log
>>switch per hour.
>>
>>Thank you again
>>
>>Mladen Gogala <mladen_at_wangtrading.com> wrote:
>>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:
>>
>>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 i! n 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
>>**************************************************************************
>>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 alway! s causes a implicit checkpoint
>> > then
>> > what is the need for this explicit checkpoint to be given? Thank you.
>> >
>> > Mladen Gogala 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 recover! y? 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.lo! g: 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).
>>
>>Do you Yahoo!?
>>Yahoo! SiteBuilder - Free web site building tool.
>><http://us.rd.yahoo.com/evt=21608/*http://webhosting.yahoo.com/ps/sb/>Try it!
>
>Hemant K Chitale
>Oracle 9i Database Administrator Certified Professional
>http://hkchital.tripod.com {last updated 24-Jan-04}
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
http://hkchital.tripod.com {last updated 24-Jan-04}
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jan 30 2004 - 20:58:00 CST
 |
 |