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 · Accepted solution]

Support team,

There are many different kind of checkpoints. The checkpoint that is issued with a logswitch is named "log switch checkpoint" in the alert log. If you issue manually an "alter system checkpoint" command, then you will see in the alert log that a "global checkpoint" is issued, which is executed immediately and turns the status of all ACTIVE redo logs to INACTIVE. These INACTIVE redo logs are no longer needed for recovery and can be dropped.

Now Oracle 10 does not trigger a full (global) checkpoint on logswitch (I think Oracle 9 used to do that after 2 consecutive log switches). Oracle 10 issues a global checkpoint when it tries to switch to an already ACTIVE redo log. The log switch checkpoint that is triggered by a log switch appears to have low priority and the database writer doesn't pick it up immediately like it does for the global checkpoint. I have seen checkpoints like this taking 3-4 minutes to complete while the data to be saved is only 50M so I can't believe that it is because of slow db writer.

Andreas