JDBC pooling Oracle driver Socket read timed out [duplicate]

Posted on

Question :

I run Java EE application on Glassfish server v3 together with Oracle 12 DB on the same machine under Windows Server 2012 64bit. I use latest ojdbc7 driver.

Connection pool config:

<jdbc-connection-pool validation-table-name="DUAL" steady-pool-size="20" statement-cache-size="100" associate-with-thread="true" statement-timeout-in-seconds="30" idle-timeout-in-seconds="60" max-wait-time-in-millis="2000" validate-atmost-once-period-in-seconds="20" datasource-classname="oracle.jdbc.pool.OracleDataSource" pool-resize-quantity="5" max-pool-size="60" res-type="javax.sql.DataSource" name="dbPool" is-connection-validation-required="true">
  <property name="driverClass" value="oracle.jdbc.OracleDriver"></property>
  <property name="user" value="xxx"></property>
  <property name="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="></property>
  <property name="password" value="xxx"></property>
  <property name="portNumber" value="1521"></property>
  <property name="databaseName" value="orcl"></property>
  <property name="serverName" value=""></property>
  <property name="oracle.jdbc.ReadTimeout" value="300000"></property>
  <property name="oracle.net.CONNECT_TIMEOUT" value="10000"></property>

After 2 or 3 hours, when there is more than 1 user (3-5) using my application, it stops responding and I get this in glassfish logs

javax.enterprise.resource.resourceadapter.com.sun.enterprise.resource.allocator|_ThreadID=152;_ThreadName=Thread-2;|RAR5038:Unexpected exception while creating resource for pool dbPool. Exception : javax.resource.spi.ResourceAllocationException: Connection could not be allocated because: IO Error: Socket read timed out
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: IO Error: Socket read timed out

From the database side it looks like this

Fatal NI connect error 12560, connecting to:

    TNS for 64-bit Windows: Version - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version - Production
  Time: 13-JUN-2014 03:14:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12560

TNS-12560: TNS:protocol adapter error
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (3404) as a result of ORA-609

When I just reset db listener everything works ok for next 1-2 hours (depends on application load). So temporary solution is to run bat script from windows scheduler to reset the listener every 1h.

I tried everything I could find – applied these parameters:

- Sqlnet.ora:
- Listener.ora:

But still without success

Answer :

Since the client is at the same server as the database you could try the IPC protocol instead of TCP as a workaround.
It is not only independent of the TCP traffic conditions but also MUCH quicker than TCP.

Your connection string would be something like


As you are using connection pooling, your only verification should be dead connection (SQLNET.EXPIRE_TIME).
Oracle has several notes explaining it’s functionality but it basically says it’s deppendent of the TCP network layout:

  • Dead Connection Detection (DCD) Explained – Doc ID 151972.1.

  • Common Questions About Dead Connection Detection (DCD) – Doc ID 1018160.6

It could be a profile configuration problem (the user connected is not allowed to stay inactive for more than x seconds, the IDLE_TIME limit).
You could verify your profiles with:

set linesize 1000
set pagesize 1000
select *
from dba_profiles
order by profile;

Also get your network checked.

Leave a Reply

Your email address will not be published. Required fields are marked *