JDBC pooling Oracle driver Socket read timed out [duplicate]

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=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"></property>
  <property name="password" value="xxx"></property>
  <property name="portNumber" value="1521"></property>
  <property name="databaseName" value="orcl"></property>
  <property name="serverName" value="127.0.0.1"></property>
  <property name="oracle.jdbc.ReadTimeout" value="300000"></property>
  <property name="oracle.net.CONNECT_TIMEOUT" value="10000"></property>
</jdbc-connection-pool>

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:
 (LOCAL=NO)

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - 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:
SQLNET.INBOUND_CONNECT_TIMEOUT=180
SQLNET.EXPIRE_TIME=5
- Listener.ora:
INBOUND_CONNECT_TIMEOUT_LISTENER_IPC=120

But still without success

Answer 1

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

jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY = LISTENER)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

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.