ORA-12519: TNS:no appropriate service handler found (followed by ORA-00020)

SCOPE

Application complains about the error ORA-12519: TNS:no appropriate service handler found while connection to the database.

Cause:  The listener could not find any available service handlers that are appropriate for the client connection.

Action: Run “lsnrctl services” to ensure that the instance(s) have registered with the listener, and are accepting connections.

But LSNRCTL SERVICES shows nothing unusual, so why can’t you get connected today when it was working fine a few hours back. One of the reasons is that your database reached maximum of allowed connections/processes, and therefore listener is blocking incoming connections.  This can be confirmed if you find this in your database logs.

Looking at the alert log made the obvious clear.

ALERT LOG

ORA-00020: maximum number of processes 0 exceeded
ORA-20 errors will not be written to the alert log for the next minute. 
Please look at trace files to see all the ORA-20 errors.
Thu Sep 02 09:30:01 2021

Using below two queries we will check what is the overall utilization and which are the sessions from schema are utilizing more number of sessions.

To check overall utilization

set linesize 300
col LIMIT_VALUE        for a15
col "%"                for 9999999
col INITIAL_ALLOCATION for a20
col ATTENTION          for a30
select INST_ID,RESOURCE_NAME,current_utilization,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE,(ROUND(((CURRENT_UTILIZATION/TO_NUMBER(LIMIT_VALUE)))*100)) "%"
, case when ((ROUND(( (CURRENT_UTILIZATION/to_number(LIMIT_VALUE)) )*100))>85.00) then '---(>85.00)% full ##'
else 'Good'
end as ATTENTION
from gvr$esource_limit
WHERE resource_name in ('processes', 'sessions');

 INST_ID    RESOURCE_NAME  CURRENT_UTILIZATION  MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE % ATTENTION
---------- --------------- -------------------- --------------- ------------------ ------------- ---------
 1 	    processes 	    152 		600 		600 		            25       Good
 1 	    sessions 	    121 		928 		928 		            13 	     Good

To check utilization with schemas or machines to identify which are causing any potential process exhaustion 

select distinct
        s.inst_id,
        s.username,
        s.machine,
        count(*)
from    gv$session s,
        gvp$rocess p
where   s.paddr       =  p.addr
and     s.inst_id     =  p.inst_id
GROUP BY         s.inst_id,
        s.username,
        s.machine
ORDER BY 4 desc;

From output of above queries we realised the connections with the schema WEB_TECH user was fluctuating and hitting the max processes and blocking new connections to the database. 

We proceed to kill the inactive sessions for schema WEB_TECH using below dynamic query.

select 'kill -9 ' || p.SPID, s.USERNAME, 'alter system kill session '''||sid||',' ||s.serial# ||''';',s.STATUS
from v$session s, v$process p
where s.PADDR = p.ADDR (+)
and s.STATUS='INACTIVE' and s.USERNAME = 'WEB_TECH'
order by 1;

Or you can use a procedure as follow

begin
    for rec in (select 'alter system kill session ''' || sid ||','|| serial# || '''' killcmd from v$session where username = 'WEB_TECH' and STATUS = 'INACTIVE')
        loop 
         execute immediate rec.killcmd;
        end loop;
end;
/ 

NOTE

If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters 

A basic formula for determining these parameter values is as follows:
    processes=x
    sessions=x*1.1+5
    transactions=sessions*1.1  

These parameters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.

sql> alter system set processes=500 scope=spfile;
sql> alter system set sessions=555 scope=spfile;
sql> alter system set transactions=610 scope=spfile;
sql> shutdown immediate
sql> startup

Many a times it happens that the database wont allow to sys users to login.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 02 10:58:44 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
 ORA-00020: maximum number of processes (150) exceeded

In such cases, you can validate the numbers of processes from OS level and kill them if not required.

ps -ef | grep $ORACLE_SID | grep -v grep | grep "LOCAL=NO" 

OR

ps -ef|grep -i DBNAME |grep -v grep|grep -v oramanaged|wc -l

AND

kill -9 `ps -ef | grep LOCAL=NO | grep DBNAME | grep -v grep | awk '{print $2}'`

Found this post interesting? Subscribe us  😊😉