ALERT日志中常见监听相关报错之中的一个:ORA-609错误的排查-阿里云开发者社区

开发者社区> 数据库> 正文

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,如需转载请自行联系原作者 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章