JDBC and Oracle 11g connection reset on ubuntu

oracle oracle11g ubuntu-12.04 ojdbc ora-12170
amit picture amit · Apr 22, 2014 · Viewed 7.3k times · Source

I am having this strange issue connection JDBC to Oracle 11g. It started happening suddenly this Monday without any settings changes that either me or the team were aware of. Reading in Stackoverflow and Oracle forums (see links at the bottom) I've learned that there has been an issue with the generation of random bytes on linux 64bit machines. It led my to try several things, which unfortunately didn't work. Here is a summary of all information I'm aware of.

The weird thing is that I can connect over the terminal using sqlplus, but not using JDBC.

Thank you for any ideas or help,

Amit

My Setup:

OS: Ubuntu 12.04
Java: Both 7 and 6_45 (Issue is the same)
JDBC: Downloaded from the Oracle official site (see further description down)
    Connection behind a Cisco VPN

JDBC-MANIFESTS:

odbc7.jar:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.7.1
Created-By: 20.12-b01 (Sun Microsystems Inc.)
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 12.1.0.1.0
Repository-Id: JAVAVM_12.1.0.1.0_LINUX.X64_130403
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver
sealed: true

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false

odbc6.jar:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.7.1
Created-By: 20.12-b01 (Sun Microsystems Inc.)
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 12.1.0.1.0
Repository-Id: JAVAVM_12.1.0.1.0_LINUX.X64_130403
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver
sealed: true

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false

On Friday everything was still working:

  • I could start my application and have a Java connection to the Oracle DB.
  • Since Monday I'm having this issue
  • My old computer (Ubuntu 12.04) as well, I hadn't touched for almost a week started having this issue too, even though I left it in a working state.

Terminal connection using SQLPLUS is working:

amit@mymachine:/usr/lib/oracle$ sqlplus /@DB_HOST_IP:1521/DB_NAME

SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 22 11:35:58 2014

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

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL>

But connection with JDBC is failing:

DriverManager.getConnection(
                    "jdbc:oracle:thin:@//DB_HOST_IP:1521/DB_NAME", username,
                    password);

Hangs for about 20 seconds and throws:

ava.sql.SQLRecoverableException: IO Error: Connection reset
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:682)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:711)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:558)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at OracleJDBC.main(OracleJDBC.java:32)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read(SocketInputStream.java:168)
    at oracle.net.ns.Packet.receive(Packet.java:311)
    at oracle.net.ns.DataPacket.receive(DataPacket.java:105)
    at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:305)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:249)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:171)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:89)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:123)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:79)
    at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:426)
    at oracle.jdbc.driver.T4C8TTIdty.receive(T4C8TTIdty.java:688)
    at oracle.jdbc.driver.T4C8TTIdty.doRPC(T4C8TTIdty.java:595)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1439)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486)
    ... 12 more

From the server's log:

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version
11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 22-APR-2014 09:57:45
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=<my.ip.address>)(PORT=42738))

From the Oracle documentation on both server errors:

ORA-12535: TNS:operation timed out
Cause: The requested operation could not be completed within the time out period.
Action: Look at the documentation on the secondary errors for possible remedy. See SQLNET.LOG to find secondary error if not provided explicitly. Turn on tracing to gather more information.

ORA-12606: TNS: Application timeout occurred
Cause: A network session did not reach an application-defined stage within the allowed time interval.
Action: This is an error which does not normally appear at the high level. The action to take is application specific, and is detailed in the higher level error description.

Solutions I've tried:

  1. Adding the following system property -Djava.security.egd=file:///dev/urandom as suggested in the Stackoverflow discussion below.

  2. Set up rng-tools to 'help' the entropy generator of the OS. (see links)

  3. Following code runs without a problem producing random byte arrays (regardless of the parameter -Djava.security.egd:

int a = 10000; while (a-- > 0){ byte[] array = new byte[2048]; new SecureRandom().nextBytes(array); System.out.println(a); }

None of the above seem to have changed the error (time-out).

EDIT: new attemps:

  1. Running my application I could recognize the exact query that causes me the live problem. This is when reading a CLOG column of 1-2kb in size.

SQL> select secure_params from session_token where id=11065073; <- query is stuck.

Restart the client,

increasingly updating this field in this specific row I could still read the field if it was 429 charactes in size, but then when I've increased this to 587 chars it got stuck again.

    SQL>  select length( secure_params) from session_token where id=11065073;

    LENGTH(SECURE_PARAMS)
    ---------------------
                      587

SQL> select secure_params from session_token where id=11065073; <- query again stuck.

Attemps number 4 brings me to the thought that there might be an additional issue here, since the application is able to create an initial connection and get some short data from the DB, but only then fails when the results-set gets a bit fat.

END OF EDIT

Links:

Stackoverflow: Oracle JDBC intermittent Connection Issue

Rng entropy generator: http://www.howtoforge.com/helping-the-random-number-generator-to-gain-enough-entropy-with-rng-tools-debian-lenny

Answer

Cameron Kerr picture Cameron Kerr · Apr 26, 2014

ORA-12170 indicates that the TCP connection to the database listener failed.

If this is an error that happens all the time, then check firewalls and network ACLs. Possibly something intermediary has changed?

Alternatively, it could be some other artifact impacting TCP connectivity, such as a IP address clash. I've recently had this problem myself, and have blogged about it at http://distracted-it.blogspot.co.nz/2014/04/ora-12170-tnsconnect-timeout-resolved.html

Hope it helps.

Related questions

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More
How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More
ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client

I'm trying to connect to a schema on 11g (v11.2.0.1.0) from a PC with 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one - it comes back with a ORA-01017 Invalid Username/Password error …

Read More