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

[Martin McCormick]

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.
--

Martin McCormick WB5AGZ Stillwater, OK
Information Technology Division Network Operations Group

[Turkbear]

martin@okstate.edu (Martin McCormick) wrote:
I am struggling to get communication established between aRedhat Linux system running SQL*Plus: Release 10.1.0.2.0and a Pinnacle server presently running an Oracle8I data base. Being new to Oracle, I don't yet have a good feel for whereto look when things don't work right.I am needing to query this server in order to get information to usein UNIX shell scripts and C programs so everything else is dead in thewater until I get past this hurdle. At this time, it has never worked so I don't know what aworking setup actually does except it is probably similar to php inthat you send your sql commands and, assuming they are proper, theserver sends you back your information, etc. The administrator of the Pinnacle server sent me examples ofthe sqlnet.ora and tnsnames.ora files she uses on a Windows systemthat accesses the same server. I did remember to fix the new lineconversion so that the extra carriage return goes away, but it doesn'tlook like there is much Windows-centric information in the files. The sqlplus client does find the sqlnet.ora and tnsnames.orafiles, but obviously does not like their contents or there issomething else missing. My question boils down to: Is there some way other than ablanket error message stating that it's broken to trace or debug whatand where the process is going wrong? It took strace, a UNIX command, to inform me that I had thetnsnames.ora and sqlnet.ora files in the wrong directory. First, the error message that is all I get when runningsqlplus. 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 2004Copyright (c) 1982, 2004, Oracle. All rights reserved.ERROR:ORA-12154: TNS:could not resolve the connect identifier specifiedSP2-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 gettingquite frustrating, not only for me, but for others who are waiting forme to start actually doing something with the information in question.Thank you for a ny constructive ideas.

The detailed error and possible reasons:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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:

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.