ORA-12170
At some points, the client sides received intermittent ORA-12170 repeatedly:
ORA-12170: TNS:Connect timeout occurred
Meanwhile, the server side did not receive any errors either in listener.log or alert.log.
ORA-12170 mean that the failed connections did not reach the listener due to network problem. Most likely, they're blocked by security rules.
Sometimes, there's a time-out, but what would cause the network time out? What factors that blocked the packets of clients in the pathway to database. Here I made a list for the possibilities of ORA-12170:
- Server is Down
- TNS Names
- Wrong IP address in TNS entry, which cannot be reachable in your local area network. There maybe a duplicate entry in your tnsnames.ora.
- Wrong port in TNS entry, which denies all connections by firewall.
- Hardware or software firewall
- Poor quality of network
- Network card interface (NIC) failure
- Anti-Virus software
- Detected suspicious packets and blocked them.
- Was scanning the whole operating system.
- Intrusion Prevention System (IPS)
- Intrusion Detection System (IDS)
- Proxy Server
Sometimes, the network is unable to send your packets to the destination because the database server is down.
Solutions
The first step, you need to make sure everything you provided is correct like I said earlier, which includes checking tnsnames.ora.
The most common mistake is that you didn't open the port 1521 on firewall. That caused ORA-12170. To open port on firewall of the database server, you may refer to these posts:
If the firewall is on network appliance, you should ask your network administrator for help.
In our case, we found an IPS applied new rules recently that could caused the problem eventually. To revert the configuration, we rolled back the policy of that IPS. No more ORA-12170.
Other Factors
By the way, if the network appliance really needs more considerable time to complete the validation, you can raise the inbound connect time value of the listener, which is also the solution to ORA-03136 Inbound Connection Timed Out.
Such cases of ORA-12170 happened on the client sides. The management usually misunderstood as a database problem, and ask DBA for resolving it. But the database was healthy during incidents, this reminds us that not all ORA errors are thrown by the database.