Re: 10gR1 - Checkpoint not complete -

On Oct 27, 10:49 am, p.santos..._at_gmail.com wrote:
> Hey guys,
> I thought I try to get some help in trying to get this problem
> resolved.
> I've struggled with the 'Checkpoint not complete' error message
> before, and thought I had it
> resolved, but I can't seem to make it go way.
>
> I'm currently running 10.1.0.4 on Solaris 64bit.
> From my analysis of this issue, I get the "Checkpoint not complete"
> message nearly evertime
> during a log switch. From looking at the checkpoint error timestamps
> and the log switches,
> 99% of the time the CKPT process fails during the log swith.
>
> I've always been reluctant to checkpoint too much, but maybe I need
> to increase checkpoints,
> sot that the entire process is faster during log switches ?????
>
> Here is the data. (log siwtches are occurring around 20+ minutes)
>
> V$LOG
> =================
>
> GROUP#| SEQUENCE#| MBYTES|ARC|STATUS |FIRST_TIME
> |LASTED_MIN
>
> ----------|----------|----------|---|---------------|--------------|-------­---
> 1| 32198| 600|YES|INACTIVE |10/27/06 09:24|
> 21
> 2| 32199| 600|YES|INACTIVE |10/27/06 09:44|
> 26
> 3| 32200| 600|NO |CURRENT |10/27/06 10:11|
> 6
> 4| 32193| 600|YES|INACTIVE |10/27/06 07:12|
> 27
> 5| 32194| 600|YES|INACTIVE |10/27/06 07:39|
> 26
> 6| 32195| 600|YES|INACTIVE |10/27/06 08:05|
> 40
> 7| 32196| 600|YES|INACTIVE |10/27/06 08:45|
> 29
> 8| 32197| 600|YES|INACTIVE |10/27/06 09:14|
> 10
>
> ALERT_LOG (timestamps match log swith)
> =================
> --- These all happen during log switch...
>
> Fri Oct 27 08:05:31 2006
> Thread 1 cannot allocate new log, sequence 32195
> Checkpoint not complete
> Current log# 5 seq# 32194 mem# 0: /z1/oradata/mail/redo_5_5.log
>
> Fri Oct 27 08:45:18 2006
> Thread 1 cannot allocate new log, sequence 32196
> Checkpoint not complete
> Current log# 6 seq# 32195 mem# 0: /z0/oradata/mail/redo_6_6.log
>
> Fri Oct 27 09:24:21 2006
> Thread 1 cannot allocate new log, sequence 32198
> Checkpoint not complete
> Current log# 8 seq# 32197 mem# 0: /z0/oradata/mail/redo_8_8.log
>
> Fri Oct 27 09:44:59 2006
> Thread 1 cannot allocate new log, sequence 32199
> Checkpoint not complete
> Current log# 1 seq# 32198 mem# 0: /z1/oradata/mail/redo_1_1.log
>
> Fri Oct 27 10:11:13 2006
> Thread 1 cannot allocate new log, sequence 32200
> Checkpoint not complete
> Current log# 2 seq# 32199 mem# 0: /z0/oradata/mail/redo_2_2.log
>
> KEY PARAMETERS
> =====================
>
> NAME |TYPE |VALUE
> -------------------------------------------|----------------|--------
> log_checkpoint_interval |integer |80000
> log_checkpoint_timeout |integer |1800
> fast_start_mttr_target |integer |0
> log_buffer |integer |524288
> disk_asynch_io |boolean |TRUE
>
> ** Haven't configured fast_start_mttr_target because log_checK*
> parameters
> were already set when I upgrade from 8.1.7 and so It was just one less
> thing to
> worry about configuring...
>
> AWR report from 9-10 AM this morning
> ========================
> Statistic |Total
> ----------------------------------------------------------- | ......
> DBWR checkpoints | 4
> background checkpoints completed | 3
> background checkpoints started | 2
>
> - the way I see it I'm probably not checkpointing enough .. which
> causes the ckpt
> process during log switches to take too long .. is this accurate ?
>
> BTW - fixing application to generate less redo, has been explored and
> we can't do any more on that front .... We generate anywhere between
> 200K - 1MB of redo per second ... averaging out around 600-700K per
> second.
>
> as always any feedback is helpful...
>
> -peter

Peter, if I am reading your post correctly your online redo logs are 600M. If the log block was meansured in 8k database blocks then you have set the interval to about 625M but if the OS block size behind this parameter is only 512 bytes then you have set it to around 40M. I suggest setting the value up and see if it makes any difference.

>> LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks. <<

HTH -- Mark D Powell -- Received on Fri Oct 27 2006 - 13:25:17 CDT