JDBC Connection Pooling : Network Adapter could not establish the connection

[smzuber]

Hi,

We have a client-server application wherein the server has a pooled connection to an Oracle 10i database. The application is developed in Java and we are using Oracle OracleConnectionCacheImpl API to manage the connection pooling( DYNAMIC pooling with a pool size of 5 and we are using ojdbc14.jar)

On certain occasions when activity is extremely high, the server attemtps to retrieve around 2 million datasets/records. The connection pool runs out of connections and we get the following error :

com.indigo.utils.DBEngineException: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at com.indigo.zclasses.DBTradingSource.getConnection(DBTradingSource.java:136)
at com.indigo.zclasses.DBTradingSource.getTicketMessages(DBTradingSource.java:8033)
at sun.reflect.GeneratedMethodAccessor749.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.indigo.basketserver.ServerSideUtil.ConfigUtil(ServerSideUtil.java:88)
at com.indigo.basketserver.RemoteServerImpl.ConfigUtil(RemoteServerImpl.java:543)
at sun.reflect.GeneratedMethodAccessor17.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
at sun.rmi.transport.Transport$1.run(Transport.java:153)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:418)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:521)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:171)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPhysicalConnection(OracleConnectionPoolDataSource.java:149)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPooledConnection(OracleConnectionPoolDataSource.java:95)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPooledConnection(OracleConnectionPoolDataSource.java:63)
at oracle.jdbc.pool.OracleConnectionCacheImpl.getNewPoolOrXAConnection(OracleConnectionCacheImpl.java:547)
at oracle.jdbc.pool.OracleConnectionCacheImpl.getPooledConnection(OracleConnectionCacheImpl.java:404)
at oracle.jdbc.pool.OracleConnectionCacheImpl.getConnection(OracleConnectionCacheImpl.java:298)
at oracle.jdbc.pool.OracleConnectionCacheImpl.getConnection(OracleConnectionCacheImpl.java:268)
at com.indigo.zclasses.DBTradingSource.getConnection(DBTradingSource.java:127)

This is happens in Production and despite our exhaustive efforts,we have been unable to reproduce it in our Testing environment so far.

A large number of login attempts are seen in the Oracle xxxxx table, around 50,000 but we dont think that should be an issue. At the time, when this problem occurs, there are other processes making connection to the DB without any issues. Also, after sometime, the connection is restored and no exceptions are thrown.

Any insight into how we could simulate,resolve or monitor this issue would be greatly appreciated.

Thanks,
Zuber

[Ezzaral]

Well, it's difficult to speculate from a general perspective on a specific implementation such as OracleConnectionCacheImpl . Have you tried searching any Oracle forums specifically for mentions of that behavior? Are you promptly releasing connections when they are no longer needed? Is this a checked exception that you should be prepared to deal with as per the API documents?

[smzuber]

Yes. The connection are released after usage and ideally should go back to the pool.
And its not a checked exception. We noticed that it doesnt happen when activity is slow/normal and only happens when large number of records are being retrieved. Also, it doesnt happen if records are retrieved in blocks of 100

[jwenting]

That error is a network error, not a database error.
Is your application generating so much network traffic it's clogging the pipeline to the database server maybe?

If it's a database error you're going to have an ORA-XXXXXX error number somewhere, which will most likely have something to do with trying to establish more simultaneous connections than your license allows.

As you state it only happens when you're retrieving massive amounts of data in bulk, it's almost certain that you're running out of bandwidth.
Either do batch retrievals and combine the results (Oracle has facilities for this) and/or increase your network's capabilities (we recently upgraded our network from 100Mbit to two 1Gbit lines for just that reason, the system could no longer handle the traffic).