Checkpoint not complete

[#1]

Dear all,

After four Oracle 10 upgrades, the only remaining warning I have on db13 in all databases I upgraded is about "checkpoint not complete" . This only happens when there is a lot of activity in the database . It never happened before though, even when there was a lot of activity in the database.

I found note #79341 and #1068186 and I have set DISABLESELFTUNE_CHECKPOINTING

to FALSE, but this didn't particularly help, still get the warnings

My questions are:

The log buffer size is increased by the upgrade to 14M (instead of 1M that is used to be). I have 2 groups of redo log files, with 2 files each. Each file is 20M on size.

I was thinking of increasing them to 30M each. Do you think that this is a good thing to do?

Under /oracle/SID/saptrace/background, I see in the log writer trace the messages below. Do you know why I get these messages and are they relevant to the issue I have with the checkpoint?

• SERVICE NAME:() 2007-08-09 09:56:38.059

• SESSION ID:(316.1) 2007-08-09 09:56:38.059

Maximum redo generation record size = 197120 bytes

Maximum redo generation change vector size = 190204 bytes

tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x10)

tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x10)

• 2007-08-09 12:55:29.062

LGWR: Archivelog for thread 1 sequence 3662 will NOT be compressed

• 2007-08-09 14:56:22.902

LGWR: Archivelog for thread 1 sequence 3663 will NOT be compressed

• 2007-08-09 15:09:29.136

LGWR: Archivelog for thread 1 sequence 3664 will NOT be compressed

• 2007-08-09 18:25:38.287

Many thanks

Andreas

[former_member84399]

Hello all, thank you for your replies so far, I awarded some points

I have had 9 systems upgraded so far. I am playing with different combinations of redo size, redo groups numbers and DISABLESELFTUNE_CHECKPOINTING. Here are my findings

Changing the DISABLESELFTUNE_CHECKPOINTING parameter hasn't made much difference anywhere. Whether set to true, false or reset (not sure what the default value is then as I can not see it in db03 or using show parameter from sqlplus), I have loads of checkpoint not completed messages in my busy systems

I had 4 redo log groups initially consisting of 1 redo log each of 20 M

Initially, I increased each redo log files size to 50M, the warnings were reduced (from 8-10 per day to 2-3). I then added 2 more groups of 1 redo log each (50M again). I now have none or 1 warning per day there

Vinod, if you can copy and paste me the extract from the database upgrade guide that is talking about the size of the redologs I will be grateful. I tried to look for it both in the SAP Oracle 10 upgrade guide and the Oracle guide from Metalink but I couldn't find it

Eric, or anybody else that understands the term "the database is fluctuating" from note 1068186, could you please explain it here?

Many thanks

Andreas

[Former Member]

Hi again

One should not guess but i read the german version of the note as well, and i am pretty sure that 'fluctuating' means that the database is still aligning with its typical load patterns. As i already mentioned this most probably has to do with the other sort of checkpoints and not log switch checkpoints. These other (incremental) checkpoints can be tuned with various parameters (log_checkpoint_timeout / fast_start_mttr_target).

But in your case we have log switch checkpoints, which can only be controlled by the size of the redo logs. As your observations say you already reduced the not complete messages significantly. I am quite sure that they will go away completely when you further increase the size. 50mb is still tiny as on a fast system the logwriter can write 20mb or more per second under load. So if you have 4 redo groups of 50mb this would give 200mb divided by 20mb/s = 10s. So DBWR either has to write all changed blocks covered in the first group to the datafiles in 10s <b>or</b> a checkpoint not complete occurs.

In worst cases a single redo entry like <i>update bigtable set counter = counter + 1</i> could change millions of table blocks and generating millions of undo blocks which have to be written by the DBWR.

<b>Redo log switches should occur once per minute under load. If log_checkpoints_to_alert is set to true you can see log switches in the alert log.</b>

If you have logswitches under one minute when the checkpoint not complete occurs, then your log size is most probably still to small.

Of course if nobody complains of the now sporadic not complete messages, and you do not experience system hangs at crucial times, you should not worry to much about them anymore.

Best regards

Michael

[Former Member]

BTW forgot, to see if an underscore parameter is set you can either do:

[code]SQL> show parameter disableselftune_checkpointing[/code]

This should work in 10g, otherwise do:

[code]SQL> select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"

from x$ksppi a, x$ksppcv b, x$ksppsv c

where a.indx = b.indx and a.indx = c.indx

and ksppinm ='_disable_selftune_checkpointing';[/code]

[Former Member]

if your system had big loads in certain periods, I would advise using 6 or 8 regolog groups of 200MB each. If this is not enough, come back to this topic.

20MB was really not enough.

50 MB is OK for small SAP systems.

200MB is usually enough for most SAP systems.