Oracle数据库：Thread 1 cannot allocate new log, sequence 22 Checkpoint not complete

 

遇到问题：

Thread 1 cannot allocate new log, sequence 22416

Checkpoint not complete

 

  Current log# 1 seq# 22411 mem# 0: /orcldb/orcl/redo01.log

Thread 1 advanced to log sequence 22412

  Current log# 2 seq# 22412 mem# 0: /orcldb/orcl/redo02.log

Thu Jul 26 00:46:37 2012

Thread 1 advanced to log sequence 22413

  Current log# 3 seq# 22413 mem# 0: /orcldb/orcl/redo03.log

 

原因：

This is a very common error while huge data writing into db and have not enough log group or group member file is small.

当系统要重新利用某个日志文件的时候，系统需要将该日志文件所包括的buffer cache 中的dirty block 写到相应的数据文件。

然后，buffer cache 中的dirty data还没有完全写到数据文件，系统的checkpoint 没有来得及完成，就已经有大量的日志需要写入到系统。

系统只能通知应用：checkpoint 还没有完成，你只能等待。

When the database waits on checkpoints,redo generation is stopped until the log switch is done

 

解决方案：

1) Give the checkpoint process more time to cycle through the logs
-  add more redo log groups
-  increase the size of the redo logs
2) Reduce the frequency of checkpoints
- increase LOG_CHECKPOINT_INTERVAL
- increase size of online redo logs
3) Improve the efficiency of checkpoints enabling the CKPT process
with CHECKPOINT_PROCESS=TRUE
4) Set LOG_CHECKPOINT_TIMEOUT = 0.  This disables the checkpointing
based on
time interval.
5) Another means of solving this error is for DBWR to quickly write
the dirty buffers on disk.

 

如表1，我们计划采取的方法为：

表1

重做日志组	线程	大小
1	1	50M
2	2	50M
3	1	50M
4	2	50M

1.为2个节点分别增加重做日志组5,6,7,8，大小设置为200M

SQL>alter database add logfile thread 1 group 5 size 200m;

SQL>alter database add logfile thread 2 group 6 size 200m;

SQL>alter database add logfile thread 1 group 7 size 200m;

SQL>alter database add logfile thread 2 group 8 size 200m;

2.删除重做日志组

//节点1

SQL>alter system switch logfile;

//节点2

SQL>alter system switch logfile;

SQL>alter database drop logfile group 1;

SQL>alter database drop logfile group 2;

SQL>alter database drop logfile group 3;

SQL>alter database drop logfile group 4;

3.重建重做日志组1,2,3,4，大小设置为200M

SQL>alter database add logfile thread 1 group 1 size 200m;

SQL>alter database add logfile thread 2 group 2 size 200m;

SQL>alter database add logfile thread 1 group 3 size 200m;

SQL>alter database add logfile thread 2 group 4 size 200m;