ORA-12519: TNS:no appropriate service handler found while connecting to database.

ORA-12519: TNS:no appropriate service handler found while connecting to database.

New database connection attempts to Oracle database fail with following Oracle error

Reason:

One of the most common reasons for TNS-12519 Oracle erroris the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS Listener become “Blocked” and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the database instance telling that thresholds are below the configured limit, the database listener will start to accept new connections

Solution:


Check if a database instance reaches the maximum number of processes using below command.

select * from v$resource_limit where resource_name = 'processes';

eg:

SQL> col LIMIT_VALUE format a20;
col RESOURCE_NAME format a20;
col INITIAL_ALLOCATION format a15;
select * from v$resource_limit where resource_name = 'processes';SQL> SQL> SQL>

RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE
-------------------- ------------------- --------------- --------------- --------------------
processes                            299             300        300             300

1 row selected.


If this is the case, to fix this Oracle error, increase the PROCESSES parameter value to allow the Oracle database Listener to spawn more server process as required in your environment or Kill some inactive and unwanted sessions using alter system kill session ‘SID, SERAL#’;