Issue: dotnet core app deployed to Kubernetes container (Linux OS) is having problem in connecting to Oracle database when it is idle for 1 to 2 hours. It gets stuck for 16 mins and throws below exception.
We found a similar issue on Github but there was no resolution provided.
Github issue url: https://github.com/oracle/dotnet-db-samples/issues/58
Oracle package used: Oracle.ManagedDataAccess.Core 2.19.70 (latest version) and System.Security.Cryptography.Pkcs 4.7.0 . Also tried with Oracle.ManagedDataAccess.Core 2.19.50 and 2.19.60 and still the same issue.
Docker info: dotnet/core/sdk:3.1-alpine3.11
Exception details:
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12570: Network Session: Unexpected packet read error
---> OracleInternal.Network.NetworkException (0x80004005): ORA-12570: Network Session: Unexpected packet read error
---> System.Net.Sockets.SocketException (110): Operation timed out
at OracleInternal.Network.ReaderStream.Read(OraBuf OB)
at OracleInternal.Network.ReaderStream.Read(OraBuf OB)
at OracleInternal.TTC.OraBufReader.GetDataFromNetwork()
at OracleInternal.TTC.OraBufReader.Read(Boolean bIgnoreData)
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)
at Oracle.ManagedDataAccess.Client.OracleException.HandleError(OracleTraceLevel level, OracleTraceTag tag, OracleTraceClassName className, OracleTraceFuncName funcName, Exception ex, OracleLogicalTransaction oracleLogicalTransaction)
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(DbCommand cmd)
2020-05-07 15:21:29,480 ERROR: ORA-12570: Network Session: Unexpected packet read error
2020-05-07 15:21:29,480 ERROR: ORA-12570: Network Session: Unexpected packet read error
2020-05-07 15:21:29,480 ERROR: Operation timed out
2020-05-07 15:21:29,483 ERROR: ORA-12570: Network Session: Unexpected packet read error
2020-05-07 15:21:29,483 ERROR: ORA-12570: Network Session: Unexpected packet read error
2020-05-07 15:21:29,483 ERROR: Operation timed out
2020-05-07 15:21:29,484 ERROR: ORA-12570: Network Session: Unexpected packet read error
2020-05-07 15:21:29,484 ERROR: ORA-12570: Network Session: Unexpected packet read error
2020-05-07 15:21:29,485 ERROR: Operation timed out