Re: TNS Errors - Please Help An Amateur

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:LuZnc.6237$eH1.3064955_at_newssvr28.news.prodigy.com...
> I'm a relative amateur so please bear with me and hopefully someone can
help
> me.

No worries.

> I had to uninstall my Oracle 9i and reinstall it and now I'm having
problems
> connecting to my databases. I can connect just fine using Oracle
Enterprise
> Manager but when I try to connect with SQLPlus I get different TNS errors
> depending on the host string I use.
>
> I get either ORA-12514 or ORA-12154 and I am not sure whatsoever how to
fix
> it. Before I had to remove Oracle it worked fine - though I did have help
> when I first installed Oracle before.

Can I point you at otn.oracle.com where there is a host of helpful documentation - and more usefully for the puposes of this excercise you need to have an account before you can use the online Oracle error search facility located at http://tahiti.oracle.com

Using this I am able to retrieve the error text for the error codes you quote. I remember a lot of garbage but which error message goes with which code is a step too far for me at least. Anyway in the first case you get

TNS-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor

Cause: The SERVICE_NAME in the CONNECT_DATA was not found in the listener's tables.

Action: Check to make sure that the SERVICE_NAME specified is correct

This is saying that your tnsnames.ora file (assuming you are using one and I bet you are) has an incorrect value for SERVICE_NAME so look for this line - tnsnames.ora is a plain text file and correct the service name. If you don't know what the service name should be and you have access to the server typing lsnrctl status will give a list of all services that are registered with that listener.

the second error on the other hand comes up with

TNS-12154 TNS:could not resolve service name

Cause: The service name specified is not defined correctly in the TNSNAMES.ORA file.

Action: Make the following checks and correct the error:

  a.. Verify that a TNSNAMES.ORA file exists and is in the proper place and accessible. See the operating system specific manual for details on the required name and location.
  b.. Check to see that the service name exists in one of the TNSNAMES.ORA files and add it if necessary.
  c.. Make sure there are no syntax errors anywhere in the file. Particularly look for unmatched parentheses or stray characters. Any error in a TNSNAMES.ORA file makes it unusable. See the Oracle9i Net Services Administrator's Guide. If possible, regenerate the configuration files using the Oracle Network Manager.
which is saying that when you use sqlplus <username>/<password>@<alias> there is in fact no entry that corresponds to <alias> in your tnsnames.ora file.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/