oracle 10g中一个奇怪的定时bug

最近遇到台机器很奇怪,oracle连接数正常,但好象隔了4-5个月的一天,就突然发现
CPU 基本是100%,HANG住了,但内存和连接数没上升,只有重新启动机器,后来发现这个应该是BUG,参考如下三个连接介绍:

http://blog.csdn.net/tianlesoftware/article/details/6592316
http://walksing.iteye.com/blog/722730
http://www.itpub.net/thread-1120192-1-1.html

下面是摘录主要部分介绍:
CPU使用率100%，vmstat 显示有大量等待运行的进程，有大量的上下文切换。 sqlplus 和 lsnrctl 命令无效。 数据基本是hang住了。 啥都不能用。



       该bug 存在与Oracle 10.2.0.1.





       结合网上google的结果，该bug 对cpu 表现是。用户使用30%左右，系统使用70%。

       系统启动198天，会触发这个bug。 解决这个问题一是升级数据库，二是定期重启操作系统。

Bug 4612267 说明



MOS 上的2篇相关文章：

Bug 4612267 - OCI client spins when machineuptime >= 249 days [ID 4612267.8]

SQL*Plus 10.2.0.1 Hangs, When System UptimeIs Long Period of Time [ID 338461.1]



说明如下：

Affects:

    
Product (Component)

      
Oracle Server

 
    
Range of versions believed to be affected

      
Versions < 11

 
    
Versions confirmed as being affected

      
10.1.0.5

 
10.2.0.1

 
    
Platforms affected

      
Generic (all / most platforms affected)

 



Fixed:

    
This issue is fixed in

      
10.2.0.2

 
11.1.0.6  (Base Release)

 





Description

       OCIclients may spin once the machine has been up for longer than 249 days (24.9days on some machines). The spin will show repeated calls to times() fromsltrgatime64().



--根据官网的说法，是OS 系统超过249天，就会触发这个bug。 有时候是24.9 * N 天。 从我google的结果看，基本都是在198和199这两天触发这个bug。



修改方法:
1) 重启主机；
2) 打patch set，如升级到10.2.0.4；
3) 对该bug单独打临时patch 4612267。
第一种方法没有彻底解决问题，以后照旧；第二种方法，升级时间长，且要求停库很久，当前生产环境暂不适合；本文采用第三种方法。

参考文档：
Doc ID: 338461.1 SQL*Plus 10.2.0.1 Hangs, When System Uptime Is Long Period of Time
Doc ID: 4612267.8 Bug 4612267 - OCI client spins when machine uptime >= 249 days

二、先停监听、dbconsole和数据库
先复制$ORACLE_BASE/oraInventory/oraInst.loc到$ORACLE_HOME目录下

$ lsnrctl stop
$ emctl stop dbconsole
$ sqlplus / as sysdba
SQL> shutdown immediate

注意：dbconsole是在已经装了Oracle EM的情况下要停止，如果未安装则无需干涉。

三、安装patch

$ mkdir $ORACLE_BASE/patches
$ cd $ORACLE_BASE/patches
$ rz    (SecureCRT里上传 p4612267_10201_LINUX.zip 文件, 其它上传方式也可以)
$ unzip p4612267_10201_LINUX.zip  
$ cd 4612267/
$ $ORACLE_HOME/OPatch/opatch apply
Invoking OPatch 10.2.0.1.0
...
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/10.2.0/db_1')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y    (此处输入y)
User Responded with: Y
...
ApplySession adding interim patch '4612267' to inventory

The local system has been patched and can be restarted.

OPatch succeeded.

四、验证patch

$ $ORACLE_HOME/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.1.0

Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..


Oracle Home       : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
   from           : /u01/app/oracle/product/10.2.0/db_1/oraInst.loc
OPatch version    : 10.2.0.1.0
OUI version       : 10.2.0.1.0
OUI location      : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch-2009_Jan_13_11-06-27-HKT_Tue.log

Lsinventory Output file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory-2009_Jan_13_11-06-27-HKT_Tue.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Products                                         10.2.0.1.0
There are 2 products installed in this Oracle Home.


Interim patches (1) :

Patch 4612267      : applied on Tue Jan 13 11:05:10 HKT 2009
   Created on 5 Oct 2005, 13:48:00 hrs US/Pacific
   Bugs fixed:
     4612267


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

OPatch succeeded.

五、启动数据库、监听和dbconsole

$ sqlplus / as sysdba
SQL> startup
$ lsnrctl start
$ emctl start dbconsole

六、如果有需要，还可以删除patch，删除前先停库

$ cd $ORACLE_BASE/patches/4612267
$ $ORACLE_HOME/OPatch/opatch rollback -id 4612267
Invoking OPatch 10.2.0.1.0
...
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/10.2.0/db_1')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y    (此处输入y)
User Responded with: Y
...
RollbackSession removing interim patch '4612267' from inventory

The local system has been patched and can be restarted.

OPatch succeeded.

此时再执行上面的验证patch命令就会发现该patch已经删除了。