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

Hi Andreas,

Thanks for the infomation. 1 more question which will clear my doubt on this checkpoint issue. I understand that increasing the redo log size will help because it will reduce the dbwR writing frequency. Increasing the number of dbwR also aid in the process. How about adding a new redo log grp ? How does this contribute to the "checkpoint not complete" issue ?

Regards

Lauran

[Former Member]

Hello Lauran,

Increasing the number of redo log groups helps as well. It is kind of equivalent to increasing the size of the redo logs. When you have more redo log groups, it takes more time for the log switches to "arrive" to the same redo log. Therefore, the db writer has more time to execute the checkpoint until the same redo log is used again.

For example, say you have 4 redo log groups called 11, 12, 13, 14. 11 is current, then a log switch happens. A checkpoint is issued when the log switch happens and the dbwriter starts working on writing the "dirty" blocks in the datafiles. In the meanwhile, the database is working and changes are recorded to redo log 12, a switch is taking place, now 13 is current, a bit later 14 is current and after this again 11. If you only had 2 redo log groups , 11 and 12 , the checkpoint on 11 will need to be completed while 12 is being written. If you have 4 like the example above, the db writer has more time ( the time that 12, 13, 14 ) take to be full.

Note 79341 shows how to increase the size of redo logs and the number of redolog groups

I hope this helps

Andreas