ORA-609錯誤分析及解決方法

某個客戶數據庫在巡檢的時候發現alert日志里不定期會出現ORA-609錯誤，由于ORA-609的緣故，ospid(xxxx)進程被aborting了，同時還

某個客戶數據庫在巡檢的時候發現alert日志里不定期會出現ORA-609錯誤，大致內容如下：

***********************************************************************

Fatal NI connect error 12537, connecting to:
(LOCAL=NO)

VERSION INFORMATION:
TNS for HPUX: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for HPUX: Version 11.2.0.3.0 - Production
Time: 19-OCT-2014 20:24:16
Tracing not turned on.
Tns error struct:
ns main err code: 12537

TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
opiodr aborting process unknown ospid (2734) as a result of ORA-609
Sun Oct 19 21:27:24 2014

***********************************************************************

由于ORA-609的緣故，ospid(xxxx)進程被aborting了，同時還伴隨著TNS-12537的錯誤，連接關閉

去MOS搜了一圈，正好有篇文檔是針對這個錯誤的，，下面是描述：

適用于：

Oracle Net Services - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

癥狀：

alert日志出現以上類似的內容（略）

變化：

Changes in database server load, client connect descriptor, changes in network infrastructure (firewall configuration).

原因：

首先，這個“opiodr aborting process unknown ospid (2734) as a result of ORA-609”消息僅僅是說明了由于ORA-609，使Oracle數據庫專用進程被關閉了

來看一段描述：

ORA-609 means "could not attach to incoming connection" so the database process was 'aborted' (closed) because it couldn't attach to the incoming connection passed to it by the listener.

ORA-609意味著不能通過監聽把它附加到即將到來的連接上，因此服務器進程被終止（關閉）

The reason for this is found in the sqlnet error stack, in our case is:
TNS-12537: TNS:connection closed.
Basically the dedicated process didn't have a client connection anymore to work with.

客戶端連接有6個步驟:

Client initiates a connection to the database so it connects to the listener
Listener starts (fork) a dedicated database process that will receive this connection (session)
After this dedicated process is started, the listener passes the connection from the client to this process
The server process takes the connection from the listener to continue the handshake with the client
Server process and client exchange information required for establishing a session (ASO, Two Task Common, User logon)
Session is opened

In the case of the above error the connection from the client was closed somewhere between 3. and 4. So when the dedicated process tries to communicate with the client it finds that connection closed.

鑒于以上的錯誤，在第3步與第4步之間時，客戶端連接就關閉了，此時當專有進程嘗試與客戶端連接時，發現連接已經關閉了

To determine the client which hit this problem we can try to match the timestamp of the error from alert log with an entry in listener.log, but this might be difficult in case of a loaded listener with many incoming connections per second.
Server sqlnet trace will not provide any information about the client.

去確定碰到問題的client，我們可以嘗試去匹配alert日志中錯誤發生的時間戳并且在監聽日志中也有相應的條目，但當加載的監聽每秒有許多連接的時候是非常困難去判斷的，服務器sqlnet的trace不會提供任何該客戶端的信息

We can enable sqlnet server trace to catch the error (the match is done based on the ospid found in sqlnet server trace file name and the line with ORA-609 error):

還可以啟用sqlnet server的trace中抓取到ORA-609錯誤，匹配成功基于sqlnet server trace文件名和ORA-609錯誤信息中的ospid

nscon: doing connect handshake...
nscon: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: exit
ntt2err: entry
ntt2err: Read unexpected EOF ERROR on 15 <<<<<<< error
ntt2err: exit
nsprecv: error exit
nserror: entry
nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
nscon: error exit
nsdo: nsctxrnk=0
nsdo: error exit
nsinh_hoff: error recving request

可能引起問原因：

Several possible situations can cause this to happen:

client changed its mind and closed the connection immediately after initiating it
client crashed
firewall kills the connection
some oracle timeout set on client
解決方案：

Because the entry from listener.log contains only CONNECT_DATA and CID related information we need to check the client configuration for any sqlnet timeouts:

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com