6 Replies Latest reply on Nov 18, 2011 9:28 AM by ray_h

    ORA-12537: TNS:connection closed

    ray_h
      Oracle DB: 11.2.0.1.0 on Linux x86

      I've just installed a database patch, which seems to have gone well. A pre-requisite for this was shutting down the database.
      Following install, I restarted the database but now cannot connect to it unless I'm connecting internally.

      When I attempt to connect normally, I get this:
      sqlplus ray@bvclone
      SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 9 16:04:12 2011

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

      Enter password:
      ERROR:
      ORA-12537: TNS:connection closed

      I've looked at a number of threads on this, none of which seem to help.

      Both tnsnames.ora and listener.ora are looking at the same port (1521)

      The only clue I have is that tnsping gives different results when run as different users:

      As oracle on Linux:
      tnsping bvclone
      (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = atlas)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bvclone)))
      OK (0 msec)

      As myself on Linux:
      tnsping bvclone

      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 09-NOV-2011 16:09:51

      Copyright (c) 1997, 2009, Oracle. All rights reserved.

      Used parameter files:

      Used HOSTNAME adapter to resolve the alias
      Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=nn.nnn.nn.nnn)(PORT=1521)))
      TNS-12535: TNS:operation timed out

      I can see that the service name is missing, which suggests I'm missing something blindingly obvious here....any thoughts on what this could be would be much appreciated.

      Thanks,
      Ray

      Edited by: 896276 on 09-Nov-2011 08:22

      Edited by: 896276 on 09-Nov-2011 08:22
        • 1. Re: ORA-12537: TNS:connection closed
          VenkatB
          Did you change the port number or anything which has made your old TNS entry obsolete?

          Normally, TNS-12535: TNS:operation timed out happens when you are blocked by firewall from accessing the database port from your client.

          If this does not hold true in your case, you may want to check if all SQLNET adapters are fine (after the upgrade).

          $ORACLE_HOME/bin> adapters

          and check if TCP/IP is there.

          If that's fine, check if you have exceeded the number of processes at database level.

          select MAX_UTILIZATION, CURRENT_UTILIZATION, LIMIT_VALUE from v$resource_limit where
          resource_name='processes';

          If nothing works, stop the listener, bounce the database and then restart the listener.
          • 2. Re: ORA-12537: TNS:connection closed
            ray_h
            Thanks Venkat.

            Unfortunately, none of the above worked (yes, even after a listener/database stop/restart).

            I should have mentioned that there are 6 databases being hosted on this one machine, so wonder if that's got something to do with it?

            I feel the tnsping result from another user is the problem - how can the service name not be shown? I'm guessing my tns/listener configuration must be wrong somehow, but I've no idea how.

            Any ideas welcomed, I've hit a brick wall with this one.
            • 3. Re: ORA-12537: TNS:connection closed
              Oviwan
              post the following when you logged in as your user:

              $id
              $echo $TNS_ADMIN

              post the tnsnames.ora
              • 4. Re: ORA-12537: TNS:connection closed
                ray_h
                Managed to solve the problem (with another pair of eyes) - it appears that the patch install had somehow changed the permissions of the tnsnames.ora file, thus denying read-access to anyone other than the oracle user.

                Permissions altered, listeners stopped and started, and all's well.

                Thanks to all of you who took the time to read/respond.
                • 5. Re: ORA-12537: TNS:connection closed
                  900919
                  Hi 896276,

                  I am having same problem.

                  how and where did you given permission for the tnsnames.ora file..


                  Thanks
                  Ahmed S
                  • 6. Re: ORA-12537: TNS:connection closed
                    ray_h
                    Hello there.

                    Somehow the permissions on this file had changed so that only oracle could read them. I suspect it's something I did as opposed to the patch.

                    In my case I just needed to chmod gou+r tnsnames.ora. You'll find it in $ORACLE_HOME/network/admin

                    Hope this helps.