[oracle@orcl12x ~]$ oerr ORA 12516
12516, 00000, "TNS:listener could not find available handler with matching protocol stack"
// *Cause: None of the known and available service handlers for the given
// SERVICE_NAME support the client's protocol stack: transport, session,
// and presentation protocols.
// *Action: Check to make sure that the service handlers (e.g. dispatchers)
// for the given SERVICE_NAME are registered with the listener, are accepting
// connections, and that they are properly configured to support the desired
// protocols.

Client use the user process and try to connect to the oracle database where PMON monitors and registers the incoming connections.

PMON updates the service to the listener every now and then ,when a new connection connect to the database.

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300

PROCESSES is the parameter which allocates the maximum load for a connection.

When the load on the database increase and reaches to maximum utilization, PMON updates the listener using service update and listener assumes resource starvation even though there are enough processes to handle the requests.

So the listener marks the status as BLOCKED to stop incoming connections.You can see the status below. Sometimes it is pretty much frustating to spend our valuable time troubleshooting a silly error right 🙂

[oracle@orcl12x ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-JUL-2020 09:35:57

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl12x)(PORT=1521)))
Services Summary…
Service “orcl12x” has 1 instance(s).
Instance “orcl12x”, status BLOCKED, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

One of the reason for this error due to less process allocation in the database

1. Increase the process to a higher value to avoid listener handler error!!!

SQL> select count(*) from v$process;

  COUNT(*)
----------
        22

SQL> alter system set processes=500 scope=spfile;

System altered.

Check the max utilization of the process.Follow this rule of thumb!!

if max_utilization = limit_value then go ahead and increase the process limit to a high value.So that application team would be happy always!!

SQL> select * from v$resource_limit where resource_name in ('processes');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
------------------------------ ------------------- --------------- ----------
LIMIT_VALU     CON_ID
---------- ----------
processes                                       22              24        300

2. Second reason might be due to JDBC connection pool errors.If the client is trying to connect to the database using java connection.

By default connection pooling is enabled if not,enable and increase the connection pooling using max pool size

Connection pooling reuses the connections to the database instead of establishing new connection.

Oracle doc says

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Number of sessions = (1.5 * PROCESSES) + 22

The total process and session limit on the database which says the number of users who can connect to the database and process which can be allocated to users

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300

SQL> show parameter session

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     472

Identify the total sessions currently connected to database currently

SQL> select count(1) from v$session;

  COUNT(1)
----------
        52

SQL> select count(1) from v$process;

  COUNT(1)
----------
        63

Total active sessions on the database

SQL> select sid,serial#,username from v$session where status='ACTIVE';

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        37      17176 SYS
        40      10985 SYS
        43      31501 SYS
        45      50043 SYS
        47      54359 SYS

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        67      59264 C##GGUSRS
        73      31623 C##GGUSRS
        74      19460 C##GGUSRS
         85       6987 SYS

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        89       9809 C##GGUSRS
        97      16161 C##GGUSRS
        99      25511 SYS

Identify the resource current and max utilized

SQL> select resource_name,current_utilization,max_utilization from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
processes                                       73              86
sessions                                        85             116

Calculate the load of the session history on database and allocate double the values of utilized sessions and processes parameter to avoid ORA-00018 maximum number of sessions exceeded.

Example: If my max utilization of processes are 500 ,it is always recommended to allocate 1000 for processes parameter likewise for sessions

SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     500

SQL> alter system set processes=1000 scope= spfile
System altered.