ALERT日志中常見監聽相關報錯之中的一個：ORA-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。根本原由于client連接配接不正常。

client通過監聽器連接配接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.client連接配接到監聽器 

2.監聽派生fork一個子程序，交轉化為專有server程序dedicated database process

3.第2步完畢後，監聽将client的連接配接轉入此專有程序dedicated process

4.server程序收到從監聽來的連接配接資訊後。須要繼續與client的連接配接進行handshake

5.server程序與client程序交換建立會話須要的資訊，如username、password等

6.以上OK後。SESSION OPEN。

在介于3、4步時client連接配接關閉，dedicated database process與client通信時發現client關閉了。

###############################

使用跟蹤來排查：

文檔：Troubleshooting Guide ORA-609 : Opiodr aborting process unknown ospid (文檔 ID 1121357.1)

對于這樣的問題的排查，使用listener.log或者SQLNET的跟蹤效果不太好，由于每秒可能有非常多連接配接同一時候SQLNET的跟蹤未提供很多其它的client資訊。

此時能夠嘗試使用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)

可能原因：

client卡住、崩潰；連接配接被防火牆KILL;client逾時設定；client連接配接後立馬關閉；網絡不穩定。

須要檢查clienttnsnames.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

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

本文轉自mfrbuaa部落格園部落格，原文連結：http://www.cnblogs.com/mfrbuaa/p/5347705.html，如需轉載請自行聯系原作者