1,105,333 Community Members

JDBC Connection Pooling : Network Adapter could not establish the connection

Member Avatar
smzuber
Newbie Poster
2 posts since Nov 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Ezzaral
Posting Sage
7,431 posts since May 2007
Reputation Points: 2,714 [?]
Q&As Helped to Solve: 953 [?]
Skill Endorsements: 31 [?]
Moderator
Featured
 
0
 

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?

Member Avatar
smzuber
Newbie Poster
2 posts since Nov 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
jwenting
duckman
8,333 posts since Nov 2004
Reputation Points: 1,399 [?]
Q&As Helped to Solve: 445 [?]
Skill Endorsements: 35 [?]
Infraction Points: 5
Team Colleague
 
0
 

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).

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article