TNS-12519 TNS: no appropriate service handler found
ORA-12519 TNS: no appropriate service handler found

Any or all of these errors might appear in the listener.log and may accompany an ORA-12520:

ORA-12520 TNS:listener could not find available handler for requested type of server

The output of the lsnrctl services command may show that the service handler
is in a “blocked” state.

e.g. ‘”DEDICATED” established:1 refused:0 state:blocked’

CHANGES

It is likely a significant increase in load has occurred.

CAUSE

By way of instance registration, PMON is responsible for updating the listener
with information about a particular instance such as load and dispatcher
information. Maximum load for dedicated connections is determined by the
PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE
information varies according to the workload of the instance. The maximum
interval between these service updates is 10 minutes.

The listener counts the number of connections it has established to the instance
but does not immediately get information about connections that have terminated.
Only when PMON updates the listener via SERVICE_UPDATE is the listener
informed of current load. Since this can take as long as 10 minutes, there can be
a difference between the current instance load according to the listener
and the actual instance load.

When the listener believes the current number of connections has reached maximum
load, it may set the state of the service handler for an instance to “blocked”
and begin refusing incoming client connections with either of the following
errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Additionally, an ORA-12520 error may appear in the listener log.

The output of the LSNRCTL services command will likely show that the service handler is “blocked”.

New database connection attempts to Oracle database 11gR2 fail with following Oracle error:

Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found [SQLCode: 12519], [SQLState: 66000] None None

Possible reason

One of the most common reasons for the TNS-12516 and/or TNS-12519 Oracle error being reported on Oracle 11gR2 is 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 the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.

Solution

There are different ways to check if a database instance reaches the maximum number of processes on Oracle 11gR2. One of the quickest SQL see below:

select * from v$resource_limit where resource_name = ‘processes’;

If this is the case, to fix this Oracle error, increase the PROCESSES parameter value to allow the Oracle databaseListener to spawn more server process as required in your environment.

It might be further investigation required though.

Update: 16-Dec-2011

Sometimes when listener throws a TNS error the number of actual processes can be below the limit. See below why this can happen.
When the listener believes the current number of connections has reached maximum load, it may set the state of the service handler for an instance to “blocked” and begin refusing incoming client connections with either of the following errors: ora-12519 or ora-12516. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is then accepting connections connectivity resumes. The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated. Only when PMON updates the listener via SERVICE_UPDATE is the listener informed of current load. Since this can take as long as 10 minutes, there can be a difference between the current instance load according to the listener and the actual instance load.

If you really reached the processes limit and it stays this way for some time you will probably get the following error trying to connect:

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

There are a couple of ‘workarounds’ for this issue … but no real solution at this time (even for Oracle 11.2) since you are not capable to connect to the DB even as SYSDBA:

– Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and kill some sessions:
alter system kill session ‘SID, SERAL#’;

– Stop a less critical application, DB monitoring, etc.

– Kill one or more of the client connections on OS level:

* UNIX: kill -9
* WINDOWS: using ORAKILL …

If none above helps and the error occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.