当Oracle重用一个日志文件的时候,该日志文件所保护的处于Buffer cache中的脏块(dirty buffer),必须写回磁盘,且必须纪录checkpoint的位置在控制文件和数据文件头。这个过程叫做检查点checkpoint.
发生checkpoint not complete, cannot allocate new log ,表示要重用的日志文件的检查点还没有完成,被日志文件保护的脏块还没有完全被写回磁盘。必须等待该日志文件的checkpoint完成,才可以重新使用该日志文件。
V$LOG中STATUS为Active的,表示日志文件checkpoint未完成,Inactive表示checkpoint完成,current表示为当前LGWR写的日志文件。在实例恢复的时候,oracle需要使用到处于Active和Current状态的日志文件。
当因为检查点没有完成而不能重用日志文件的时候,从v$session_wait中可以看到很多session等待log file switch (checkpoint incomplete)事件,系统基本处于hang的状态。
我碰到一些情况是因为磁盘存在坏块,影响DBWR I/O.还需要注重其他问题
日志文件太少。
日志文件过大和检查点参数设置原因,导致检查点发生不频繁。
DBWR效率不高,I/O等待繁忙。
设置log_checkpoints_to_alert = TRUE后可以在alert.log中看到checkpoint发生情况,包括开始和结束标记。
here is the begin of the checkpoint on the data protected by log 2
Beginning log switch checkpoint up to RBA [0x1a01.2.10], SCN: 0x0000.644a1dbb
Thread 1 advanced to log sequence 6657
Current log# 3 seq# 6657 mem# 0: /redo02/oradata/eisp/redo3a.log
Current log# 3 seq# 6657 mem# 1: /extra01/oradata/eisp/redo3b.log
Arch finished copying log 2 for us here
Tue Feb 25 05:06:35 2003
ARC0: Beginning to archive log# 2 seq# 6656
Tue Feb 25 05:07:00 2003
ARC0: Completed archiving log# 2 seq# 6656
Tue Feb 25 05:51:12 2003
Beginning log switch checkpoint up to RBA [0x1a02.2.10], SCN: 0x0000.644a251e
Thread 1 advanced to log sequence 6658
Current log# 1 seq# 6658 mem# 0: /redo01/oradata/eisp/redo1a.log
Current log# 1 seq# 6658 mem# 1: /redo02/oradata/eisp/redo1b.log
Tue Feb 25 05:51:12 2003
ARC0: Beginning to archive log# 3 seq# 6657
Tue Feb 25 05:51:35 2003
ARC0: Completed archiving log# 3 seq# 6657
Tue Feb 25 06:39:05 2003
Thread 1 cannot allocate new log, sequence 6659
Checkpoint not complete Current log# 1 seq# 6658 mem# 0: /redo01/oradata/eisp/redo1a.log
Current log# 1 seq# 6658 mem# 1: /redo02/oradata/eisp/redo1b.log
Here is where the checkpoint finished up -- the one started for number 2
Tue Feb 25 06:56:30 2003
Completed checkpoint up to RBA [0x1a01.2.10], SCN: 0x0000.644a1dbBTue Feb 25 06:56:30 2003
Beginning log switch checkpoint up to RBA [0x1a03.2.10], SCN: 0x0000.644a2b29
Thread 1 advanced to log sequence 6659
Current log# 2 seq# 6659 mem# 0: /redo01/oradata/eisp/redo2a_NEW.log
Current log# 2 seq# 6659 mem# 1: /extra01/oradata/eisp/redo2b.log
有时候checkpoint完成需要很多时间,可能是DBWR有很多dirty buffer要写回磁盘。假如log files 500meg,它可能保护着几gigabytes的diry buffer(修改一block可能只需要几十byte redo,但DBWR checkpoint就要写8k的block)。这种情况下,推荐用较小的日志文件或者配置参数使checkpoint发生更频繁。