Here's an example snippet of an incoming client connection that was posted to the listener.log: 20-JAN-2009 17:08:45 (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=D:\oracle\product\10.1.0\Db_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=myclient)
Note that the exact timestamp, program name and client host will often be recorded. Again, once you've located the offending client, enable tracing (see above) to try to capture the connection failure.
3) Enable server side Oracle Net tracing and capture the TNS error along with the incoming connection.
Match the PID that accompanies the ORA-609 to the server trace label. e.g.
ORA-609 : opiodr aborting process unknown ospid (4799_1) *Note the PID
This PID would correspond to server trace labeled: svr_4799.trc. Check the server trace for either TNS error (the 609 will not appear) and try to locate the originating client address. If assistance is needed for this investigation, please open an SR with Oracle Support.
See below for instuctions on enabling Oracle Net server tracing.
The following details the discovery of the source of an ORA-609 for a real case:
The alert.log reports the following messages intermittently but frequently:
Mon Nov 16 22:39:22 2009ORA-609 : opiodr aborting process unknown ospid (nnnn)
Enabled Oracle Net server tracing:
TRACE_LEVEL_SERVER=16
TRACE_DIRECTORY_SERVER=
TRACE_TIMESTAMP_SERVER=TRUE
DIAG_ADR_ENABLED=off
Reloaded listener and wait for error to appear again.:
ORA-609 : opiodr aborting process unknown ospid (5233_1)
Note that the server trace file set that corresponded to this event was named svr_5233*.trc.
Of course the timestamps of the alert.log event and the server trace creation matched as well.
A review of the server trace showed only an EOF failure and the TNS-12537 error:
nserror: nsres: id=0, op=68, ns=12537
In this particular case, there was no information about the client in the trace. This is atypical for a server trace. It may be that the client aborted before all the client information was posted to the file. However, there was post in the listener.log for an emagent connection that was established at the same point in time.
Here's an excerpt from a listener.log entry where an emagent establishes a connection:
Checked the EM Agent traces and logs and discovered the following entry:
(LOCAL=NO)
VERSION INFORMATION:
TNS for Solaris: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.1.0.7.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.1.0.7.0 - Production
Time: 16-NOV-2009 22:39:22
****Tracing to file: /backup/sid_traces/sqlnetlog/ svr_5233.trc
Tns error struct:
ns main err code: 12547
TNS-12547: TNS:lost contact
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
****Note the name of the server trace which contains the PID: svr_5233.trc
Also, the timestamp of the agent event matches the timestamp of the alert.log error.