解決Linux下sqlplus無(wú)響應(yīng)的三種方法
Oracle數(shù)據(jù)庫(kù)的sqlplus無(wú)響應(yīng)掛起處理，網(wǎng)上查看資料得知是oracle的bug引起的，事實(shí)上只要Linux x86主機(jī)運(yùn)行天數(shù)是24.8的倍數(shù)都有可能引發(fā)該bug，因?yàn)閠ime()函數(shù)值為null，造成無(wú)限死循環(huán)，從而耗盡cpu。接下來(lái)我們介紹一下解決辦法。
解決辦法三種：
1) 重啟主機(jī)；
2) 打patch set，如升級(jí)到10.2.0.4；
3) 對(duì)該bug單獨(dú)打臨時(shí)patch 4612267。
第一種方法沒(méi)有徹底解決問(wèn)題，以后照舊；第二種方法，升級(jí)時(shí)間長(zhǎng)，且要求停庫(kù)很久，當(dāng)前生產(chǎn)環(huán)境暫不適合；
參考文檔：
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
我采取的是第三種方法，打補(bǔ)丁包的方式。而且據(jù)oracle官方文檔說(shuō)明，oracle11已經(jīng)修復(fù)該問(wèn)題。
下面是pache 4612267補(bǔ)丁包的安裝及驗(yàn)證方法：
先停監(jiān)聽(tīng)、dbconsole和數(shù)據(jù)庫(kù)
- $ lsnrctl stop
- $ emctl stop dbconsole
- $ sqlplus / as sysdba
- SQL> shutdown immediate
注意：dbconsole是在已經(jīng)裝了Oracle EM的情況下要停止，如果未安裝則無(wú)需干涉。
安裝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.
驗(yàn)證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.
啟動(dòng)數(shù)據(jù)庫(kù)、監(jiān)聽(tīng)和dbconsole
- $ sqlplus / as sysdba
- SQL> startup
- $ lsnrctl start
- $ emctl start dbconsole
如果有需要，還可以刪除patch，刪除前先停庫(kù)
- $ 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.
此時(shí)再執(zhí)行上面的驗(yàn)證patch命令就會(huì)發(fā)現(xiàn)該patch已經(jīng)刪除了。
注：
- Running STRACE tool shows:
- $ strace /oracle/home/bin/sqlplus -V 2>&1 |less
- ......
- old_mmap(NULL, 385024, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x41794000
- gettimeofday({1122996561, 411035}, NULL) = 0
- access("/usr/local/UD/conf/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
- access("/usr/local/UD/lib/oracle/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
- access("/usr/local/UD/conf/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
- access("/usr/local/UD/lib/oracle/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
- fcntl64(-1218313656, F_SETFD, FD_CLOEXEC) = -1 EBADF (Bad file descriptor)
- It is looping on the times() function.--死循環(huán)中
- times(NULL) = -1825782405
- times(NULL) = -1825782405
- times(NULL) = -1825782405
- times(NULL) = -1825782405
- times(NULL) = -1825782405
- times(NULL) = -1825782405
- times(NULL) = -1825782405
關(guān)于Linux下sqlplus沒(méi)有反應(yīng)的問(wèn)題就介紹到這里了，希望通過(guò)本次的介紹能夠帶給您一些收獲！
【編輯推薦】
分享到微信 
分享到微博
