I was looking at one of alert file of a development database and checking v$system_event
in alert log I found 2 Checkpoint not complete warnings
and in v$system_event total_waits of log file switch (checkpoint incomplete) is 98
Shouldnt these two values be same...? Or in v$system_event it records the waits of all processes that was waiting for this event?
"Checkpoint not complete" message causes due to the following one of the events:
1 When Redo log size is very small and it gets filled very qucikly. Then oracle activates DBWR to flush the dirty buffer.
At the same time, ARCH process tries to start reading the redo file to copy the redo log file into archive directory, but it fails.
2 When the log buffer is very small, or LOG_CHECKPOINT_INTERVAL is small, this error may occur.
3 When Hardware Level disk mirroring is configured and the REDO LOG file is placed on this disk, as well as Software level Mirrored for the REDO Log file is configured (duplexed), this error may occur, becuse when one redo log file is filled, Oracle thinks that it is the time to instruct ARCH process to do archiving, but OS did not copy the mirrored file (Hardware level set up), then ARCH process complaints that check point not complete. In general, duplexing can be done either at hardware level or software (Oracle) level. Both should not be configured. If you need, place the duplexing redo log file on the non-mirrored disk.
"98 waits" means Oracle experienced 98 times waits for switching the log files. But only in 2 times the check point was not completed. That is why you see 2 message in ALER.LOG file. As I said after the OS finishes copying (duplexing) the redo file only, ARCH process can start.
Do not get confused LOG SWITCH WAIT and CHECK POINT NOT COMPLETE. The statistics you see in V$SYSTEM_EVENT refers to former, and the error in ALERT.LOG refers to later.