blog.abigold.fr

  • Increase font size
  • Default font size
  • Decrease font size
Home Les projets chez Abigold Securité sous oracle ORA-12520: TNS:listener could not find available handler for requested type of server

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

Envoyer Imprimer PDF

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

If you are seeing this error, first check the listener services status. Notice the 'state:blocked' on my listener below:

oracle@blog$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.2.0 - Production

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...

Service "srv9" has 1 instance(s).
  Instance "srv9", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:blocked
         LOCAL SERVER

The problem turned out to be caused by the fact that my listener was not named LISTENER. The database was still registering with it somehow, presumably because it was using the default port, but when attempting to open a connection to the service I saw the following error:

/home/oracle/tns_admin$ sqlplus system@srv9

SQL*Plus: Release 10.2.0.4.0 - Production

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

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

To resolved the issue I configured the instances local_listener parameter to point at my listener:

alter system set local_listener=
  '(ADDRESS=(PROTOCOL=TCP)(HOST=blog)(PORT=1521))' scope=spfile;

If you have an entry in your tnsnames.ora that points to the listener, you can simply use the listener name instead:

alter system set local_listener='<listener_name>' scope=spfile;
 
col network for a60
SELECT CONF_INDX, NAME, NETWORK FROM V$DISPATCHER;
 Performance:
col PROTOCOL for a40
SELECT NAME "NAME", SUBSTR(NETWORK,1,23) "PROTOCOL", OWNED,
 STATUS "STATUS", (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY"
 FROM V$DISPATCHER
or:
SELECT MAXIMUM_CONNECTIONS "MAX CONN", MAXIMUM_SESSIONS "MAX SESS",
 SERVERS_STARTED "STARTED", SERVERS_TERMINATED "TERMINATED",
 SERVERS_HIGHWATER "HIGHWATER" FROM V$SHARED_SERVER_MONITOR;

The MAXIMUM_CONNECTIONS is the value of the maximum number of Virtual Circuits in use at one time.

The MAXIMUM_SESSIONS is the highest number of Shared Server sessions in use at one time since the instance started.

The SERVERS_STARTED and SERVERS_TERMINATED columns maintain a running total of Shared Server process creation and termination by PMON (but do not include the number set in the SHARED_SERVERS parameter).

The SERVERS_HIGHWATER value holds the high-water mark for the Shared Server count since the instance startup.


These statistics are useful indicators to check if SERVERS is set too low or too high. I

f the SERVERS_STARTED or SERVERS_TERMINATED are zero, this is an indication that too many Shared Servers may have been configured. S

imilarly, if the values of SERVERS_STARTED and SERVERS_TERMINATED grow quickly, the number for SHARED_SERVERS is likely to be too low

and should be set to SERVERS_HIGHWATER + 1 (the "+ 1" is for good measure and has no intrinsic meaning).

 

 shut down Dispatcher: 

To shut down Dispatcher D002, issue the following statement:

 

ALTER SYSTEM SHUTDOWN 'D002';

 

The Dispatcher stops accepting new connections and will wait until all of the sessions it handles are disconnected before shutting down.

For a more "immediate" shutdown of the Dispatcher (it is in a bad state or it won't shutdown with the above command) issue this command:

 

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';

 

The IMMEDIATE keyword stops the Dispatcher from accepting new connections and immediately terminates all existing connections through that Dispatcher. After all sessions are cleaned up, the Dispatcher process shuts down.

When a Dispatcher is shut down, other Dispatchers will not inherit the name, so it is possible to see some Dispatchers missing. When starting up a new Dispatcher, it will obtain the next open Dispatcher name. In the case where TCPS D002 Dispatcher was shut down, and a new Dispatcher for TCP was started, the new Dispatcher will be called D002. 

Mise à jour le Mardi, 25 Mai 2010 11:38  

Calendrier

«  May 2011  »
MTWTFSS
 1
2345678
9101112131415
16171819202122
23242526272829
3031 

Newsflash

Mélange de grilles de serveurs et de virtualisation, le "cloud computing" pourrait enfin concrétiser le concept d'informatique à la demande. Mais l'offre comporte des restrictions.

 

Infogérance, hébergement mutualisé ou dédié, software as a service (SaaS), ou encore grid computing, les entreprises semblaient avoir déjà l'embarras du choix pour externaliser tout ou partie de leur infrastructure. Qu'apporte donc le cloud computing ? Selon ses promoteurs, il s'agit d'offrir aux entreprises le moyen de "disperser" leur informatique en faisant disparaître toutes les contraintes matérielles et géographiques. "Avec le cloud computing, la notion d'un lieu physique d'exécution de l'application n'existe plus", précise Jean-Marc Ferré, responsable solutions d'infrastructure Europe du sud chez IBM. Ce lieu, rendu transparent par les réseaux, peut en effet changer à tout moment.