Checkpoint not complete

[former_member84399]

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 Member]

Hi Andreas

You can ignore the LGWR not compressed messages. If needed i can post the corresponding oracle metalink doc here, it is 284618.1. I don't hope you are on Oracle 10.1.0.X

Checkpoint not complete mostly happens, when the DBWR cannot keep up writing dirty blocks from the buffer cache to the database files. Every logswitch (redo log full, switch to next redo group) triggers a log checkpoint. Here is a general recommondation, please apply careful as this is depending on your system:

- if possible go to four redo log groups

- 20mb per redo log is tiny. if possible extend to 50mb.

I have seen systems with more than 20mb redo generated per second, they needed redo log sizes around 1gb. 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.

>> Beginning log switch checkpoint up to RBA [0x13d2.2.10], SCN: 44944154

With 4 groups and 50mb, there should be less pressure on the dbwr. Please do not hesitate to come back if you need further clarification.

Regards

Michael