Monday, May 20, 2013

Network Adapter Could not Establish Connection

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):

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

No comments:

Post a Comment