Problems caused by ORA-12519

See this post mentioned a ORA-12519 problem handling:

Oerr explanation:

12519,000 00, "TNS: no appropriate service handler found"
// * Cause: The listener cocould 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.

The practice on the post is to check the lsnrctl service, the instance has been registered, and the status is ready.

MOS 240710.1 illustrates this problem and points out:

By way of instance registration, PMON is responsible for updating the listener with information about a special 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.

Using the instance registration method, the PMON process is responsible for updating the monitoring information (load, distribution, etc ). The maximum load is determined by the PROCESSES parameter. The update frequency of PMON services varies depending on the instance load. 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.

The listener calculates the number of connections that have been connected to this instance, but does not immediately know the information about the process to be terminated. The listener only knows the current load when PMON updates the listener through SERVICE_UPDATE. Since the interval is 10 minutes, there is a difference between the load currently known by the listener and the actual 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 cocould 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 ".
E.g. '"DEDICATED" established: 1 refused: 0 state: blocked'

When the listener determines that the current number of connections has reached the maximum load, it sets the service handler status of each instance to "blocked" and begins to reject new client connection requests. An error is returned:

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

SPFILE error causing database startup failure (ORA-01565)

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

1 2 Next Page