Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TNSPING works, SQLPLUS does not

Posted on 2007-11-13
5
Medium Priority
?
4,978 Views
Last Modified: 2013-12-19
Trying to get a client to have access to a new remote database we have set up. Same TNSNAME.ORA and SQLNET.ORA has been provided to over 100 clients and all have been abel to connect , except 1.

They can execute a TNSPING but get an ORA-12154 when using SQLPLUS.
I had him installed 10G Client Runtime ( he mistakenly started to install Instant Client). He previously had Oracle 8 Client.

I had him copy the same TNSNAMES.ORA file to all of his NETWORK/ADMIN directories - still did not work.
I had remove all of the other copeis and it still did not work.

I can connect using his UID/PWD from my machine so I know the database and listener are fine.

What shoudlI have him look for on his machine?

0
Comment
Question by:sbessette
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20275377
Make sure the sqlplus and tnsping are using the same oracle home

It's possible you're tnsping-ing from oralce8 but sqlplus is from oracle10.  or vice versa.

Double check the sqlnet.ora and tnsnames.ora in both homes to see what might be different.

Also check the search order in sqlnet.ora.  Are you using onames in 8?  That doesn't work in 10 anymore.  If LDAP, do you have ldap.ora copied in both?


0
 
LVL 48

Expert Comment

by:schwertner
ID: 20279328
For STATIC registration of the Oracle service to the Listener:

Go to the remote 10g installation where the Oracle server resides.

Backup LISTENER.ORA file!!!!!!!

In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20279362
the previous suggestion, assumes the db uses static registration.

However, I don't see how it applies here, since the author can connect to the db
so it's not a problem on the server or with the listener.  It must be on the client side.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 20280218
He can connect but from which machine?
Connection from server machine doesn't
help to connect fropm client machine.

Normally this message means wrong syntax
of an entry in tnsnames.ora, even more - the existance of more
then one tnsnames.ora on the client and usage of nonappropriate
tnsnames.ora (to avoid this - use TNS_ADMIN environment variable or analyze
how many tnsnames.ora have you on the client).

If it is only one turn off the default firewall, the antiviruses.

See these notes:

- If you are using local naming (TNSNAMES.ORA file):

- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

- If you are using directory naming:

- Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

- Verify that the LDAP directory server is up and that it is accessible.

- Verify that the net service name or database name used as the connect identifier is configured in the directory.

- Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

- If you are using easy connect naming:

- Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

- Make sure the host, port and service name specified are correct.

- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.
0
 

Author Comment

by:sbessette
ID: 20280413
I made sure the ORACLE_HOME and TNS_ADMIN were set. I created a new dir for TNSA_ADMIN and copied the .ora files into that. Still did not work.

Added EZCONNECT to sqlnet.ora
removed all but one entry in TNNAMES.ORA

Sqlplus worked
Then I removed EZCONNECT from sqlnet.ora. Still worked so it must have been a missing parentheses in TNSNAMES.ORA

Thanks for the clear directions
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question