This is a funny scenario: you have a java program that connects to an Oracle database using IP adresses, but cannot connect to the database even if you use the same exact connect string in tnsnames and you're able to connect to that database using the SQL/Plus client.
But let's start from the beginning, when you don't have any issues at all (because the Oracle database server name has a full-qualified domain name and is resolvable):
As you can see, there are three different connect strings defined in this tnsnames file: one with a FQDN as dedicated connection, one with an IP address as shared connection, and the last one with an IP address as dedicated connection. Now you test the name of the server and try to connect using these three services:
Everything was fine as expected, and even this little handy java test program is working:
But what happens when the host name is not resolvable anymore? Like when commenting the nameserver lines in /etc/resolv.conf:
As expected, the first connect string that uses the hostname is not working, but the other two that use IP addresses still work. Therefore, the java program has to work as well, right?
That's not the case. According to Oracle's Metalink note 139775.1, when you're trying to connect to an Oracle service via a dedicated connection using java, the connect string has IP addresses instead of full-qualified domain names, and those IP addresses are not resolvable in the client machine, you will get a Network Adapter Could not Establish Connection error message, even if from that client you can reach the database using the SQL/Plus client. In this example the error message is different, I guess because I'm using Oracle 10g XE.
More information:
Io exception: The Network Adapter could not establish the connection
NL Exception trying to connect to 10g RAC w/JDBC
But let's start from the beginning, when you don't have any issues at all (because the Oracle database server name has a full-qualified domain name and is resolvable):
oracle@test:~$ cd $ORACLE_HOME/network/admin
oracle@test:~/app/oracle/product/10.2.0/server/network/admin$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
XE2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.108.76)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = XE)
)
)
XE3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.108.76)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
As you can see, there are three different connect strings defined in this tnsnames file: one with a FQDN as dedicated connection, one with an IP address as shared connection, and the last one with an IP address as dedicated connection. Now you test the name of the server and try to connect using these three services:
oracle@test:~$ ping hera
PING hera.localdomain (192.168.108.76) 56(84) bytes of data.
64 bytes from hera.localdomain (192.168.108.76): icmp_req=1 ttl=64 time=8.34 ms
64 bytes from hera.localdomain (192.168.108.76): icmp_req=2 ttl=64 time=3.58 ms
--- hera.localdomain ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1003ms
rtt min/avg/max/mdev = 3.589/5.964/8.340/2.376 ms
oracle@test:~$ sqlplus hr/hr@xe
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:16 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
oracle@test:~$ sqlplus hr/hr@xe2
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:21 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
oracle@test:~$ sqlplus hr/hr@xe3
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:26 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Everything was fine as expected, and even this little handy java test program is working:
oracle@test:~$ cat OracleJdbcExample.java |grep jdbc
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.108.76)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))";
oracle@test:~$ javac -g OracleJdbcExample.java
oracle@test:~$ export CLASSPATH=$CLASSPATH:/usr/lib/oracle/MyJDBC/ojdbc6.jar
oracle@test:~$ java OracleJdbcExample
Current Date from Oracle : 2013-05-15 16:06:08
done
But what happens when the host name is not resolvable anymore? Like when commenting the nameserver lines in /etc/resolv.conf:
oracle@test:/etc# ping hera
ping: unknown host hera
oracle@test:~$ sqlplus hr/hr@xe
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:25 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
oracle@test:~$ sqlplus hr/hr@xe2
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:44 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
oracle@test:~$ sqlplus hr/hr@xe3
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:50 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
As expected, the first connect string that uses the hostname is not working, but the other two that use IP addresses still work. Therefore, the java program has to work as well, right?
oracle@test:~$ java OracleJdbcExample
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Connection reset
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:421)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:531)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:221)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at OracleJdbcExample.main(OracleJdbcExample.java:25)
Caused by: java.net.SocketException: Connection reset
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:96)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at oracle.net.ns.DataPacket.send(DataPacket.java:199)
at oracle.net.ns.NetOutputStream.flush(NetOutputStream.java:211)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:227)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:359)
... 7 more
That's not the case. According to Oracle's Metalink note 139775.1, when you're trying to connect to an Oracle service via a dedicated connection using java, the connect string has IP addresses instead of full-qualified domain names, and those IP addresses are not resolvable in the client machine, you will get a Network Adapter Could not Establish Connection error message, even if from that client you can reach the database using the SQL/Plus client. In this example the error message is different, I guess because I'm using Oracle 10g XE.
More information:
Io exception: The Network Adapter could not establish the connection
NL Exception trying to connect to 10g RAC w/JDBC
