Oracle11g - Connection timed out on probable idle connections
We have been trying hard to simulate the gradual connection resource reduction observed in the connection pool in our customer's environment. To track it further the oracle-ds.xml is applied with the config below -
<local-tx-datasource>
<jndi-name>OraData</jndi-name>
<connection-url>jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=no)(ADDRESS=(PROTOCOL=TCP) (HOST= host1-ip) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME= oracle-service-name)))</connection-url>
<user-name>USER_MAIN</user-name>
<password>PWD_MAIN</password>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<min-pool-size>30</min-pool-size>
<max-pool-size>100</max-pool-size>
<new-connection-sql>SELECT 1 FROM DUAL</new-connection-sql>
<check-valid-connection-sql>SELECT 1 FROM DUAL</check-valid-connection-sql>
<blocking-timeout-millis>180000</blocking-timeout-millis>
<idle-timeout-minutes>60</idle-timeout-minutes>
<track-statements>true</track-statements>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>
Among the stack trace, we're observing what is mentioned in the URL Oracle JDBC IO Error: Connection timed out - Red Hat Customer Portal, which seems to be in "solution in progress" status for some time. Other error stack we see in the log are as below -
2015-12-29 00:10:02,069 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Exception destroying ManagedConnection org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@40e040e0[state=DESTROYED mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@361b361b handles=0 lastUse=1451307705141 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@398b398b context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@b9c0b9c]
org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (java.sql.SQLException: IO Error: Connection timed out)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkException(BaseWrapperManagedConnection.java:541)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.destroy(BaseWrapperManagedConnection.java:255)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.doDestroy(InternalManagedConnectionPool.java:539)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.removeTimedOut(InternalManagedConnectionPool.java:415)
at org.jboss.resource.connectionmanager.IdleRemover$1.run(IdleRemover.java:70)
at java.lang.Thread.run(Thread.java:738)
Caused by:
java.sql.SQLException: IO Error: Connection timed out
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:504)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:3754)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.destroy(BaseWrapperManagedConnection.java:251)
... 4 more
Caused by:
java.net.SocketException: Connection timed out
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:140)
at oracle.net.ns.Packet.receive(Packet.java:282)
at oracle.net.ns.DataPacket.receive(DataPacket.java:103)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230)
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.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:61)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:491)
... 6 more
The JBossAS version in this deployment is the old 4.x version. Oracle version is 11.x, and the oracle jdbc driver is ojdbc6.jar. We've not yet ruled out our application having some connection leaks, but we do not see similar connection resource reduction in pool in our development environment with one-fifth reduced pool size settings, doing similar actions on the application. Customer is very sure there is no firewall or possibility of intermittent short network breaks(between JBossAS host and OracleDB host).
It would be great if we could get inputs on what mistakes we are making, or if we can do more to analyse the issue better.
