How to restore OracleConnection after ORA-03113

[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.

[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.

[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.

[Shalex]

After 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.

[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.

[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?

[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.

[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?