Page 1 of 1
How to restore OracleConnection after ORA-03113
Posted: Wed 15 Jul 2020 07:09
by fad
Hi,
after network issues (e.g. firewall closes the tcp connection between the client and the oracle server after some inactivity time), when we try to execute any operation (query, store procedure, package function, etc..) we receive the ORA-03113 error (ORA-03113: end-of-file on communication channel).
We tried to intercept this error and re-create a connection to the Oracle server again without success.
Is there any way to reset the connection? (we have also tried to disable pooling).
We are using version 9.5.520 with direct mode on Linux (mono 6.10.0.104).
Best regards,
Stefano.
Re: How to restore OracleConnection after ORA-03113
Posted: Thu 16 Jul 2020 18:34
by Shalex
Refer to
https://www.devart.com/dotconnect/oracl ... story.html :
9.6.646 29-Nov-18
The behaviour is improved: now the ORA-01013 error is generated instead of ORA-03113 after command timeout is reached in the Direct mode
Upgrade to v9.6.646 or higher. We recommend using the newest build v9.12.1054.
Re: How to restore OracleConnection after ORA-03113
Posted: Fri 17 Jul 2020 16:30
by fad
Hi,
I've tried to use latest build (9.12.1054) but I receive the same exception.
Devart.Data.Oracle.OracleException
ORA-03113: end-of-file on communication channel
After that I tried to re-create a connection to the Oracle server without success.
Maybe firewall timeout is not trated as "command timeout", I think that firewall close forcedly the socket.
Any suggestion?
My code:
Code: Select all
private OracleConnection m_connessione;
....
....
private OracleConnection initConnection()
{
if (m_connessione == null)
{
OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Direct = true;
oraCSB.Server = m_Host;
oraCSB.Port = m_Port;
oraCSB.Sid = m_Sid;
oraCSB.UserId = m_User;
oraCSB.Password = m_Pwd;
oraCSB.Pooling = false;
oraCSB.ConnectionTimeout = m_timeout; //15
m_connessione = new OracleConnection(oraCSB.ConnectionString);
m_connessione.Error += m_connessione_Error;
m_connessione.Open();
}
}
....
....
void m_connessione_Error(object sender, OracleConnectionErrorEventArgs e)
{
if (e.Code == DbSessionConsts.ERR_3113 || e.Code == DbSessionConsts.ERR_3114)
{
if (m_connessione != null)
{
try
{
m_connessione.Close();
}
catch { }
m_connessione = null;
}
}
}
....
....
public void executeTask()
{
initConnection();
OraclePackage pkg = new OraclePackage();
pkg.Connection = m_session.Connessione;
pkg.PackageName = "MY_PACKAGE";
OracleParameterCollection pars = new OracleParameterCollection();
pars.Add("p_1", "my data 1");
pars.Add("p_2", "my data 2");
OracleParameter ret = pars.Add("RESULTS", OracleDbType.Boolean);
ret.Direction = ParameterDirection.ReturnValue;
pkg.ExecuteProcedure("runScript", pars);
....
....
}
Thanks,
Stefano.
Re: How to restore OracleConnection after ORA-03113
Posted: Wed 22 Jul 2020 18:06
by Shalex
fad wrote: ↑Fri 17 Jul 2020 16:30After that I tried to re-create a connection to the Oracle server without success.
What operation does help to reopen connection in your environment? Turning off firewall, restarting application, etc.
Re: How to restore OracleConnection after ORA-03113
Posted: Thu 23 Jul 2020 06:40
by fad
Hi Shalex,
What operation does help to reopen connection in your environment? Turning off firewall, restarting application, etc.
the only way to resolve this issue is restart the application.
I can reproduce this situation systematically.
Best regards,
Stefano.
Re: How to restore OracleConnection after ORA-03113
Posted: Mon 27 Jul 2020 12:38
by Shalex
Please use the following test code on your Linux and set the Server connection string parameter to IP address instead of DNS name:
Code: Select all
class Program
{
private static OracleConnection m_connessione;
static void Main(string[] args)
{
string response = null;
OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Direct = true;
oraCSB.Server = "XXX.XXX.XXX.XXX/oracle19";
oraCSB.Port = 1521;
oraCSB.UserId = "scott";
oraCSB.Password = "tiger";
oraCSB.Pooling = false;
oraCSB.ConnectionTimeout = 15;
m_connessione = new OracleConnection(oraCSB.ConnectionString);
m_connessione.Error += m_connessione_Error;
while (response != "exit")
{
try
{
m_connessione.Open();
Console.WriteLine(m_connessione.State);
m_connessione.Close();
Console.WriteLine("\r\nPress any key to call conn.Open() again or type exit");
response = Console.ReadLine();
Console.Clear();
}
catch (OracleException e)
{
Console.WriteLine(e.Message + e.StackTrace);
}
}
}
static void m_connessione_Error(object sender, OracleConnectionErrorEventArgs e)
{
if (m_connessione != null)
{
try
{
Console.WriteLine("Error handler will sleep for 3 seconds and call conn.Open() again.");
Thread.Sleep(3000);
m_connessione.Open();
}
catch { }
}
}
}
1. Run the code and make sure that m_connessione.State=State.Open.
2. Turn off network. Runtime enters the m_connessione_Error handler and tries to open connection every 3 seconds, no uncatched errors.
3. Turn on network. m_connessione.Open() is successful in the m_connessione_Error handler, runtime goes out the m_connessione_Error method and generates "Network error:: 10051 - A socket operation was attempted to an unreachable network XXX.XXX.XXX.XXX:1521 Host = XXX.XXX.XXX.XXX:1521" in the Main() method, the error is catched. Then, code runs successfully.
Is your scenario the same?
Re: How to restore OracleConnection after ORA-03113
Posted: Thu 30 Jul 2020 09:42
by fad
Hi Shalex,
my scenario is slightly different.
1. open connection
2. execute some opreations (e.g. simple query)
3. do nothing for many time (e.g. three hours)
4. execute some operations (e.g. simple query) -> FAIL
I receive ORA 03113 as first error and then ORA 03114.
I not able to reset the session, I can resolve only with application restart.
This is the code:
Code: Select all
using System;
using System.Threading;
using Devart.Data.Oracle;
using MyLib.Common; // for log component
namespace MyApp
{
public static class TestDevArt3
{
private static OracleConnection m_connessione;
private static LogFile log;
public static void Run()
{
log = new LogFile("TESTDEVART", LogLevel.INFO);
log.Start();
log.WriteInfo("Run", "START", null);
OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Direct = true;
oraCSB.Server = "xxx.xxx.xxx.xxx/mysid";
oraCSB.Port = 1521;
oraCSB.UserId = "myuser";
oraCSB.Password = "mypwd";
oraCSB.Pooling = false;
oraCSB.ConnectionTimeout = 15;
m_connessione = new OracleConnection(oraCSB.ConnectionString);
m_connessione.Error += M_connessione_Error;
m_connessione.StateChange += M_connessione_StateChange;
log.WriteInfo("Run", "OPEN CONNECTION", null);
m_connessione.Open();
if (m_connessione.State == System.Data.ConnectionState.Open)
{
log.WriteInfo("Run", "EXECUTE QUERY #1", null);
bool queryOk = ExecuteQuery();
if (queryOk)
{
log.WriteInfo("Run", "WAITING", null);
Thread.Sleep(3600 * 3 * 1000);
log.WriteInfo("Run", "EXECUTE QUERY #2", null);
queryOk = false;
do
{
queryOk = ExecuteQuery();
if (!queryOk)
{
Thread.Sleep(1000 * 30);
}
}
while (!queryOk);
log.WriteInfo("Run", "DONE #2", null);
}
}
log.WriteInfo("Run", "EXIT", null);
m_connessione.Close();
log.Stop();
}
private static void M_connessione_StateChange(object sender, System.Data.StateChangeEventArgs e)
{
log.WriteInfo("M_connessione_StateChange", e.OriginalState.ToString() + " > " +e.CurrentState.ToString(), null);
}
private static void M_connessione_Error(object sender, OracleConnectionErrorEventArgs e)
{
log.WriteError("M_connessione_Error", e.Message, null);
if (m_connessione != null)
{
try
{
log.WriteInfo("M_connessione_Error", "Error handler will sleep for 10 seconds and call conn.Open() again.", null);
Thread.Sleep(10 * 1000);
m_connessione.Open();
}
catch { }
}
}
private static bool ExecuteQuery()
{
bool ret = true;
try
{
OracleCommand cmd = new OracleCommand("SELECT 1 FROM DUAL");
cmd.Connection = m_connessione;
OracleDataReader reader = cmd.ExecuteReader();
try
{
if (reader.Read())
{
log.WriteInfo("Run", "READ DATA: " + reader.GetOracleNumber(0).Value.ToString(), null);
}
}
finally
{
reader.Close();
}
}
catch (Exception e)
{
ret = false;
log.WriteError("ExecuteQuery", e.Message, null);
}
return ret;
}
}
}
and its output
Code: Select all
0 |6:29:04 PM.765| SYSTEM |START TESTDEVART - LogLevel INFO
1 |6:29:04 PM.767| SYSTEM |START TESTDEVART - PID: 43485
2 |6:29:04 PM.768| INFO |Run - START
3 |6:29:04 PM.833| INFO |Run - OPEN CONNECTION
4 |6:29:05 PM.207| INFO |M_connessione_StateChange - Closed > Open
5 |6:29:05 PM.207| INFO |Run - EXECUTE QUERY #1
6 |6:29:05 PM.233| INFO |Run - READ DATA: 1
7 |6:29:05 PM.234| INFO |Run - WAITING
8 |9:29:05 PM.235| INFO |Run - EXECUTE QUERY #2
9 |9:29:05 PM.273| ERROR |M_connessione_Error - ORA-03113: end-of-file on communication channel
10 |9:29:05 PM.273| INFO |M_connessione_Error - Error handler will sleep for 10 seconds and call conn.Open() again.
11 |9:29:15 PM.275| ERROR |ExecuteQuery - ORA-03113: end-of-file on communication channel
12 |9:29:45 PM.275| ERROR |M_connessione_Error - ORA-03114: not connected to ORACLE
13 |9:29:45 PM.275| INFO |M_connessione_Error - Error handler will sleep for 10 seconds and call conn.Open() again.
14 |9:29:55 PM.275| ERROR |ExecuteQuery - ORA-03114: not connected to ORACLE
15 |9:30:25 PM.276| ERROR |M_connessione_Error - ORA-03114: not connected to ORACLE
16 |9:30:25 PM.276| INFO |M_connessione_Error - Error handler will sleep for 10 seconds and call conn.Open() again.
17 |9:30:35 PM.276| ERROR |ExecuteQuery - ORA-03114: not connected to ORACLE
18 |9:31:05 PM.276| ERROR |M_connessione_Error - ORA-03114: not connected to ORACLE
19 |9:31:05 PM.277| INFO |M_connessione_Error - Error handler will sleep for 10 seconds and call conn.Open() again.
20 |9:31:15 PM.277| ERROR |ExecuteQuery - ORA-03114: not connected to ORACLE
21 |9:31:45 PM.277| ERROR |M_connessione_Error - ORA-03114: not connected to ORACLE
22 |9:31:45 PM.277| INFO |M_connessione_Error - Error handler will sleep for 10 seconds and call conn.Open() again.
23 |9:31:55 PM.277| ERROR |ExecuteQuery - ORA-03114: not connected to ORACLE
24 |9:32:25 PM.278| ERROR |M_connessione_Error - ORA-03114: not connected to ORACLE
25 |9:32:25 PM.278| INFO |M_connessione_Error - Error handler will sleep for 10 seconds and call conn.Open() again.
26 |9:32:35 PM.278| ERROR |ExecuteQuery - ORA-03114: not connected to ORACLE
etc..
Best regards,
Stefano.
Re: How to restore OracleConnection after ORA-03113
Posted: Fri 07 Aug 2020 12:55
by Shalex
The output of executing your code in our environment with dotConnect for Oracle v9.12.1064:
Code: Select all
TESTDEVART
Run START 07.08.2020 12:01:56
Run OPEN CONNECTION 07.08.2020 12:01:56
M_connessione_StateChange Closed > Open07.08.2020 12:01:56
Run EXECUTE QUERY #1 07.08.2020 12:01:56
Run READ DATA: 107.08.2020 12:01:56
Run WAITING 07.08.2020 12:01:56
Run EXECUTE QUERY #2 07.08.2020 15:01:56
Run READ DATA: 107.08.2020 15:01:56
Run DONE #2 07.08.2020 15:01:56
Run EXIT 07.08.2020 15:01:56
M_connessione_StateChange Open > Closed07.08.2020 15:01:56
Could you test your code against a different Oracle Server or/and in another network?