C# .net Core reconnect to Oracle after database enabled

I have .net Core web-api project in docker, where I connect to Oracle database. For connecting I use nugget package oracleClientCore

How I connect and call stoted procedure:

string cs = "Data Source = 172.10.200.100:1521/dev;PERSIST SECURITY INFO=True;USER ID=test; Password=devtest;";
using (OracleConnection connection = new OracleConnection(cs)){
connection.Open();
using (OracleCommand cmd = connection.CreateCommand()) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_check_db";
cmd.ExecuteNonQuery();
connection.Close();
}
}


In stored procedure sp_check_db just make insert into table

sp_check_db

insert into table

Sometimes the connection to database falling down and I got exception ORA-03114: not connected to ORACLE. After the database was enabled again I continue to receive same error ORA-03114: not connected to ORACLE till I rebuild and redeploy the project.

ORA-03114: not connected to ORACLE

ORA-03114: not connected to ORACLE

What can I do in this situation, it's not right behavior?
Something wrong with my code or connection string?

1 Answer
1

When your application is dependent on external integrations (in this case your db) which can be unreachable for small periods of time due to patches, network failare etc, its
suitable to implement an retry policy. The retry logic will, depend on how you configure it, rerun the code a certain of time if a specific exception occours.

Here's an example of how to implement a simple retry logic with the framework Polly which will rerun your code three times in a ten second span if an exception containing the ORA-03114 occours. If the code still throws the same exception after the third time the exception will go throw.

ORA-03114

var retryTimes = 3;
var waitBetweenExceptions = TimeSpan.FromSeconds(10);

var retryPolicy = Policy
.Handle<OracleException>(e => e.Message.Contains("ORA-03114"))
.WaitAndRetry(retryTimes, i => waitBetweenExceptions);

await retryPolicy.Execute(() =>
{
string cs = "Data Source = 172.10.200.100:1521/dev;PERSIST SECURITY INFO=True;USER ID=test; Password=devtest;";
using (OracleConnection connection = new OracleConnection(cs)){
connection.Open();
using (OracleCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_check_db";
cmd.ExecuteNonQuery();
connection.Close();
}
}
});


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.