Forum Stats

  • 3,759,504 Users
  • 2,251,554 Discussions
  • 7,870,682 Comments

Discussions

ORA-12537: TNS:connection closed

Ray_H
Ray_H Member Posts: 58
edited Nov 18, 2011 4:28AM in General Database Discussions
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:
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

Answers

  • VenkatB
    VenkatB Member Posts: 568
    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.
  • Ray_H
    Ray_H Member Posts: 58
    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.
  • Oviwan
    Oviwan Member Posts: 1,062
    post the following when you logged in as your user:

    $id
    $echo $TNS_ADMIN

    post the tnsnames.ora
  • Ray_H
    Ray_H Member Posts: 58
    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.
  • 900919
    900919 Member Posts: 1
    Hi 896276,

    I am having same problem.

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


    Thanks
    Ahmed S
  • Ray_H
    Ray_H Member Posts: 58
    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.
This discussion has been closed.