Oracle.ManagedDataAcess.Client how to connect using TNSNAMES when there are two definitions of alias - first is with FAILOVER

Question

I want to connect using alias from tnsnames.ora. I parse the file selected based on environment and registry configuration. I don't know diferent way to connect by alias. I am currently facing an issue with one of my client becouse he has strange configuration in tnsnames. He has defined two aliases with the same name in the configuration file, with one of them specifying FAILOVER_MODE. Trick is with tnsping.exe that returns proper connectionstring.

Definition of tnsnames.ora - notice that aliases are the same, but tnsping for alias tns_1 returns second connection string.

tns_1 = 
    (DESCRIPTION=
    (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
    (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=orcl)
        (FAILOVER_MODE =
                (TYPE=SESSION)
                (METHOD=BASIC)
                (RETRIES=250)
                (DELAY=3)
        )
    )
)
 
tns_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  ) 

How can I obtain the correct connection string for connecting to a database using Oracle.ManagedDataAccess? I prefer not to use tnsping because not all clients have it installed on their machines.

Answer 1

What do you mean by "I don't know different way to connect by alias"?

With the ODP.NET Managed Driver you can simply use the alias. Be, aware the ODP.NET Managed Driver does not read the Windows Registry to determine the location of your tnsnames.ora file, see Determining location of relevant tnsnames.ora file

For the ODP.NET Managed Driver, you can specify the location also similar to this:

cd %ORACLE_HOME%
ODP.NET\managed\x64\OraProvCfg.exe /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:ODP.NET\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:C:\oracle\network\admin
ODP.NET\managed\x86\OraProvCfg.exe /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:ODP.NET\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:C:\oracle\network\admin

You should not have the same alias multiple times in your tnsnames.ora, the result is not determined. As software developer, I would even raise an error.