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.

Bullroarer

do you use connection pooling in your application? – Troopship

We are using spring-boot-starter-jdbc that has dependency to tomcat-jdbc that is default. Do I need to manually specify the following setting if I use the Tomcat connection pool?: # Number of ms to wait before throwing an exception if no connection is available. spring.datasource.tomcat.max-wait=10000 # Maximum number of active connections that can be allocated from this pool at the same time. spring.datasource.tomcat.max-active=50 # Validate the connection before borrowing it from the pool. spring.datasource.tomcat.test-on-borrow=true – Bullroarer

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.

Jeremyjerez

Answer 2

c.zaxxer.hikari.pool.ProxyConnection|HikariPool-1 - Connection oracle.jdbc.driver.T4CConnection@2f72db6d marked as broken because of SQLSTATE(08000), ErrorCode(17410) java.sql.SQLRecoverableException: No more data to read from the socket

The above exception is coming from the database database side if you have written proper code, there are many possible reasons for this exception -

1. Database Configurations - Check the database configuration like URL, username or password.

2. Network - Check the connection between your application and the Oracle database server.

3. Database Server Load - Check the load on the Oracle database server.

4. Oracle JDBC Driver Issues - Make sure you are using a compatible version of the Oracle JDBC driver with your Oracle database. Upgrading or downgrading the JDBC driver may resolve compatibility issues.

5. Connection Pool Configuration - Review your connection pool configuration, especially the maximum connection pool size. If the pool is too small, it may lead to connection issues.

This may be the cause, In my case, someone had changed the database password, therefore I was getting this issue.

Thank You

Aestivation