search

oracle / dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet
http://otn.oracle.com/dotnet
MIT License
413 stars 191 forks source link

Connection Timeout #108

Closed Tealons closed 3 years ago

Tealons commented 3 years ago

We are using ODP.NET for connecting to a Oracle database. The code is something like this:

        connectionStringBuilder.DataSource = txtBx_sid.Text;
        connectionStringBuilder.UserID = txtBx_user.Text;
        connectionStringBuilder.Password = txtBx_pass.Text;

        var connectionOracleClient = new OracleConnection(connectionStringBuilder.ConnectionString);

        if (connectionOracleClient != null)
        {

            if (connectionOracleClient.State != ConnectionState.Open)
            {
                try
                {
                    connectionOracleClient.Open();
                    MessageBox.Show("Connection establised!");
                }
                catch (Exception exception)
                {
                    MessageBox.Show($"ErrorMessage:{Environment.NewLine} {exception.Message} {Environment.NewLine}{Environment.NewLine} InnerException:{Environment.NewLine}{exception.InnerException} {Environment.NewLine}{Environment.NewLine} Stacktrace:{Environment.NewLine} {exception.StackTrace}");

                }

            }

            connectionOracleClient.Close();
            MessageBox.Show("Connection Closed.");

The datasource is in this format: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=servicename)));

This works in our test environment and with several customers.

The problem is dat we get a connection timeout with one of our customers. We tested the server connection with powershell with the following command: Test-NetConnection -ComputerName "hostname" -Port 1521. And that works, so it's not a network problem.

We also tried connecting with the database via SQLPlus from another client and that also works.

We also looked at the listener log, but don't see any log items of the client that we try to connect with.

We have no idea how to proceed with debugging this issue. Can you help us with the next steps? One significant difference btw, is the fact that this customer runs the database in the Oracle Cloud. Our own test environment and other customers run their database on-premise. I'm not sure if that makes any difference?

We currently use Oracle.ManagedDataAccess version 19.9.0.

alexkeh commented 3 years ago

Are you using a TLS wallet with the connection? If so, problems with the wallet sometimes takes longer than the default 15 second connection timeout to materialize. The first thing I would do then is to increase the connection string Connection Timeout value to 120. You will then be able to see the actual error then.

You can also turn on ODP.NET tracing. In ODP.NET Core, you can add something like these two lines when it starts up prior to opening the first connection.

OracleConfiguration.TraceFileLocation = @"D:\traces"; OracleConfiguration.TraceLevel = 7;

Since I don't see a TnsAdmin nor WalletLocation set, check to make sure the wallet and *.ora files are being picked up by ODP.NET for the correct location. The trace will tell you the location if these files were picked up.

Tealons commented 3 years ago

Thanks. Our current application is a WPF application on .NET 4.6.1. I will write a test application that runs on .NET Core. Also, the connection timeout is readonly, you can override it with the connectionstring I assume?

I will let you know what the tracing file gives after I created the .NET Core application.

alexkeh commented 3 years ago

Sorry, I assumed you were using ODP.NET Core. The trace setting code also works with managed ODP.NET assuming you are using one of the newer versions. If not, you can always enable tracing via the .NET config file.

Yes, the connection timeout is writeable in the connection string.

Tealons commented 3 years ago

Thanks, the error message is now different indeed:

Beginning of the trace file:

