log4net Adonet Appender Connection Issue

Question

I have developed a windows service in which I am using a timer control to perform some scheduled tasks. The timer elapse event occurs every 5 minutes in which a log entry is made using log4net appender to Oracle database.

All works fine until the DB Server closes all connections for nightly cold backup. Since that time all logs in DB are missed and nothing is logged unless service is restarted even though the backup process takes less than 30 mins.

From other posts I found that log4net uses only one connection which if lost then all subsequent logs are discarded. To remedy this I started using ReconnectOnError attribute set as true in its configuration. But unfortunately, the issue is still there. The logs are still missing after the backup. I enabled tracing and found following errors but I don't know how to resolve this issue.

log4net:ERROR [CustomAdoNetAppender] Exception while writing to database Oracle.DataAccess.Client.OracleException ORA-03113: end-of-file on communication channel at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) 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 log4net.Appender.AdoNetAppender.SendBuffer(IDbTransaction dbTran, LoggingEvent[] events) at log4net.Appender.AdoNetAppender.SendBuffer(LoggingEvent[] events)

and:

log4net:ERROR [CustomAdoNetAppender] Exception while writing to database System.InvalidOperationException: Connection is already part of a local or a distributed transaction at Oracle.DataAccess.Client.OracleConnection.BeginTransaction(IsolationLevel isolationLevel) at Oracle.DataAccess.Client.OracleConnection.BeginDbTransaction(IsolationLevel isolationLevel) at System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction() at log4net.Appender.AdoNetAppender.SendBuffer(LoggingEvent[] events)

Any help on this highly appreciated!!

Answer 1

Personally I'd consider this to be a bug in the log4net AdoNetAppender.

In log4net 1.2.11 AdoNetAppender commits the cardinal sin of keeping an open connection rather than using connection pooling.

Also the ReconnectOnError option looks broken: it only attempts to reconnect if the current connection state is not ConnectionState.Open, which seems wrong: I don't believe that the connection state changes when there has been an error (the enum value ConnectionState.Broken is documented in MSDN as being reserved for future versions of the product).

All in all, I'd recommend you implement your own custom appender that does connection pooling properly. It's not a very big class so would be easy to duplicate and fix.