Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5028
  • Last Modified:

TNSPING works, SQLPLUS does not

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
sbessette
Asked:
sbessette
  • 2
  • 2
1 Solution
 
sdstuberCommented:
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
 
schwertnerCommented:
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
 
sdstuberCommented:
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
 
schwertnerCommented:
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
 
sbessetteAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now