ALERT日誌中常見監聽相關報錯之一：ORA-609錯誤的排查，alertora-609

ALERT日誌中常見監聽相關報錯之一：ORA-609錯誤的排查，alertora-609
參考MOS文檔有：
Troubleshooting Guide ORA-609 : Opiodr aborting process unknown ospid (文檔 ID 1121357.1)
Alert.log shows ORA-609 with TNS-12537: TNS:connection closed (文檔 ID 1538717.1)
Fatal NI Connect 12560' And 'ORA-609 Opiodr Aborting Process' Errors In The Alert Log (文檔 ID 987162.1)
資料庫的ALERT日誌中常會見到ORA-609、ORA-3136/ORA-609 TNS-12537 and TNS-12547 or TNS-12170  12170, 'TNS-12535等相關錯誤，對此類型問題進行整理歸納，如下：
1.ORA-609錯誤的排查指南：
Alert log 可以看到如下錯誤資訊:
    Fatal NI connect error 12537, connecting to:
     (LOCAL=NO)
    
      VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
      Time: 26-FEB-2013 02:23:51
      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 (28725) as a result of ORA-609
    
    First an explanation of this kind of errors.

The message
    opiodr aborting process unknown ospid (.....) as a result of ORA-609
is just a notifications that oracle database closed (aborted) a dedicated process because of ORA-609.

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.
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.
此報錯類似通知：ORACLE因為ORA-609關閉或者叫中止了一個到資料庫的專有串連--ospid (28725)。
ORA-609錯誤原因是：無法與進入的串連進行聯絡，所以無法將此串連轉入監聽器，所以資料庫的process中止此進程。
此時報錯TNS-12537: TNS:connection closed，根本原因為用戶端串連不正常。

用戶端通過監聽器串連ORACLE資料庫的過程：

1.    Client initiates a connection to the database so it connects to the listener
2.    Listener starts (fork) a dedicated database process that will receive this connection (session)
3.    After this dedicated process is started, the listener passes the connection from the client to this process
4.    The server process takes the connection from the listener to continue the handshake with the client
5.    Server process and client exchange information required for establishing a session (ASO, Two Task Common, User logon)
6.    Session is opened
簡單說就是：
1.用戶端串連到監聽器
2.監聽派生fork一個子進程，交轉化為專有伺服器處理序dedicated database process
3.第2步完成後，監聽將用戶端的串連轉入此專有進程dedicated process
4.伺服器處理序收到從監聽來的串連資訊後，需要繼續與用戶端的串連進行handshake
5.伺服器處理序與用戶端進程交換建立會話需要的資訊，如使用者名稱、密碼等
6.以上OK後，SESSION OPEN。
在介於3、4步時用戶端串連關閉，dedicated database process與用戶端通訊時發現用戶端關閉了。

###############################
使用跟蹤來排查：
文檔：Troubleshooting Guide ORA-609 : Opiodr aborting process unknown ospid (文檔 ID 1121357.1)
對於這種問題的排查，使用listener.log或者SQLNET的跟蹤效果不太好，因為每秒可能有很多串連同時SQLNET的跟蹤未提供更多的用戶端資訊。
此時可以嘗試使用OS層面的跟蹤。
如：1111為監聽進程，ps -ef|grep tnslsnr   查出
LINUX: strace -rf -o /tmp/lsnr1.log -p 1111
HP-UX: tusc -T hires -afpo /tmp/lsnr1.log 1111


如果使用TRACE跟蹤，如下：
3. Oracle Net Level 16 Server tracing. Add to server side SQLNET.ORA file
DIAG_ADR_ENABLED=off                  # Disable ADR if version 11g
TRACE_LEVEL_SERVER = 16               # Enable level 16 trace
TRACE_TIMESTAMP_SERVER = ON           # Set timestamp in the trace files
TRACE_DIRECTORY_SERVER = <DIRECTORY>  # Control trace file location

TRACE_FILELEN_SERVER =<n>   #Control size of trace set in kilobytes eg 20480
TRACE_FILENO_SERVER =<n>       #Control number of trace files per process

使用Errorstack方法如下:
4. Errorstack: Setup errorstack to capture failure. This can be particular useful when capturing an Oracle Net client trace is not feasible.
SQL> alter session set events '609 errorstack(3)';

Once a few traces have been collected while the error is reproduced:
SQL> alter session set events '609 off';
###############################################


關於此問題的解決方案有：
文檔：Alert.log shows ORA-609 with TNS-12537: TNS:connection closed (文檔 ID 1538717.1)
可能原因：
用戶端卡住、崩潰；串連被防火牆KILL;用戶端逾時設定；用戶端串連後立刻關閉；網路不穩定；
需要檢查用戶端tnsnames.ora/sqlnet.ora中資訊：

    possible timeouts in sqlnet.ora in client oracle home:

    sqlnet.outbound_connect_time
    sqlnet.recv_timeout
    sqlnet.send_timeout
    tcp_connect_timeout
    
   possible timeout in client connect descriptor (hardcoded in client application or in client tnsnames.ora):
    connect_timeout
    
--------------

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。