oracle cannot allocate new log,解决Can not allocate log问题的方法

RAC环境ORACLE其中一个节点报告大量的cannot allocate new log提示，详细信息如下：ORACLE Instance xxdb2 - Can not allocate log, archival required

Tue Dec 1 16:04:35 2009

Thread 2 cannot allocate new log, sequence 7466

All online logs needed archiving

Current log# 3 seq# 7465 mem# 0: +XXDBGROUP/xxdb/onlinelog/group_3.268.6409755

Current log# 3 seq# 7465 mem# 1: +XXDBGROUP/xxdb/onlinelog/group_3.269.6409755

检查上面的提示，其中有一行是"All online logs needed archiving". 说明所有的redo log都需要归档。

导致这个问题的原因是redo log的group太少，当大transaction运行的时候，将所有的redo log都用了，transaction还没有完成。[@more@]

解决的方法是增加redolog 的group或增大group各member的尺寸，我选择增加group数量的方法。过程如下：

检查现在的归档日志情况：SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ----------------------------- ---------

1 1 7501 104857600 1 NO CURRENT 504828041 01-DEC-09

2 1 7500 104857600 1 YES INACTIVE 503944970 01-DEC-09

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ----------------------------- ---------

3 2 7464 104857600 1 NO ACTIVE 503944966 01-DEC-09

4 2 7465 104857600 1 NO CURRENT 504828036 01-DEC-09

4 rows selected.

发现现在数据库的两个节点上，每个节点有2组日志，计划每个节点增加到4组。过程如下：SQL> alter database add logfile thread 1 group 5 ;

SQL> Database altered.

SQL> alter database add logfile thread 2 group 6 ;

SQL> Database altered.

SQL> alter database add logfile thread 1 group 7 ;

SQL> Database altered.

SQL> alter database add logfile thread 2 group 8 ;

SQL> Database altered.