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

www.obieefans.com 


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.