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

分类： ORA-xxxxx Oracle Product DB Oracle Network 2012-12-24 17:41  2060人阅读  评论(0)  收藏  举报

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

[plain]  view plain copy print ?

1. # alert_wind.log  
2.   
3. Thu Dec 20 08:51:32 2012  
4.   
5.   
6. ***********************************************************************  
7.   
8. Fatal NI connect error 12537, connecting to:  
9.  (LOCAL=NO)  
10.   
11.   VERSION INFORMATION:  
12.         TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production  
13.         TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production  
14.         Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production  
15.   Time: 20-DEC-2012 08:51:32  
16.   Tracing not turned on.  
17.   Tns error struct:  
18.     ns main err code: 12537  
19.      
20. TNS-12537: TNS:connection closed  
21.     ns secondary err code: 12560  
22.     nt main err code: 0  
23.     nt secondary err code: 0  
24.     nt OS err code: 0  
25. opiodr aborting process unknown ospid (18874448) as a result of ORA-609  
26.   
27. # ORA-12537  
28. # trace log  
29.   
30. $ cat wind_ora_18874448.trc|more  
31. Trace file /oracle/diag/rdbms/wind/wind/trace/wind_ora_18874448.trc  
32. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
33. With the Partitioning, OLAP, Data Mining and Real Application Testing options  
34. ORACLE_HOME = /oracle/product/11.2  
35. System name:    AIX  
36. Node name:      P1WCSDB01  
37. Release:        1  
38. Version:        6  
39. Machine:        00F752144C00  
40. Instance name: wind  
41. Redo thread mounted by this instance: 1  
42. Oracle process number: 32  
43. Unix process pid: 18874448, image: oracle@P1WCSDB01  
44.   
45.   
46. *** 2012-12-20 08:51:32.269  
47. *** CLIENT ID:() 2012-12-20 08:51:32.269  
48. *** SERVICE NAME:() 2012-12-20 08:51:32.269  
49. *** MODULE NAME:() 2012-12-20 08:51:32.269  
50. *** ACTION NAME:() 2012-12-20 08:51:32.269  
51.    
52.   
53. opiino: Attach failed due to ORA-12537   
54.   
55. # listener.log  
56. 20-DEC-2012 08:51:32 * (CONNECT_DATA=(SID=wind)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wcadmin))) * (ADDRESS=(PROTOCOL=t  
57. cp)(HOST=10.10.10.11)(PORT=51074)) * establish * wind * 0  
58. 20-DEC-2012 08:51:32 * (CONNECT_DATA=(SID=wind)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wcadmin))) * (ADDRESS=(PROTOCOL=t  
59. 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

[plain]  view plain copy print ?

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