Re: checkpoint not complete

On 31 May 1996 16:46:03 GMT, bfntd_at_aimnet.com (Tony Damon) wrote:

>Chuck Hamilton (chuckh_at_dvol.com) wrote:
>: What happens when the database tries to do a log switch and encounters
>: a "Checkpoint no complete" error. Does it wait for the checkpoint to
>: complete before switching to the next log? If so does this mean that
>: all sessions that're changing the database freeze until the checkpoint
>: completes?
>:
>
>Don't know.

Yes it will, all activity pretty much stops while dbwr is given the go ahead to go full steam and write dirty blocks.

>
>: I get this error frequently on a data warehouse when doing it's
>: monthly update. I'm using 4 online redo log groups (2 files per group)
>: each 1m in length, and noarchivelog mode. Would adding another log or
>: changing the size of the logs help?
>: --
>
>I believe Thomas Kyte has stated using very large redo log files
>will reduce/eliminate this "problem". He uses up to 25M.

4 meg is very small for a large load. Optimally you would have as much log as needed to carry you through the heavy period.

You can dynamically add log files whenever you want (as well as drop them). If you don't want the logs to always be there, you can create a couple before the load and drop them after.

I myself use 2 25meg log groups on a moderate use database. Your mileage will vary.

I would look into creating larger log files. Increase their size until the checkpoint not complete goes away.

There are lots of tradeoffs between lots of little log files and a few big ones. A couple of things to consider are:

• a few large log files may increase your time to recover in the event of a system.
• a lot of small log files will cause checkpoints to happen frequently, not bad if you are creating new data; bad if you are updating existing data (you might flush a block 100 times since checkpoints happen more often instead of just one time -- if you update the same block a lot during your load)

>
>Tony Damon
Received on Fri May 31 1996 - 00:00:00 CEST