ORA-12537: TNS:connection closed

oracle

I'm seeing random TNS connection closed errors when connecting to one of our databases. It's not affecting all users. (Appears to be happening during spikes of user activity) Occasionally I'm also seeing ORA-12518: TNS:listener could not hand off client connection.

The connections are being made from .Net using the ODP driver. (Which generates a lot of connect/disconnect activity)

Best Answer

From here:

Your server is probably running out of memory and need to swap memory to disk.

One cause can be an Oracle process consuming too much memory. A possible workaround is to set following parameter in the listener.ora and restart the listener:
 DIRECT_HANDOFF_TTC_LISTENER=OFF
Should you be working with Multi threaded server connections, you might need to increase the value of large_pool_size.

You could examine the listener log file. By issuing the lsrnctl status command you can see log file. Check listener parameter file and see listener version.

See this blog post for other possible causes and additional help: ORA-12518: TNS:listener could not hand off client connection

Related Solutions

TNS-12535: TNS:operation timed out — Oracle DB

The particular error and the fact that it happens to all applications running against the database would strongly point to a network hiccup as the source of the problem.

How are TNS aliases resolved? Are you using a local tnsnames.ora file?
Assuming you are using a local tnsnames.ora file, is the TNS alias for the database using an IP address or a host name? Using an IP address eliminates the need to hit DNS, so it may be worthwhile to try that in case the problem is that your DNS server is briefly going nuts.
You may also try configuring a backup listener and adding a failover option to the TNS alias. If the problem is that the network hiccups and loses the packets from the client to the listener communication randomly, having a failover option that can be tried may resolve the vast majority of issues without needing to figure out what piece of the network is going flaky. Of course, that assumes that the problem corrects itself quickly enough that the next connection attempt succeeds, but that may well be a reasonable assumption. If adding a backup listener resolves the problem, you can be all but certain that it's a network issue.

Prevent ORA-28001 Error in Oracle 11g – How to Guide

The expiration of passwords in Oracle is controlled by the profile that is assigned to the user. You can see which profile is assigned to each user by querying the DBA_USERS table

SELECT username, profile
  FROM dba_users

You appear to be hitting the PASSWORD_LIFE_TIME limit. Prior to 11g, the DEFAULT profile had this set to UNLIMITED. To improve security, 11.1 started forcing periodic password changes. You can revert to the old behavior either by creating a new profile and assigning that new profile to your existing users or by updating the existing profile that is already assigned to your users. Since it sounds like you want to change this behavior for everyone, it's probably easier to just modify the DEFAULT profile.

ALTER PROFILE default 
 LIMIT password_life_time UNLIMITED

Of course, if the first query indicates that your users are assigned a profile other than DEFAULT, you'd want to alter that profile instead.

If you're interested in what other behaviors a profile controls, the CREATE PROFILE documentation is quite useful.

Best Answer

Related Solutions

TNS-12535: TNS:operation timed out — Oracle DB

Prevent ORA-28001 Error in Oracle 11g – How to Guide

Related Topic