Search code examples
c#oracle-databasepolly

Polly not handling OracleExceptions ORA-03113 and ORA-03114 properly

My previous question on Polly and Oracle Connectivity is as below

async await throwing error for Polly code while connecting to Oracle DB

Extending this, I am trying to handle multiple Oracle Exceptions based on connectivity along with FTP connection. It is able to handle FTP Exceptions properly. But when it comes to Oracle, it is not able to handle 0RA-03113 and ORA-03114

This is the code I have implemented..

static void Main()
    {
        var retryTimes = 100;
        var retryableOracleErrorCodes = new[] { "ORA-03113", "ORA-03114", "ORA-12543", "ORA-12170", "ORA-12154"  };

        RetryPolicy retryPolicyFTP = Policy
            .Handle<Xceed.Ftp.FtpInvalidStateException>()
            .WaitAndRetry(retryTimes, _ => TimeSpan.FromSeconds(10));

        RetryPolicy retryPolicyFTP1 = Policy
            .Handle<Xceed.Ftp.FtpIOException>()
            .WaitAndRetry(retryTimes, _ => TimeSpan.FromSeconds(10));

        RetryPolicy retryPolicyOracle = Policy
            .Handle<OracleException>(ex => retryableOracleErrorCodes.Any(errorCode => ex.Message.Contains(errorCode)))
            .RetryForever();

        retryPolicyFTP.Execute(() =>
        {
            retryPolicyFTP1.Execute(() =>
            {
                retryPolicyOracle.Execute(() =>
                {
                    ApplicationMain applicationMain = new ApplicationMain();
                    applicationMain.Start();
                });
            });
        });
    }

Could you please throw light on this... why it is not able to handle particular Oracle Exceptions..I mean if I break the connection between Program and Oracle DB, it should go silent without throwing that exceptions and again when connection is available, it should be able to retrieve the database records..

Why it is not going silent and why it is breaking...for ORA-03113 & ORA-03114. The error thrown is shown below..

enter image description here

The detailed exception is as below..

36722763    Source.FtpInternal1.Poll    Failed  2021-04-30 08:40:00 2021-04- 
30 08:40:04     Oracle.DataAccess.Client.OracleException ORA-03113: end-of- 
file on communication channel
Process ID: 149519
Session ID: 1655 Serial number: 65101    at 
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, 
OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object 
src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, 
OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* 
pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, 
Boolean fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
at Data.MetaRecord.Fill(DataTable dataTable, RecordCommand recordCommand, 
Object[] parameterValues, FillResultType fillResultType) in 
C:\Data\MetaRecord.cs:line 1348
at Data.MetaRecord.FillWithComposites(RecordCommandCompositeCollection 
recordCommandComposites, RecordCommandComposite parentRecordCommandComposite, 
Object[] parameterValues, FillResultType fillResultType) in 
C:\Data\MetaRecord.cs:line 1537
at Data.MetaRecord.FillWithComposites(RecordCommandCompositeCollection 
recordCommandComposites, RecordCommandComposite parentRecordCommandComposite, 
Object[] parameterValues, FillResultType fillResultType) in 
C:\Data\MetaRecord.cs:line 1551
at Data.MetaRecord.FillWithComposites(RecordCommandCompositeCollection 
recordCommandComposites, RecordCommandComposite parentRecordCommandComposite, 
Object[] parameterValues, FillResultType fillResultType) in 
C:\Data\MetaRecord.cs:line 1551
at Data.MetaRecord.RetrieveByPrimaryKey(RecordRetrieveAttributes 
recordRetrieveAttributes, Object[] primaryKeyValues) in 
C:\Data\MetaRecord.cs:line 1830
at Data.MetaRecord.GetRecordByPrimaryKey(RecordGetAttributes 
recordGetAttributes, Object[] primaryKeyValues) in C:\Data\MetaRecord.cs:line 
873
at Data.MetaRecord.GetRecordByPrimaryKey(Object[] primaryKeyValues) in 
C:\Data\MetaRecord.cs:line 923
at Processors.ExecuteInfo.GetAndRegisterRecord(RecordType recordType, 
Object[] primaryKeyValues) in C:\Core\Processors\ExecuteInfo.cs:line 143
at Processors.InternalPoll.ProcessExecute(DataTransaction dataTransaction, 
ExecuteInfo executeInfo, ProcessInfo processInfo) in 
C:\Core\Processors\InternalPoll.cs:line 67
at Processors.Processor.Execute(ExecuteInfo executeInfo, ProcessInfo 
processInfo) in C:\Core\Processors\Processor.cs:line 184

This is the final code I have prepared but still it is not throwing OracleException message

var retryTimes = 100;
var retryableOracleErrorCodes = new[] { "ORA-03113", "ORA-03114", "ORA-12543", "ORA-12170", "ORA-12154" };

RetryPolicy retryPolicyFTP = Policy
    .Handle<Xceed.Ftp.FtpInvalidStateException>().Or<Xceed.Ftp.FtpIOException>()
    .WaitAndRetry(retryTimes, _ => TimeSpan.FromSeconds(10));

RetryPolicy retryPolicyOracle = Policy
    .Handle<OracleException>(ex => retryableOracleErrorCodes
                                    .Any(errorCode => ex.ToString().Contains(errorCode)))
    .RetryForever();

Policy.Wrap(retryPolicyFTP, retryPolicyOracle).Execute(() =>
{
    try
    {
         ApplicationMain applicationMain = new ApplicationMain();
         applicationMain.Start();
    }
    catch (OracleException oraEx)
    {
         MessageBox.Show(oraEx.Message.ToString());
    }
});

