Re: Trying to get SQL*Plus to Talk to a Remote Server

martin_at_okstate.edu (Martin McCormick) wrote:

>
> I am struggling to get communication established between a
>Redhat Linux system running SQL*Plus: Release 10.1.0.2.0
>and a Pinnacle server presently running an Oracle8I data base.
>
> Being new to Oracle, I don't yet have a good feel for where
>to look when things don't work right.
>I am needing to query this server in order to get information to use
>in UNIX shell scripts and C programs so everything else is dead in the
>water until I get past this hurdle.
>
> At this time, it has never worked so I don't know what a
>working setup actually does except it is probably similar to php in
>that you send your sql commands and, assuming they are proper, the
>server sends you back your information, etc.
>
> The administrator of the Pinnacle server sent me examples of
>the sqlnet.ora and tnsnames.ora files she uses on a Windows system
>that accesses the same server. I did remember to fix the new line
>conversion so that the extra carriage return goes away, but it doesn't
>look like there is much Windows-centric information in the files.
>
> The sqlplus client does find the sqlnet.ora and tnsnames.ora
>files, but obviously does not like their contents or there is
>something else missing.
>
> My question boils down to: Is there some way other than a
>blanket error message stating that it's broken to trace or debug what
>and where the process is going wrong?
>
> It took strace, a UNIX command, to inform me that I had the
>tnsnames.ora and sqlnet.ora files in the wrong directory.
>
> First, the error message that is all I get when running
>sqlplus. It is sort of my sql banner screen these days:
>
>SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jun 14 08:29:06 2004
>
>Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
>ERROR:
>ORA-12154: TNS:could not resolve the connect identifier specified
>
>
>SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
>
> Here is a modified version of the tnsnames.ora file:
>
># TNSNAMES.ORA
># Generated by Oracle configuration tools.
>
>fully.qualified.server.domain =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = ip.hostname.of.server)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = shortname)
> )
> )
>
> Now for sqlnet.ora:
>
># SQLNET.ORA
># Generated by Oracle configuration tools.
>
>NAMES.DEFAULT_DOMAIN = our.site.domain.edu
>
># SQLNET.AUTHENTICATION_SERVICES= (NTS)
>
>NAMES.DIRECTORY_PATH= (HOSTNAME, TNSNAMES)
>
> Thank you for any constructive suggestions. This is getting
>quite frustrating, not only for me, but for others who are waiting for
>me to start actually doing something with the information in question.
>Thank you for a ny constructive ideas.

The detailed error and possible reasons:

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

Action: Make the following checks and correct the error:

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. Check to see that the service name exists in one of the TNSNAMES.ORA files and add it if necessary. 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.

Be sure your ORACLE_HOME is properly set. Received on Mon Jun 14 2004 - 18:15:54 CEST