25 August 2010

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

If you are seeing this error, first check the listener services status. Notice the 'state:blocked' on my listener below:
oracle@bloo$ 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 "scr9" has 1 instance(s).
Instance "scr9", 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@scr9

SQL*Plus: Release 10.2.0.2.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=bloo)(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='' scope=spfile;

No comments: