Private strand flush not complete的警告

Kevin Zou

2011-8-26

在db上的alert文件上遇到这样的警告信息：

Thu Aug 13 20:20:29 2009
Thread 1 cannot allocate new log, sequence 18036
Private strand flush not complete
  Current log# 10 seq# 18035 mem# 0: /oradata1/DSTSPA01/redo/redo10a.log
Thread 1 advanced to log sequence 18036
  Current log# 11 seq# 18036 mem# 0: /oradata2/DSTSPA01/redo/redo11a.log

如果遇到Thread 1 cannot allocate new log的警告，一般是由于事务提交频繁，DBWR 写dirty data速度太慢，导致在logfile switch 时没有可以重用的logfile，才会报这样的警告。同时会跟上这样的一句话：

Checkpoint not complete

但要注意在这个警告信息并非Checkpoint not complete，而是：

Private strand flush not complete

这又是什么？

这是一个ORACLE的“BUG”，发生在10GR2处于归档模式的数据库，手工切换日志文件时，如果有事务进行没有提交时，就会在alert.log日志上出现这样的警告信息。这是与10g引入的IMU与priviate redo特性相关。

METALINK的上的原文：

Technically all the strands need to be flushed when the log switch is being initiated and hence the messages for checkpoint not complete and private strand flush not complete are synonymous. The crux is that the strand still have transactions active which need to be flushed before this redo can be overwritten, would recommend letting Oracle perform. the checkpointing by default and tune fast_start_mttr_target to achieve what one is looking for." 
This issue is very easy to duplicate on any database running in archivelog mode.  When the online logs are manually switched with no sessions connected, the problem messages are not displayed in the alert log. 

SQL> alter system switch logfile; 
System altered. 

Alert log shows the following. 

Thread 1 advanced to log sequence 206 
Current log# 3 seq# 206 mem# 0: +GROUP01/oasm/onlinelog/group_3.258.609176403 
Current log# 3 seq# 206 mem# 1: +GROUP01/oasm/onlinelog/group_3.263.609176613 

Now connect to another session (using scott/tiger schema object in the example) and leave an update TX active. 

SQL> update emp set sal = sal * 1; 
14 rows updated. 
... do not commit and keep session active

Then manually switch the logs in the original session, and now the alert log shows the following messages. 

Thread 1 cannot allocate new log, sequence 207 
Checkpoint not complete 
Current log# 3 seq# 206 mem# 0: +GROUP01/oasm/onlinelog/group_3.258.609176403 
Current log# 3 seq# 206 mem# 1: +GROUP01/oasm/onlinelog/group_3.263.609176613

Or the following.

Thread 1 cannot allocate new log, sequence 85 
Private strand flush not complete 
Current log# 3 seq# 84 mem# 0: C:\ORACLE\ORA11\ORADATA\ORCL11\REDO03.LOG

This is not a problem according to development, but expected behavior. So when you manually switch logs on a DB with any activity at all, you will likely see these messages written to the alert log.

可是我在10GR2的归档模式的DB上，测试多次都无法模拟出文章上说的情况。

METALINK 说的BUG号为5438220

Bug 5438220: THREAD 1 CANNOT ALLOCATE NEW LOG, SEQUENCE 2966
症状：
Every manual log switch using alter system switch logfile , it throws message in the alert log file 

Thread 1 cannot allocate new log, sequence 2966
Private strand flush not complete

由于这是一个数据库预计的行为，所以不要紧张。

但如果经常遇到Checkpoint not complete的提示，就要检查下数据库的DBWR，可以考虑增加日志组来减少Checkpoint not complete 的出现的频率。

参考文档：

BUG:5438220 - THREAD 1 CANNOT ALLOCATE NEW LOG, SEQUENCE 2966
NOTE:147468.1 - Checkpoint Tuning and Troubleshooting Guide
NOTE:265831.1 - Automatic Checkpoint Tuning in 10g
NOTE:372557.1 - Alert Log Messages: Private Strand Flush Not Complete

-THE END-