Got the below message after adding .Or<Exception>() to the Final Code

Oracle.DataAccess.Client.OracleException ORA-03114: not connected to ORACLE    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at Data.OraclePackageCommand.ExecuteNonQuery(OracleTransaction transaction) in C:\Core\Data\OraclePackageCommand.cs:line 74
   at Data.OraclePackageCommand.ExecuteNonQuery() in C:\Core\Data\OraclePackageCommand.cs:line 63
   at Data.OraclePackage.ExecuteServerHeartbeat(Int32 serverId, Int32 heartbeatInterval, DateTime processTime, Int32& processingStatusId, DateTime& lastHeartbeatTime, DateTime& nextHeartbeatTime, Int32& availableProcessCount, Boolean& recordModificationExists) in C:\Core\Data\OraclePackage.cs:line 2385
   at Processors.ServerInfo.Heartbeat(DateTime processTime, ServerProcessingStatus& heartbeatProcessingStatus, Int32& availableProcessCount, Boolean& recordModificationExists) in C:\Core\Processors\ServerInfo.cs:line 252
   at Processors.ServerManager.ThreadStart() in C:\Core\Processors\ServerManager.cs:line 337
Solution

  • TL;DR: I think the root cause of your problem is the swallowed exception.

    try
{
     ApplicationMain applicationMain = new ApplicationMain();
     applicationMain.Start();
}
catch (OracleException oraEx)
{
     MessageBox.Show(oraEx.Message.ToString());
}

    When an OracleException is thrown then it is shown on the UI
    but then the method ends and will not trigger any policy.

    In order to be able to reproduce your problem I've changed the following things:

    • I've replaced the 3rd party exceptions to built-in ones
      • FtpInvalidStateException >> NotSupportedException
      • OracleException >> ArgumentException
    • I've defined two delegates for each onRetry to print out the exception message when the policy is triggered
    • I've changed the max retry and wait period between retries to smaller
    • I've extracted the to be decorated piece of code into a separate function, called FaultyMethod
    • Lastly I've made the FaultyMethod really faulty :)

    Main with policy declarations and usage

    static readonly string[] retryableErrorCodes = 
    new[] { "ORA-03113", "ORA-03114", "ORA-12543", "ORA-12170", "ORA-12154" };
const int retryTimes = 3;

static void Main()
{
    RetryPolicy retryForNotSupported = Policy
        .Handle<NotSupportedException>()
        .WaitAndRetry(
            retryTimes,
            _ => TimeSpan.FromSeconds(1),
            (ex, ts) => Console.WriteLine(ex.Message));

    RetryPolicy retryForArgument = Policy
        .Handle<ArgumentException>(ex =>
            retryableErrorCodes.Any(errorCode => ex.Message.Contains(errorCode)))
        .RetryForever(ex => Console.WriteLine(ex.Message));

    try
    {
        Policy.Wrap(retryForNotSupported, retryForArgument).Execute(FaultyMethod);
    } catch (Exception ex)
    {
        Console.WriteLine(ex);
        Environment.Exit(-1);
    }
    Console.WriteLine("Finished");
}

    The to be decorated function

    static int errorCount = -1;
static void FaultyMethod()
{
    try
    {
        if (++errorCount >= retryableErrorCodes.Length)
            throw new NotSupportedException();
        throw new ArgumentException($"{retryableErrorCodes[errorCount]}");
    }
    catch (ArgumentException ex)
    {
        Console.WriteLine(ex.Message);
    }
}

    If you run this application you will see the following:

    ORA-03113
Finished
    1. ArgumentException is thrown with message ORA-03113
    2. The exception is catched inside the FaultyMethod and its message is printed out
    3. None of the retry policies are triggered
    4. No exception is thrown by the Policy's Execute that's why Finished is printed

    Now let's change the code to throw NotSupportedException instead

    static void FaultyMethod()
{
    try
    {
        if(++errorCount <= retryTimes)
            throw new NotSupportedException();
        
        //if (++errorCount >= retryableErrorCodes.Length) 
        //    throw new ArgumentException($"{retryableErrorCodes[errorCount]}");
    }
    catch (ArgumentException ex)
    {
        Console.WriteLine(ex.Message);
    }
}

    Then the output will be:

    Specified method is not supported.
Specified method is not supported.
Specified method is not supported.
System.NotSupportedException: Specified method is not supported.
...
    1. FaultyMethod throws NotSupportedException
    2. Its catch block does not catch this exception
    3. Inner retry policy checks whether it is a handled exception from its perspective
    4. No, it's not that's why it escalates the problem to the outer policy
    5. Outer retry policy checks whether it is a handled exception from its perspective
    6. Yes, it is that's why it waits 1 second before initiates a new retry
    7. This exact same sequence (from 1 to 6) repeats 2 more times
    8. When the outer policy reaches its retry count threshold then it will throw the original exception the NotSupportedException
    9. Main's try-catch will catch that and it will print it out then exits

    So, if you remove the try-catch from the FaultyMethod

    static void FaultyMethod()
{
    if (++errorCount >= retryableErrorCodes.Length)
        throw new NotSupportedException();
    throw new ArgumentException($"{retryableErrorCodes[errorCount]}");
}

    then the output will look like this:

    ORA-03113
ORA-03114
ORA-12543
ORA-12170
ORA-12154
Specified method is not supported.
Specified method is not supported.
Specified method is not supported.
System.NotSupportedException: Specified method is not supported.
    • All ArgumentException is handled by the inner policy without exceeding its retry count (infinite)
    • All NotSupportedException is handled by the outer policy before exceeding its retry count (3)