ORA-609 : TNS-12537: TNS:connect…

 

AlertLog中最近出现的错误：

 

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

Fatal NI connect error 12537, connecting to:
 (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: Version11.1.0.7.0 - Production
  Time: 12-DEC-2011 08:52:38
  Tracing not turned on.
  Tns error struct:
    ns main errcode: 12537
   
TNS-12537: TNS:connection closed
    ns secondaryerr code: 12560
    nt main errcode: 0
    nt secondaryerr code: 0
    nt OS errcode: 0
ORA-609 : opiodr aborting process unknown ospid (8484_1)
Mon Dec 12 08:56:28 2011
Incremental checkpoint up to RBA [0x77a5.870bb.0], current log tailat RBA [0x77a5.9ff51.0]
Mon Dec 12 09:00:53 2011

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

 

查询MetaLink:

ORA-609 TNS-12537 and TNS-12547 in 11gAlert.log [ID 1116960.1] 

Cause

The ORA-609 error is thrown when a client connection of any kindfailed to complete or aborted the connection process before theserver process was completely spawned.

Very often, this connection abort is due to atimeout.  Beginning with 10gR2, a default valuefor inbound connect timeout has been set at 60seconds.  This time limit is often inadequate forthe entire connection process tocomplete.   

We have also discovered that the ORA-609 occurs frequently ininstallations where the database is monitored by DB Console and theEnterprise Manager agent(emagent).   After the DB Consoleis started and as a matter of routine, the emagent will repeatedlytry to connect to the target instances.  We cansee frequent emagent connections in the listener.log withouterror.  However, on occasion it may have failed tocomplete the connection process at the database so an ORA-609 isthrown.  The emagent will simply retry theconnection and may be successful on the subsequenttry.  (Provided there is no real fault occurringat the listener or database).  This temporaryfailure to connect will not be reported back to DB Console andthere will be no indication, except for the ORA-609, that a faultoccurred.

 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Solution

It can be somewhat challenging  to determinethe origin of the client that is causing theerror. 

For that reason, we often recommend increasing the values forINBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.orafile as a preventive measure.  If theproblem  is due to connection timeouts, anincrease in the following parameters should eliminate or reduce theoccurrence of the ORA-609s.

e.g.

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

These settings are in seconds.  Again, the defaultis 60.

If the issue persists and inbound connect does not haveany effect, the following steps are intended to helplocate  the client that may be causing theerrors.

1)  Suppress the TNS errors inthe alert.log by setting the following listener.ora fileparameter: 

DIAG_ADR_ENABLED_listener_name=OFF

This will cause the TNS errors to be posted to theORACLE_HOME/network/log/sqlnet.log file that is local to thedatabase and may yield useful information about the client'saddress.

For example, here's a snippet from a server side sqlnet.log whereclient address info was posted:

Production Time: 15-FEB-2010 07:15:01

Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=yourhost)(Port=1521))(CONNECT_DATA=(SID=PROD1DR)(CID=(PROGRAM=sqlplus)(HOST=client_host)(USER=client))))


Observe the PROGRAM and HOST fields on the lastline.  This is where the connectionoriginated.
Be sure to match timestamps in the sqlnet.log with the timestampsof the alert.log errors.  Once you've located theoffending client, you can enable client tracing to try anddetermine the cause:

TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=<dirlocation>
TRACE_TIMESTAMP_CLIENT=TRUE
DIAG_ADR_ENABLED=off  <<<<<11gor newer client requirement

If you need assistance with client or server tracing, please openan SR with Global Customer Support.

2)  Check the listener.log forclient connections that were logged at timestamps that match theORA-609 timestamps as they appear in thealert.log.  The client information is recorded ineach listener.log entry.  Since this error occursAFTER the listener has handled the connection, do not expect to seeerrors in the listener.log.

Here's an example snippet of an incoming client connection that wasposted 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 willoften be recorded.  Again, once you've located theoffending client, enable tracing (see above) to try to capture theconnection failure.

3)  Enable server side Oracle Nettracing and capture the TNS error along with the incomingconnection.
Match the PID that accompanies the ORA-609 to the server tracelabel.  e.g. 

ORA-609 : opiodr aborting process unknown ospid(4799_1)  *Note the PID

This PID would correspond to server tracelabeled:  svr_4799.trc.  Checkthe server trace for either TNS error (the 609 will not appear) andtry to locate the originating client address.  Ifassistance is needed for this investigation, please open an SR withOracle Support.

See below for instuctions on enabling Oracle Net servertracing.

Thefollowing details the discovery of the source of an ORA-609 for areal case:

The alert.log reports the following messages intermittently butfrequently:

MonNov 16 22:39:22 2009
ORA-609 : opiodr aborting process unknown ospid (nnnn)

Enabled Oracle Net server tracing:

TRACE_LEVEL_SERVER=16
TRACE_DIRECTORY_SERVER=<dirlocation>
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 eventwas named svr_5233*.trc.
Of course the timestamps of the alert.log event and the servertrace creation matched as well.

A review of the server trace showed only an EOF failure andthe  TNS-12537 error:

Readunexpected EOF ERROR
nserror: nsres: id=0, op=68, ns=12537

In this particular case, there was no information about the clientin the trace. This is atypical for a server trace.  It may be that the client aborted before all theclient information was posted to the file. However, there was post in the listener.log for an emagentconnection that was established at the same point in time.

Here's an excerpt from a listener.log entry where an emagentestablishes a connection:

PROGRAM=D:\oracle\product\10.1.0\Db_1\bin\emagent.exe)

Checked the EM Agent traces and logs and discovered the followingentry:

FatalNI connect error 12547, connecting to:
(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 thePID:  svr_5233.trc
Also, the timestamp of the agent event matches the timestamp of thealert.log error.


 

Check the following locations for EM Agenttraces. If working with support on this issue and the EM Agent issuspected, upload ALL files under:

$ORACLE_HOME/sysman/log/emagent.trc < Single nodeagent trace location
$ORACLE_HOME/host/sysman/log/emagent.trc < RAC agenttrace location

It was determined that in this case, the emagent was aborting theconnection before it was complete and then simply reconnecting andsucceeding on the subsequent try.  No errors werereported in the listener log or listener trace. No errors werereturned to the DB Console.  There was no apparentoutage of any kind.  No actionwas taken to correct the ORA-609 in this case.  Itwas decided that the message was informational and completelybenign.



Please review the following documents for more information abouttimeouts and tracing:

Note 119706.1 Troubleshooting Guide TNS-12535 or ORA-12535 orORA-12170
Errors

Note 345197.1 Connections that Used to Work in Oracle 10.1Now
Intermittently Fail with ORA-3113,ORA-3106 or ORA-3136 from
10.2 Onwards

Note 405755.1 Files Needed for Troubleshooting an EM 10GService Request
if an RDA is not Available

Note 395525.1 How to Enable Oracle SQLNet Client , Server ,Listener ,
Kerberos and External procedure Tracing from Net Manager

Note 454927.1 Using and Disabling the Automatic DiagnosticRepository
(ADR) with Oracle Net for 11g