Connection failed on 3.21.4 but working on 2.19.91

[wahmedswl]

Hi,

We are moving from Oracle.ManagedDataAccess.Core 2.19.91 to Oracle.ManagedDataAccess.Core 3.21.4 and after update we are not able to connect to the database. Database version is 12.1.0.2.0 - 64bit Production.

We have public listener, it work on v2.19.91 but fail on v3.21.4. Is something changed in handling of tns file with public and local address etc?

2021-11-05 11:41:26.556108 TID:5   (NET)      Using host: 212.69.144.65 and hostname: 212.69.144.65
2021-11-05 11:41:26.571060 TID:5   (NET) (ENT) TcpTransportAdapter.Connect()
2021-11-05 11:41:26.571934 TID:5   (NET)      Trying (host=212.69.144.65) (port=1630)
2021-11-05 11:41:26.571965 TID:5   (NET) (EXT) TcpTransportAdapter.Connect()
2021-11-05 11:41:26.573522 TID:5   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2021-11-05 11:41:26.575572 TID:5   (NET)      Trying (address=212.69.144.65) (port=1630)
2021-11-05 11:41:26.850433 TID:5   (PRI) (BUF) (ALLOCATION) (bufid:1)
2021-11-05 11:41:26.879858 TID:5   (NET) (SND) 01 24 00 00 01 00 00 00   |.$......|
2021-11-05 11:41:26.879883 TID:5   (NET) (SND) 01 3D 01 2C 0C 01 FF FF   |.=.,....|
2021-11-05 11:41:26.879888 TID:5   (NET) (SND) FF FF 4F 98 00 00 00 01   |..O.....|
2021-11-05 11:41:26.879892 TID:5   (NET) (SND) 00 DE 00 46 00 00 00 00   |...F....|
2021-11-05 11:41:26.879895 TID:5   (NET) (SND) 01 01 00 00 00 00 00 00   |........|
2021-11-05 11:41:26.879898 TID:5   (NET) (SND) 00 00 00 00 00 00 00 00   |........|
2021-11-05 11:41:26.879901 TID:5   (NET) (SND) 00 00 00 00 00 00 00 00   |........|
2021-11-05 11:41:26.879907 TID:5   (NET) (SND) 00 00 00 00 FF FF 00 00   |........|
2021-11-05 11:41:26.879911 TID:5   (NET) (SND) FF FF 00 00 00 00 28 44   |......(D|
2021-11-05 11:41:26.879914 TID:5   (NET) (SND) 45 53 43 52 49 50 54 49   |ESCRIPTI|
2021-11-05 11:41:26.879917 TID:5   (NET) (SND) 4F 4E 3D 28 53 4F 55 52   |ON=(SOUR|
2021-11-05 11:41:26.879920 TID:5   (NET) (SND) 43 45 5F 52 4F 55 54 45   |CE_ROUTE|
2021-11-05 11:41:26.879924 TID:5   (NET) (SND) 3D 79 65 73 29 28 43 4F   |=yes)(CO|
2021-11-05 11:41:26.879928 TID:5   (NET) (SND) 4E 4E 45 43 54 5F 44 41   |NNECT_DA|
2021-11-05 11:41:26.879931 TID:5   (NET) (SND) 54 41 3D 28 53 45 52 56   |TA=(SERV|
2021-11-05 11:41:26.879934 TID:5   (NET) (SND) 49 43 45 5F 4E 41 4D 45   |ICE_NAME|
2021-11-05 11:41:26.879937 TID:5   (NET) (SND) 3D 45 44 49 33 36 30 29   |=EDI360)|
2021-11-05 11:41:26.879939 TID:5   (NET) (SND) 28 43 49 44 3D 28 50 52   |(CID=(PR|
2021-11-05 11:41:26.879942 TID:5   (NET) (SND) 4F 47 52 41 4D 3D 43 3A   |OGRAM=C:|
2021-11-05 11:41:26.879946 TID:5   (NET) (SND) 5C 47 69 74 6C 61 62 5C   |\Gitlab\|
2021-11-05 11:41:26.879949 TID:5   (NET) (SND) 43 6F 72 65 5C 4E 65 74   |Core\Net|
2021-11-05 11:41:26.879953 TID:5   (NET) (SND) 35 30 5C 62 69 6E 5C 44   |50\bin\D|
2021-11-05 11:41:26.879956 TID:5   (NET) (SND) 65 62 75 67 5C 6E 65 74   |ebug\net|
2021-11-05 11:41:26.879959 TID:5   (NET) (SND) 35 2E 30 5C 56 69 73 75   |5.0\Visu|
2021-11-05 11:41:26.879961 TID:5   (NET) (SND) 61 6C 42 61 73 69 63 2E   |alBasic.|
2021-11-05 11:41:26.879964 TID:5   (NET) (SND) 64 6C 6C 29 28 48 4F 53   |dll)(HOS|
2021-11-05 11:41:26.879968 TID:5   (NET) (SND) 54 3D 57 41 48 4D 45 44   |T=WAHMED|
2021-11-05 11:41:26.879971 TID:5   (NET) (SND) 29 28 55 53 45 52 3D 57   |)(USER=W|
2021-11-05 11:41:26.879974 TID:5   (NET) (SND) 61 71 61 73 41 68 6D 65   |aqasAhme|
2021-11-05 11:41:26.879977 TID:5   (NET) (SND) 64 29 29 29 28 41 44 44   |d)))(ADD|
2021-11-05 11:41:26.879980 TID:5   (NET) (SND) 52 45 53 53 3D 28 50 52   |RESS=(PR|
2021-11-05 11:41:26.879983 TID:5   (NET) (SND) 4F 54 4F 43 4F 4C 3D 54   |OTOCOL=T|
2021-11-05 11:41:26.879987 TID:5   (NET) (SND) 43 50 29 28 48 4F 53 54   |CP)(HOST|
2021-11-05 11:41:26.879990 TID:5   (NET) (SND) 3D 32 31 32 2E 36 39 2E   |=212.69.|
2021-11-05 11:41:26.879993 TID:5   (NET) (SND) 31 34 34 2E 36 35 29 28   |144.65)(|
2021-11-05 11:41:26.879996 TID:5   (NET) (SND) 50 4F 52 54 3D 31 36 33   |PORT=163|
2021-11-05 11:41:26.879999 TID:5   (NET) (SND) 30 29 29 29               |0)))    |
2021-11-05 11:41:27.130601 TID:5   (NET) (REC) 00 67 00 00 04 00 00 00   |.g......|
2021-11-05 11:41:27.130884 TID:5   (NET) (REC) 22 00 00 5B 28 44 45 53   |"..[(DES|
2021-11-05 11:41:27.130890 TID:5   (NET) (REC) 43 52 49 50 54 49 4F 4E   |CRIPTION|
2021-11-05 11:41:27.130893 TID:5   (NET) (REC) 3D 28 54 4D 50 3D 29 28   |=(TMP=)(|
2021-11-05 11:41:27.130895 TID:5   (NET) (REC) 56 53 4E 4E 55 4D 3D 31   |VSNNUM=1|
2021-11-05 11:41:27.130896 TID:5   (NET) (REC) 36 39 38 37 30 33 33 36   |69870336|
2021-11-05 11:41:27.130898 TID:5   (NET) (REC) 29 28 45 52 52 3D 31 32   |)(ERR=12|
2021-11-05 11:41:27.130899 TID:5   (NET) (REC) 35 31 34 29 28 45 52 52   |514)(ERR|
2021-11-05 11:41:27.130901 TID:5   (NET) (REC) 4F 52 5F 53 54 41 43 4B   |OR_STACK|
2021-11-05 11:41:27.130905 TID:5   (NET) (REC) 3D 28 45 52 52 4F 52 3D   |=(ERROR=|
2021-11-05 11:41:27.130907 TID:5   (NET) (REC) 28 43 4F 44 45 3D 31 32   |(CODE=12|
2021-11-05 11:41:27.130908 TID:5   (NET) (REC) 35 31 34 29 28 45 4D 46   |514)(EMF|
2021-11-05 11:41:27.130910 TID:5   (NET) (REC) 49 3D 34 29 29 29 29      |I=4)))) |
2021-11-05 11:41:27.165457 TID:5   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2021-11-05 11:41:27.166457 TID:5   (NET)      Using host: 10.20.31.93 and hostname: 10.20.31.93
2021-11-05 11:41:27.166517 TID:5   (NET) (ENT) TcpTransportAdapter.Connect()
2021-11-05 11:41:27.166538 TID:5   (NET)      Trying (host=10.20.31.93) (port=1521)
2021-11-05 11:41:27.166547 TID:5   (NET) (EXT) TcpTransportAdapter.Connect()
2021-11-05 11:41:27.166554 TID:5   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2021-11-05 11:41:27.166616 TID:5   (NET)      Trying (address=10.20.31.93) (port=1521)
2021-11-05 11:41:48.225502 TID:5   (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2021-11-05 11:41:48.226844 TID:5   (PRI) (ENT) OracleConnectionImpl.Connect()
2021-11-05 11:41:48.227061 TID:5   (PRI) (ENT) OracleException.ctor()
2021-11-05 11:41:48.227403 TID:5   (PRI) (ENT) OracleError.ctor()
2021-11-05 11:41:48.227609 TID:5   (PRI) (EXT) OracleError.ctor()
2021-11-05 11:41:48.227628 TID:5   (PRI) (EXT) OracleException.ctor()
2021-11-05 11:41:48.230366 TID:5   (PRI) (SVC) (ERR) OracleConnectionImpl.Connect() (txnid=n/a) OracleInternal.Network.NetworkException (0x80004005): Network Transport: TCP transport address connect failure
 ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (10060): 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.20.31.93:1521

Thanks

[wahmedswl]

Hi,

We have captured traces from v2.19.91 and its sending full address list but v3.21.4 is not sending the full address list only the 1st one. There is related issue #164 but we already using the single address list and multiple addresses within it.

Thanks

[alexkeh]

If you upgrade to ODP.NET Core 2.19.100, do you also see this error? We have another customer that is seeing a similar problem, but with a different error, starting with that release. It could be the same issue.

[wahmedswl]

yes, only v2.19.91 is the version which is working correctly!

Thanks

[wahmedswl]

Hi, It seems like TNS parsing is creating issues. OracleCommunication.ConnectViaCO is not receiving the fully generated ConnectionOption.ConnectData. In v2.19.91, full address list is sent but in v3.21.4, only 1st address is sent.

[wahmedswl]

Hi, AddressResolution.GetEnumerator semantic has been changed, failover scenarios may be but it seems like SOURCE_ROUTE handling is missed!

When a client goes through an Oracle Connection Manager for a connection to a service, the first address (or set of addresses) is for a client connection to an Oracle Connection Manager and the second address (or set of addresses) is for an Oracle Connection Manager connection to a service using listener.

When the Oracle Connection Manager parameter SOURCE_ROUTE is set to YES, it instructs the client to go through all the addresses listed before connecting.

Shown in Example C-5 is an example with multiple Oracle Connection Manager addresses:

Example C-5 Multiple Oracle Connection Manager Addresses tnsnames.ora
sample1=
 (description= 
   (source_route=yes) 
   (address=(protocol=tcp)(host=host1)(port=1630))    # hop 1 
   (address_list=  
     (failover=on) 
     (load_balance=off)                                # hop 2 
     (address=(protocol=tcp)(host=host2a)(port=1630)) 
     (address=(protocol=tcp)(host=host2b)(port=1630)))
   (address=(protocol=tcp)(host=host3)(port=1521))    # hop 3
   (connect_data=(service_name=sales.us.acme.com)))

Following is the stacktrace

AddressResolution.<GetEnumerator>d__139.MoveNext() at C:\Users\waqas\AppData\Roaming\JetBrains\Rider2021.2\resharper-host\DecompilerCache\decompiler\8fd31a26c15b464bb7ad60e7434d6daf449140\7c\25d19688\AddressResolution.cs:line 793
OracleCommunication.DoConnect() at C:\Users\waqas\AppData\Roaming\JetBrains\Rider2021.2\resharper-host\DecompilerCache\decompiler\8fd31a26c15b464bb7ad60e7434d6daf449140\4a\01e934da\OracleCommunication.cs:line 404
OracleCommunication.Connect()
OracleConnectionImpl.Connect()
PoolManager<OraclePoolManager, OraclePool, OracleConnectionImpl>.CreateNewPRThreadFunc()
QueueUserWorkItemCallbackDefaultContext.Execute()
ThreadPoolWorkQueue.Dispatch()
_ThreadPoolWaitCallback.PerformWaitCallback()
[Native to Managed Transition]

[alexkeh]

Thanks for conducting some debugging. The dev team has a good idea what the problem is and will fix it.

I've filed bug 33542715 to track this issue.

[alexkeh]

In the meantime, there's a workaround that can be used to avoid the buggy code. Change the CMAN and DB server configuration so that the DB registers itself with CMAN. Thus, CMAN will know of the service name and no source routing is necessary.

[alexkeh]

Bug is fixed in version 21.10.