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;
col PROTOCOL for a40
SELECT NAME "NAME", SUBSTR(NETWORK,1,23) "PROTOCOL", OWNED,
STATUS "STATUS", (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY"
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.