ORA-00020: maximum number of processes (2000) exceeded

Error: TNS-12518: TNS:listener could not hand off client connection

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

TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error

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;

Second Solution if listener not picking service: We need to manually add the service in listener.ora file

Go to post: https://smarttechways.com/2022/02/24/ora-12518tnslistener-could-not-hand-off-client-connection/