Checkpoint not complete

数据库出现下面的错误：

Tue Apr 08 06:47:49 GMT+08:00 2014Thread 1 advanced to log sequence 5818 (LGWR switch)

  Current log# 2 seq# 5818 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 07:42:51 GMT+08:00 2014Thread 1 advanced to log sequence 5819 (LGWR switch)

  Current log# 1 seq# 5819 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 08:37:46 GMT+08:00 2014Thread 1 advanced to log sequence 5820 (LGWR switch)

  Current log# 2 seq# 5820 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 09:32:53 GMT+08:00 2014Thread 1 advanced to log sequence 5821 (LGWR switch)

  Current log# 1 seq# 5821 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 09:38:32 GMT+08:00 2014Thread 1 advanced to log sequence 5822 (LGWR switch)

  Current log# 2 seq# 5822 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 09:41:44 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5823

Checkpoint not complete

  Current log# 2 seq# 5822 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 09:41:47 GMT+08:00 2014Thread 1 advanced to log sequence 5823 (LGWR switch)

  Current log# 1 seq# 5823 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 09:47:13 GMT+08:00 2014Thread 1 advanced to log sequence 5824 (LGWR switch)

  Current log# 2 seq# 5824 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 10:27:49 GMT+08:00 2014Thread 1 advanced to log sequence 5825 (LGWR switch)

  Current log# 1 seq# 5825 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 10:53:28 GMT+08:00 2014Thread 1 advanced to log sequence 5826 (LGWR switch)

  Current log# 2 seq# 5826 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 11:32:56 GMT+08:00 2014Thread 1 advanced to log sequence 5827 (LGWR switch)

  Current log# 1 seq# 5827 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 11:57:51 GMT+08:00 2014Thread 1 advanced to log sequence 5828 (LGWR switch)

  Current log# 2 seq# 5828 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 12:22:49 GMT+08:00 2014Thread 1 advanced to log sequence 5829 (LGWR switch)

  Current log# 1 seq# 5829 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 13:17:51 GMT+08:00 2014Thread 1 advanced to log sequence 5830 (LGWR switch)

  Current log# 2 seq# 5830 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 14:02:43 GMT+08:00 2014Thread 1 advanced to log sequence 5831 (LGWR switch)

  Current log# 1 seq# 5831 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 14:51:45 GMT+08:00 2014Thread 1 advanced to log sequence 5832 (LGWR switch)

  Current log# 2 seq# 5832 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 15:12:50 GMT+08:00 2014Thread 1 advanced to log sequence 5833 (LGWR switch)

  Current log# 1 seq# 5833 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 15:37:51 GMT+08:00 2014Thread 1 advanced to log sequence 5834 (LGWR switch)

  Current log# 2 seq# 5834 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 16:02:47 GMT+08:00 2014Thread 1 advanced to log sequence 5835 (LGWR switch)

  Current log# 1 seq# 5835 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 16:17:52 GMT+08:00 2014Thread 1 advanced to log sequence 5836 (LGWR switch)

  Current log# 2 seq# 5836 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 17:12:51 GMT+08:00 2014Thread 1 advanced to log sequence 5837 (LGWR switch)

  Current log# 1 seq# 5837 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 18:07:52 GMT+08:00 2014Thread 1 advanced to log sequence 5838 (LGWR switch)

  Current log# 2 seq# 5838 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 19:02:53 GMT+08:00 2014Thread 1 advanced to log sequence 5839 (LGWR switch)

  Current log# 1 seq# 5839 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 19:57:58 GMT+08:00 2014Thread 1 advanced to log sequence 5840 (LGWR switch)

  Current log# 2 seq# 5840 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 22:00:37 GMT+08:00 2014Thread 1 advanced to log sequence 5841 (LGWR switch)

  Current log# 1 seq# 5841 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 22:01:39 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5842

Checkpoint not complete

  Current log# 1 seq# 5841 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 22:01:41 GMT+08:00 2014Thread 1 advanced to log sequence 5842 (LGWR switch)

  Current log# 2 seq# 5842 mem# 0: /dev/rdb_Redo1_2

Wed Apr 09 02:00:08 GMT+08:00 2014Thread 1 advanced to log sequence 5843 (LGWR switch)

  Current log# 1 seq# 5843 mem# 0: /dev/rdb_Redo1_1

