Re: checkpoint not complete

In article <31add637.5284444_at_news2.ios.com>, chuckh_at_dvol.com (Chuck Hamilton) writes:
>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?
>
>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?
>--
>Chuck Hamilton
>chuckh_at_dvol.com
>
>Never share a foxhole with anyone braver than yourself!

Chuck,

This usually happens when the online redolog needs to be archived, however the 'arch' process has not completed the physical write to the arch_dest the writing of the previous archived log. A # of reasons:

1. Slow disk i/o coupled with major increased trans rate in the db.
2. Very small redolog size, resulting in faster rollovers.
3. Incorrect setting of the log_checkpoint_interval in init<sid>.ora.

In very transaction intensive systems, I like to use large redolog sizes. A rule of thumb is to use a log size which will not rollover at the busiest times for about 15 to 20 actual mts, this should > than the actual time the archprocess writes out the file. The setting of the log_checkpoint_interval value should be > than the size of the redolog (asuming that chekpoints are not set on time) A word of caution on this however, in case of crash the recovery could be an issue.

Hope this helps,

--
Vikram Goel                                 Motorola email: vgoel_at_pts.mot.com
Sr. Oracle DBA - Consultant
Aerotek Inc.                                My email:  vgoel_at_emi.net

Motorola Info:
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426