Oracle 12c - getting SQL Error: 17410, SQLState: 08000

Question

I am working on the project which used ORACLE 12c db as back-end. and My application is build in java 8. It was working fine since last few month. But suddenly client got an error

SQL Error: 17410, SQLState: 08000
No more data to read from socket. 

I searched about the issue, so I found the following solutions related to the 11g not for 12c...

Case 1: When using two JDBC connections accessing the same Oracle database, “SqlException : No more data to read from socket” thrown.

This is because your database doesn’t support connection sharing, or it’s a dedicated server that will not respond to different clients.

Solution: (same to 1 but manual work):

open $ORACLE_HOME/network/admin/tnsnames.ora
change “(SERVER = DEDICATED)” to “(SERVER = SHARED)”
restart database and listener

BUT I am not running two jdbc connections.

Case 2: If you are using java this might helo you: java/jdk1.6.0_31/jre/lib/security/java.security change securerandom.source=file:/dev/urandom to securerandom.source=file:///dev/urandom

Anyone can give the correct reason of this issue and which solution will be best to resolve the issue, so that in future we will not get this again.

Update: I have two separate application that deal with same db. One is a service that create pending items to be process and saving in DB and client is pick the pending item from db and process it. and both runs in separate JVM.

Answer 1

Typically, following error in standard Oracle configuration is due to external reason: SQL Error: 17410, SQLState: 08000 No more data to read from socket.

Questions:

1. Does the Oracle DB and application resides in same network segment?

1.1. Workaround #1: If not, then move them into the same network segment to eliminate router related issue

1.2. Workaround #2: If already same network, or no router issue, then install both DB and application into the same machine to eliminate network related issue

1.3. Info #3: Pay attention to the DB connection idle time from last use. The pattern of this behavior could be due to the idle time when it is across 2 different network segment

2. Does the application is running a long running SQL that takes long time to process in Oracle DB, e.g. long running SELECT, INSERT...SELECT, UPDATE?

2.1. Info #1: Enable SQL trace in order to visualize the SQL that failed, as this could be related and affecting only long running SQL

2.2. Info #2: Run sqlplus from the application server machine to the remote Oracle DB. Keep the connection open and idle after login for 1-3 hours. Simply run any command after let it sit from idle, e.g. desc table1, select sysdate, and see whether the connection is broken. If yes, then talk with DBA and network admin whether they setup a "policy" in either Oracle or network equipment to forcefully close idle connections

2.3. Info #3: Does the application has connection pool manager? If yes, then tune its parameter so that it keeps near 0 idle connections. Progressively increase it to see at what threshold the problem will be visible

3. Is there a firewall involved?

4. Is there a load balancer involved?

5. Is there a virtual hostname that point to different server when application is actively talking to the prior server?

---

In tnsnames.ora, setting "(SERVER = SHARED)" in the TNS is often a wrong approach. Many people doesn't fully understand what is the purpose of this setup, and not addressing the root cause of the problem. In other word, if it is due to network hardware issue, router issue, virtual hostname fail-over, etc.