.Net Core Oracle Connection time out-CodePudding

we have application .net core with oracle database published since 8 months unexpectedly returned request connection time and we didn't make any deployment and we didn't use it much time because we are waiting for phase 2

.Net Core 5 Packages version

Microsoft.EntityFrameworkCore >> 5.0.17
Oracle.ManagedDataAccess.Client >> 3.21.61
Oracle.EntityFrameworkCore >> 5.21.61

we tried the following:

Restart IIS
restart the app server
restart the database server
increase the time out in the connection string
increase min & max pool size
I tried Dapper and gave the same error
I created the small app with one DB set (table) and deployed on
the server it is worked successfully on the same database and same server
telnet on the database server from server and worked

Some Notes:

the application always now gave request connection time on the production server.
the application works with the same database on the visual studio on my machine.
and also I deployed the app on my machine and worked
the Issue is only on production servers
we access the database from local machine using VPN

the log below:

{ "time": "2022-05-13 00:28:40.8846", "level": "ERROR(Microsoft.EntityFrameworkCore.Database.Connection)", "url": "http:\/\/dedportal.uaqgov.ae\/api\/Activity\/ActivityListAll", "actionName": "ActivityListAll", "Message": "An error occurred using the connection to database '' on server host\/service name'." },


{ "time": "2022-05-13 00:28:40.9270", "level": "ERROR(Microsoft.EntityFrameworkCore.Infrastructure)", "url": "http:\/\/dedportal.uaqgov.ae\/api\/Activity\/ActivityListAll", "actionName": "ActivityListAll", "Message": "1443-10-12 00:28:40.923393 ThreadID:4   (ERROR)   OracleExecutionStrategy.ExecuteAsync() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Connection request timed out
  at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
  at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
  at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
  at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
  at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)\r\n--- End of stack trace from previous location ---
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)" },



{ "time": "2022-05-13 00:28:40.9660", "level": "ERROR(Microsoft.EntityFrameworkCore.Query)", "url": "http:\/\/dedportal.uaqgov.ae\/api\/Activity\/ActivityListAll", "actionName": "ActivityListAll", "ExceptionType": "Oracle.ManagedDataAccess.Client.OracleException", "ExceptionMessage": "Connection request timed out", "StackTrace": "   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
  at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
  at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
  at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
  at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)\r\n--- End of stack trace from previous location ---
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()", "Message": "An exception occurred while iterating over the results of a query for context type 'UAQDED.Infrastructure.DB.DBContext.ModelContext'.\r\nOracle.ManagedDataAccess.Client.OracleException (0x80004005): Connection request timed out
  at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
  at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
  at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
  at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
  at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)\r\n--- End of stack trace from previous location ---
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
  at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()" },

CodePudding user response：

We solved this it seems that our scan listeners have an issue I used the IP of the database nodes and it is working.