Saturday, April 11, 2009

ORA-12520: TNS:listener could not fin...


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


Example:

/home/oracle/tns_admin$ sqlplus system/<password>@DATABASE

    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


Details:


None of the known and available service handlers for requested
type of server (dedicated or shared) are appropriate for the
client connection.

Solution:


Run "lsnrctl services" to ensure that the instance(s) have
registered with the listener and that the appropriate handlers are
accepting connections.

Configure the instances local_listener parameter to point at your listener:

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

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

    alter system set local_listener='<listener_name>' scope=spfile;