Wed Apr 09 02:01:13 GMT+08:00 2014ALTER SYSTEM ARCHIVE LOG

Wed Apr 09 02:01:13 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5844

Checkpoint not complete

  Current log# 1 seq# 5843 mem# 0: /dev/rdb_Redo1_1

Wed Apr 09 02:01:15 GMT+08:00 2014Thread 1 advanced to log sequence 5844 (LGWR switch)

  Current log# 2 seq# 5844 mem# 0: /dev/rdb_Redo1_2

Wed Apr 09 02:01:16 GMT+08:00 2014ALTER SYSTEM ARCHIVE LOG

Wed Apr 09 02:01:16 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5845

Checkpoint not complete

 可能的原因有以下：

  1>日志文件过小，切换过于频繁；

  2>日志组太少，不能满足正常事务量的需要；

  3>日志文件所在磁盘I/O存在瓶颈，导致写出缓慢，阻塞数据库正常运行；

  4>由于数据文件磁盘I/O瓶颈，DBWR写出过于缓慢；

  5>由于事务量巨大，DBWR负荷过高，不堪重负。

如果系统在关键作业时生成的redo size峰值量很高，并且持续很长时间。我们在Alert log上可以看到频繁的online log切换，同时连带出现“check point not complete”或者“could not allocate log sequence”提示。说明日志切换过于频繁。

从AWR报告上，我们主要关注两个与redo log相关的事件：log file parallel write和log file sync。如果两个事件出现在top events中，作为dba和调试人员就需要注意了。

在Oracle的官方资料中，对redo log的大小设置也是以切换频率而定的，要求调整到15-20分钟进行一次切换。调整的手段主要是增加日志组数量和调大日志成员文件大小。这样，都可以给dbwr和arc进程更多的时间在后台进行数据写入和归档。

在实际中，、还要建议关注日志组成员的数目，我们对日志采用多路径冗余手段，是为了防止出现单磁盘文件以外损坏。但是，过多的日志成员数目也会带来性能瓶颈。曾见过一个日志组成员数量是10个文件，散布在10个设备上。这样，就意味lgwr要写入十次才能将一个log entry写入，性能可想而知。
可以通过下面的命令查看oracle两个日志切换之间的时间间隔
select
b.recid,to_char(b.first_time,'yyyy-mon-dd hh24:mi:ss') start_time,
a.recid,to_char(a.first_time,'yyyy-mon-dd hh24:mi:ss') end_time,
round(((a.first_time-b.first_time)*25)*60,2) minutes
from v$log_history a,v$log_history b
where a.recid=b.recid+1
order by a.first_time asc;
使用下面的命令可以查看重做日志的显示信息
select a.member,b.* from v$logfile a,v$log b where a.group#=b.group#;

查看日志文件

 select * from v$logfile;

 

查看oracle日志有4个组并且每个组中只有一个成员。

建议增加group组或者增加组里的成员

使用下面的命令进行增加：

首先创建lv

/usr/sbin/mklv -y 'db_Redo3_1'  -T O -w n -s n -r n -t 'raw' datavg 2

/usr/sbin/mklv -y 'db_Redo3_2'  -T O -w n -s n -r n -t 'raw' datavg 2

/usr/sbin/mklv -y 'db_Redo4_1'  -T O -w n -s n -r n -t 'raw' datavg 2

/usr/sbin/mklv -y 'db_Redo4_2'  -T O -w n -s n -r n -t 'raw' datavg 2

给新建的lv增加权限

#chown oracle:dba /dev/rdb_Redo3_1

#chown oracle:dba /dev/rdb_Redo3_2

#chown oracle:dba /dev/rdb_Redo4_1

#chown oracle:dba /dev/rdb_Redo4_2

增加group组或者将logfile增加到日志组中

alter database add logfile '/dev/rdb_Redo3_1';

alter database add logfile '/dev/rdb_Redo3_2';

alter database add logfile '/dev/rdb_Redo4_1';

alter database add logfile '/dev/rdb_Redo4_2';

或者将logfile增加到日志组中

alter database add logfile member '/dev/rdb_Redo3_2' to group 1;

这里采取第一种方法，增加后的效果如下：

select * from v$logfile;

可以使用下面的方法切换redo写的组，查看状态
alter system switch logfile;
alter system checkpoint;

select GROUP#,THREAD#,STATUS from v$log;