How to Resolve ORA-12170: TNS:Connect timeout occurred

  • by

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.

Causes

Obviously, the failed connections did not reach their destination while errors showed on the client side. Sometimes, it's a listener time-out to reject connections, but why would the listener 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:

  • Hardware or software firewall
  • Poor quality of network
  • Network card interface (NIC) failure
  • Anit-Virus software
    • Detected suspicious packets and blocked them.
    • Was scanning the whole operating system.
  • Intrusion Prevention System (IPS)
  • Intrusion Detection System (IDS)
  • Proxy Server

Solutions to ORA-12170

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 your 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 the incidents, this reminds us that not all ORA errors are thrown by the database.

Leave a Reply

Your email address will not be published. Required fields are marked *