ORA-12518, TNS:listener could not hand off client connection

Question

I am using ORACLE database in a windows environment and running a JSP/servlet web application in tomcat. After I do some operations with the application it gives me the following error.

ORA-12518, TNS: listener could not hand off client connection

can any one help me to identify the reason for this problem and propose me a solution?

Brunswick

dba-oracle.com/… – Sterne

Answer 1

The solution to this question is to increase the number of processes :

1. Open command prompt 
2. sqlplus / as sysdba; //login  sysdba user
3. startup force;
4. show parameter processes;  // This shows 150(some default) processes allocated, then increase the count to 800
5. alter system set processes=800 scope=spfile;

As Tried and tested.

Kopeisk

startup force; -- This caused my database to hang status and nothing was operations. I had to fix it by restarting all the services. – Deryl

Answer 2

In my case I found that it is because I haven't closed the database connections properly in my application. Too many connections are open and Oracle can not make more connections. This is a resource limitation. Later when I check with oracle forum I could see some reasons that have mentioned there about this problem. Some of them are.

1. In most cases this happens due to a network problem.
2. 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.

if it is the second one, please verify large_pool_size or check dispatcher were enough for all connection.

You can refer bellow link for further details. https://community.oracle.com/message/1874842#1874842

Brunswick

Answer 3

I ran across the same problem, in my case it was a new install of the Oracle client on a new desktop that was giving the error, other clients were working so I knew it wouldn't be a fix to the database configuration. tnsping worked properly but sqlplus failed with the ora-12518 listener error.

I had the tnsnames.ora entry with a SID instead of a service_name, then once I fixed that, still the same error and found I had the wrong service_name as well. Once I fixed that, the error went away.

Ginoginsberg

And make sure the listener started :) Happy querying – Celtic

In our case a user had deleted our tnsnames.ora file. That was it. – Rocket

Answer 4

If from one day to another the issue shows for no apparent reasons, add these following lines at the bottom of the listner.ora file. If your oracle_home environment variable is set like this:

(ORACLE_HOME = C:\oracle11\app\oracle\product\11.2.0\server)

The lines to add are:

ADR_BASE_LISTENER = C:\oracle11\app\oracle\

DIRECT_HANDOFF_TTC_LISTENER=OFF

Highness

Answer 5

I had the same problem when executing queries in my application. I'm using Oracle client with Ruby on Rails.

The problem started when I accidentally started several connections with the DB and didn't close them.

When I fixed this, everything started to work fine again.

Hope this helps another one with the same problem.

Citrine

Answer 6

I experienced the same error after upgrading to Windows 10. I solved it by starting services for Oracle which are stopped.

Start all the services as shown in the following image:

Roadstead

Answer 7

I had the same issue. After restarting all Oracle services it worked again.

Shriner

Answer 8

same problem encountered for me. And from oracle server listener log, can see more information. and I found that the SERVICE_NAME is not match the tnsnames.ora configured Service name. so I changed the application's data source configuration from SID value to Service_NAME value and it fixed.

23-MAY-2019 02:44:21 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=XXXXXX$))(SERVICE_NAME=orclaic)) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=50818)) * establish * orclaic * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   64-bit Windows Error: 203: Unknown error

Carbaugh

Answer 9

I had the same issue in real time application and the issue gone by itself next day. upon checking, it was found that server ran out of memory due to additional processes running.

So in my case, the reason was server run out of memory

Dieball

Answer 10

first of all check the listener log check the show parameter processes vs select count(*) from v$processes; increase the process, it may require SGA increase ;

Jackstraw

Answer 11

I faced this issue in newly created oracle DB hosted on windows server. TNSNAMES, LISTENER were fine, TNSping was working. still TNS-12518: TNS:listener could not hand off client connection error used to reflect while making connection through service name.

Along with all the above suggestions do follow the changes I made. hostname was 16 characters, i changed hostname to less than 16 characters. It worked perfect.

Thanks.

Byington

There is no "above" on StackOverflow. Your post is the top most beneath the question, at least for the display order I chose. You do not know where your post appears for any user. – Firecrest

Answer 12

If you are running into this error from a Linux Distribution you may need to change the following settings:

Edit /etc/systemd/system.conf Set DefaultTasksMax to 'infinity'

This could be happening owing to a failure to create the required number of processes!!

Mastoiditis