Recently, one developer came to me complaining about error ORA-17410. They are using JDBC thin to connect to Oracle databaes. The error was interesting because the application was executing a MERGE statement, which was used to compute quarterly commission fee. Those data would already have been computed on a daily basis and the task would succeed if the time span is shorter than 90 days( one week, for intance). The task would be executed at the beginning of every quarter and during last execution ORA-17002 connection reset was raised.
The relevant information is as follows:
DB version: Oracle 22.214.171.124 RAC on Solaris SPARC 10 JDBC Driver version: 10.2.0 JDK version: 1.7
Searching JDBC and ORA-17410 on MOS lead me to the following documents:
Starting With Oracle JDBC Drivers (Doc ID 401934.1) Failure While Fetching Rows from ResultSet ORA-17410 ORA-1013 ORA-3111 on JDBC/thin SSL (Doc ID 1083922.1) 11g JDBC Driver Changes Behavior While Using A Firewall Between The Client And The Database Server (ORA-17410) (Doc ID 1325081.1) Unable to Connect Using JDBC / SSL With Firewall Between Networks (Doc ID 1082926.1) JDBC Session Stays In The Database After The Java Program Was Canceled Using CTRL + C (Doc ID 1394687.1)
I noticed that it states specifically that ONLY Oracle JDBC driver 126.96.36.199 (and greater) versions support JDK 7 and JDK 8 in doc 401934.1. However, I erroneously determined that this was not the root cause of our issue and continued my focus on tcpdump snapshots and firewall configurations. I was excited that we could reproduce this issue on test servers at will.
In wireshark, I can see that the DB server returned several Marker, Attention responses to application server. Subsequently, the application replied with “TCP segment of a reassembled PDU” and DB sent FIN to active close the connection. Based on those observations, I suspected that some kind of error occurred during MERGE, although I didn’t know what kind of error it could be, and due to configuration errors of firewall, the application exited unexpectedly.
After several days of fruitless experiments and discussions, I finally realised that maybe I should review the JDBC driver version again: my initial conclusion that JDBC driver version is not relevant was too subjective and baseless. Indeed, when I upgraded the JDBC driver to 11.2.0 and executed the task again, it worked!
In retrospect, I learned that if you want to rule out one possibility, you must have concrete evidence. Be objective.