Oracle Net Services - Version 11.1.0.7 and later Oracle Database - Enterprise Edition - Version 11.1.0.7 and later Information in this document applies to any platform.
PURPOSE
ORA-609 is being reported in the alert log. The error is intermittent and may not occur for days at a time.
1 2 3 4 5 6 7 8 9 10 11 12 13
Mon Oct 1210:03:392009 Errors in file e:\app\oracle\diag\rdbms\center\center\trace\center_ora_7464.trc: ORA-00609: could not attach to incoming connection ORA-12537: TNS:connection closed ORA-609 : opiodr aborting process unknown ospid (2436\_7464)
AI写代码bash
* 1 * 2 * 3 * 4 * 5
Sqlnet.log that is local to the database server may report these errors:
Fatal NI connect error 12537, connecting to: (LOCAL=NO)
VERSION INFORMATION: TNS for 64-bit Windows: Version 11.1.0.7.0 - Production Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production Time: 12-OCT-2009 10:03:39 Tracing to file: E:\app\oracle\product\11.1.0\db_1\NETWORK\trace\svr1_7464.trc 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
Listener log shows the connection was established with no apparent errors. This is because the connection failed AFTER the listener has handed off the connection to the server process.
Notice in this example Oracle Net Server trace, filename “svr_7464.trc”
Here the problem is seen when receiving the connection packet from client. Note that the ORA-609 does not appear in the Oracle Net trace. The ORA-609 is represented by the accompanying ns=12537 in this trace snippet.
Fatal NI connect error 12537, connecting to: (LOCAL=NO)
VERSION INFORMATION: TNS for Solaris: Version 11.2.0.2.0 - Production Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.2.0 - Production TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.2.0 - Production Time: 21-DEC-2009 15:52:15 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
Notice the client address in our example is “sample.com”. One option is to locate several clients and enable client tracing at those sites. You might inspect the log file (ORACLE_HOME/network/log) at the client(s) and check specifically for timeout errors that might have occurred at the same timestamp.
2. Oracle Net Level 16 Client tracing. Add to a clients SQLNET.ORA file
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
DIAG_ADR_ENABLED=off # Disable ADR if version 11g
TRACE_LEVEL_CLIENT = 16 # Enable level 16 trace TRACE_TIMESTAMP_CLIENT = ON # Set timestamp in the trace files TRACE_DIRECTORY_CLIENT = <DIRECTORY> # Control trace file location
TRACE_FILELEN_CLIENT =<n> #Control size of trace set in kilobytes eg 20480 TRACE_FILENO_CLIENT =<n> #Control number of trace files per process
AI写代码bash
* 1 * 2 * 3 * 4 * 5 * 6 * 7 * 8
If the connection model is JDBC thin, Javanet tracing of a client is required. See Document 793415.1 How to Perform the Equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver. If 11.2 JDBC thin client used, the following note can be used Document 1050942.1 How to Trace the Network Packets Exchanged Between JDBC and the RDBMS in Release 11.2
3. Oracle Net Level 16 Server tracing. Add to server side SQLNET.ORA file
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
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
AI写代码bash
* 1 * 2 * 3 * 4 * 5 * 6 * 7
Cyclic tracing will allow you to control the size of and number of trace files that are produced.
The TRACE_FILELEN parameter is for the size of a trace file. The TRACE_FILENO parameter is the number of traces per process.
The SQLNET.ORA file is only read once oncreationofaprocess. RDBMSBackgroundprocessandsharedserverdispatcherswillneedtoberestartedforparameterchangesintheSQLNET.ORAtobepickedup. Onceaprocesshasstartedtobetraced, tracingwillnotstopuntilthattheprocessstops.
In an environment where both GRID and RDBMS homes are installed, instance would reference a sqlnet.ora filein RDBMS_HOME/network/admin by default. (Whereas the listener would refer to GRID_HOME/network/admin for its .ora files)
Please note, that enabling Oracle Net server tracing can produce large amounts of trace, ina very shorttime frame. Even with cyclic tracing, eachprocess will have the TRACE_FILENO_SERVER value amount of traces produced. Optimal tracing workflow should be to enable tracing, reproduce problem andthen disable tracing. Thus limiting amount oftime tracing is enabled.
Setting TRACE_FILENO_SERVER to1and TRACE_FILELEN_SERVER = 20480, could be a solution to lowering the amount of trace generated per process. Remember the trace file will be overwritten and you could lose the important data covering the failure.
AI写代码bash
* 1 * 2 * 3 * 4 * 5 * 6 * 7 * 8 * 9
4. Errorstack: Setup errorstack to capture failure. This can be particular useful when capturing an Oracle Net client trace is not feasible.
1 2 3 4 5
SQL>altersystemset events '609 errorstack(3)';
AI写代码sql
*1
Once a few traces have been collected while the error is reproduced:
1 2 3 4 5
SQL> altersessionset events '609 off';
AI写代码sql
* 1
Once you get a failure:
Review the SQLNET.LOG file on server.
Find the matching entry in the ALERT. LOG, compare via timestamp.
From the entry in the SQLNET.LOG file, you will have the Oracle Net server trace name, from the line “Tracing to file”.
Open the server trace and grep / search for the Connection ID value.
Then search the clients trace client directory for the same Connection ID value.
You’ll then having matching client and server traces. This process is described in full in Document 374116.1 How to Match Oracle Net Client and Server Trace Files
Upload for review:
Matching Oracle Net client and server traces or matching Javanet and Server trace.
ALERT.LOG and LISTENER.LOG files. (Only required to upload data cover issue, not all the log files)
SQLNET.LOG from server ORACLE_HOME/network/log
Trace from errorstack.
Known Issues:
Often the ORA-609 is reported due to client disconnecting before the connection can be established fully. Timeout parameters INBOUND_CONNECT_TIMEOUT_<listener_name> in the LISTENER.ORA file and SQLNET.INBOUND_CONNECT_TIMEOUT in the SQLNET.ORA need to be reviewed. If using the default of 60 seconds, (no explicit setting), then it is likely these parameters will need to be increased.
Review and check network settings for the server machine where the database is running. Ensure settings are all correct and as expected and DNS servers are available
If the server platform is Microsoft Windows, check the Windows Services for TNS listener and database ensure each service is started using the same account.
Note: Some helpful information about finding diagnostic output.
Note 438148.1 How to Find the alert.log File (11g and Later)
REFERENCES
NOTE:1050942.1 - How to Trace the Network Packets Exchanged Between JDBC and the RDBMS NOTE:609.1 - ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log NOTE:793415.1 - How to Perform the Equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver NOTE:438148.1 - How to Find the alert.log File (11g and Later)
