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.