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]

Thank you for your answers, I awarded some more points

I think I have my problem solved, 4 groups of 100M each has done the trick. I noticed a strange behaviour while resizing the redo logs, which might be the cause of the problem.

I understand that a log switch is always triggering a checkpoint (and the opposite is not true). When I was dropping redo log groups, I got two different kind of errors. One was the the redo log group to be dropped can not be dropped because it is current. This makes sense and I easily issued an "alter system switch logfile" statement.

SQL> ALTER DATABASE DROP LOGFILE GROUP 13;

ALTER DATABASE DROP LOGFILE GROUP 13

*

ERROR at line 1:

ORA-01623: log 13 is current log for instance BWT (thread 1) - cannot drop

ORA-00312: online log 13 thread 1: '/oracle/BWT/origlogA/log_g13m1.dbf'

ORA-00312: online log 13 thread 1: '/oracle/BWT/mirrlogA/log_g13m2.dbf'

SQL> alter system switch logfile;

System altered.

Then, when I tried to drop the same redo log group (that was not current any more, as I had just switched to the next one) I got the message

SQL> ALTER DATABASE DROP LOGFILE GROUP 13;

ALTER DATABASE DROP LOGFILE GROUP 13

*

ERROR at line 1:

ORA-01624: log 13 needed for crash recovery of instance BWT (thread 1)

ORA-00312: online log 13 thread 1: '/oracle/BWT/origlogA/log_g13m1.dbf'

ORA-00312: online log 13 thread 1: '/oracle/BWT/mirrlogA/log_g13m2.dbf'

So despite the switch, a checkpoint has not been issued yet. When I executed,

alter system checkpoint;

I could then drop log froup 13

I had similar issues with other log groups . What I do not understand is why I had to issue a manual checkpoint, I believed that the log switch should have issued a checkpoint itself.

Many thanks

Andreas

[Former Member]

I believed that the log switch should have issued a checkpoint itself.

well, a checkpoint is issued, but the work can take some time to execute. This work is being done by the CKPT process. so, this is normal behavior and is expected.

[Former Member]

<b>ORA-01624: log 13 needed for crash recovery of instance BWT (thread 1)</b>

This means that this group is still active and its logswitch checkpoint is not done yet.

Again a brief overview:

- every change generates redo (written to the current redo group) and changed blocks in the buffer cache (memory)

- everytime the redo log gets full a logswitch checkpoint is triggered, indicating DBWR to write all changed blocks to the data files, a new log group becomes current

- to loggroup stays active until the checkpoint completes (meaning all changed blocks covered in the redo are now written to disk. If the instance crashes before the checkpoint completed, this very log group would be needed for the recovery. Therefore you are not allowed to drop either a current, or an active log group.

So a checkpoint is mainly done by DBWR, the CKPT process only updates the file headers when a checkpoint completes.

Regards Michael