We are using Oracle as the database for our Web application. The application runs well most of the time, but we get this «No more data to read from socket» error.
Caused by: java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1142)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
at org.hibernate.loader.Loader.doQuery(Loader.java:718)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2449)
... 63 more
We use spring, hibernate and i have the following for the datasource in my applciation context file.
<bean class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" id="dataSource">
<property name="driverClassName" value="${database.driverClassName}" />
<property name="url" value="${database.url}" />
<property name="username" value="${database.username}" />
<property name="password" value="${database.password}" />
<property name="defaultAutoCommit" value="false" />
<property name="initialSize" value="10" />
<property name="maxActive" value="30" />
<property name="validationQuery" value="select 1 from dual" />
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="true" />
<property name="poolPreparedStatements" value="true" />
<property name="removeAbandoned" value="true" />
<property name="logAbandoned" value="true" />
</bean>
I am not sure whether this is because of application errors, database errors or network errors.
We see the following on the oracle logs
Thu Oct 20 10:29:44 2011
Errors in file d:oraclediagrdbmsadsadstraceads_ora_3836.trc (incident=31653):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:oraclediagrdbmsadsadsincidentincdir_31653ads_ora_3836_i31653.trc
Thu Oct 20 10:29:45 2011
Trace dumping is performing id=[cdmp_20111020102945]
Thu Oct 20 10:29:49 2011
Sweep [inc][31653]: completed
Sweep [inc2][31653]: completed
Thu Oct 20 10:34:20 2011
Errors in file d:oraclediagrdbmsadsadstraceads_ora_860.trc (incident=31645):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:oraclediagrdbmsadsadsincidentincdir_31645ads_ora_860_i31645.trc
Thu Oct 20 10:34:21 2011
Oracle Version : 11.2.0.1.0
In this guide, we will cover the steps to troubleshoot and resolve the ‘No More Data to Read from Socket’ error. This error is commonly encountered in database connections, specifically Oracle databases. We will go through a step-by-step process to understand the root cause and suggest solutions accordingly.
Table of Contents
- Understanding the ‘No More Data to Read from Socket’ Error
- Step 1: Verify Database Connection Settings
- Step 2: Check Database Listener Configuration
- Step 3: Inspect Firewall and Network Settings
- Step 4: Analyze Database Logs
- Step 5: Update JDBC Driver
- FAQs
Understanding the ‘No More Data to Read from Socket’ Error
The ‘No More Data to Read from Socket’ error occurs when a client tries to read data from a TCP/IP socket that has been closed by the server. This error is usually associated with Oracle databases and JDBC (Java Database Connectivity) connections. There could be several reasons for this error, including connection timeouts, firewall issues, or problems with the database listener.
Step 1: Verify Database Connection Settings
The first step in resolving the ‘No More Data to Read from Socket’ error is to verify the database connection settings. Check the following:
The JDBC connection string should be correct and include the appropriate hostname, port number, and service name or SID. You can refer to the Oracle documentation for the correct connection string format.
Ensure that the database username and password are correct.
Verify that the database server is running and accessible. You can use tools like ping, telnet, or traceroute to check network connectivity.
Step 2: Check Database Listener Configuration
The next step is to check the database listener configuration. The listener is responsible for managing incoming client connections to the database. Follow these steps:
Confirm that the listener is running on the database server. You can use the lsnrctl status command to check its status.
Verify that the listener is configured correctly. The listener.ora file should have the correct hostname, port number, and service name or SID. You can refer to the Oracle documentation for the correct listener configuration.
Check if the listener is configured with a connection timeout. If the timeout value is too low, it may cause the ‘No More Data to Read from Socket’ error. You can increase the timeout value in the listener.ora file, if necessary.
Step 3: Inspect Firewall and Network Settings
Firewall and network settings can also cause the ‘No More Data to Read from Socket’ error. Check the following:
Ensure that the firewall on the database server allows incoming connections on the listener port.
Check if there are any network devices (such as routers or switches) between the client and the server that may be causing connection issues. You may need to consult your network administrator for assistance.
Step 4: Analyze Database Logs
Database logs can provide valuable information on the cause of the ‘No More Data to Read from Socket’ error. Check the following logs:
Oracle alert log: This log contains important database events and error messages. You can find this log in the $ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace directory. Look for any error messages related to the ‘No More Data to Read from Socket’ error.
Listener log: This log contains information about client connections to the database. You can find this log in the $ORACLE_BASE/diag/tnslsnr/<HOSTNAME>/listener/trace directory. Look for any error messages or connection issues related to the ‘No More Data to Read from Socket’ error.
Step 5: Update JDBC Driver
If you are still experiencing the ‘No More Data to Read from Socket’ error, consider updating the JDBC driver. An outdated driver may have compatibility or bug issues that cause connection problems. You can download the latest JDBC driver from the Oracle website.
FAQs
1. What is the ‘No More Data to Read from Socket’ error?
The ‘No More Data to Read from Socket’ error occurs when a client tries to read data from a TCP/IP socket that has been closed by the server. This error is commonly associated with Oracle databases and JDBC connections.
2. Can this error be caused by a firewall or network issue?
Yes, firewall or network settings can cause the ‘No More Data to Read from Socket’ error. Make sure that the firewall on the database server allows incoming connections on the listener port and that there are no network devices causing connection issues.
3. How can I check the listener configuration?
You can check the listener configuration by examining the listener.ora file, which should have the correct hostname, port number, and service name or SID. You can also use the lsnrctl status command to check the listener’s status.
4. Can an outdated JDBC driver cause this error?
Yes, an outdated JDBC driver can cause the ‘No More Data to Read from Socket’ error due to compatibility or bug issues. Updating the JDBC driver to the latest version may resolve the problem.
5. Where can I find the Oracle alert log and listener log?
The Oracle alert log can be found in the $ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace directory, while the listener log can be found in the $ORACLE_BASE/diag/tnslsnr/<HOSTNAME>/listener/trace directory. These logs can provide valuable information to help diagnose the cause of the ‘No More Data to Read from Socket’ error.
How do I troubleshoot the error «javasqlSQLException: No more data to read from socket» when I’m trying to connect to my Amazon RDS for Oracle instance?
I get the error «javasqlSQLException: No more data to read from socket» when I try to connect to my Amazon Relational Database Service (Amazon RDS) for Oracle DB instance.
Resolution
You get the error “javasqlSQLException: No more data to read from socket“ because of a connectivity issue between the Oracle server and the client JDBC driver. The most common reasons and troubleshooting options for these connection failures are the following:
- The connection is abruptly terminated due to network interruptions: To troubleshoot this issue, check the alert.log file of the instance for any TNS timeout errors posted during the time when the connection timed out from the application end. For more information, see Oracle documentation for TNS timeout errors. For more information on accessing the alert log for RDS instances, see Oracle database log files.
- The connection is terminated because of Oracle errors on the server side: Check the alert.log file for ORA-0600 or ORA-07445 errors. Collect the trace dump for specific Oracle errors. Check if these errors have a known fix provided by Oracle support.
- The client-server connection is not active: To troubleshoot this issue, set the parameter SQLNET.EXPIRE_TIME to a specified interval, in minutes, to send a probe that verifies that the client-server connections are active. For more information, see Oracle documentation for SQLNET. EXPIRE_TIME.
- The RDS for Oracle instance is not available or was restarted when the JDBC client was trying to use an existing connection to the Oracle server: To troubleshoot this issue, retrieve events for the RDS instance and check if the instance was restarted or stopped when the connections were established from the JDBC client.
- The JDBC drivers used for connecting to the RDS for Oracle Instance are incompatible: To troubleshoot this issue, confirm that the version of JDBC driver is compatible with that of the DB instance. For the list of compatible JDBC drivers, see Oracle documentation for Compatibility matrix for Java machines and JDBC drivers used with ODI. If the JDBC driver is incompatible, download the latest JAR file in your source code. Then, include this file in your classpath when you compile the class that creates connections to the database. For more information, see Downloading the JDBC driver.
- The memory components on the client side cause timeouts: To troubleshoot this issue, check if the Oracle Data Integrator has memory components on the client side that cause unwanted timeouts. Be sure that you set the correct values for these components on the client side. For more information, see Oracle documentation for How to define Java options (such as the limits of memory heap, the location of non-Java libraries, etc.) in ODI.
Related information
Oracle documentation for A «No More Data to Read From Socket» error has been signaled from an ODI integration interface
AWS OFFICIALUpdated 2 years ago
Relevant content
-
Accepted Answer
-
AWS OFFICIALUpdated 2 years ago
-
AWS OFFICIALUpdated 2 years ago
-
AWS OFFICIALUpdated 2 years ago
-
AWS OFFICIALUpdated a year ago
-
Solution 1
For errors like this you should involve oracle support. Unfortunately you do not mention what oracle release you are using. The error can be related to optimizer bind peeking. Depending on the oracle version different workarounds apply.
You have two ways to address this:
- upgrade to 11.2
- set oracle parameter
_optim_peek_user_binds = false
Of course underscore parameters should only be set if advised by oracle support
Solution 2
We were facing same problem, we resolved it by increasing initialSize and maxActive size of connection pool.
You can check this link
Maybe this helps someone.
Solution 3
Another case: If you are sending date parameters to a parameterized sql, make sure you sent java.sql.Timestamp and not java.util.Date. Otherwise you get
java.sql.SQLRecoverableException: No more data to read from socket
Example statement:
In our java code, we are using org.apache.commons.dbutils and we have the following:
final String sqlStatement = "select x from person where date_of_birth between ? and ?";
java.util.Date dtFrom = new Date(); //<-- this will fail
java.util.Date dtTo = new Date(); //<-- this will fail
Object[] params = new Object[]{ dtFrom , dtTo };
final List mapList = (List) query.query(conn, sqlStatement, new MapListHandler(),params);
The above was failing until we changed the date parameters to be java.sql.Timestamp
java.sql.Timestamp tFrom = new java.sql.Timestamp (dtFrom.getTime()); //<-- this is OK
java.sql.Timestamp tTo = new java.sql.Timestamp(dtTo.getTime()); //<-- this is OK
Object[] params = new Object[]{ tFrom , tTo };
final List mapList = (List) query.query(conn, sqlStatement, new MapListHandler(),params);
Solution 4
This is a very low-level exception, which is ORA-17410.
It may happen for several reasons:
-
A temporary problem with networking.
-
Wrong JDBC driver version.
-
Some issues with a special data structure (on database side).
-
Database bug.
In my case, it was a bug we hit on the database, which needs to be patched.
Solution 5
Try two things:
- Set in $ORACLE_HOME/network/admin/tnsnames.ora on the oracle server server=dedicated to server=shared to allow more than one connection at a time. Restart oracle.
- If you are using Java this might help you: In
java/jdk1.6.0_31/jre/lib/security/Java.securitychangesecurerandom.source=file:/dev/urandomtosecurerandom.source=file:///dev/urandom
Comments
-
We are using Oracle as the database for our Web application. The application runs well most of the time, but we get this «No more data to read from socket» error.
Caused by: java.sql.SQLRecoverableException: No more data to read from socket at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1142) at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1869) at org.hibernate.loader.Loader.doQuery(Loader.java:718) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) at org.hibernate.loader.Loader.doList(Loader.java:2449) ... 63 moreWe use spring, hibernate and i have the following for the datasource in my applciation context file.
<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource"> <property name="driverClassName" value="${database.driverClassName}" /> <property name="url" value="${database.url}" /> <property name="username" value="${database.username}" /> <property name="password" value="${database.password}" /> <property name="defaultAutoCommit" value="false" /> <property name="initialSize" value="10" /> <property name="maxActive" value="30" /> <property name="validationQuery" value="select 1 from dual" /> <property name="testOnBorrow" value="true" /> <property name="testOnReturn" value="true" /> <property name="poolPreparedStatements" value="true" /> <property name="removeAbandoned" value="true" /> <property name="logAbandoned" value="true" /> </bean>I am not sure whether this is because of application errors, database errors or network errors.
We see the following on the oracle logs
Thu Oct 20 10:29:44 2011 Errors in file d:oraclediagrdbmsadsadstraceads_ora_3836.trc (incident=31653): ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] [] Incident details in: d:oraclediagrdbmsadsadsincidentincdir_31653ads_ora_3836_i31653.trc Thu Oct 20 10:29:45 2011 Trace dumping is performing id=[cdmp_20111020102945] Thu Oct 20 10:29:49 2011 Sweep [inc][31653]: completed Sweep [inc2][31653]: completed Thu Oct 20 10:34:20 2011 Errors in file d:oraclediagrdbmsadsadstraceads_ora_860.trc (incident=31645): ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] [] Incident details in: d:oraclediagrdbmsadsadsincidentincdir_31645ads_ora_860_i31645.trc Thu Oct 20 10:34:21 2011Oracle Version : 11.2.0.1.0
-
Have contacted oracle and it is a _optim_peek_user_binds issue and we had to either upgrade or patch it.
-
I still get this error on 11.2.0.2.0 — 64bit, after restart oracle db — pool not reconnerct..
-
Yes, problem goes away when you restart your app, but this is not an anwer to a question, and you cannot keep doing restarts all the time that in production.
-
This error message is indicates a crash on the server-side. There are lots of these, so without checking the server-side logs any fixes are just a guess. Upgrading to the latest supported JDBC client is probably a good idea most of the time.
-
Replacing my installation of Oracle with a modern database management system worked for me.
-
This is actually a perfectly legit answer. When the db gets restarted sometimes the depending backend/application does not handle it properly. So yes, the app should be fixed, but that does not help you at the moment when you need the app to respond. Restarting the app does.
-
@javagirl And what was the question you are referring to? The OP actually did not phrase any question. He did not ask «how should we rewrite the app?» And this answer also does not suggest «this is a long-term solution.» When tens of people are waiting for the app to start responding you don’t tell them «rewrite it.»
-
logged into stackoverflow only to upvote @K.AlanBates comment
-
I am using JDK1.8,SpringJDBCTemplate,OracleDB,SpringRestService & org.commons.dbcp.BasicDataSource. Getting this error intermittently. Will I get any issue if use the above given properties?
Recents
Related
Application using JDBC 10.1.0.5 fails with below error after upgrading your database from Oracle 9i to 10g.
JDBC Version 10.1.0.5 to 11.1.0.7 are affected with this issue.
java.sql.SQLException: OALL8 is in an inconsistent state.
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.T4C8Oall.init(T4C8Oall.java:325)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:170)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3001) Caused by: java.sql.SQLException: No more data to read from socket at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:898)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:994)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:951)
at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:100)
at oracle.jdbc.driver.T4CConnection.do_rollback(T4CConnection.java:478)
at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1263)
Cause:-
The error is due to the published Bug 5851267.
This issue is caused by constraints used by the optimizer getting attempted to be used at run time when they should not. This issue is documented in Note:463899.1
You should see the following error in the trace file during the execution of the transaction
ORA-07445: Exception aufgetreten: CORE Dump [delrefi()+34] [SIGSEGV] [Address not mapped to object] [0x8] [] []
Solution: This issue has been fixed in 11.1.0.7.
If you are running in 10g then you can apply the patch for Bug:5851267
Or
you can use the following workaround to resolve the issue.
Set database parameter «_optimizer_join_elimination_enabled» to false. By doing this you can disable the elimination transformation.
SQL> alter session set “_optimizer_join_elimination_enabled”=false;
For more information refer below metalink notes
BUG:5851267 — ORA-07445 DURING EXECUTION OF PACKAGE WHERE DELETE OCCURS
NOTE:463899.1 — ORA-7445 [Delrefi] Where Delete Occurs
Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com
We are using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 and we have an error on a query that gives us the error
Error: No more data to read from socket
SQLState: null
ErrorCode: 17410
The query is similar to the following
select * from (
select ... <many, MANY fields>
from table
inner join
left outer join
left outer join
inner join
where string1='value1' and string2='value2'
) where rownum > 500 and rownum < 510
If I try to remove the final where statement the query runs fine.
Also if I try to «reduce» the rownum filter to a lower level of records the query runs fine:
where rownum<10
Searching on internet I have found that it could be caused by the parameter «cursor_sharing» to «similar» that seems buggy on some version of oracle. I tried to set it to «exact» but unfortunately I have the same error.
I was wondering if the retrieved data of a select statement may have a limit in bytes in oracle10g but I haven’t found anything. I have found a limit on oracle8i but nothing to oracle10g.
We have the same problem executing the query on different client (dbvisualizer, squirrel) and on oracle console.
Issue
We are using Oracle as the database for our Web application. The application runs well most of the time, but we get this «No more data to read from socket» error.
Caused by: java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1142)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
at org.hibernate.loader.Loader.doQuery(Loader.java:718)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2449)
... 63 more
We use spring, hibernate and i have the following for the datasource in my applciation context file.
<bean class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" id="dataSource">
<property name="driverClassName" value="${database.driverClassName}" />
<property name="url" value="${database.url}" />
<property name="username" value="${database.username}" />
<property name="password" value="${database.password}" />
<property name="defaultAutoCommit" value="false" />
<property name="initialSize" value="10" />
<property name="maxActive" value="30" />
<property name="validationQuery" value="select 1 from dual" />
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="true" />
<property name="poolPreparedStatements" value="true" />
<property name="removeAbandoned" value="true" />
<property name="logAbandoned" value="true" />
</bean>
I am not sure whether this is because of application errors, database errors or network errors.
We see the following on the oracle logs
Thu Oct 20 10:29:44 2011
Errors in file d:oraclediagrdbmsadsadstraceads_ora_3836.trc (incident=31653):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:oraclediagrdbmsadsadsincidentincdir_31653ads_ora_3836_i31653.trc
Thu Oct 20 10:29:45 2011
Trace dumping is performing id=[cdmp_20111020102945]
Thu Oct 20 10:29:49 2011
Sweep [inc][31653]: completed
Sweep [inc2][31653]: completed
Thu Oct 20 10:34:20 2011
Errors in file d:oraclediagrdbmsadsadstraceads_ora_860.trc (incident=31645):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:oraclediagrdbmsadsadsincidentincdir_31645ads_ora_860_i31645.trc
Thu Oct 20 10:34:21 2011
Oracle Version : 11.2.0.1.0
Solution
For errors like this you should involve oracle support. Unfortunately you do not mention what oracle release you are using. The error can be related to optimizer bind peeking. Depending on the oracle version different workarounds apply.
You have two ways to address this:
- upgrade to 11.2
- set oracle parameter
_optim_peek_user_binds = false
Of course underscore parameters should only be set if advised by oracle support
Answered By — steve
Hi Dennis,
Thanks for the prompt reply.
I tried all you suggested:
- 1.0.9-SNAPSHOT
- ojdbc6 from the project’s lib
and
- the Oracle XE instance from docker image you’re using in tests
- a separate user / schema dedicated for the journal and snapshot tables
- monitored number of used connections, it was always < max allowed connections
- recipe from https://community.oracle.com/message/3701989
result is the same:
20:50:36.299 [isb-akka.actor.default-dispatcher-3] ERROR s.StatementExecutor$$anon$1 - SQL execution failed (Reason: IO Error: Connection reset):
Below is akka-persistence-jdbc debug log:
20:49:32.856 [isb-akka.persistence.dispatchers.default-plugin-dispatcher-26] DEBUG scalikejdbc.ConnectionPool$ - Registered connection pool : ConnectionPool(url:jdbc:oracle:thin:@boot2docker:1521:, user:PSYS_AKKA) using factory : commons-dbcp
20:49:32.858 [isb-akka.persistence.dispatchers.default-plugin-dispatcher-26] DEBUG scalikejdbc.ConnectionPool$ - Registered singleton connection pool : ConnectionPool(url:jdbc:oracle:thin:@boot2docker:1521:, user:PSYS_AKKA)
20:49:33.019 [isb-akka.actor.default-dispatcher-21] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
SELECT * FROM PSYS_AKKA.akka_snapshot WHERE persistence_id = 'DispatcherActor' AND sequence_nr <= 9223372036854775807 ORDER BY sequence_nr DESC; (12 ms)
[Stack Trace]
...
akka.persistence.jdbc.snapshot.GenericStatements$class.selectSnapshotsFor(Statements.scala:52)
akka.persistence.jdbc.snapshot.OracleSyncSnapshotStore.selectSnapshotsFor(SnapshotStores.scala:16)
akka.persistence.jdbc.snapshot.JdbcSyncSnapshotStore$$anonfun$loadAsync$1.apply(JdbcSyncSnapshotStore.scala:21)
akka.persistence.jdbc.snapshot.JdbcSyncSnapshotStore$$anonfun$loadAsync$1.apply(JdbcSyncSnapshotStore.scala:21)
scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:401)
scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
...
20:49:33.063 [isb-akka.actor.default-dispatcher-21] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
SELECT message FROM PSYS_AKKA.akka_journal WHERE persistence_id = 'DispatcherActor' AND (sequence_number >= 1 AND sequence_number <= 9223372036854775807) AND ROWNUM <= 9223372036854775807 ORDER BY sequence_number; (9 ms)
[Stack Trace]
...
akka.persistence.jdbc.journal.OracleStatements$class.selectMessagesFor(Statements.scala:102)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.selectMessagesFor(Journals.scala:16)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$asyncReplayMessages$1.apply$mcV$sp(JdbcSyncWriteJournal.scala:78)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$asyncReplayMessages$1.apply(JdbcSyncWriteJournal.scala:78)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$asyncReplayMessages$1.apply(JdbcSyncWriteJournal.scala:78)
scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:401)
scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
...
20:49:33.083 [isb-akka.actor.default-dispatcher-21] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
SELECT MAX(sequence_number) FROM PSYS_AKKA.akka_journal WHERE persistence_id = 'DispatcherActor'; (3 ms)
[Stack Trace]
...
akka.persistence.jdbc.journal.GenericStatements$$anonfun$selectMaxSequenceNr$1.apply$mcJ$sp(Statements.scala:67)
akka.persistence.jdbc.journal.GenericStatements$$anonfun$selectMaxSequenceNr$1.apply(Statements.scala:69)
akka.persistence.jdbc.journal.GenericStatements$$anonfun$selectMaxSequenceNr$1.apply(Statements.scala:69)
scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:401)
scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
...
20:50:35.812 [isb-akka.persistence.dispatchers.default-plugin-dispatcher-32] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
INSERT INTO PSYS_AKKA.akka_journal (persistence_id, sequence_number, marker, message, created) VALUES ('DispatcherActor',1,'A','CucSCAES4hKs7QAFc3IATWNvbS5wZXJzcGVjc3lzLmlzYi5pbXBsLmFra2Euam9iZXhlY3V0b3IuZGlzcGF0Y2hlci5ldmVudC5O... (3344)', current_timestamp); (12 ms)
[Stack Trace]
...
akka.persistence.jdbc.journal.GenericStatements$class.insertMessage(Statements.scala:43)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.insertMessage(Journals.scala:16)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:24)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:22)
scala.collection.Iterator$class.foreach(Iterator.scala:727)
scala.collection.AbstractIterator.foreach(Iterator.scala:1157)
scala.collection.IterableLike$class.foreach(IterableLike.scala:72)
scala.collection.AbstractIterable.foreach(Iterable.scala:54)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$class.writeMessages(JdbcSyncWriteJournal.scala:22)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.writeMessages(Journals.scala:16)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply$mcV$sp(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
scala.util.Try$.apply(Try.scala:161)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1.applyOrElse(SyncWriteJournal.scala:27)
...
20:50:36.022 [isb-akka.persistence.dispatchers.default-plugin-dispatcher-32] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
INSERT INTO PSYS_AKKA.akka_journal (persistence_id, sequence_number, marker, message, created) VALUES ('DispatcherActor',2,'A','CsMCCAESvgKs7QAFc3IARWNvbS5wZXJzcGVjc3lzLmlzYi5pbXBsLmFra2Euam9iZXhlY3V0b3IuZGlzcGF0Y2hlci5ldmVudC5H... (716)', current_timestamp); (5 ms)
[Stack Trace]
...
akka.persistence.jdbc.journal.GenericStatements$class.insertMessage(Statements.scala:43)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.insertMessage(Journals.scala:16)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:24)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:22)
scala.collection.Iterator$class.foreach(Iterator.scala:727)
scala.collection.AbstractIterator.foreach(Iterator.scala:1157)
scala.collection.IterableLike$class.foreach(IterableLike.scala:72)
scala.collection.AbstractIterable.foreach(Iterable.scala:54)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$class.writeMessages(JdbcSyncWriteJournal.scala:22)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.writeMessages(Journals.scala:16)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply$mcV$sp(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
scala.util.Try$.apply(Try.scala:161)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1.applyOrElse(SyncWriteJournal.scala:27)
...
20:50:36.056 [isb-akka.persistence.dispatchers.default-plugin-dispatcher-32] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
INSERT INTO PSYS_AKKA.akka_journal (persistence_id, sequence_number, marker, message, created) VALUES ('DispatcherActor',3,'A','CogECAESgwSs7QAFc3IAS2NvbS5wZXJzcGVjc3lzLmlzYi5pbXBsLmFra2Euam9iZXhlY3V0b3IuZGlzcGF0Y2hlci5ldmVudC5K... (980)', current_timestamp); (3 ms)
[Stack Trace]
...
akka.persistence.jdbc.journal.GenericStatements$class.insertMessage(Statements.scala:43)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.insertMessage(Journals.scala:16)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:24)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:22)
scala.collection.Iterator$class.foreach(Iterator.scala:727)
scala.collection.AbstractIterator.foreach(Iterator.scala:1157)
scala.collection.IterableLike$class.foreach(IterableLike.scala:72)
scala.collection.AbstractIterable.foreach(Iterable.scala:54)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$class.writeMessages(JdbcSyncWriteJournal.scala:22)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.writeMessages(Journals.scala:16)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply$mcV$sp(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
scala.util.Try$.apply(Try.scala:161)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1.applyOrElse(SyncWriteJournal.scala:27)
...
20:50:36.072 [isb-akka.persistence.dispatchers.default-plugin-dispatcher-32] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
INSERT INTO PSYS_AKKA.akka_journal (persistence_id, sequence_number, marker, message, created) VALUES ('DispatcherActor',4,'A','CucSCAES4hKs7QAFc3IATWNvbS5wZXJzcGVjc3lzLmlzYi5pbXBsLmFra2Euam9iZXhlY3V0b3IuZGlzcGF0Y2hlci5ldmVudC5O... (3404)', current_timestamp); (6 ms)
[Stack Trace]
...
akka.persistence.jdbc.journal.GenericStatements$class.insertMessage(Statements.scala:43)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.insertMessage(Journals.scala:16)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:24)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$$anonfun$writeMessages$1.apply(JdbcSyncWriteJournal.scala:22)
scala.collection.Iterator$class.foreach(Iterator.scala:727)
scala.collection.AbstractIterator.foreach(Iterator.scala:1157)
scala.collection.IterableLike$class.foreach(IterableLike.scala:72)
scala.collection.AbstractIterable.foreach(Iterable.scala:54)
akka.persistence.jdbc.journal.JdbcSyncWriteJournal$class.writeMessages(JdbcSyncWriteJournal.scala:22)
akka.persistence.jdbc.journal.OracleSyncWriteJournal.writeMessages(Journals.scala:16)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply$mcV$sp(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1$$anonfun$1.apply(SyncWriteJournal.scala:27)
scala.util.Try$.apply(Try.scala:161)
akka.persistence.journal.SyncWriteJournal$$anonfun$receive$1.applyOrElse(SyncWriteJournal.scala:27)
...
20:50:36.299 [isb-akka.actor.default-dispatcher-3] ERROR s.StatementExecutor$$anon$1 - SQL execution failed (Reason: IO Error: Connection reset):
MERGE INTO PSYS_AKKA.akka_snapshot snapshot USING (SELECT 'DispatcherActor' AS persistence_id, 4 AS seq_nr from DUAL) val ON (snapshot.persistence_id = val.persistence_id and snapshot.sequence_nr = val.seq_nr) WHEN MATCHED THEN UPDATE SET snapshot='qAAAAKztAAVzcgAtYWtrYS5wZXJzaXN0ZW5jZS5zZXJpYWxpemF0aW9uLlNuYXBzaG90SGVhZGVyAAAAAAAAAAECAAJJAAxzZXJp... (3900)' WHEN NOT MATCHED THEN INSERT (PERSISTENCE_ID, SEQUENCE_NR, SNAPSHOT, CREATED) VALUES ('DispatcherActor', 4, 'qAAAAKztAAVzcgAtYWtrYS5wZXJzaXN0ZW5jZS5zZXJpYWxpemF0aW9uLlNuYXBzaG90SGVhZGVyAAAAAAAAAAECAAJJAAxzZXJp... (3900)', 1421977836080)
20:50:39.635 [isb-akka.persistence.dispatchers.default-plugin-dispatcher-33] DEBUG s.StatementExecutor$$anon$1 - SQL execution completed
[SQL Execution]
DELETE FROM PSYS_AKKA.akka_snapshot WHERE persistence_id = 'DispatcherActor' AND sequence_nr = 4; (6 ms)
[Stack Trace]
...
akka.persistence.jdbc.snapshot.GenericStatements$class.deleteSnapshot(Statements.scala:30)
akka.persistence.jdbc.snapshot.OracleSyncSnapshotStore.deleteSnapshot(SnapshotStores.scala:16)
akka.persistence.jdbc.snapshot.JdbcSyncSnapshotStore$class.delete(JdbcSyncSnapshotStore.scala:34)
akka.persistence.jdbc.snapshot.OracleSyncSnapshotStore.delete(SnapshotStores.scala:16)
akka.persistence.snapshot.SnapshotStore$$anonfun$receive$1.applyOrElse(SnapshotStore.scala:44)
akka.actor.Actor$class.aroundReceive(Actor.scala:465)
akka.persistence.jdbc.snapshot.OracleSyncSnapshotStore.aroundReceive(SnapshotStores.scala:16)
akka.actor.ActorCell.receiveMessage(ActorCell.scala:516)
akka.actor.ActorCell.invoke(ActorCell.scala:487)
akka.dispatch.Mailbox.processMailbox(Mailbox.scala:254)
akka.dispatch.Mailbox.run(Mailbox.scala:221)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
java.lang.Thread.run(Thread.java:745)
...
I’ve never been an Oracle guru, but it seems that MERGE... statement causes the problem. Execution fails always on MERGE. Apparently, the next DELETE statement passes through.
My application executes some other SQLs to this schema in parallel without any hiccups.
What do you think, could we use some other combination of SQL statements instead of MERGE to achieve the same functionality?
May 27, 2021
I got ” ORA-17410: No more data to read from socket ” error in Oracle database.
ORA-17410: No more data to read from socket
Details of error are as follows.
java.sql.SQLException 17410 No more data from socket ORA-17410: No more data to read from socket
No more data to read from socket
This ORA-17410 error is related to the bug, Oracle client software problems or Network and Firewall problems.
To solve this error, check and fix your network and firewall problem.
OR
Open the tnsnames.ora ( $ORACLE_HOME/network/admin ) file and change “(SERVER = DEDICATED)” to “(SERVER = SHARED)” , then restart database and listener and try again.
OR
If you use Oracle 11gR1, then execute the following alter command.
alter system set "_optim_peek_user_binds"=false scope=both sid='*';
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
1,546 views last month, 1 views today
About Mehmet Salih Deveci
I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].- -Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için [email protected] a mail atabilirsiniz.