Re: log_checkpoint_timeout new meaning in 8i

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Thu, 04 Jan 2001 20:56:04 +0100
Message-Id: <10731.125754@fatcity.com>

--------------87797DD5A94B60D9C6AEC6B2
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Lisa,

I think that many people read LOG_checkpoint_interval when I believe that one should read log_CHECKPOINT_interval. The problem stems from the fact that at any given point in time, the database files lag behind the SGA - and the committed delta is stored to redo logs. If your database suddenly crashes, on startup this delta must be applied to the files - and then restored rollback segments must be used to rollback possibly uncommitted transactions. This may take time, and by forcing CHECKPOINTs, i.e. updating data file blocks with the most recent memory contents, you can guarantee that you will never have to roll forward more than log_checkpoint_interval seconds worth of transaction data. So what ? If no update occurs on your database, it means that SGA and datafile blocks stay in synch, and there is no reason to see any checkpoint occur either. And for redo log files, they only switch when full or explicitly told to do so - I have never found anything better to guarantee a maximum elapsed time between switches (for a standby database) than running a job issuing ALTER SYSTEM SWITCH LOGFILE when needed.

Regards,

Stéphane Faroult
Oriole Corporation

> hello all,

> I've got LOG_CHECKPOINT_TIMEOUT set to 1800 (30 minutes) in one of my
> databases. I also have LOG_CHECKPOINTS_TO_ALERT set to true.
> However, every morning I look at the archive logs and I see that 1.
> the logs haven't switched since the nightly batch quit and 2. there
> are no checkpoints in the alert log since the log switch. This makes
> me a little nervous.
>
> It seems that LOG_CHECKPOINT_TIMEOUT is not working. However, I find
> this on metalink that kind of explains it.
>
> The parameter log_checkpoint_timeout has been re-interpreted. In prior
> releases, every log_checkpoint_timeout seconds, Oracle started an
> interval checkpoint. Starting with Oracle 8.1, log_checkpoint_timeout
> will be interpreted to mean that the incremental checkpoint should be
> at the log position where the tail of the log was
> log_checkpoint_timeout seconds ago. In other words, the incremental
> checkpoint should lag the tail of the log by no more than
> log_checkpoint_timeout seconds worth of redo.
>
> I don't quite understand. Can someone paraphrase this? Does this
> mean that every LOG_CHECKPOINT_TIMEOUT seconds it makes sure that
> there is no redo that hasn't been written to disk that is more than
> LOG_CHECKPOINT_TIMEOUT seconds old? In other words, it checks to see
> if a checkpoint is necessary instead of just doing it?
>
> Thanks
>
> Lisa Rutland Koivu
> Oracle Database Administrator
> Qode.com
> 4850 North State Road 7
> Suite G104
> Fort Lauderdale, FL 33319
>
> V: 954.484.3191, x174
> F: 954.484.2933
> C: 954.658.5849
> http://www.qode.com
>
> "The information contained herein does not express the opinion or
> position of Qode.com and cannot be attributed to or made binding upon
> Qode.com."

--------------87797DD5A94B60D9C6AEC6B2
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html>
Lisa,
   I think that many people read LOG_checkpoint_interval when I believe that one should read log_CHECKPOINT_interval. The problem stems from the fact that at any given point in time, the database files lag behind the SGA - and the committed delta is stored to redo logs. If your database suddenly crashes, on startup this delta must be applied to the files - and then restored rollback segments must be used to rollback possibly uncommitted transactions. This may take time, and by forcing CHECKPOINTs, i.e. updating data file blocks with the most recent memory contents, you can guarantee that you will never have to roll forward more than log_checkpoint_interval seconds worth of transaction data.
   So what ? If no update occurs on your database, it means that SGA and datafile blocks stay in synch, and there is no reason to see any checkpoint occur either. And for redo log files, they only switch when full or explicitly told to do so - I have never found anything better to guarantee a maximum elapsed time between switches (for a standby database) than running a job issuing ALTER SYSTEM SWITCH LOGFILE when needed.

<p>Regards,
<p>St&eacute;phane Faroult
<br>Oriole Corporation
<blockquote TYPE=CITE><font face="Arial"><font size=-1>hello all,</font></font></blockquote>

<blockquote TYPE=CITE>I've got LOG_CHECKPOINT_TIMEOUT set to 1800 (30 minutes) in one of my databases.  I also have LOG_CHECKPOINTS_TO_ALERT set to true.  However, every morning I look at the archive logs and I see that 1. the logs haven't switched since the nightly batch quit and 2. there are no checkpoints in the alert log since the log switch.  This makes me a little nervous.  It seems that LOG_CHECKPOINT_TIMEOUT is not working.  However, I find this on metalink that kind of explains it.
The parameter log_checkpoint_timeout has been re-interpreted. In prior releases, every log_checkpoint_timeout seconds, Oracle started an interval checkpoint. Starting with Oracle 8.1, log_checkpoint_timeout will be interpreted to mean that the incremental checkpoint should be at the log position where the tail of the log was log_checkpoint_timeout seconds ago. In other words, the incremental checkpoint should lag the tail of the log by no more than log_checkpoint_timeout seconds worth of redo. I don't quite understand.  Can someone paraphrase this?  Does this mean that every LOG_CHECKPOINT_TIMEOUT seconds it makes sure that there is no redo that hasn't been written to disk that is more than LOG_CHECKPOINT_TIMEOUT seconds old?  In other words, it checks to see if a checkpoint is necessary instead of just doing

it?</font></font>
<p><font face="Arial"><font size=-1>Thanks</font></font>
<p><b><font face="Arial"><font size=-2>Lisa Rutland Koivu</font></font></b>
<br><font face="Arial"><font size=-2>Oracle Database Administrator</font></font>
<br><font face="Arial"><font size=-2>Qode.com</font></font>
<br><font face="Arial"><font size=-2>4850 North State Road 7</font></font>
<br><font face="Arial"><font size=-2>Suite G104</font></font>
<br><font face="Arial"><font size=-2>Fort Lauderdale, FL&nbsp; 33319</font></font>
<p><font face="Arial"><font size=-2>V: 954.484.3191, x174</font></font>
<br><font face="Arial"><font size=-2>F: 954.484.2933</font></font>
<br><font face="Arial"><font size=-2>C: 954.658.5849</font></font>

http://www.qode.com "The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."</blockquote> Received on Thu Jan 04 2001 - 13:56:04 CST