This discussion is archived
6 Replies Latest reply: Feb 6, 2013 2:21 AM by Justin_Mungal RSS

ORA-12170: TNS: Connect Timeout

930592 Newbie
Currently Being Moderated
Hi,

currently we have a problem connecting a client to an oracle database. In advance I have to straighten out that I'm just the firewall administrator and don't know anything about oracle. Perhaps you can help me.

The client gets the following error message:
SQL*Plus: Release 11.2.0.1.0 Production on Mi Mrz 14 17:08:35 2012

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

Kennwort eingeben:
ERROR:
ORA-12170: TNS: Connect Timeout aufgetreten
Between the client and the oracle db there are two firewalls which establish a vpn connection, so the route is:

client -> firewall -> vpn tunnel -> firewall -> oracle scan listener -> oracle db

On the firewall which establish the vpn connection we use NAT, so the client connects to the NAT address of the scan listener which will be translated by the firewall and sent to the second firewall using the vpn tunnel.

After searching a long time I found the troubleshooting guide "ORA - 12170 Occured While Connecting to RAC DB using NAT external IP address [ID 453544.1]" which says that the problem lies with the NAT firewall. To fix the problem, there are two solutions:
Solution 1:

1. Set the LOCAL_LISTENER database parameter to point to its local listener's end point.

node1
-----
local_listener='(address=(protocol=tcp)(host=node1)(port=1521))'

node2
-----
local_listener='(address=(protocol=tcp)(host=node2)(port=1521))'

node1,node2 are the hostname of the RAC nodes. If the RAC is 10g version then use its vip-hostname

2. Verify the listener services output 'lsnrctl services <listener_name>'

Service "oracle" has 2 instance(s).
Instance "oracle1", status READY, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Instance "oracle2", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1521))

Make sure that the 'lsnrctl services' output shows the expected hostname. The hostnames 'node1' or 'node2' will be sent to the client when redirection occurs.

If you are in MTS mode, make sure that the dispatcher is started on the expected hostname. You can verify it with the 'lsnrctl services' command. And that >hostname should have its
corresponding external IP address.

3. Configure your client as shown below,

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
)
)
)

4. Make sure that the clients /etc/hosts file contains proper mapping. The important thing here is that the hostnames 'node1' and 'node2' should be mapped to its >External NATed IP addresses.

For example if the RAC nodes external IP addresses are 150.10.10.1 and 150.10.10.2
And its internal IP addresses are 10.10.10.1 and 10.10.10.2 then your clients hosts entry should look like the below,

150.10.10.1 node1
150.10.10.2 node2

This configuration makes sure that the client will always use the RAC nodes External IP address while connecting and while redirection occurs, thus you do not >require the overhead of installing and
configuring CMAN.
Instead of an entry in the hosts file, you can make use of DNS if you have one in your network environment.

Solution 2:

Use CMAN in between NATing firewall and your RAC DB. This will avoid the redirection packet sent back to the client.
I asked the oracle db admin if he could implement one of the two solutions mentioned in the document but he answered that it's not possible because they use oracle 11g (RAC One Node) with a SCAN Listener. Is that true? Isn't it to possible to connect to a db behind a SCAN Listener via NAT? What can I do to fix this problem?

Thanks in advance.
  • 1. Re: ORA-12170: TNS: Connect Timeout
    sb92075 Guru
    Currently Being Moderated
    927589 wrote:
    Hi,

    currently we have a problem connecting a client to an oracle database. In advance I have to straighten out that I'm just the firewall administrator and don't know anything about oracle. Perhaps you can help me.

    The client gets the following error message:
    SQL*Plus: Release 11.2.0.1.0 Production on Mi Mrz 14 17:08:35 2012

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

    Kennwort eingeben:
    ERROR:
    ORA-12170: TNS: Connect Timeout aufgetreten
    Between the client and the oracle db there are two firewalls which establish a vpn connection, so the route is:

    client -> firewall -> vpn tunnel -> firewall -> oracle scan listener -> oracle db
    the most common cause for ORA-12170 is Firewall exists between or on Client & DB Server.

    Firewall must be configured to allow DB Server to open new Connection to client on any port# above 1024
  • 2. Re: ORA-12170: TNS: Connect Timeout
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    this error sometime related to network configruation Please check with network administrator about this .

    Osama ...
  • 3. Re: ORA-12170: TNS: Connect Timeout
    949206 Newbie
    Currently Being Moderated
    make firewall exception for 1521/port where tnsnames.ora connects. this solved by problem
  • 4. Re: ORA-12170: TNS: Connect Timeout
    989429 Newbie
    Currently Being Moderated
    From just earned experience, the client needs to talk to the hostnames of the cluster VIPs directly, not the SCAN address.

    So, the troubleshoot article is correct, including the need for a correct /etc/hosts which maps the cluster hostnames to the corresponding NAT addresses.

    We use a two node 11g RAC, so I could imagine that for a one-node RAC you just have to leave out the load balancing part in the client's tnsnames.ora, but still talk to the node directly, not the scan address. When using the scan address, tcpdump -A clearly showed that the listener returned the internal IP of the cluster node, not the NAT address and also not the hostname, which caused the client trying to connect to a false address.

    The firewall needs to be configured appropriately, allowing 1521 to the cluster node(s) and correct NATing.
  • 5. Re: ORA-12170: TNS: Connect Timeout
    Justin_Mungal Journeyer
    Currently Being Moderated
    Please review the following so you'll understand how SCAN works:
    http://www.oracle.com/technetwork/products/clustering/overview/scan-129069.pdf
    client -> firewall -> vpn tunnel -> firewall -> oracle scan listener -> oracle db
    I'm going to label the two networks as follows:
    client (network A) -> firewall -> vpn tunnel -> firewall -> oracle scan listener -> oracle db (network B)

    Ping the SCAN from the client on network A, and see what address it resolves to. It depends on how your routing is configured, but you may not be able to connect to internal addresses on network B. In that case, resolution to the internal address would be causing a problem.

    What I would suggest doing is making sure that you can connect to port 1521 on the external IP address via telnet from the client on network A. If that works, configure the client to connect directly to the external IP address of the VIP that correlates to the node that the instance is running on. If that doesn't work, then you likely have some routing/firewall issues to resolve.

    If it works, then you can look at a permanent solution, such as creating a DNS zone that the client on network A can use which will resolve names to their external addresses. You could also configure the hosts file of the client if it's just this one client that will be connecting.
  • 6. Re: ORA-12170: TNS: Connect Timeout
    Justin_Mungal Journeyer
    Currently Being Moderated
    user10677207 wrote:
    From just earned experience, the client needs to talk to the hostnames of the cluster VIPs directly, not the SCAN address.

    So, the troubleshoot article is correct, including the need for a correct /etc/hosts which maps the cluster hostnames to the corresponding NAT addresses.

    We use a two node 11g RAC, so I could imagine that for a one-node RAC you just have to leave out the load balancing part in the client's tnsnames.ora, but still talk to the node directly, not the scan address. When using the scan address, tcpdump -A clearly showed that the listener returned the internal IP of the cluster node, not the NAT address and also not the hostname, which caused the client trying to connect to a false address.

    The firewall needs to be configured appropriately, allowing 1521 to the cluster node(s) and correct NATing.
    That behavior is by design. From the link I posted:

    "When a SCAN Listener receives a connection request, the SCAN Listener will check for the least loaded instance
    providing the requested service. It will then re-direct the connection request to the local listener on the node where the
    least loaded instance is running. Subsequently, the client will be given the address of the local listener. The local listener
    will finally create the connection to the database instance."

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points