This issue below might be an application problem, but I cannot seem to rule out a poor database setup, I was hoping to get some insights into potential database issues.
I am maintaining a legacy Java application, our hosting provider provides DBA support for our Oracle database. We were on 10g, several months ago we upgraded to 11g. I in turn upgraded the Java drivers from ojdbc5.jar to ojdbc6.jar. Everything mostly seems fine….
The application uses Hibernate ORM tool, one of the fields of this one entity happens to be a Blob type. When the application saves changes to this entity, the generated SQL is an update statement that includes this Blob column regardless if it has changed or not.
On this one operation, the user updates their annual subscription, so they do it once a year, but when it calls the update here Java throws "Unable to access blob stream!" and then claims the underlying connection is closed. This ONLY occurs for this one operation.
If I completely shut down and restart the server, and then perform the same operation again it works perfectly fine and continues to work fine for that one record forever and ever amen.
But it seems to happen here for every single record in this one table at least once.
Needless to say this is a nightmare to debug because I cannot reproduce it on a single record, just observe it once per record on my test system, then never see it happen again, and yes the same behavior occurs on my test environment that is a mirror of my production database.
My question is if it is possible that when the Oracle 11g upgrade occurred that it corrupted the LOB space in some way to where this seems to be happening?
EDIT:
Now I can't reproduce it on any record….
its like the database felt that I was trying to debug it and temporarily reverted into a state of good behavior
Maybe the LOB space was sleeping, then woke up?
EDIT 2:
The problem happened again this morning, after I left the database to sit unused for several hours.
As requested here is the exact error:
Unable to access blob stream!
Cause: Closed Connection
SQL State: 08003
Vendor Code: 17008
Preliminary research seems to suggest that this might be an issue with the Oracle JDBC drivers? Oracle JDBC 11.2.0.3 ojdbc6.jar
Best Answer
I have faced similar situation for a procedure call with input Blob parameter. Same piece of code was working just fine on 11.2.0.2, but began to throw "java.io.IOException: I/O Exception: Connection reset by peer: socket write error" on 11.2.0.3. I have downgraded from ojdbc6.jar to ojdbc14.jar+ocrs12.jar now it is working. I don't know why but it seems ok for now.