Solution:Thread # cannot allocate new log,Checkpoint not complete

字数 2634阅读 4843评论 6赞 0

一套11gr2 RAC节点，共2个节点，默认安装。

生产过程中，在日志中发现alert.sid2.log:

Thread 2 cannot allocate new log, sequence 319

Checkpoint not complete

Current log# 4 seq# 318 mem# 0: +DATA/abc/onlinelog/group_4.736.761757825

Current log# 4 seq# 318 mem# 1: +DATA/abc/onlinelog/group_4.737.761757825

Thread 2 advanced to log sequence 319 (LGWR switch)

Current log# 3 seq# 319 mem# 0: +DATA/abc/onlinelog/group_3.734.761757823

Current log# 3 seq# 319 mem# 1: +DATA/abc/onlinelog/group_3.735.761757825

Tue Oct 18 00:04:38 2011

log使用情况

sql>select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS

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

1 1 425 52428800 1024 2 NO CURRENT

2 1 424 52428800 1024 2 YES INACTIVE

3 2 343 52428800 1024 2 YES INACTIVE

4 2 344 52428800 1024 2 NO CURRENT

we have two group per node,and every group have two member。

也就是默认安装，每个实例都有2个redo group；

In support.oracle.com," Can Not Allocate Log - Archival Required [ID 1265962.1]" give a solution:

1) Add REDO log files to groups or enlarge the existing REDO log files.

2) Tune checkpoints

3) Increase I/O speed for REDO log

问题：

我想使用增加redo log file to group；

这句话的意思是：为group组增加redo log fiile？还是增加redo group组呢？

查了下资料：Advice from oracle admini Guide：
2）When redo log files are multiplexed, LGWR concurrently writes the same redo log
information to multiple identical redo log files, thereby eliminating a single point of redo log failure.

Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.

lgwr是向一个redo group组里的成员写相同日志。
也就是说一个redo group内的memeber都是镜像的，对不？

如此，我就只需要增加redo group组。

各位老大，理解对不？

guangzhou老大，这算是我的免费可以提的一个问题吧。

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论6 条评论

feidaodao系统运维工程师GA
2011-10-22 20:51

不太清楚，这个ID中讲了需要确定最优化的log files size。估计是叫建一个最优化大小的GROUP组成员，并最终使用新建的group吧。

myguangzhou技术经理Uniwise
2011-10-22 20:37

从上面的解释来看，就是增加日志组；不知道为什么前面却说“Add REDO log files to groups ”，估计是笔误？

feidaodao系统运维工程师GA
2011-10-22 20:01

myguangzhou: 增加日志组，而不是日志成员；同组的成员之间是镜像关系

嗯。老大指点得好，书上也是这样说的：“LGWR concurrently writes the same redo log information to multiple identical redo log files,”

我就怕理解错了

feidaodao系统运维工程师GA
2011-10-22 20:00

myguangzhou: 1) Add REDO log files to groups or enlarge the existing REDO log files.
这句是哪来的？扩大日志容量倒是可以，但是增加成员就没什么用了。增加成员只是为了安全

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1265962.1

1) Add REDO log files to groups or enlarge the existing REDO log files.
A good way to determine the optimal size of redo log files is to look into the alert.log file and see how frequently the log sequence# are changing. Each log switch is recorded in the alert.log.

The alert log should be examined when there are heavier loads in the database.
Redo log switching should occur approximately every 20-30 minutes to have a better database performance. If they switch too frequently, you will need to check the current size of redo log files from V$LOG and you will need to create redo log groups with bigger size .

You should start with a minimum of 100 MB redo log files for any production database.

See these note for detailed how to:

Note 602066.1 How To Resize and/or Add Redo Logs
Note 1038851.6 How to Estimate Size of Redo Logs

You may use the V$INSTANCE_RECOVERY view, column OPTIMAL_LOGFILE_SIZE, to determine the size of your online redo logs.

myguangzhou技术经理Uniwise
2011-10-22 19:22

1) Add REDO log files to groups or enlarge the existing REDO log files.
这句是哪来的？扩大日志容量倒是可以，但是增加成员就没什么用了。增加成员只是为了安全而已

myguangzhou技术经理Uniwise
2011-10-22 19:12

增加日志组，而不是日志成员；同组的成员之间是镜像关系

Ctrl+Enter 发表

匿名评论

Solution:Thread # cannot allocate new log,Checkpoint not complete

添加新评论6 条评论

作者其他文章