Member Avatar for 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

  • 3 Contributors
  • 3 Replies
  • 468 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jwenting

Dani AI

Generated

A short diagnosis and a focused troubleshooting checklist that builds on ’s network point and ’s reminder about connection release. The stack trace in the thread shows a JDBC I/O/network-layer failure; common root causes include listener/connect-string problems, listener or database-side resource limits, OS/TCP limits (ephemeral ports / TIME_WAIT), IPv4/IPv6 mismatches, or transient network/firewall timeouts. Documented JDBC troubleshooting lists these same categories and recommends OS, listener and driver-level checks as first steps. (docs.oracle.com)

Immediate checks (fast wins — run during the problem and while reproducing under load):

  • Check the listener and services: lsnrctl status / lsnrctl services and scan listener.log for refused/hand-off messages.
  • Check DB resource limits and current sessions: query v$resource_limit and v$session.
  • Watch OS sockets and TIME_WAIT counts (ss -s, ss -tan | grep TIME_WAIT | wc -l) and inspect ephemeral port range (/proc/sys/net/ipv4/ip_local_port_range).
  • Capture a short tcpdump on the DB port to see resets/timeouts.

Example commands / SQL:

lsnrctl status
lsnrctl services
ss -s
ss -tan | grep TIME_WAIT | wc -l
cat /proc/sys/net/ipv4/ip_local_port_range

-- as SYSDBA
SELECT resource_name, current_utilization, max_utilization, limit_value
  FROM v$resource_limit
 WHERE resource_name IN ('SESSIONS','PROCESSES');

SELECT username, program, status, COUNT(*) FROM v$session GROUP BY username, program, status;

Run listener and sqlnet tracing if the above are inconclusive. (docs.oracle.com)

Application-side mitigations (why batching fixed it in test): reduce large single-result traffic and tune fetch behavior — use Statement.setFetchSize() or Oracle’s setDefaultRowPrefetch() so the driver pulls rows in manageable packets, and prefer forward-only streaming result sets for huge scans. Also move off the legacy OracleConnectionCacheImpl to a modern pool (Oracle UCP or a well-supported third-party pool) and enable validation/abandoned-connection reclamation, connection-wait timeouts and login/connect timeouts. Upgrading an ancient JDBC driver (ojdbc14 era) is also recommended. (docs.oracle.com)

Operational notes: reproduce with a controlled load generator while monitoring ss/netstat, listener logs and v$ views; check for ephemeral-port exhaustion and tune OS TCP keepalive / port-range only if necessary. Kernel TCP/sysctl tuning and connection-pooling (reuse) are common fixes when short-lived connections exhaust local ports. (kernel.org)

Summary: triage at three levels — network/listener, DB resource limits, and client/pool behavior (fetch sizing + driver/pool). The next useful artifacts when asking for help are listener.log + alert.log snippets, netstat/ss output during failure, and a short tcpdump.

Recommended Answers

All 3 Replies

Member Avatar for 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?

Member Avatar for 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

Member Avatar for 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).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.