提示:
Thread 1 cannot allocate new log, sequence 373
Private strand flush not complete
告警日志:
Mon Dec 15 22:51:26 2014
Thread 1 cannot allocate new log, sequence 373
Private strand flush not complete
Current log# 3 seq# 372 mem# 0: /u03/app/oracle/oradata/pinggu/redo03.log
Thread 1 advanced to log sequence 373 (LGWR switch)
Current log# 1 seq# 373 mem# 0: /u03/app/oracle/oradata/pinggu/redo01.log
Mon Dec 15 22:51:30 2014
Archived Log entry 368 added for thread 1 sequence 372 ID 0xd00aa71e dest 1:
Tue Dec 16 02:00:00 2014
Clearing Resource Manager plan via parameter
Tue Dec 16 06:00:35 2014
Thread 1 cannot allocate new log, sequence 374
Private strand flush not complete
Current log# 1 seq# 373 mem# 0: /u03/app/oracle/oradata/pinggu/redo01.log
Thread 1 advanced to log sequence 374 (LGWR switch)
Current log# 2 seq# 374 mem# 0: /u03/app/oracle/oradata/pinggu/redo02.log
Tue Dec 16 06:00:40 2014
Archived Log entry 369 added for thread 1 sequence 373 ID 0xd00aa71e dest 1:
Tue Dec 16 08:25:33 2014
Thread 1 advanced to log sequence 375 (LGWR switch)
Current log# 3 seq# 375 mem# 0: /u03/app/oracle/oradata/pinggu/redo03.log
Tue Dec 16 08:25:34 2014
Archived Log entry 370 added for thread 1 sequence 374 ID 0xd00aa71e dest 1:
这里面涉及到一些Redo 的机制问题。
一个redo条目包含了相应操作导致的数据库变化的所有信息,所有redo条目最终都要被写入redo文件中去。 Redo log buffer是为了避免Redo文件IO导致性能瓶颈而在sga中分配出的一块内存。 一个redo条目首先在用户内存(PGA)中产生,然后由oracle服务进程拷贝到log buffer中,当满足一定条件时,再由LGWR进程写入redo文件。
由于log buffer是一块“共享”内存,为了避免冲突,它是受到redo allocation latch保护的,每个服务进程需要先获取到该latch才能分配redo buffer。因此在高并发且数据修改频繁的oltp系统中,我们通常可以观察到redo allocation latch的等待。
为了减少redo allocation latch等待,在oracle 9.2中,引入了log buffer的并行机制。其基本原理就是,将log buffer划分为多个小的buffer,这些小的buffer被成为Shared Strand。每一个strand受到一个单独redo allocation latch的保护。多个shared strand的出现,使原来序列化的redo buffer分配变成了并行的过程,从而减少了redo allocation latch等待。
为了进一步降低redo buffer冲突,在10g中引入了新的strand机制——Private strand。Private strand不是从log buffer中划分的,而是在shared pool中分配的一块内存空间。
Private strand的引入为Oracle的Redo/Undo机制带来很大的变化。每一个Private strand受到一个单独的redo allocation latch保护,每个Private strand作为“私有的”strand只会服务于一个活动事务。获取到了Private strand的用户事务不是在PGA中而是在Private strand生成Redo,当flush private strand或者commit时,Private strand被批量写入log文件中。如果新事务申请不到Private strand的redo allocation latch,则会继续遵循旧的redo buffer机制,申请写入shared strand中。事务是否使用Private strand,可以由x$ktcxb的字段ktcxbflg的新增的第13位鉴定:
对于使用Private strand的事务,无需先申请Redo Copy Latch,也无需申请Shared Strand的redo allocation latch,而是flush或commit是批量写入磁盘,因此减少了Redo Copy Latch和redo allocation latch申请/释放次数、也减少了这些latch的等待,从而降低了CPU的负荷。
解决:
看了这些理论知识,我们在来看一下之前的错误:
Private strand flush not complete
当我们flush或者commit的时候,必须先将buffer中的内容写入到redo中,才能去接收新的记录。 这个错误就是发生在这个过程中。
These messages are not a cause for concern unless there is a significant time gap between the "cannot allocate new log" message and the "advanced to log sequence" message.
Increasing the value for db_writer_processes can in some situations help to avoid the message from being generated. Why, because one of the DBWR main function is to keep the buffer cache clean by writing out dirty buffer blocks. So having multiple db_writer_processes should be able to produce a higher throughput.
Finally, these messages have also been seen when there are issues with the storage side or network for the archive log destination, as this leads to delay or hang in LGWR switch.
(1) 忽略,在使用之前,必须要等待buffer的信息flush完成。 这时候进程是会短暂的hang住。
(2) 考虑系统是否能开启异步I/O以及是否已经开启异步I/O,如果没有开启先开启。
(3) 如果不支持异步I/O,或者已经开启,或者开启之后没有缓解,那么考虑设置db_writer_processes参数。设置原则是:
如果系统只有一个CPU,则不能设置db_writer_processes大于一,此时使用DBWR_IO_SLAVES参数
如果系统有多个CPU,db_writer_processes=num(cpu)/8
设置DBWR_IO_SLAVES的话,则只能强制设置一个db_writer_processes
网上参考:http://blog.itpub.net/16566331/viewspace-674926/
http://blog.csdn.net/tianlesoftware/archive/2010/11/17/6014898.aspx
http://blog.csdn.net/tianlesoftware/archive/2010/11/17/6014898.aspx
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29193965/viewspace-1369729/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29193965/viewspace-1369729/