Solved

TNSPING works, SQLPLUS does not

Posted on 2007-11-13
5
4,915 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
  • 2
  • 2
5 Comments
 
LVL 73

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 47

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 73

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 47

Accepted Solution

by:
schwertner earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pl/SQL Query 31 74
Parametric query in oracle 6 49
Read only access to a Procedure in oracle? 4 45
Query to identify changes between rows of two tables 8 37
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

919 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now