SocketException the first time OracleConnection.Open is used with fully managed Oracle data provider

Question

Using Oracle.ManagedDataAccess.Client, the following VB.NET lines throw an internal exception:

Dim conn As New OracleConnection(connStr)
conn.ConnectionString = "Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))); User id=username; Password=password"
conn.Open()

The conn.Open statement succeeds but throws the following exception:

A first chance exception of type 'System.Net.Sockets.SocketException' occurred in System.dll

More importantly, it takes 1.5 second to open the first connection.

There are no problems if I close the connection and open a new one.

I had no such problems with the unmanaged Oracle data provider where the first-time connection would be made in a fraction of a second. It makes no difference if I am connecting to an Oracle 11g or 12c database server so it looks as if the data provider is the culprit.

Is the data provider attempting something which fails after which it defaults to something else for the rest of the session and if so, is there anything I can do to force it to take the successful path the first time?

Is the long wait for the first connection simply due to the cost of initializing the Oracle pool manager? If so, I suppose there is nothing I can do to fix that part of it but even if that is the case I would still like to know if it is possible to do anything to get rid of the SocketException.

The stack trace on the .NET side when the SocketException is hit looks like this:

System.dll!System.Net.Sockets.Socket.EndConnect(System.IAsyncResult asyncResult)
System.dll!System.Net.Sockets.TcpClient.EndConnect(System.IAsyncResult asyncResult)
Oracle.ManagedDataAccess.dll!OracleInternal.Network.TcpTransportAdapter.Connect(OracleInternal.Network.ConnectionOption conOption)
Oracle.ManagedDataAccess.dll!OracleInternal.Network.OracleCommunication.ConnectViaCO(OracleInternal.Network.ConnectionOption connOption, OracleInternal.Network.AddressResolution addrRes)
Oracle.ManagedDataAccess.dll!OracleInternal.Network.OracleCommunication.DoConnect(string tnsDescriptor)
Oracle.ManagedDataAccess.dll!OracleInternal.Network.OracleCommunication.Connect(string tnsDescriptor, bool externalAuth, string instanceName)
Oracle.ManagedDataAccess.dll!OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(Oracle.ManagedDataAccess.Client.ConnectionString cs, bool bOpenEndUserSession, string instanceName)
Oracle.ManagedDataAccess.dll!OracleInternal.ConnectionPool.PoolManager<OracleInternal.ConnectionPool.OraclePoolManager,OracleInternal.ConnectionPool.OraclePool,OracleInternal.ServiceObjects.OracleConnectionImpl>.CreateNewPRThreadFunc(object state)
mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)
mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)
mscorlib.dll!System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()
mscorlib.dll!System.Threading.ThreadPoolWorkQueue.Dispatch()

Looking at the Oracle .trc files, nothing seems to be out of the ordinary.

Answer 1

The main problem appears to have been with the listener.ora and tnsnames.ora configuration.

This is a local 12c standard database server installation and the listener.ora configuration file included an IPC protocol entry which I certainly at no point during installation asked it to include. Once I ran the Network Configuration Assistant to reconfigure the listener, this entry disappeared. Also, the host name for the TCP protocol was changed from localhost to the real machine name.

As for tnsnames.ora, the Network Configuration Assistant updated the host name in the descriptor from localhost to the real machine name and removed the SERVER = DEDICATED value.

I am fairly sure I did not specify localhost at any point during the standard/typical installation of the 12c database server but the response file does include the entry "ORACLE_HOSTNAME=localhost" so either I did specify this or the installer defaulted to this value.

But the main culprit as far as the SocketException is concerned appears to have been the presence of the IPC protocol entry in the listener.ora configuration file.

I am no longer seeing an internal, first-time SocketException and OracleConnection.Open execution time is now down to less than half a second, i.e. one second less than was the case originally.

EDIT

I can confirm that a typical installation of the standard 12c database will

• Default to setting ORACLE_HOSTNAME to localhost, which will be used in listener.ora and tnsnames.ora.

• Add the IPC protocol entry to the listener descriptor.

Answer 2

 Dim oradb As String = "Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)));User Id=username;Password=password;"
 Dim conn As New OracleConnection(oradb)
 conn.Open()