2020-10-13 15:15:41.439884 TID:1   (CFG) (ENV)      Machine Name : MMCMP-201
2020-10-13 15:15:41.439884 TID:1   (CFG) (ENV)      User Name : 650664
2020-10-13 15:15:41.439884 TID:1   (CFG) (ENV)      OS Version : Microsoft Windows NT 6.2.9200.0
2020-10-13 15:15:41.439884 TID:1   (CFG) (ENV)      64-bit OS : True
2020-10-13 15:15:41.455510 TID:1   (CFG) (ENV)      64-bit Process : False
2020-10-13 15:15:41.455510 TID:1   (CFG) (ENV)      .NET Runtime Version : 4.0.30319.42000
2020-10-13 15:15:41.455510 TID:1   (CFG) (ENV)      Application Directory : C:\Users\650664\Desktop\Connection Tester v3
2020-10-13 15:15:41.455510 TID:1   (CFG) (VER)      Oracle Data Provider for .NET, Managed Driver Version : 4.122.19.1
2020-10-13 15:15:41.455510 TID:1   (CFG) (VER)      Oracle Data Provider for .NET, Managed Driver Informational Version : 4.122.19.1:20200827
2020-10-13 15:15:41.455510 TID:1   (CFG) (SQLNET)   FilePath : (null)
2020-10-13 15:15:41.455510 TID:1   (CFG) (TNSNAMES) FilePath : (null)
2020-10-13 15:15:41.455510 TID:1   (CFG) (OCFG)     OracleConfiguration.TraceFileLocation() : C:\Users\650664\Desktop\Connection Tester v3
2020-10-13 15:15:41.455510 TID:1   (CFG) (OCFG)     OracleConfiguration.TraceLevel() : 7
2020-10-13 15:15:41.455510 TID:1   (CFG) (OCFG)     OracleConfiguration.TNSNames() : SampleDataSource : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
2020-10-13 15:15:41.439884 TID:1   (PUB) (ENT) OracleConnection.Open() (conid=2863675) (state=Closed) (sessid=0) (implid=0) (pooling=T) (txnid=n/a) 
2020-10-13 15:15:41.455510 TID:1   (PRI) (ENT) (CP) OracleConnectionDispenser..cctor()
2020-10-13 15:15:41.455510 TID:1   (PRI) (EXT) (CP) OracleConnectionDispenser..cctor()
2020-10-13 15:15:41.455510 TID:1   (PRI) (ENT) (CP) OracleConnectionDispenser.Get()
2020-10-13 15:15:41.471137 TID:1   (PRI) (ENT) (CP) PoolManager.ctor()
2020-10-13 15:15:41.471137 TID:1   (PRI) (EXT) (CP) PoolManager.ctor()
2020-10-13 15:15:41.471137 TID:1   (PRI) (ENT) (CP) PoolManager.Initialize() (constr=USER ID=CMP_POC_USER;CONNECTION TIMEOUT=120;DATA SOURCE="(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP) (Host = {host url redacted}) (Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = DWH_A3)));")
2020-10-13 15:15:41.471137 TID:1   (PRI) (EXT) (CP) PoolManager.Initialize() (pmid=65204782) (constr=USER ID=CMP_POC_USER;CONNECTION TIMEOUT=120;DATA SOURCE="(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP) (Host = {host url redacted}) (Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = DWH_A3)));")
2020-10-13 15:15:41.471137 TID:1   (PRI) (BUF) (OBP.CTOR) (poolid:49972132) (OracleConnectionDispenser`3.GetPM)
2020-10-13 15:15:41.471137 TID:1   (PRI) (ENT) (CP) OraclePoolManager.Get()
2020-10-13 15:15:41.486764 TID:1   (PRI) (ENT) (CP) PoolManager.Get() (txnid=n/a) (bForceMatch=F)
2020-10-13 15:15:41.486764 TID:1   (PRI) (ENT) (CP) PoolManager.Get() MultiTenant : Searching for a idle connection, retryCountWithoutAffinity: 0
2020-10-13 15:15:41.486764 TID:1   (PRI) (ENT) PoolManager.ProcessCriteriaCtx_NonEnlistedConnection()
2020-10-13 15:15:41.486764 TID:1   (PRI) (EXT) PoolManager.ProcessCriteriaCtx_NonEnlistedConnection()
2020-10-13 15:15:41.486764 TID:1   (PRI) (ENT) (CP) PoolManager.CreateNewPR() (txnid=n/a) 
2020-10-13 15:15:41.689889 TID:1   (PRI) (ENT) TimeStamp.GetLocalTZOffset()
2020-10-13 15:15:41.689889 TID:1   (PRI) (EXT) TimeStamp.GetLocalTZOffset()
2020-10-13 15:15:41.689889 TID:4   (PRI) (ENT) (CP) PoolManager.CreateNewPRThreadFunc()
2020-10-13 15:15:41.689889 TID:4   (PRI) (ENT) (CP) PoolManager.CreateNewPRThreadFunc() (initiated by TID:1)
2020-10-13 15:15:41.706639 TID:4   (PRI) (SVC) (ENT) OracleConnectionImpl.Connect() (oper=open) (aff=n/a) (inst=) (affmatch=n/a) (pr.service=) (pr.pdb=) (pr.edition=) (sessid=-1:-1) (F;F;F;;N) (pmid=65204782) 
2020-10-13 15:15:41.706639 TID:4   (PRI) (BUF) (COBP.CTOR) (poolid:1) (parentpoolid:49972132) (OracleConnectionImpl.Connect)
2020-10-13 15:15:41.737895 TID:4   (NET) (ENT) TcpTransportAdapter.Connect()
2020-10-13 15:15:41.753521 TID:4   (NET)      Trying (host={host url redacted}) (port=1521)
2020-10-13 15:15:41.753521 TID:4   (NET) (EXT) TcpTransportAdapter.Connect()
2020-10-13 15:15:41.753521 TID:4   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2020-10-13 15:15:41.753521 TID:4   (NET)      Trying (address=10.129.168.102) (port=1521)
2020-10-13 15:15:41.753521 TID:4   (PRI) (BUF) (ALLOCATION) (bufid:1)
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) 00 46 00 00 01 00 00 00   |.F......|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) 01 3D 01 2C 0C 01 FF FF   |.=.,....|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) FF FF 4F 98 00 00 00 01   |..O.....|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) 01 10 00 46 00 00 00 00   |...F....|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) 01 01 00 00 00 00 00 00   |........|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) 00 00 00 00 00 00 00 00   |........|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) 00 00 00 00 00 00 00 00   |........|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) 00 00 00 00 FF FF 00 00   |........|
2020-10-13 15:15:41.769146 TID:4   (NET) (SND) FF FF 00 00 00 00         |......  |

End: 

2020-10-13 15:15:41.784771 TID:4   (NET) (ENT) TcpTransportAdapter.Connect()
2020-10-13 15:15:41.784771 TID:4   (NET)      Trying (host=10.129.168.100) (port=1521)
2020-10-13 15:15:41.784771 TID:4   (NET) (EXT) TcpTransportAdapter.Connect()
2020-10-13 15:15:41.784771 TID:4   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2020-10-13 15:15:41.784771 TID:4   (NET)      Trying (address=10.129.168.100) (port=1521)
2020-10-13 15:16:02.812390 TID:4   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2020-10-13 15:16:02.812390 TID:4   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2020-10-13 15:16:02.812390 TID:4   (PRI) (ENT) OracleConnectionImpl.Connect()
2020-10-13 15:16:02.812390 TID:4   (PRI) (ENT) OracleException.ctor()
2020-10-13 15:16:02.812390 TID:4   (PRI) (ENT) OracleError.ctor()
2020-10-13 15:16:02.812390 TID:4   (PRI) (EXT) OracleError.ctor()
2020-10-13 15:16:02.812390 TID:4   (PRI) (EXT) OracleException.ctor()
2020-10-13 15:16:02.812390 TID:4   (PRI) (SVC) (ERR) OracleConnectionImpl.Connect() (txnid=n/a) OracleInternal.Network.NetworkException (0x80004005): Network Transport: TCP transport address connect failure ---> System.Net.Sockets.SocketException (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.129.168.100:1521
   at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at OracleInternal.Network.TcpTransportAdapter.ConnectIterate()
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String tnsDescriptor, Boolean doNAHandshake, String IName, ConnectionOption CO)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)
2020-10-13 15:16:02.812390 TID:4   (PRI) (EXT) OracleConnectionImpl.Connect()
2020-10-13 15:16:02.812390 TID:4   (PRI) (SVC) (EXT) OracleConnectionImpl.Connect() (oper=open) (aff=n/a) (inst=) (affmatch=n/a) (pr.service=) (pr.pdb=) (pr.edition=) (sessid=-1:-1) (F;F;F;;N) (pmid=65204782) 
2020-10-13 15:16:02.812390 TID:4   (PRI) (EXT) (CP) PoolManager.CreateNewPRThreadFunc()
2020-10-13 15:16:02.812390 TID:1   (PRI) (ENT) PoolManager.CreateNewPR()
2020-10-13 15:16:02.812390 TID:1   (PRI) (ERR) (CP) PoolManager.CreateNewPR() (txnid=n/a) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Network Transport: TCP transport address connect failure ---> OracleInternal.Network.NetworkException (0x80004005): Network Transport: TCP transport address connect failure ---> System.Net.Sockets.SocketException (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.129.168.100:1521
   at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at OracleInternal.Network.TcpTransportAdapter.ConnectIterate()
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String tnsDescriptor, Boolean doNAHandshake, String IName, ConnectionOption CO)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)
   at OracleInternal.ConnectionPool.PoolManager`3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, OracleConnection connRefForCriteria, String instanceName, List`1 switchFailedInstNames)
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) PoolManager.CreateNewPR()
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) (CP) PoolManager.CreateNewPR() (aff=n/a) (inst=) (affmatch=n/a) (pr.service=) (pr.pdb=) (pr.edition=) (sessid=-1:-1) (F;F;F;;N) (pmid=65204782) 
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) (CP) PoolManager.Get() (txnid=n/a) PM.Get(aff=;force=F) returning (null)
2020-10-13 15:16:02.812390 TID:1   (PRI) (ENT) OraclePoolManager.Get()
2020-10-13 15:16:02.812390 TID:1   (PRI) (ERR) (CP) OraclePoolManager.Get() (txnid=n/a) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Network Transport: TCP transport address connect failure ---> OracleInternal.Network.NetworkException (0x80004005): Network Transport: TCP transport address connect failure ---> System.Net.Sockets.SocketException (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.129.168.100:1521
   at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at OracleInternal.Network.TcpTransportAdapter.ConnectIterate()
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String tnsDescriptor, Boolean doNAHandshake, String IName, ConnectionOption CO)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)
   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)
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) OraclePoolManager.Get()
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) (CP) OraclePoolManager.Get() (txnid=n/a) 
2020-10-13 15:16:02.812390 TID:1   (PRI) (ENT) OracleConnectionDispenser.Get()
2020-10-13 15:16:02.812390 TID:1   (PRI) (ERR) (CP) OracleConnectionDispenser.Get() (txnid=n/a) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Network Transport: TCP transport address connect failure ---> OracleInternal.Network.NetworkException (0x80004005): Network Transport: TCP transport address connect failure ---> System.Net.Sockets.SocketException (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.129.168.100:1521
   at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at OracleInternal.Network.TcpTransportAdapter.ConnectIterate()
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String tnsDescriptor, Boolean doNAHandshake, String IName, ConnectionOption CO)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)
   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)
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) OracleConnectionDispenser.Get()
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) (CP) OracleConnectionDispenser.Get() (txnid=n/a) 
2020-10-13 15:16:02.812390 TID:1   (PRI) (ENT) OracleConnection.Open()
2020-10-13 15:16:02.812390 TID:1   (PUB) (ERR) OracleConnection.Open() (txnid=n/a) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Network Transport: TCP transport address connect failure ---> OracleInternal.Network.NetworkException (0x80004005): Network Transport: TCP transport address connect failure ---> System.Net.Sockets.SocketException (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.129.168.100:1521
   at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at OracleInternal.Network.TcpTransportAdapter.ConnectIterate()
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String tnsDescriptor, Boolean doNAHandshake, String IName, ConnectionOption CO)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)
   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()
2020-10-13 15:16:02.812390 TID:1   (PRI) (EXT) OracleConnection.Open()
2020-10-13 15:16:02.812390 TID:1   (PUB) (EXT) OracleConnection.Open() (conid=2863675) (state=Closed) (sessid=0) (implid=0) (pooling=T) (txnid=n/a) 
2020-10-13 15:17:07.255298 TID:1   (PUB) (ENT) OracleConnection.Close() (conid=2863675) (state=Closed) (sessid=0) (implid=0) (pooling=T) (txnid=n/a) 
2020-10-13 15:17:07.255298 TID:1   (PUB) (EXT) OracleConnection.Close() (conid=2863675) (state=Closed) (sessid=0) (implid=0) (pooling=T) (txnid=n/a) 
2020-10-13 15:17:10.195589 TID:2   (PUB) (ENT) OracleConnection.dtor()
2020-10-13 15:17:10.195589 TID:2   (PUB) (ENT) OracleConnection.Dispose()
2020-10-13 15:17:10.195589 TID:2   (PUB) (EXT) OracleConnection.Dispose()
2020-10-13 15:17:10.195589 TID:2   (PUB) (EXT) OracleConnection.dtor()
2020-10-13 15:17:10.195589 TID:2   (PUB) (ENT) OracleConnection.Dispose()
2020-10-13 15:17:10.195589 TID:2   (PUB) (EXT) OracleConnection.Dispose()

I don't see anything related to a TLS wallet. I'm not sure that is needed? We tried connecting with SQLPlus from another machine with the same connection string and that worked. We also checked the connection to the server with Powershell and that also seems to work. Please advice what to do next.

alexkeh commented 3 years ago

The lack of response error is indicative that something is swallowing the message on the way out (or possibly in). Did you try your PowerShell connection test from the same machine the ODP.NET client is running from? If Powershell is not working, that would be indicative of a firewall, such as from a corporate intranet, that is blocking the communication.

TLS is more typical for those using Oracle Autonomous Database, but it's not what you are using it appears.

Tealons commented 3 years ago

Yes, we tested via powershell on the same machine. I'm not that familiar with the Oracle Cloud, but my customer is hosting a 12.1 database there. Is there any ip firewall restriction possible with an Oracle database?

One strategy I'm now considering is letting the customer install SQL Developer on the same machine ODP.NET is running on to test if the IDE can make the connection work. If not, some firewall in the network is blocking, otherwise the problem is in ODP.NET. However, I assume the protocol that ODP.NET and SQL Developer use is the same?

alexkeh commented 3 years ago

I'll have to ask my dev team if they've seen this issue.

One can restrict network access to an Oracle cloud DB instance. The fact another machine can reach the DB instance can mean either such a firewall permits this access or at least that particular machine's IP is greenlisted for DBCS access. If Powershell can reach the DB machine, then ODP.NET should be able to reach the machine as well.

Yes, running SQL Developer on the same machine would verify whether DB access works from that machine since Powershell is just verifying machine access. However, the protocol ODP.NET and SQL Developer use are different. SQL Developer generally uses thin JDBC and managed ODP.NET uses a similar Oracle native protocol implemented in C# instead of Java.

alexkeh commented 3 years ago

Hi @Tealons One of our developers reviewed your trace. Here's his comments below. He would also like to see the rest of your trace. If it's too long or if you don't want to post it on this forum, you can send it via email to dotnet_us(at)oracle.com.

In general, he tried to connect on some redacted hostname, that resolved to multiple IPs. The connect appears to have failed on the first IP (10.129.168.102), for an unknown reason since the trace was chopped. He then went on to try to connect on OTHER IPs from the resolved list (maybe...maybe only 2 IPs), and finally failed w/ a timeout on 10.129.168.100, which appears to be the LAST IP.

My guess is that they expect to connect on one of those INITIAL IPs (eg, 10.129.168.102), and are failing with a timeout on the later IPs, because those later hosts are not really fully setup.

Tealons commented 3 years ago

Hi @alexkeh, I have just send you the tracefile. I hope this helps. I also asked our customer to install SQL Developer. I will report back when this is done and we tested the connection.

alexkeh commented 3 years ago

@Tealons Got the file. We'll let you know what we find.

alexkeh commented 3 years ago

After further discussion with the DB team, this appears to have been an issue with the RAC setup itself, not an ODP.NET issue. Closing the issue.