ORA-609 - opiodr aborting process unknown ospid (18874448) as a result of ORA-609

ORA-609 - opiodr aborting process unknown ospid (18874448) as a result of ORA-609
alert.log, trace, listener.log 日志

# alert_wind.log

Thu Dec 20 08:51:32 2012


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

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

  VERSION INFORMATION:
        TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
        TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
        Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
  Time: 20-DEC-2012 08:51:32
  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 (18874448) as a result of ORA-609

# ORA-12537
# trace log

$ cat wind_ora_18874448.trc|more
Trace file /oracle/diag/rdbms/wind/wind/trace/wind_ora_18874448.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2
System name:    AIX
Node name:      P1WCSDB01
Release:        1
Version:        6
Machine:        00F752144C00
Instance name: wind
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 18874448, image: oracle@P1WCSDB01


*** 2012-12-20 08:51:32.269
*** CLIENT ID:() 2012-12-20 08:51:32.269
*** SERVICE NAME:() 2012-12-20 08:51:32.269
*** MODULE NAME:() 2012-12-20 08:51:32.269
*** ACTION NAME:() 2012-12-20 08:51:32.269
 

opiino: Attach failed due to ORA-12537 

# listener.log
20-DEC-2012 08:51:32 * (CONNECT_DATA=(SID=wind)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wcadmin))) * (ADDRESS=(PROTOCOL=t
cp)(HOST=10.10.10.11)(PORT=51074)) * establish * wind * 0
20-DEC-2012 08:51:32 * (CONNECT_DATA=(SID=wind)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wcadmin))) * (ADDRESS=(PROTOCOL=t
cp)(HOST=10.10.10.11)(PORT=51075)) * establish * wind * 0

连接状态 如下
SQL> select sid,username,schemaname,osuser,program,status from v$session;
发现有大量的INACTIVE的连接，另外从listener.log的日志也可以发现，ORA-609与JDBC的连接有关系。

参见Metalink的文章
# Oracle metalink resolution support.oracle.com
# Search ORA-609
Troubleshooting Guide ORA-609 : Opiodr aborting process unknown ospid [ID 1121357.1]
https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_afrLoop=181679786432410&type=DOCUMENT&id=1116960.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=8sjjib6wb_520
ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log [ID 1116960.1]
https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_afrLoop=178438770090175&type=DOCUMENT&id=1116960.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=8sjjib6wb_464

分析原因：
The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection process before the connection/authentication process was complete.
Very often, this connection abort is due to a timeout.  Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.  This time limit is often inadequate for the entire connection process to complete.  
We have also discovered that the ORA-609 occurs frequently in installations where the database is monitored by DB Console and the Enterprise Manager agent (emagent).   After the DB Console is started and as a matter of routine, the emagent will repeatedly try to connect to the target instances.  We can see frequent emagent connections in the listener.log without error.  However, on occasion it may have failed to complete the connection process at the database so an ORA-609 is thrown.  The emagent will simply retry the connection and may be successful on the subsequent try.  (Provided there is no real fault occurring at the listener or database).  This temporary failure to connect will not be reported back to DB Console and there will be no indication, except for the ORA-609, that a fault occurred.
解决方法：
It can be somewhat challenging  to determine the origin of the client that is causing the error. 

For that reason, we often recommend increasing the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.  If the problem  is due to connection timeouts, an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s

e.g.
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120