Private strand flush not complete错误
(2013-10-22 11:04:58)
转载
▼
分类： FAQ
最近的一个报表系统数据库，总是出现checkpoint not complete错误或者Private strand flush not complete错误，具体如下：
Mon Oct 7 09:49:20 2013
Thread 1 cannot allocate new log, sequence 132198
Checkpoint not complete
Current log# 1 seq# 132197 mem# 0: /data/oradata/testrpt/redo01.log
Mon Oct 7 09:49:59 2013
Thread 1 advanced to log sequence 132198
Current log# 2 seq# 132198 mem# 0: /data/oradata/testrpt/redo02.log
Mon Oct 7 12:10:41 2013
Thread 1 advanced to log sequence 132199
Current log# 3 seq# 132199 mem# 0: /data/oradata/testrpt/redo03.log
Mon Oct 7 19:04:43 2013
Thread 1 cannot allocate new log, sequence 132200
Private strand flush not complete
Current log# 3 seq# 132199 mem# 0: /data/oradata/testrpt/redo03.log
Thread 1 advanced to log sequence 132200
Current log# 1 seq# 132200 mem# 0: /data/oradata/testrpt/redo01.log
根据check point not complete错误，决定采取增加日志文件来缓解出现频率，具体步骤如下：
sys@testrpt> select * from v$logfile;
sys@testrpt> set wrap off
sys@testrpt> /
rows will be truncated
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE /data/oradata/testrpt/redo03.log
2 ONLINE /data/oradata/testrpt/redo02.log
1 ONLINE /data/oradata/testrpt/redo01.log
sys@testrpt> select group#,bytes/1024/1024 m from v$log;
GROUP# M
---------- ----------
1 200
2 200
3 200
sys@testrpt> alter database add logfile group 4
2 '/data/oradata/testrpt/redo04.log' size 200m;
Database altered.
sys@testrpt> alter database add logfile group 5
2 '/data/oradata/testrpt/redo05.log' size 200m;
Database altered.
sys@testrpt> alter database add logfile group 6
2 '/data/oradata/testrpt/redo06.log' size 200m;
Database altered.
针对 Private strand flush not complete问题 Oracle 提了2个方法：
（1） 忽略，在使用之前，必须要等待buffer的信息flush完成。 这时候进程是会短暂的hang住。
（2） 增加db_writer_process的数据。
sys@testrpt>
show parameter db_wri
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 1
该参数的静态参数，需要重启数据库才能生效：
sys@testrpt>
alter system set db_writer_processes=3;
alter system set db_writer_processes=3
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
因此需要采取如下方式来使其生效：
sys@testrpt>
alter system set db_writer_processes=3 scope=spfile;
System altered.
sys@testrpt>
shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@testrpt>
startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 1220432 bytes
Variable Size 285212848 bytes
Database Buffers 1845493760 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
sys@testrpt>
alter system register;
System altered.
sys@testrpt>
show parameter db_write
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 3
sys@testrpt>
alter system switch logfile;
System altered.
sys@testrpt>
sys@testrpt> exit
以上操作完毕后，查看bdump文件夹，又出现了名称testrpt_lgwr_26081.trc 具体内容如下：
Media recovery not enabled or manual archival only 0x10000
*** 2013-10-07 22:49:15.914
Media recovery not enabled or manual archival only 0x10000
*** 2013-10-07 22:49:34.342
Media recovery not enabled or manual archival only 0x10000
*** 2013-10-07 22:50:00.129
Media recovery not enabled or manual archival only 0x10000
*** 2013-10-07 22:50:21.719
Media recovery not enabled or manual archival only 0x10000
针对该trace文件的该错误，ORACLE 的解释：
Cause
Bug 4591936
Abstract: KCCSGA_UPDATE_CKPT,KCCDEBUG_LEVEL,MEDIA RECOVERY MESSAGES ARE LOGGED AT STARTUP
Fixed In Ver: 11.0
Solution
These messages were added for debugging purpose and can be ignored.
Delete these files manually or use some job to clean up diskspace.
At the time of writing this note , there isn't any one-off patch to fix this issue on 10.2.
You can always raise a new SR requesting for a backport fix on top of 10.2 for your platform.
一个小BUG，至多占点磁盘空间，不会危害数据库。
关于strand的解释可以参考如下链接：
http://blog.csdn.net/tianlesoftware/article/details/6015400
转载于:https://www.cnblogs.com/woxing/p/3974220.html
相关资源：JAVA上百实例源码以及开源项目
