Let's Fix TNS-12518 Now

ORA-12518, TNS:listener could not hand off client connection fix is still hard as this is still one of the most common error occurring in the Oracle setup on on-prem or cloud VMs.

 

Step 1: Check listener status

> lsnrctl status

> lsnrctl start

Step 2: Check database status

> ps -ef | grep pmon

> sqlplus '/ as sysdba'

> startup

Step 3: Check process related limits

        > show parameter processes OR select value from v$parameter where name = 'processes';

        > select count(*) from v$processes

> select status,count(*) from v$session

    -- 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;

> alter system set processes = 500 scope = spfile;

> Restart the Oracle instance. i.e. shutdown immediate and then startup commands.

Step 4: 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');

Note: If you have tried all of above and still the issue is occuring, then you need to check with your application owners to make sure that sessions are being closed gracefully. 

Also check if the server is under-resourced in terms of memory and CPU.