Re: ** v$log.status
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: [EMAIL PROTECTED] root]# fuser /data/db/OraHome1/oradata/compldb/*.log /data/db/OraHome1/oradata/compldb/redo02.log: 6019 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: ** v$log.status
There is a pretty good explanation in this usenet thread: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=OoSWa.122%246d2.89%40newssvr22.news.prodigy.comrnum=1prev=/groups%3Fq%3Dv%2524log.status%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3DOoSWa.122%25246d2.89%2540newssvr22.news.prodigy.com%26rnum%3D1 Regards, Dave [EMAIL PROTECTED] 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. Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://us.rd.yahoo.com/evt=21608/*http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: ** v$log.status
Mladen, Thanks for info. So all the dirty blocks need to be written to disk after eachcheckpoint. After that is donethe status becomes 'INACTIVE'. Just that sometimes this is very unpredictable. My question : Ifa log switch always causes a implicit checkpoint then what is the need for this explicit checkpoint to be given? Thank you.Mladen Gogala [EMAIL PROTECTED] 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 DBWRhas some more work to do to catch up. The only log that is open by theinstance is the one marked "CURRENT". Here is an example:SQL select group#,status from v$log;GROUP# STATUS-- 1 CURRENT2 INACTIVE3 INACTIVESQL alter system switch logfile;System altered.SQL select group#,status from v$log;GROUP# STATUS-- 1 ACTIVE2 CURRENT3 INACTIVESo, after the check! point, 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:[EMAIL PROTECTED] root]# fuser /data/db/OraHome1/oradata/compldb/*.log/data/db/OraHome1/oradata/compldb/redo02.log: 6019[EMAIL PROTECTED] root]#After another checkpoint, everyhing is back to normal:SQL alter system checkpoint2 /System altered.SQL select group#,status from v$log;GROUP# STATUS-- 1 INACTIVE2 CURRENT3 INACTIVE-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mladen GogalaINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
Re: ** v$log.status
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 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 ** 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] root]# fuser /data/db/OraHome1/oradata/compldb/*.log /data/db/OraHome1/oradata/compldb/redo02.log: 6019 [EMAIL PROTECTED] root]# After
Re: ** v$log.status
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 ACTIVE2 CURRENT3 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 sequenceredo log which is group 3 to get 'INACTIVE' or will itallocate 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 activityinstead of just going by the 'thumb rule' of having one log switch per hour.
Thank youagain
Mladen Gogala [EMAIL PROTECTED] wrote:
David Hau explained this much better and in much more clearfashion then me. Here is the most important part from the usenetthread he was referring you to:**This makes sense if you think about where the various v$ dynamic performanceviews get their info from, and which Oracle background process isresponsible 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 infofrom 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 theprevious redo log file from CURRENT to ACTIVE in the control file, andsignals DBWR to do a checkpoint on the previous !
redo log
file.2. When DBWR finishes with the checkpoint, it signals CKPT to updatedatafile headers and update checkpoint info (only) in the control file.This is the info read by v$datafile_header.checkpoint_change# andcheckpoint_time. Note that CKPT does not update redo log info in thecontrol 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 thecontrol file from ACTIVE to INACTIVE. This is the info read byv$log.status. This update task is a low priority item for LGWR because theonly process that cares about whether the redo log status is active or notis LGWR itself. The redo log status tells LGWR whether it can reuse a redolog file or not (i.e. whether checkpoint has completed on that redo logfile.) That is, by delaying this operation, LGWR is not blocking the workof any other process.LGWR will update the redo log status i!
n the
control file when any of theseoccurs (and others too, that I don't know of):1. when LGWR periodically checks for compliance with theLOG_CHECKPOINT_TIMEOUT parameter, which says that the checkpoint positionshould 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 inactiveafter a checkpoint, one way to do it is to decrease the value ofLOG_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 <[EMAIL PROTECTED]>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
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 [EMAIL PROTECTED] 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
