Solved

TNSPING works, SQLPLUS does not

Posted on 2007-11-13
5
4,910 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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

14 Experts available now in Live!

Get 1:1 Help Now