TNS-12516: TNS:listener could not find available handler with matching protocol stack

Visionando il file trace.log per una istanza ho incontrato questo errore: TNS-12516: TNS:listener could not find available handler with matching protocol stack.
Facendo una ricerca su Metalink esiste una nota 240710.1 che spiega la problematica.

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 11.2.0.2 - Release: 9.2 to 11.2

Information in this document applies to any platform.

Checked for relevance on 15-FEB-2007.

------------------------------------------------------------------------------



Symptoms

Client connections may fail intermittently with any of the following errors:



TNS-12516 TNS: listener could not find instance with matching protocol stack

ORA-12516 TNS: listener could not find instance with matching protocol stack



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".



e.g. '"DEDICATED" established:1 refused:0 state:blocked'

Increase the pfile or spfile setting for PROCESSES.



------------------------------------------------------------------------------



Solution:

Increase the pfile or spfile setting for PROCESSES.

------------------------------------------------------------------------------

Infatti eseguendo lo statement per vedere i parametri iniziali dell'istanza compare:

select name, value from v$parameter where name in ('processes', 'sessions');



NAME            VALUE

---------------------------------

processes           300     

sessions             335

Verificando il conto totale dei processi nel DB quando questa segnalazione compare nel trace.log  con:

select count(*) from v$process;



COUNT(*)             

-----------

297                   

In effetti il sistema sta si avvicinando pericolosamente al limite previsto, quindi occorre aumentare il valore dei process quindi:

SQL> Alter system set processes = 350 scope=spfile;

SQL> shutdown immediate

SQL> startup open

Ovviamente l'istanza deve essere riavviata.