Question Re: Checkpoint not complete in DB13 in Technology Q&A

Checkpoint not complete in DB13

Former Member — Thu, 14 Feb 2008 16:38:53 GMT

Hi all,

I'm getting this warning message every day in the checkDB job of DB13 :

BR0976W Database message alert - level: WARNING, line: 193239, time: 2008-02-13 13.01.39, message: Checkpoint not complete BR0976W Database message alert - level: WARNING, line: 194490, time: 2008-02-14 00.32.58, message: Checkpoint not complete BR0976W Database message alert - level: WARNING, line: 194509, time: 2008-02-14 00.34.10, message: Checkpoint not complete BR0976W Database message alert - level: WARNING, line: 194630, time: 2008-02-14 01.25.17, message: Checkpoint not complete BR0976W Database message alert - level: WARNING, line: 194655, time: 2008-02-14 01.26.37, message: Checkpoint not complete

Here is the content of the alert file at line 194655

Thu Feb 14 01:26:37 2008 Thread 1 cannot allocate new log, sequence 104916 Checkpoint not complete Current log# 13 seq# 104915 mem# 0: G:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF Current log# 13 seq# 104915 mem# 1: H:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF

Can someone helps me to solve that problem ?

Many thanks,

Alex

Re: Checkpoint not complete in DB13

stefan_koehler — Thu, 14 Feb 2008 17:09:59 GMT

Hello Alex,

"Checkpoint not complete" means that a redolog file can not be used, because of the corresponding data in the buffer cache is not written down to the data files by the DBWR. (=checkpoint not complete, which is performed by a log switch)

This can have different reasons:

1) Many log switches in a short period of time

2) I/O performance problems

You can do 2 things now:

1) Increase your redolog files or add more redolog groups

2) Add more DBWRs

In your case the redolog files are switched too often - so the solution is point one.

-> 2008-02-14 00.34.10

-> 2008-02-14 01.25.17

-> 2008-02-14 01.26.37

Sapnote #309526 describes how you can increase your redolog files.

Regards

Stefan

Re: Checkpoint not complete in DB13

Former Member — Fri, 15 Feb 2008 10:24:48 GMT

Hi Stefan,

Redolog size is already set to 50M, do you think i can increase it to 70M for exeample.

Could you explain me the impact on system if i'll do that change ?

Thanks a lot.

Re: Checkpoint not complete in DB13

stefan_koehler — Fri, 15 Feb 2008 10:35:09 GMT

Hi Alex,

>> Redolog size is already set to 50M, do you think i can increase it to 70M for exeample

>> Could you explain me the impact on system if i'll do that change ?

You have to think about that your database is hanging, if a "Checkpoint not complete" occurs.

The impact: Your database will hang from time to time, if you do nothing.

So you have to increase your redologfiles to a adequate size:

I think that 70 MB are not enough. Specify a size, that redolog switchs are happening only every 10-20 minutes.

Regards

Stefan

Re: Checkpoint not complete in DB13

fidel_vales — Fri, 15 Feb 2008 12:17:23 GMT

Hi,

> Redolog size is already set to 50M,

what do you mean with "already".

50M is the default for new installations.

It has to be tuned, for small systems it could be OK, for big/busy systems is too small.

The biggest I've ever seen is 600Mb, but I'm sure there are bigger sizes.

The impact of changing the size (increasing it) => it will decrease the number of switches, increasing the probablitly that a checkpoint is finished before the redo log has to be used again.

Re: Checkpoint not complete in DB13

Former Member — Fri, 15 Feb 2008 13:28:15 GMT

I use 200M as redolog file size.

and I use more than 4 groups. so, itall depends on the size of your system and most importantly, the load on it.

start with 6 groups of 200M each.

Re: Checkpoint not complete in DB13

Former Member — Wed, 20 Feb 2008 10:41:59 GMT

Follow the instructions below

1. Check at first if the problem only happens at times with an extraordinary high amount of redo logs.Check if it is possible to reduce the amount of redo log information being generated. If e.g. the "checkpoint not complete" situation only happens during online backups, you should make sure that the tablespaces are set into backup mode sequentially and not in parallel.

2. Check if there is a temporary or permanent I/O bottleneck related to DBWR. Possible indications are high values for the wait events "write complete waits", "free buffer waits" or "db file parallel write. In addition you can perform an ORADEBUG trace of the DBWR process. In case of doubts please also contact your hardware vendor. Especially if the database seems to get stuck with "checkpoint not complete" for a longer time (> 1 minute), (temporary) I/O problems are likely.

3. If "checkpoint not complete" occurs sporadically especially during times of high system load, you can increase the amount of redo log space or the number of DBWR processes:

a) Increase the size of the redo log files (e.g. double size) Increasing the size of the redo log files is particularly useful if there are small time frames between the log switches (< 1 minute during peak load). The disadvantage of big redo log files is the lower checkpoint frequency and the longer time Oracle needs for an instance recovery.

Hope this helps

Farooq

Re: Checkpoint not complete in DB13

Former Member — Tue, 11 Mar 2008 06:51:10 GMT

Hi,

Note 79341 - Checkpoint not complete

Try this one also.

Regards

Vijay