Oracle DATABASE HANGS CHECKPOINT DOES NOT COMPLETE ALL LOGS ACTIVE 1 CURRENT

[Rybakova]

We use Oracle 7.3.4.5 on HP-UX 10.20. We have problem with database, database hangs with the message "checkpoint does not complete" all logs are active, one is current. It was identified by Oracle as a bug no. 671842 which is caused by operating system. They recommend to find a patch for HP-UX 10.20. I tried to, but I didn't find any patch for this bug. Have you any idea, which patch it might have been?
Thanks in advance,

Ivana

[A. Clay Stephenson]

Hi,

The only patch I can remember that would definitely hang Oracle on 10.20 unless it is present is PHKL_16751. It's probably been superceded by now. I remember that it would hang dbwriter on HFS filesystems. It any event, this patch fixed so many problems that it should be installed.

Regards, Clay

[Andreas D. Skjervold]

Hi
I see from Oracle Metalink that this bug occured when the database(dbstart) is started by Omniback, and not when started manually. Is this the case with your problem as well?
Then maybe Omniback is to blame. I don't know if there are pathes for this problem though.

Andreas

[Rybakova]

I don't use Omniback, we start database manually. I also tried to apply patch no. PHKL_16751, but it didn't help. Problem still occurs.

[Volker Borowski]

Not sure if it helps,
but if you use more than one DB_WR, reduce it to one.
Volker

[Rybakova]

Thank you Volker, for your tip, but we use only one DB_WR unfortunately. We also tried to do a checkpoint manually, it didn't help either.

Ivana

[Alexander M. Ermes]

Hi there.
Is it possible, that your logfiles are too small ? Are you running archivelogmode with offline redo log files ?
If yes, it is possible, that the logwriter is busy writing several log writing requests.
As long as the contents of the online log is not written, the file will still be active.
Bring the database to singe user mode ( mount exclusive ) and enhance the log files.
This is just an idea.
Rgds
Alexander M. Ermes

[Rybakova]

Thank you Alexander for your tip,

we use 16 logs each 262MB big. Is it big enough? We don't use database in archive log mode.

Regards,

Ivana Rybakova

[Andreas D. Skjervold]

Hi again
I found this at Metalink:
---
The way to resolve incomplete checkpoints is through tuning checkpoints and
logs:

1) Give the checkpoint process more time to cycle through the logs
- add more redo log groups
- increase the size of the redo logs
2) Reduce the frequency of checkpoints
- increase LOG_CHECKPOINT_INTERVAL
- increase size of online redo logs
3) Improve the efficiency of checkpoints enabling the CKPT process with CHECKPOINT_PROCESS=TRUE
4) Set LOG_CHECKPOINT_TIMEOUT = 0. This disables the checkpointing based on
time interval.
5) Another means of solving this error is for DBWR to quickly write the dirty
buffers on disk. The parameter linked to this task is:

DB_BLOCK_CHECKPOINT_BATCH.

DB_BLOCK_CHECKPOINT_BATCH specifies the number of blocks which are dedicated
inside the batch size for writing checkpoints. When you want to accelerate
the checkpoints, it is necessary to increase this value.
---
I think option 1 is OK in your case as you have many large logs, but you might have to look into the frequency of checkpoints, which should not happen to often.
You should have a low number of log switches per hour (just a couple) and preferably the same (low) number of ckpt (as ckpt occures at log_switch)

Andreas