Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Rybakova
Occasional Visitor

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

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
8 REPLIES
A. Clay Stephenson
Acclaimed Contributor

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

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
If it ain't broke, I can fix that.
Andreas D. Skjervold
Honored Contributor

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

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
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Rybakova
Occasional Visitor

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

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
Honored Contributor

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

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

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

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
Honored Contributor

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

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
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Rybakova
Occasional Visitor

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

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
Honored Contributor

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

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

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!