ORA-3113 End of communication channel with Select query vs sqlnet.ora
This blog post writing based on the recent issue i’ve seen, Let’s start, customer reported as connection to the database 10.2.0.5 from the client version 11gR2 was successful, when they ran any select query “select * from dual;” end of communication channel occurred and thus lost the connection but this is not happening every time quickly, some times for few runs it is working fine, but in next run at any time it is failing with the same error. The error is below for example and it is windows environment.
F:\oracle\WQ\102\BIN>sqlplus sapadm/sapadm@wq SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 21 13:24:23 2016 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from dual; D - X SQL> / select * from dual * ERROR at line 1: ORA-03113: end-of-file on communication channel
[oracle@ORA-C1 ~]$ oerr ora 3113 03113, 00000, "end-of-file on communication channel" // *Cause: The connection between Client and Server process was broken. // *Action: There was a communication error that requires further investigation. // First, check for network problems and review the SQL*Net setup. // Also, look in the alert.log file for any errors. Finally, test to // see whether the server process is dead and whether a trace file // was generated at failure time. [oracle@ORA-C1 ~]$
I saw similar errors earlier but in Linux and the fix was with the permissions not proper for the lib/files and then strace helped me to find out, now it is windows system as i see there is no issues at base level and also it is ORA_DBA group. Next i thought it could be with the firewall? server terminated? but in fact not and so on, finally i wanted to check sqlnet.ora file configuration and not sure what are the entries are used in server side, Interestingly i found many parameters and started reviewing each of them and i suspect the two parameters and they are.
- SQLNET.RECV.TIMEOUT=10
- SQLNET.SEND_TIMEOUT=10
Let’s understand the behavior of these parameters, by setting these parameters database server will wait for the client data after the connection and also expected to send some data within the specified time, the default value is NONE where these additional metrics been set. Definitely that is what we are not looking for, hence decided to take out as they are not relevant to our environment. Please note that for changes in sqlnet.ora no restart of listener required any how. Next tried with the same like above output to see the response when we run the select query.
SQL> set time on 14:50:54 SQL> select * from dual; D - X 14:50:58 SQL> / D - X 14:50:59 SQL> / D - X 14:51:37 SQL> / D - X 14:52:35 SQL> / D - X 14:53:01 SQL> / D - X 14:54:25 SQL> / D - X 14:55:21 SQL> / D - X 14:56:57 SQL>
Tested for more times and no more issues seen, blogged this post so that could help the users who are using these two parameters of sqlnet.ora and how they impact even the select queries with the ORA-3113. Thanks for reading. 🙂
Reference: https://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm
