no more data to read from socket

[karthick ryan]

I received the following error �no more data to read from socket�. The problem is that the database was UP But, instead we applied some ASM patch, but it was done like one node at a time, to avoid downtime.


Is the DB_VALIDATE_CONNECTION parameter setting in OracleDataSource, to do anything with this ?

[Paul Campbell]

Which version of Oracle are you using?

[karthick ryan]

Oracle 9i.

Also I did the following steps:
1. Re-started my database when the application is alive
2. Tried to login to the application
3. I got the following exception:
NO MORE DATA TO READ
4. Then i set the Oracle Data Source property DB_VALIDATE_CONNECTION=true
5. But still i get the following error when i log in first time to the application. But not in subsequent attempts. I am able to login to the application except for the first time.
�java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache�.


Please let me know why is this.

Thanks!!

[Paul Campbell]

You should catch the stale connection error and retry the connection after the connection pool is purged.

Here is more information about it:

If a JDBC or JMS connection becomes invalid while it is in a connection pool, then a StaleConnectionException will occur when an application tries to use the connection. WebSphere Application Server will then discard its reference to the connection and purge the connection pool based on the Purge Policy setting. The best practice for dealing with stale connections is to handle them in the application by catching the StaleConnectionException and then retrying the connection (after the pool is purged). For more information about this, see this technote(http://www.ibm.com/support/docview.wss?rs=180&uid=swg21063645), this Information Center reference (http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/cdat_stalecon.html) on stale connections, and this Information Center reference(http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/rdat_stalconexp.html) on how to handle the StaleConnectionException. Another option is to pretest each connection to check for invalid connections, but this does add the overhead of running a test query on every connection allocation. Here is more information on pretesting connections(http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/tdat_pretestconn.html).

For more information on best programming practices for connection pools, see this article(http://www-128.ibm.com/developerworks/websphere/library/bestpractices/using_jdbc_connection_pooling.html).