Tnsping ping fails even though I can successfully connect to the database

Question

When I try to establish a connection from my workstation (in fact, I see the same behavior on WinXP32 and Win764 workstations) on the Oracle server, the first thing I always try is tnsping. When I do this, this is what I get:

> C:\>tnsping MYDBNAME > > TNS Ping Utility for 32-bit Windows: > Version 10.2.0.1.0 - Production on > 25-JAN-2 011 15:03:35 > > Copyright (c) 1997, 2005, Oracle. All > rights reserved. > > Message 3511 not found; No message > file for product=NETWORK, > facility=TNSMessage 3512 not found; > No message file for product=NETWORK, > facility=TNSAttempting to contact > (DESCRIPTION = (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP) (HOST = > thisismyservername.com)(PORT = 1577))) > (CONNECT_DATA = (SID = MYDBNAME))) > Message 3509 not found; No message > file for product=NETWORK, facility=TNS 

So, as you can see, it correctly detects my tnsnames file and selects the correct server address and port for the specified database, but tnsping fails with errors 3511 and 3509.

The strange part, using sqlplus or Toad, from the same workstation, I can connect to the same database successfully.

Can anyone explain what is going on here?

FWIW:

The ORACLE_HOME system variable (and any other oracle environment variables) is not set.

Update

The environment variable ORACLE_HOME does not have to be set; it depends on the machine somehow. My special problem: Oracle 10g had / had a known defect in that tnsping.exe just didn't work. There is a patch

Answer 1

I believe this can happen if your ORACLE_HOME environment variable is set incorrectly. tnsping is still accessible from PATH , but it cannot find its message files. What does echo %ORACLE_HOME% show, and where is the Oracle client actually installed?

This will not necessarily affect access with Toad, as it should not directly access ORACLE_HOME ; you may have specified the full connection string, or it may get the service name if TNS_ADMIN set.

Edit You may also have several Oracle products that interfere with each other. If so, try starting Start-> All programs-> Oracle Installation Products-> Home Selector and select the one that is associated with the tnsping that you are running. (Actually, this is for the 9i client, I think that for 10g you may need a universal installer that changes through Installed Products-> Environment, but cannot verify).

Answer 2

In addition to setting the ORACLE_HOME variable, you must also make sure c: \ orant \ bin; the record arrives before any other path records to other oracle clients that you may have set.