ORA-12518: TNS: listener could not hand off client connection on my database.

Error:

The error occurred during the application connectivity. Suddenly application starts throwing the following error and starts droping the new connection.

ORA-12518: TNS: listener could not hand off client connection on my database.

Application Error: ODBC Error 12518: [Oracle][ODBC][Ora]ORA-12518: TNS:listener could not hand off client connection

Solutions:

  1. Check the resources usage limit in Oracle
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('sessions','processes');

2. Current utilization reached the limit value of the resources. Then we need to increase the process parameters in Oracle and restart the database to make changes effects.

--- check the processes allocated to DB
SQL> show parameter processes;  

--- Double the  increase the count of processes
SQL> alter system set processes=800 scope=spfile;

3. Restart the Database to make changes in effects:

Shutdown immediate;
Startup;

Note: Check which module causing lot of application process or sessions:

-- Check the no of processes
SELECT s.program,s.machine,count(p.spid) from v$session s,v$process p where 
s.paddr = p.addr group by s.program,s.machine having count(p.spid) > 5;

--Check the no of sessions
SELECT s.program,s.machine,count(*) from v$session s group by s.program,s.machine;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.