1 Reply Latest reply on Dec 28, 2016 8:18 AM by Anup Dey

    Oracle11g - Connection timed out on probable idle connections

    Ranjit P Newbie

      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.

        • 1. Re: Oracle11g - Connection timed out on probable idle connections
          Anup Dey Apprentice

          Hi,

           

          - This is a generic error indicating that connectivity to the backing database server has been interrupted/terminated by something outside of JBoss.

          - The Oracle JDBC driver has lost (or is unable to establish) connectivity to the database server.

          - The exception may be caused by a protocol error, network error, defect in the JDBC driver, back-end RDBMS failure, etc.

          - The exception may relate to firewalls, switches on the network or the actual back-end server (in this case the Oracle server).

          - Validate the connection URL, check connectivity between the systems, network/firewall issues, etc.

          - Need to verify the database is running correctly  at the specified destination

          - Verify the connection properties.

          - Make sure that an instance of Orale Server is running on the host and accepting TCP/IP connections at the port.

          - Make sure that TCP connections to the port are not blocked by a firewall.

          - The error that you see signifies that the sqlserver driver is unable to make a connection to the database, and it is timing out. This is usually a network issue.

          - The fact that restarting JBoss might fix the connections suggests that you may have invalid connections left in your pool after the network issue.

          - You might like to check with your DBA to check what actually went wrong during that time stamp.

          - You can also check your database connectivity is properly established between your application and database

          - Test the network with the usual means like ping, telnet, ssh... connection tests.

           

          Regards,
          Anup