1 2 Previous Next 15 Replies Latest reply: Aug 9, 2012 12:58 AM by 817202 RSS

    ORA-12537 TNS:connection closed

    817202
      Hi Guru's,


      My DB Version: 10.2.0

      OS : Solaris


      I have 2 Database installed on the server one with the name of orcl and one with the name of mz50. When i am trying to connect from client side using toad to orcl i am able to connect to that db successfully but when i try to connect to mz50 DB i getting above mentioned error.

      I check the tnsnames file as well as listener file and when i am trying to do tnsping both DB reply

      My tnsname file is
      ORCL =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orcl)
          )
        )
      
      mz50 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = mz50)
          )
        )
      
      mz5audit =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = mz5audit)
          )
        )
      
      BSCSPROD =
        (DESCRIPTION =
          (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = 192.168.32.42)
            (PORT = 1521)
          )
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = BSCSPROD)
          )
        )
      
      TSI =
        (DESCRIPTION =
          (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = 172.30.70.245)
            (PORT = 1521)
          )
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = TSIINDIA)
          )
      )
      
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      listener file is
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = /export/home/oracle/product/10.2.0/db_1)
            (PROGRAM = extproc)
          )
          (SID_DESC =
            (GLOBAL_DBNAME = mz50)
            (ORACLE_HOME = /export/home/oracle/product/10.2.0/db_1)
            (SID_NAME = mz50)
          )
          (SID_DESC =
            (GLOBAL_DBNAME = orcl)
            (ORACLE_HOME = /export/home/oracle/product/10.2.0/db_1)
            (SID_NAME = orcl)
          )
      
      
        )
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          )
        )
      my sqlnet file is :
      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
      SQLNET.AUTHENTICATION_SERVICES = (NONE)
      when i do tnsping i get
      -bash-3.00$ tnsping orcl
      
      TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 08-AUG-2012 22:00:22
      
      Copyright (c) 1997, 2005, Oracle.  All rights reserved.
      
      Used parameter files:
      /export/home/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
      OK (1390 msec)
      
      -bash-3.00$ tnsping mz50
      
      TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 08-AUG-2012 22:01:03
      
      Copyright (c) 1997, 2005, Oracle.  All rights reserved.
      
      Used parameter files:
      /export/home/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mz50)))
      OK (10 msec)
      I done modifications as well as recreate the tnsnames.ora file as well as listner file with giving ip of the machine as well but still not able to connect to mz50 DB .

      Please help me over the same due to this problems my application are not connected to db
        • 1. Re: ORA-12537 TNS:connection closed
          EdStevens
          vk82 wrote:
          Hi Guru's,


          My DB Version: 10.2.0

          OS : Solaris


          I have 2 Database installed on the server one with the name of orcl and one with the name of mz50. When i am trying to connect from client side using toad to orcl i am able to connect to that db successfully but when i try to connect to mz50 DB i getting above mentioned error.

          I check the tnsnames file as well as listener file and when i am trying to do tnsping both DB reply

          My tnsname file is
          ORCL =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
          )
          )
          
          mz50 =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = mz50)
          )
          )
          
          mz5audit =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = mz5audit)
          )
          )
          
          BSCSPROD =
          (DESCRIPTION =
          (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = 192.168.32.42)
          (PORT = 1521)
          )
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = BSCSPROD)
          )
          )
          
          TSI =
          (DESCRIPTION =
          (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = 172.30.70.245)
          (PORT = 1521)
          )
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = TSIINDIA)
          )
          )
          
          EXTPROC_CONNECTION_DATA =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
          )
          )
          listener file is
          SID_LIST_LISTENER =
          (SID_LIST =
          (SID_DESC =
          (SID_NAME = PLSExtProc)
          (ORACLE_HOME = /export/home/oracle/product/10.2.0/db_1)
          (PROGRAM = extproc)
          )
          (SID_DESC =
          (GLOBAL_DBNAME = mz50)
          (ORACLE_HOME = /export/home/oracle/product/10.2.0/db_1)
          (SID_NAME = mz50)
          )
          (SID_DESC =
          (GLOBAL_DBNAME = orcl)
          (ORACLE_HOME = /export/home/oracle/product/10.2.0/db_1)
          (SID_NAME = orcl)
          )
          
          
          )
          
          LISTENER =
          (DESCRIPTION_LIST =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521))
          )
          )
          my sqlnet file is :
          NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
          SQLNET.AUTHENTICATION_SERVICES = (NONE)
          when i do tnsping i get
          -bash-3.00$ tnsping orcl
          
          TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 08-AUG-2012 22:00:22
          
          Copyright (c) 1997, 2005, Oracle.  All rights reserved.
          
          Used parameter files:
          /export/home/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
          
          
          Used TNSNAMES adapter to resolve the alias
          Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
          OK (1390 msec)
          
          -bash-3.00$ tnsping mz50
          
          TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 08-AUG-2012 22:01:03
          
          Copyright (c) 1997, 2005, Oracle.  All rights reserved.
          
          Used parameter files:
          /export/home/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
          
          
          Used TNSNAMES adapter to resolve the alias
          Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blade0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mz50)))
          OK (10 msec)
          I done modifications as well as recreate the tnsnames.ora file as well as listner file with giving ip of the machine as well but still not able to connect to mz50 DB .

          Please help me over the same due to this problems my application are not connected to db
          http://www.dba-oracle.com/t_ora_12537_tns_error.htm
          http://www.oradev.com/ORA-12537_TNS_connection_closed.jsp

          and many, many others turned up with google.
          • 2. Re: ORA-12537 TNS:connection closed
            817202
            Thanks for your response

            But if the problem is that one then i am not able to connect even to orcl but this is happening with mz50 that is also installed on the same machine.I also change the ip with i address and check but still getting the same problem.


            Moreover i am able to connect through sqlplus when try to connect to db on the server through sqlplus to both orcl and mz50. But when i try to connect remotely i am able to connect to only orcl and not to mz50.


            I read the blogs which you posted but if i am able to connect to one db and not to other which is installed on the same server then there is some other reason for the same.


            Kindly help me over the same.
            • 3. Re: ORA-12537 TNS:connection closed
              sb92075
              what clues exist within listener.log file for the failed connection requests

              post results of following OS commands

              lsnrctl status
              lsnrctl service

              COPY results from above then PASTE all back here
              • 4. Re: ORA-12537 TNS:connection closed
                817202
                Thanks for your reply
                -bash-3.00$ lsnrctl status
                
                LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-AUG-2012 06:51:37
                
                Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER
                Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
                Start Date                08-AUG-2012 18:27:47
                Uptime                    0 days 12 hr. 23 min. 53 sec
                Trace Level               off
                Security                  ON: Local OS Authentication
                SNMP                      OFF
                Listener Parameter File   /export/home/oracle/product/10.2.0/db_1/network/admin/listener.ora
                Listener Log File         /export/home/oracle/product/10.2.0/db_1/network/log/listener.log
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=blade0)(PORT=1521)))
                Services Summary...
                Service "PLSExtProc" has 1 instance(s).
                  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                Service "mz50" has 2 instance(s).
                  Instance "mz50", status UNKNOWN, has 1 handler(s) for this service...
                  Instance "mz50", status READY, has 1 handler(s) for this service...
                Service "mz50_XPT" has 1 instance(s).
                  Instance "mz50", status READY, has 1 handler(s) for this service...
                Service "orcl" has 2 instance(s).
                  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
                  Instance "orcl", status READY, has 1 handler(s) for this service...
                Service "orclXDB" has 1 instance(s).
                  Instance "orcl", status READY, has 1 handler(s) for this service...
                Service "orcl_XPT" has 1 instance(s).
                  Instance "orcl", status READY, has 1 handler(s) for this service...
                The command completed successfully
                -bash-3.00$ lsnrctl service
                
                LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-AUG-2012 06:52:42
                
                Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                Services Summary...
                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 "mz50" has 2 instance(s).
                  Instance "mz50", status UNKNOWN, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:2 refused:45564
                         LOCAL SERVER
                  Instance "mz50", status READY, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:34 refused:0 state:blocked
                         LOCAL SERVER
                Service "mz50_XPT" has 1 instance(s).
                  Instance "mz50", status READY, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:34 refused:0 state:blocked
                         LOCAL SERVER
                Service "orcl" has 2 instance(s).
                  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:0 refused:0
                         LOCAL SERVER
                  Instance "orcl", status READY, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:13 refused:0 state:ready
                         LOCAL SERVER
                Service "orclXDB" has 1 instance(s).
                  Instance "orcl", status READY, has 1 handler(s) for this service...
                    Handler(s):
                      "D000" established:0 refused:0 current:0 max:992 state:ready
                         DISPATCHER <machine: blade0, pid: 25216>
                         (ADDRESS=(PROTOCOL=tcp)(HOST=blade0)(PORT=33537))
                Service "orcl_XPT" has 1 instance(s).
                  Instance "orcl", status READY, has 1 handler(s) for this service...
                    Handler(s):
                      "DEDICATED" established:13 refused:0 state:ready
                         LOCAL SERVER
                The command completed successfully
                Edited by: vk82 on Aug 9, 2012 7:54 AM
                • 5. Re: ORA-12537 TNS:connection closed
                  sb92075
                  vk82 wrote:

                  -bash-3.00$ lsnrctl service

                  LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-AUG-2012 06:52:42

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

                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                  Services Summary...
                  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 "mz50" has 2 instance(s).
                  Instance "mz50", status UNKNOWN, has 1 handler(s) for this service...
                  Handler(s):
                  "DEDICATED" established:2 refused:45564
                  Interesting observation above.
                  Obviously something is amiss, but I'm not sure exactly what it might be.


                  what clues exist within listener.log file?


                  post results from following OS command

                  sqlplus scott/tiger@mz50
                  • 6. Re: ORA-12537 TNS:connection closed
                    817202
                    when i am running what u say i am getting this
                    -bash-3.00$ oraenv
                    ORACLE_SID = [orcl] ? mz50
                    ORACLE_HOME = [/export/home/oracle] ? /export/home/oracle/product/10.2.0/db_1
                    -bash-3.00$ sqlplus sys/tiger@mz50 as sysdba
                    
                    SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 9 07:12:03 2012
                    
                    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                    
                    ERROR:
                    ORA-12537: TNS:connection closed
                    
                    
                    Enter user-name:
                    But yesterday i am able to connect
                    • 7. Re: ORA-12537 TNS:connection closed
                      sb92075
                      vk82 wrote:
                      when i am running what u say i am getting this
                      -bash-3.00$ oraenv
                      ORACLE_SID = [orcl] ? mz50
                      ORACLE_HOME = [/export/home/oracle] ? /export/home/oracle/product/10.2.0/db_1
                      I NEVER mentioned doing anything regarding "oraenv"; since it is NOT required!
                      -bash-3.00$ sqlplus sys/tiger@mz50 as sysdba
                      I NEVER said to do anything regarding AS SYSDBA

                      why do you decide to ignore what is requested & substitute your own incorrect variations?
                      If you REALLY knew what you were doing, you would not be here requesting assistance & help!

                      post results from line below!

                      sqlplus scott/tiger@mz50
                      • 8. Re: ORA-12537 TNS:connection closed
                        817202
                        ok sorry for above mentioned


                        here what you require

                        -bash-3.00$ sqlplus scott/tiger@mz50

                        SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 9 07:27:55 2012

                        Copyright (c) 1982, 2005, Oracle. All rights reserved.

                        ERROR:
                        ORA-12537: TNS:connection closed
                        • 9. Re: ORA-12537 TNS:connection closed
                          sb92075
                          on DB Server system do as below as OS user "oracle"

                          date
                          lsnrctl stop
                          mv $ORACLE_HOME/network/admin/listsner.ora $ORACLE_HOME/network/admin/listsner.sav
                          lsnrctl start
                          # now wait 70 seconds to elapse
                          date
                          lsnrctl status
                          lsnrctl service
                          sqlplus scott/tiger@mz50

                          COPY commands & results from above then PASTE all back here
                          • 10. Re: ORA-12537 TNS:connection closed
                            817202
                            As per ur saying:
                            -bash-3.00$ date
                            Thu Aug  9 07:44:49 AFT 2012
                            
                            -bash-3.00$ lsnrctl stop
                            
                            LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-AUG-2012 07:45:35
                            
                            Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                            
                            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                            The command completed successfully
                            
                            mv $ORACLE_HOME/network/admin/listsner.ora $ORACLE_HOME/network/admin/listsner.sav
                            
                            -bash-3.00$ lsnrctl start
                            
                            LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-AUG-2012 07:47:50
                            
                            Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                            
                            Starting /export/home/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
                            
                            TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
                            Log messages written to /export/home/oracle/product/10.2.0/db_1/network/log/listener.log
                            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=blade0)(PORT=1521)))
                            
                            Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                            STATUS of the LISTENER
                            ------------------------
                            Alias                     LISTENER
                            Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
                            Start Date                09-AUG-2012 07:47:54
                            Uptime                    0 days 0 hr. 0 min. 3 sec
                            Trace Level               off
                            Security                  ON: Local OS Authentication
                            SNMP                      OFF
                            Listener Log File         /export/home/oracle/product/10.2.0/db_1/network/log/listener.log
                            Listening Endpoints Summary...
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=blade0)(PORT=1521)))
                            The listener supports no services
                            The command completed successfully
                            
                            -bash-3.00$ date
                            Thu Aug  9 07:48:43 AFT 2012
                            
                            -bash-3.00$ lsnrctl status
                            
                            LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-AUG-2012 07:49:18
                            
                            Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                            
                            Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                            STATUS of the LISTENER
                            ------------------------
                            Alias                     LISTENER
                            Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
                            Start Date                09-AUG-2012 07:47:54
                            Uptime                    0 days 0 hr. 1 min. 24 sec
                            Trace Level               off
                            Security                  ON: Local OS Authentication
                            SNMP                      OFF
                            Listener Log File         /export/home/oracle/product/10.2.0/db_1/network/log/listener.log
                            Listening Endpoints Summary...
                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=blade0)(PORT=1521)))
                            Services Summary...
                            Service "mz50" has 1 instance(s).
                              Instance "mz50", status READY, has 1 handler(s) for this service...
                            Service "mz50_XPT" has 1 instance(s).
                              Instance "mz50", status READY, has 1 handler(s) for this service...
                            Service "orcl" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                            Service "orclXDB" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                            Service "orcl_XPT" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                            The command completed successfully
                            
                            -bash-3.00$ lsnrctl service
                            
                            LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 09-AUG-2012 07:49:45
                            
                            Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                            
                            Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                            Services Summary...
                            Service "mz50" has 1 instance(s).
                              Instance "mz50", status READY, has 1 handler(s) for this service...
                                Handler(s):
                                  "DEDICATED" established:0 refused:0 state:blocked
                                     LOCAL SERVER
                            Service "mz50_XPT" has 1 instance(s).
                              Instance "mz50", status READY, has 1 handler(s) for this service...
                                Handler(s):
                                  "DEDICATED" established:0 refused:0 state:blocked
                                     LOCAL SERVER
                            Service "orcl" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                                Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                     LOCAL SERVER
                            Service "orclXDB" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                                Handler(s):
                                  "D000" established:0 refused:0 current:0 max:992 state:ready
                                     DISPATCHER <machine: blade0, pid: 25216>
                                     (ADDRESS=(PROTOCOL=tcp)(HOST=blade0)(PORT=33537))
                            Service "orcl_XPT" has 1 instance(s).
                              Instance "orcl", status READY, has 1 handler(s) for this service...
                                Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                     LOCAL SERVER
                            The command completed successfully
                            
                            -bash-3.00$ sqlplus scott/tiger@mz50
                            
                            SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 9 07:50:19 2012
                            
                            Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                            
                            ERROR:
                            ORA-12520: TNS:listener could not find available handler for requested type of
                            server
                            • 11. Re: ORA-12537 TNS:connection closed
                              sb92075
                              GOOD!
                              the error has changed

                              post last 100 or so lines from file below
                              /export/home/oracle/product/10.2.0/db_1/network/log/listener.log
                              • 12. Re: ORA-12537 TNS:connection closed
                                817202
                                -bash-3.00$ tail -100 listener.log
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:10 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63651)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:10 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63652)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:11 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63653)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:11 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63654)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:11 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63655)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:11 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63656)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:12 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63657)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:12 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63659)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:12 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63660)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:12 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63661)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:13 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63662)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:14 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63664)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:15 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63665)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:16 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63666)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:18 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63671)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:18 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63672)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:18 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63673)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:18 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63674)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:19 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63675)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:20 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63676)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:21 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63681)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:22 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63682)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:25 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63684)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:26 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63685)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:26 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63686)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:28 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63689)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:30 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63690)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:30 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63691)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:31 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63694)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:33 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63695)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:33 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63696)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:35 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63698)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:37 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63701)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:38 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63702)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:39 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63703)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:39 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63704)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:40 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63705)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:40 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63706)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:41 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63707)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:41 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63708)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:42 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63709)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:42 * service_update * orcl * 0
                                09-AUG-2012 07:57:43 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63710)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:43 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63712)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:45 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63716)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:45 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63718)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:47 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63724)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:48 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63728)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:49 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63730)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                09-AUG-2012 07:57:51 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63738)) * establish * mz50 * 12519
                                TNS-12519: TNS:no appropriate service handler found
                                -bash-3.00$



                                Now what to do next
                                • 13. Re: ORA-12537 TNS:connection closed
                                  sb92075
                                  vk82 wrote:
                                  -bash-3.00$ tail -100 listener.log
                                  TNS-12519: TNS:no appropriate service handler found
                                  09-AUG-2012 07:57:10 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63651)) * establish * mz50 * 12519
                                  TNS-12519: TNS:no appropriate service handler found
                                  09-AUG-2012 07:57:10 * (CONNECT_DATA=(SID=mz50)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.15.101)(PORT=63652)) * establish * mz50 * 12519
                                  none of the posted records are from the sqlplus session.
                                  ALL posted error records are from mis-configured JAVA process that attempts new connection every 20 seconds or so.
                                  What is story behind this rogue JAVA code?

                                  when was last time SQL* Net worked without throwing any errors?
                                  What changed since then.
                                  • 14. Re: ORA-12537 TNS:connection closed
                                    saurabh
                                    check session limit in your database using v$resource_limit. This error might be due to this parameter as number of user connection has reached to its max limit. You are getting telnet but no connection.
                                    I faced the same issue and it was due to number of user connection reached to max limits.

                                    Edited by: saurabh on Aug 9, 2012 9:40 AM
                                    1 2 Previous Next