RE: checkpoint incomplete issue

On Fri, 19 Nov 2004, Freeman Robert - IL wrote:

> Hmmmm... maybe I was wrong, or the behavor has changed at some point and
> time (7 vs 8??).... looking at my 9i and 10g alert logs, it appears that the
> checkpoint not complete messages only appear if it can't switch logfiles.
>
> Maybe I'm just getting to old and senile.

Well that may be the case with me as well. I wrote the article in 1932 based on Oracle 7.3.2 on a hand-cranked granite computer. But I don't think the fundamental rule has changed. "Cannot allocate new log - checkpoint not complete".

You can have 10 logs, and switch through them all without completing a checkpoint (if using LOG_CHECKOINT_INTERVAL it will happily start over with a new checkpoint at each log switch even if the previous one is not complete), until you get to the last log, and they are all status ACTIVE, at which point it runs out of logs that are INACTIVE and has nowhere to switch to. Then you get CNC.

This problem, once rife, is a real rarity these days.

Often someone has decided to use an immature filesystem and it serializes on file access (defeating multiple DB writers).

Another fave is the old software mirroring across arrays. That software mirroring software (like VxVM) goes into full resync mode if the machine crashes or the mirror gets otherwise out of sync. This can suck up huge quantities of available IO on a running production system.

Finally, most people who think they are using async IO are not, due to incorrect filesystem, OS kernel or volume manager configuration. The only way to verify is to use a system call tracer to see what flags the DB writer is using to open the file. There are numerous platform and filesystem-specific recipes on MetaLink for getting async to work and making sure it is really on.

--
Jeremiah Wilton
Independent Oracle Professional
Oracle Certified Master
Disaster Recovery - Seminars - Technical Interviews
http://www.speakeasy.net/~jwilton
--
http://www.freelists.org/webpage/oracle-l