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

Dear all,

Thank you for your replies so far. In the meanwhile, I increased the size of my redologs from 20M to 50M. The checkpoint warnings still exist but they are significally reduced. I plan to put two more redo log groups (I only have two at the moment) and see what happens

Anyone knows why this behaviour in Oracle 10? The systems I am working on haven't had any increase in the user load and never had checkpoint warnings. I found notes 79341 and 1068186 but it is not clear to me what exactly happens for Oracle not to issue checkpoints as before. As I said, I have this issue directly after the upgrade to Oracle 10 and without any additional load to the system.

Many thanks

Andreas

[Former Member]

Team,

Up to my knowledge, in 10 G Yes it is recmdd to go for Big redos in place of small in 9i.Oracle upgrade Doc says that.So I guess we can say in 10g checkponting is fast? NOt sure !

Depending on the number of datafiles in a database, a checkpoint can be a

highly resource intensive operation, And Tuning checkpoints involves four key initialization parameters.'

And this issue I heard many times after fresh upgrade.

Regards

Vinod

[Former Member]

SAP note 1068186 explains why.

if you want to revert to Oarcfle 9i'w way, you can set the parameter "_disable_selftune_checkpointing" to the value "TRUE"

good luck.

[Former Member]

<b>Anyone knows why this behaviour in Oracle 10? The systems I am working on haven't had any increase in the user load and never had checkpoint warnings. I found notes 79341 and 1068186 but it is not clear to me what exactly happens for Oracle not to issue checkpoints as before. As I said, I have this issue directly after the upgrade to Oracle 10 and without any additional load to the system.</b>

We had exactly the same behaviour like you have when going from 9i to 10g. I have made the following observation:

- 9i: dbwr immediately starts to work after the first log switch checkpoint

- 10g: dbwr is kind of lazy and is not working a lot before a SECOND logswitch is pending

You can easily see what is going on, with querying v$log:

[code]SQL> select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

---

-

---

-

---

-

---

-

---

--- -

---

-

---

-

---

15 1 54069 1048576000 1 YES INACTIVE 1.8710E+12 27-AUG-07

16 1 54070 1048576000 1 YES ACTIVE 1.8710E+12 27-AUG-07

17 1 54071 1048576000 1 NO CURRENT 1.8710E+12 27-AUG-07[/code]

In this example there is one checkpoint - of group 16 - pending, therefore this redo is still active. When there is no inactive group, and the current is full, a checkpoint not complete occurs.

I am not very convinced that the disableselftune_checkpointing will help much in your case (but i could be wrong, so be free to try). dbwr must write all changed blocks covered in the group before the log group can be reused. If under load the log gets full, there is no way to prevent a log switch checkpoint (there are of course checkpoint events other than log switches).

Regards Michael