OBE   OBIEE   Tutorial     
       
          Installing OBIEE on Windows  

 

 

 

“TNS: could not resolve” error during ODBC configuration and admininstration

 

Q: I’m trying to get the installation tutorial to work.  I’ve configured the ODBC data source using the tnsnames.ora example given in the tutorial, substituting my own host name, but I’m getting a “TNS: could not resolve” error when I test the connection from the ODBC driver configuration.  And in the Admin Tool when I try to update the row count for Products I also get the same message?

 

For a start you don’t need to create an ODBC Data Source to carry out this tutorial.  The only reason for doing so might be to test that the Net Service Name in file “tnsnames.ora” has been set up correctly, but you can do this with less effort, if required, from SQL*Plus.

 

So let’s start with what you will need for this tutorial.  You will need an entry in file “tnsnames.ora”.  You’ll find the file in directory “\network\admin”.  If the file doesn’t exist in this directory you’ll need to create one using, for example, Notepad.  You’ll also need an entry in the file similar to the following (substituting for your “host” and “service_name” as appropriate – the “service_name” will probably be “orcl”):

 

bitest =
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = tcp)(HOST = hp)(PORT = 1521))
      )
      (CONNECT_DATA =
         (SERVICE_NAME = orcl)
      )
   )

 

This entry maps a Net Service Name of “bitest” onto a Database Service Name of “orcl” at the specified address.  You can verify that this connection is working by logging into SQL*Plus and entering

 

connect sh/<password>@bitest

 

The example for the Net Service Name given in the tutorial, “orcl SH”, won’t work because it contains an embedded space.  If you enter this value for the Data Source Name in the Connection Pool window of the Administration Tool then you’ll get a

 

*  ORA-12154: TNS: could not resolve the connect identifier specified

 

error message.  If you look at the value entered in the tutorial for the Connection Pool Data Source Name it’s “orclSH”, rather than “orcl SH” – so this looks like a case of the “Oracle fat-finger” syndrome.  So if you change the Net Service Name in the “tnsnames.ora” file from “orcl SH” to “orclSH”, then the main part of the tutorial should work (but the Net Service Name doesn’t have to be “orclSH”; any valid name will work as long as it’s the same name in both places).

 

Now to creating the ODBC Data Source.  The tutorial states that the Data Source Name must be the same as the instance name.  It doesn’t have to be; you can choose any name you like (note the Data Source Name specified in the ODBC Driver Configuration window has nothing to do with the Data Source Name entered in the Connection Pool window in the Administration Tool – the former is just an arbitrary name for the data source; the latter is the value of a Net Service Name from the “tnsnames.ora” file).

 

The TNS Service Name entered in the ODBC Driver Configuration window must be the same as the Net Service Name you want to test, for example “orclSH”.  Now the value of the TNS Service Name specified in the tutorial, “orcl”, is a Database Service Name.  Using a Database Service Name won’t work, which is probably why you also got an error when testing the connection from the ODBC Driver Configuration window (the tutorial states that the TNS Service Name can be equal to the Service Name, but this is not correct, in general).  However, it’s very common for a “tsnnames.ora” file to contain an entry that maps a Net Service Name of “orcl” onto a Database Service Name of “orcl”:

 

orcl =
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = tcp)(HOST = hp)(PORT = 1521))
      )
      (CONNECT_DATA =
         (SERVICE_NAME = orcl)
      )
   )

 

If this is the case then using “orcl” for the TNS Service Name would work, but then you wouldn’t be testing the “orclSH” connection.  You probably don’t have this entry in your “tnsnames.ora” file or else you have a non-default Database Service Name (first verify that you can connect to the database from within SQL*Plus).  If you enter a value for the TNS Service Name equal to that of the Net Service Name from the “tnsnames.ora” file then the connection test should be successful.

 

 

“Could not connect” during update of row count for Products

 

Q: I’m trying to update the row count for Products in the OBIEE installation tutorial.  I get a “Could not connect message”, even though I can connect using SQL*Plus and the ODBC data source?

 

When you enter the Connection Pool window you’ll find that the values imported from the “sh” repository don’t match the values in the tutorial.  You’ll get values of

 

*  Call Interface: “OCI 8i/9i”

*  Data Source Name: “ORCL”

 

instead of

 

*  Call Interface: “OCI 10g”

*  Data Source Name: “orclSH”

 

The value for the call interface won’t cause the connection to fail, but it’s worth selecting a value appropriate to your database from the list of values.  If you have an entry with a Net Service Name of “orcl” in your “tnsnames.ora” file then you don’t have to change the value of the Data Source Name.  If you do use “orclSH” then make sure there is a corresponding entry with this Net Service Name in the “tnsnames.ora” file.

 

Now to your question.  Given that you can connect from SQL*Plus and an ODBC client the problem is likely to be the password.  The default password for schema “sh” is “sh”, and this is the password that is imported from the repository.  When your DBA installed the database it’s quite likely he changed the passwords when he unlocked the schemas.  If schema “sh” has a different password you’ll be able to connect from SQL*Plus using the changed password.  You’ll also be able to connect from an ODBC client since the ODBC data source definition doesn’t store the password – unlike the Administration Tool – but asks you for it each time you test a connection.  If you’re assuming that the Administration Tool has picked up the changed password from somewhere else then that could be the problem.  Just change the password in the Connection Pool to the one that worked from SQL*Plus and you should be able to connect and update the row count.