dotConnect getting confused between multiple oracle homes

Simon C · Post by **Simon C** » Mon 14 Dec 2009 16:46

If you run the following code on a computer with two or more oracle homes installed, the second connection fails as it cannot resolve the connection identifier - it seems it's getting confused between the two oracle homes. This is using dotConnect 5.35.57.0. What am I doing wrong here?

Code: Select all

IList homes = OracleConnection.Homes;

OracleHome home1 = (OracleHome)homes[0];
OracleHome home2 = (OracleHome)homes[1];

string db1 = home1.GetServerList()[0];
string db2 = home2.GetServerList()[0];

using (OracleConnection db1Conn = new OracleConnection {Home = home1.Name, Server = db1, UserId = "xxxx", Password = "xxxx"}) {
    db1Conn.Open();
}

using (OracleConnection db2Conn = new OracleConnection {Home = home2.Name, Server = db2, UserId = "xxxx", Password = "xxxx"}) {
    db2Conn.Open();
}

StanislavK · Post by **StanislavK** » Tue 15 Dec 2009 10:17

It seems that you have TNS names not set for one of your Oracle clients. Please try copying the tnsnames.ora file from the NETWORK\ADMIN\ folder of the first client to the same folder of the second client. For more information on the problem, see the Oracle help:
http://ora-12154.ora-code.com/ .

Simon C · Post by **Simon C** » Tue 15 Dec 2009 12:25

Both oracle homes have a tnsnames.ora file, but they have different entries in them. For the second connection, I believe it's trying to use the first oracle home tns entry in the second connection, which doesn't exist (hence the error)

StanislavK · Post by **StanislavK** » Tue 15 Dec 2009 13:48

This is how the GetServerList function works: it reads TNS aliases of the default Oracle home only.

Simon C · Post by **Simon C** » Tue 15 Dec 2009 13:50

Even though it's called on a particular OracleHome instance? Is there any way of getting tns aliases of non-default homes?

StanislavK · Post by **StanislavK** » Tue 15 Dec 2009 16:08

You were right: the GetServerList method of OracleHome instance returns the TNS list for the current Oracle home. Please check under debug what exact server you are connecting to and make sure that you can access it by hardcoded OracleConnection with the same parameters.

Simon C · Post by **Simon C** » Tue 15 Dec 2009 20:24

Still get the same error (ORA-12154) with the following code:

Code: Select all

using (OracleConnection db1Conn = new OracleConnection { Home = "OraClient10g_home1", Server = "DB1", UserId = "xxxx", Password = "xxxx" })
{
    db1Conn.Open();
}

using (OracleConnection db2Conn = new OracleConnection { Home = "OraClient10g_home2", Server = "DB2", UserId = "xxxx", Password = "xxxx" })
{
    db2Conn.Open();
}

This is the contents of tnsnames.ora in OraClient10g_home1:

Code: Select all

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = simonctest.testnet)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = 10gR2db1)
    )
  )

and in OraClient10g_home2:

Code: Select all

DB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = simonctest.testnet)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = 10gR2db2)
    )
  )

However, I do get the same error when I try to connect to DB2 using sqlplus in home2. Is this a limitation of the oracle client itself?

StanislavK · Post by **StanislavK** » Wed 16 Dec 2009 10:49

This appears to be a problem with Oracle server or/and client settings. Maybe, Port is invalid in tnsnames.ora for OraClient10g_home2? For more information, please refer to the Oracle help on ORA-12154.

Simon C · Post by **Simon C** » Wed 16 Dec 2009 12:12

Ah, apologies. sqlplus on home2 wasn't set up correctly. Setting ORACLE_HOME to each home allows me to connect to the corresponding database using each tns identifier. Do I need to set the ORACLE_HOME environment variable to do the same with devart?

Are you able to reproduce this issue on your systems?

StanislavK · Post by **StanislavK** » Wed 16 Dec 2009 15:32

To open OracleConnection using OCI you need an operable Oracle client. To ensure it is operable, please try to access your Oracle server via some standard tool like SQL Plus. For help on setting up the client, please refer to the Oracle documentation.

If the connection via SQL Plus passes and the one via OracleConnection fails please specify the following so that we'll be able to reproduce the error:
1) the exact error message you receive;
2) the versions (including minor) of your Oracle client and DBMS;
3) the exact connection string you use.

Speaking about ORACLE_HOME, which one do you mean? Is it the registry record (like HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_YourOracleHome) or ORACLE_HOME from My Computer > Properties > Advanced > Environmental variables?

Simon C · Post by **Simon C** » Wed 16 Dec 2009 17:08

I'm referring to the ORACLE_HOME environment variable - does setting this for the app affect what OracleConnection does? Does this need to be set as well as specifying the OracleConnection.Home property?

- Both oracle clients are 10.2.0.1.0, both server instances are 10.2.0.3.0
- I do not use a single connection string, I specify connection details using properties on the OracleConnection class, as in the code examples above
- The error is ORA-12154: TNS:could not resolve the connect identifier specified when it tries to open the db2Conn connection

StanislavK · Post by **StanislavK** » Thu 17 Dec 2009 11:25

As for ORACLE_HOME environment variable, please see the Oracle help, section "Setting Variables in the Environment or the Registry":
http://download.oracle.com/docs/html/B1 ... rahome.htm .
In a nutshell, it is strictly recommended to leave the ORACLE_HOME variable unset when using multiple Oracle homes. Otherwise, all Oracle homes except the one set in this variable may become unoperable.

At the moment, we cannot reproduce the error.

If ORACLE_HOME has empty value, are you able to connect to both servers
a) with SQL Plus or some other standard utility?
b) with OracleConnection?

You said you had set up SQL Plus. Could you please specify what kind of settings it was necessary to change? Was the ORACLE_HOME value the only change you made?

Also, please see our article "Using dotConnect for Oracle"."Using Direct Mode" at
http://www.devart.com/dotconnect/oracle/docs/ .
If the limitations described there are not unacceptable for you, you may use the Direct Mode as a workaround.

Simon C · Post by **Simon C** » Wed 06 Jan 2010 17:09

With ORACLE_HOME unset, I am able to connect to DB1 with the sqlplus exe in home1, and to DB2 with the sqlplus exe in home2. Vice versa produces a ORA-12154 error. No special settings were required.

I am unable to do the same using devart OracleConnection and specifying the Home property to the home I wish to use - it always uses the default home (in my case, home2)

Unfortunately, I cannot use direct connection within my application, as users need to specify the TNS identifier they wish to connect to.

StanislavK · Post by **StanislavK** » Fri 08 Jan 2010 12:47

Could you please specify how do you determine which Oracle Home is used by OracleConnection?

Also, a small test project would be appreciated, as we cannot reproduce the problem at the moment.

Simon C · Post by **Simon C** » Fri 08 Jan 2010 13:06

I determine the homes each oracle connection is using via the Homes property on OracleConnection in the VS debug view (which is set to what it should be)