ORA-12535 and TNS-12535
ORA-12535 is the same as TNS-12535, they all indicate that the connection between the client and the listener has been timed out.
First, let’s see how we reproduce TNS-12535.
C:\Users\ed>tnsping compdb
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:03:21
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
TNS-12535: TNS:operation timed out
Causes
TNS-12535 means the connect identifier can be found in tnsnames.ora and the hostname is resolvable by DNS or /etc/hosts, but somehow network problems make the connection timed out.
There could be several causes of TNS-12535 that I listed below.
- The hostname in the connect descriptor of compdb points to a wrong IP address.
- The port specified in the connect descriptor of compdb points to a wrong one.
- The port specified in the connect descriptor of compdb is not opened on firewall of the database server.
- DNS or /etc/hosts points to a wrong IP address while resolving domain name.
- Any other anti-virus software on clients.
- The server has been shutdown, there's no way to go.
Solutions
- Inspect the content of tnsnames.ora closely once again to make sure there's no any typo or mistake.
- Check your DNS, /etc/hosts or C:\Windows\System32\drivers\etc\hosts file to make sure the IP mapping is correct. Additionally, make sure the format of IP mapping is correct.
- Open the port 1521.
- For firewalld or iptables, you may refer to: Open Port 1521 on Linux for Database Server.
- For Windows platform, you may refer to: How to Open Port 1521 on Windows Server.
- Revert any changes of security policy on network appliances including IPS, IDS and firewall. I have ever met a case related to IPS.
- If the server is down, then recover server's availability.
- Usually, TNS-12535 accompanies with ORA-3136 (or ORA-03136), you may consider to raise timeout period on the server.
For more connection troubleshooting, you may refer to Oracle 19c Net Services Administrator's Guide: 15 Testing Connections.