1 2 Previous Next 16 Replies Latest reply: Oct 8, 2009 3:25 PM by 726648 RSS

    ORA-12520: TNS:listener could not find available handler .. not resolving.

    726648
      I am trying to resolve this strange listener problem. It resolves and soon appears again. I noticed that the database is up but the connections were not going through when logging in from outside.


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

      Listener is up and running.

      lsnrctl services show that the service was blocked.

      Service "dbtest2" has 1 instance(s).
      Instance "dbtest2", status READY, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:199 refused:0 state:blocked
      LOCAL SERVER
      The command completed successfully


      I ran this in the database, bounced the database.

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

      Listener services show it as ready bow, connections work.... In a few minutes, I get the

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


      again upon connecting ...


      Service "dbtest2" has 1 instance(s).
      Instance "dbtest2", status READY, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
      LOCAL SERVER
      The command completed successfully

      Have you had any experiences with this error? Thanks for any answers.
        • 1. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
          sb92075
          Post Operating System (OS) name & version for DB server system.
          Post results of SELECT * from v$version.
          • 2. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
            726648
            Oops...

            Here is the v$version information:

            SQL> select * from v$version;

            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Enterprise Edition Release 10.1.0.4.2 - Prod
            PL/SQL Release 10.1.0.4.2 - Production
            CORE 10.1.0.4.0 Production
            TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
            NLSRTL Version 10.1.0.4.0 - Production
            • 3. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
              sb92075
              Post Operating System (OS) name & version for DB server system.
              • 4. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                726648
                O.S is windows server 2003 32 bit. Database is 10.1.0.4.2
                • 5. Re: ORA-12520: TNS:listener could not find available handler .. not resolving.
                  635694
                  Hi,

                  You don't neet to set local_listener parameter when you use default port 1521.

                  Why do you want to set this parameter?
                  • 6. Re: ORA-12520: TNS:listener could not find available handler .. not resolving.
                    726648
                    Hi Sukan,
                    Well, it was not working for me. I therefore tried this link:
                    http://www.shutdownabort.com/errors/ORA-12520.php
                    I am not finding much information about the error...
                    • 7. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                      sb92075
                      12520, 00000, "TNS:listener could not find available handler for requested type of server"
                      // *Cause: None of the known and available service handlers for requested type 
                      // of server (dedicated or shared) are appropriate for the client connection.
                      // *Action: Run "lsnrctl services" to ensure that the instance(s) have
                      // registered with the listener and that the appropriate handlers are 
                      // accepting connections. 
                      do the following:

                      lsnrctl stop

                      move/rename listener.ora file to a new name

                      lsnrctl start
                      lsnrctl status
                      lsnrctl service

                      CUT all commands above & their results and PASTE back here
                      • 8. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                        726648
                        Here we go.. It is not showing up in services..




                        C:\Documents and Settings\Oracle.ASRV>lsnrctl stop

                        LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 08-OCT-2009 13:27
                        :32

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

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ASRV)(PORT=1521
                        )))
                        The command completed successfully

                        C:\Documents and Settings\Oracle.ASRV>lsnrctl start

                        LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 08-OCT-2009 13:27
                        :37

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

                        Starting tnslsnr: please wait...

                        TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Production
                        System parameter file is D:\oracle\oracleas\network\admin\listener.ora
                        Log messages written to D:\oracle\oracleas\network\log\listener.log
                        Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ASRV)(PORT=1521
                        )))
                        Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)
                        ))

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ASRV)(PORT=1521
                        )))
                        STATUS of the LISTENER
                        ------------------------
                        Alias LISTENER
                        Version TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Produ
                        ction
                        Start Date 08-OCT-2009 13:27:40
                        Uptime 0 days 0 hr. 0 min. 2 sec
                        Trace Level off
                        Security ON: Local OS Authentication
                        SNMP OFF
                        Listener Parameter File D:\oracle\oracleas\network\admin\listener.ora
                        Listener Log File D:\oracle\oracleas\network\log\listener.log
                        Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ASRV)(PORT=1521)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
                        Services Summary...
                        Service "dbtest2" has 1 instance(s).
                        Instance "dbtest2", status UNKNOWN, has 1 handler(s) for this service...
                        Service "PLSExtProc" has 1 instance(s).
                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                        The command completed successfully

                        C:\Documents and Settings\Oracle.ASRV>lsnrctl status

                        LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 08-OCT-2009 13:27
                        :51

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

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ASRV)(PORT=1521
                        )))
                        STATUS of the LISTENER
                        ------------------------
                        Alias LISTENER
                        Version TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Produ
                        ction
                        Start Date 08-OCT-2009 13:27:40
                        Uptime 0 days 0 hr. 0 min. 13 sec
                        Trace Level off
                        Security ON: Local OS Authentication
                        SNMP OFF
                        Listener Parameter File D:\oracle\oracleas\network\admin\listener.ora
                        Listener Log File D:\oracle\oracleas\network\log\listener.log
                        Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ASRV)(PORT=1521)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
                        Services Summary...
                        Service "dbtest2" has 1 instance(s).
                        Instance "dbtest2", status UNKNOWN, has 1 handler(s) for this service...
                        Service "PLSExtProc" has 1 instance(s).
                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                        The command completed successfully

                        C:\Documents and Settings\Oracle.ASRV>lsnrctl services

                        LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 08-OCT-2009 13:27
                        :54

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

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ASRV)(PORT=1521
                        )))
                        Services Summary...
                        Service "dbtest2" has 1 instance(s).
                        Instance "dbtest2", status UNKNOWN, has 1 handler(s) for this service...
                        Handler(s):
                        "DEDICATED" established:0 refused:0
                        LOCAL SERVER
                        Service "PLSExtProc" has 1 instance(s).
                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                        Handler(s):
                        "DEDICATED" established:0 refused:0
                        LOCAL SERVER
                        The command completed successfully

                        C:\Documents and Settings\Oracle.ASRV>
                        • 9. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                          sb92075
                          lsnrctl services show that the service was blocked.
                          I did not see anything BLOCKED is last post.
                          12520, 00000, "TNS:listener could not find available handler for requested type of server"
                          // *Cause: None of the known and available service handlers for requested type 
                          // of server (dedicated or shared) are appropriate for the client connection.
                          // *Action: Run "lsnrctl services" to ensure that the instance(s) have
                          // registered with the listener and that the appropriate handlers are 
                          // accepting connections. 
                          If/when error happens again, post tail end (last 30 - 40 lines) from listener.log file
                          • 10. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                            726648
                            Hi There, I am still getting the ORA-1250 error. Here are the lines from listener.log. thank you..


                            TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Production on 08-OCT-2009 13:49:43

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

                            System parameter file is D:\oracle\oracleas\network\admin\listener.ora
                            Log messages written to D:\oracle\oracleas\network\log\listener.log
                            Trace information written to D:\oracle\oracleas\network\trace\listener.trc
                            Trace level is currently 0

                            Started with pid=3464
                            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ASRV )(PORT=1521)))
                            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))

                            TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
                            08-OCT-2009 13:49:46 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821762)) * status * 0
                            08-OCT-2009 13:49:46 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=101.101.101.101)(PORT=4936)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:49:46 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=101.101.101.101)(PORT=4940)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:49:51 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821762)) * status * 0
                            08-OCT-2009 13:49:55 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821762)) * services * 0
                            08-OCT-2009 13:49:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=101.101.101.101)(PORT=4946)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:50:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=198.203.239.202)(PORT=2883)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:50:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=198.203.239.202)(PORT=2887)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:50:03 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbtest2)(CID=(PROGRAM=C:\oracle\product\10.2.0\client_1\BIN\sqlplus.exe)(HOST=BOWIT-AVM)(USER=avm)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.100.1.8)(PORT=2925)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:50:04 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=101.101.101.101)(PORT=4957)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:50:04 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=101.101.101.101)(PORT=4959)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:50:04 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=101.101.101.101)(PORT=4961)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            08-OCT-2009 13:50:04 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=dbtest2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=101.101.101.101)(PORT=4964)) * establish * ASRV * 12514
                            TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                            • 11. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                              sb92075
                              (HOST=101.101.101.101)
                              Which host has above IP#
                              12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
                              // *Cause:  The listener received a request to establish a connection to a
                              // database or other service. The connect descriptor received by the listener
                              // specified a service name for a service (usually a database service)
                              // that either has not yet dynamically registered with the listener or has
                              // not been statically configured for the listener.  This may be a temporary
                              // condition such as after the listener has started, but before the database
                              // instance has registered with the listener.
                              // *Action: 
                              //  - Wait a moment and try to connect a second time.
                              //  - Check which services are currently known by the listener by executing:
                              //    lsnrctl services <listener name>
                              //  - Check that the SERVICE_NAME parameter in the connect descriptor of the
                              //    net service name used specifies a service known by the listener.
                              //  - If an easy connect naming connect identifier was used, check that
                              //    the service name specified is a service known by the listener.
                              //  - Check for an event in the listener.log file.
                              When did this last work?
                              What changed?
                              • 12. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                                726648
                                Hi, sorry for any confusion. I assumed it would carry an implied meaning. I changed it (masked my original ip) when posting this reply so that the company ip will not show up publicly.
                                • 13. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                                  726648
                                  When I bounce the database, it is working. I can test login from sqlplus. I also noticed a difference in lsnrctl services.

                                  As shown below, there is one more service in lsnrctl services (after PLSExtProc) when it is working properly.

                                  The last service is in the form dbname.hostname . Again, I replaced the original hostname with just <hostname>.

                                  Its is working now but will stop working again in like 5 mins. It happened already a few times since this morning. I don't know what is going on. lsnrctl services will not list the dbname.hostname again. I will need to bounce the database again and this loop continues .. Something needs to be fixed.

                                  If that service is not listed under lsnrctl services, connections won't work. Please let me know what is wrong here. Thank you!!!


                                  ---------- lsnrctl services after listener stop and start and when connections are not working --------------

                                  C:\Documents and Settings\Oracle.ASRV>lsnrctl services

                                  LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 08-OCT-2009 13:27
                                  :54

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

                                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ASRV)(PORT=1521
                                  )))
                                  Services Summary...
                                  Service "dbtest2" has 1 instance(s).
                                  Instance "dbtest2", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  Service "PLSExtProc" has 1 instance(s).
                                  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  The command completed successfully

                                  C:\Documents and Settings\Oracle.ASRV>




                                  ---------- lsnrctl services when connections are working --------------


                                  C:\Documents and Settings\Oracle.ASRV>lsnrctl services

                                  LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 08-OCT-2009 13:27
                                  :54

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

                                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ASRV)(PORT=1521
                                  )))
                                  Services Summary...
                                  Service "dbtest2" has 1 instance(s).
                                  Instance "dbtest2", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  Service "PLSExtProc" has 1 instance(s).
                                  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0
                                  LOCAL SERVER
                                  Service "dbtest2.<hostname>" has 1 instance(s).
                                  Instance "dbtest2", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:11 refused:0 state:ready
                                  LOCAL SERVER
                                  The command completed successfully

                                  C:\Documents and Settings\Oracle.ASRV>
                                  • 14. Re: ORA-12520: TNS:listener could not find available handler .. not resolvi
                                    sb92075
                                    When did this last work?
                                    What changed?

                                    I do NOT care about actual IP#.
                                    Is listener looking on correct host for requested Oracle Service/SID???
                                    lsnrctl status showed HOST=NAME
                                    Does NAME map to masked IP#?
                                    1 2 Previous Next