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

«  septembre 2015  »
lmmjvsd
 123456
78910111213
14151617181920
21222324252627
282930 

Newsflash

Du Bluetooth 3.0 dans le prochain iPhone ?

Fudzilla fait une intéressante supposition sur l'apparition éventuelle du bluetooth 3.0 dans la prochaine version de l'iPhone. Grâce aux pré-versions décortiquées de l'iPhone OS 3, on sait qu'Apple intégrera dans ses prochains mobiles une puces Broadcom BCM4329