Friday, October 12, 2007

Oracle: InstantClient, TNSPING, and PL/SQL Developer

Well, anyone that has tried to use tnsping with Oracle's InstantClient (http://www.oracle.com/technology/tech/oci/instantclient/index.html) was in for a big nasty surprise... it's not included. Not only is not included, you can't download a version that is compatible with InstantClient. I've been a fan of tnsping for some time. It has always been valuable as a quick way to test the "reachability" factor of a particular database. I now have to admit that it's not required since SQLPlus technically can perform this functionality. Well, habits die hard.

Goal: Get InstantClient working with tnsping and AllAroundAutomation's PL/SQL Developer (http://www.allroundautomations.nl/plsqldev.html).

Solution:

Disclaimer:

  1. I don't want to tell you how long it took me to figure this all out, but it was way too long.
  2. It's an unacceptable solution from a long-term maintenance, support standpoint.
Steps:
  1. Download the appropriate InstantClient "base" (in other words, English-only or International).
  2. Download the InstantClient SQL-Plus "add-on".
  3. Extract both Zip files into the same directory [for this example, "c:\oracle\instantclient"].
  4. At this point, we haven't done anything special.
  5. Define an environment variable oracle_home that points to the location of your InstantClient directory (e.g.: "c:\oracle\instantclient").
  6. Create a subdirectory called "network" (c:\oracle\instantclient\network).
  7. In "network", create a subdirectory called "admin".
  8. If desired, put your tnsnames.ora and sqlnet.ora in c:\oracle\instantclient\network\admin [technically, this isn't required but it follows Oracle's "standards"].
  9. Define an environment variable tns_admin that points to the location of your tnsnames.ora (if you are using one).
  10. In "network", create a subdirectory called "mesg".
  11. From an Oracle 10.x Fat client installation, copy tnsus.msb into c:\oracle\instantclient\network\mesg. This is the file that tnsping uses to "lookup" the message text while running.
  12. At this point, you should be able to fire up PL/SQL Dev and still have things work.
  13. Okay, now the weird part starts.
  14. Create a subdirectory called "bin" (c:\oracle\instantclient\bin), and yes, it must be called bin.
  15. From an Oracle 10.x Fat client installation the following files into c:\oracle\instantclient\bin:
    • heteroxa10.dll
    • oci.dll
    • ocijdbc10.dll
    • ociw32.dll
    • oraclient10.dll
    • oracommon10.dll
    • oracore10.dll
    • orageneric10.dll
    • orahasgen10.dll
    • oraldapclnt10.dll
    • oran10.dll
    • orancds10.dll
    • orancrypt10.dll
    • oranhost10.dll
    • oranl10.dll
    • oranldap10.dll
    • oranls10.dll
    • orannzsbb10.dll
    • oranro10.dll
    • orantcp10.dll
    • orantns10.dll
    • oraocci10.dll
    • oraociicus10.dll
    • oraocr10.dll
    • oraocrb10.dll
    • oraplp10.dll
    • orapls10.dll
    • ORASLAX10.DLL
    • orasnls10.dll
    • orasql10.dll
    • Orasqlplusic10.dll
    • oraunls10.dll
    • orauts.dll
    • orauts_imp.dll
    • oravsn10.dll
    • oraxml10.dll
    • sqora32.dll
    • sqoras32.dll
    • sqresja.dll
    • sqresus.dll
    • tnsping.exe
  16. Now, copy all the files from c:\oracle\instantclient into c:\oracle\instantclient\bin except those that already exist.
At this point, InstantClient is "valid", PL/SQL Developer works [but it's actually using c:\oracle\instantclient\bin, and tnsping should work from c:\oracle\instantclient\bin. Talk about smells bad. So if you want tnsping for InstantClient, create this nightmare-ish drink from this recipe or complain to Oracle and ask for a InstantClient-compatible version of tnsping. Jason

17 comments:

pt said...

I did something similar. I have one system with a 10.2.0.3.0 version of SQLPLUS (from an InstantClient setup) installed in "C:\Oracle_Instant_Client_10_2\". I put all the EXE and DLL files into that dir. I've also got a 10.2.0.3.0 version of TNSPING.EXE from a "full boat" install on a different system. I copied the TNSPING.exe file to the InstantClient system, then kept running it, noticing the "missing file" error, then copying that file from "full install" system. Here's the list of files I needed, pretty much in the order that TNSPING was complaining:
TNSPING.EXE
oranl10.dll
oran10.dll
orancrypt10.dll
oracore10.dll
oranls10.dll
oraunls10.dll
orauts.sym
orauts_imp.dll
orauts.dll
oranro10.dll
oraldapclnt10.dll
orageneric10.dll
orasnls10.dll
oracommon10.dll
oraclient10.dll
oravsn10.dll
orapls10.dll
ORASLAX10.DLL
oraplp10.dll
orasql10.dll
oraxml10.dll
orantcp10.dll
orahasgen10.dll
oraocr10.dll
oraocrb10.dll
oranldap10.dll
oranhost10.dll
orancds10.dll
orantns10.dll

And then the "tnsus.msb" ... now here's a weird one ...
With no "ORACLE_HOME" env var, and with TNS_ADMIN=C:\Oracle_Instant_Client_10_2\,
I was getting errors "No message file for product=NETWORK, facility=TNS". Using SysInternals "FileMon" utility, I found that TNSPING was looking for that file in the "C:\Oracle_Instant_Client_10_2\ETWORK\mesg\" directory (note the missing "N"!!!). As silly as it seems, I created "ETWORK\mesg\" and copied the msb file there, and it worked.
A lot of experimentation later, I figured out that PATH, ORACLE_HOME, and TNS_ADMIN should all point to the Oracle installation dir. ORACLE_HOME and TNS_ADMIN require the trailing backslash path delimiter character. Filemon tells me that TNSPING is looking for "oracle.key", but copying that from the other system causes all kinds of ruckus.

I want to thank you for your hints on this, and, as always, the fine folks at Oracle .... ackphfffft.

Dávid said...

Following your instructions I created my own bundle which contains tnsping.

But I'm still missing exp. I add message files and the exp binary, but I got only the following error message:

exp username/pwd@mydb
Export: Release 10.2.0.4.0 - Production on Tue May 20 01:26:36 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
EXP-00056:
EXP-00000: Export terminated unsuccessfully

Do you have any experience adding exp to instant client?

Jason said...

Unfortunately, I haven't done anything with exp in this context. However, I believe that Oracle has released a new version of the InstantClient library. You might want to check that out.

Anonymous said...

Thanks heaps for this info Jason. A real life saver for us.

http://www.oracledba.in said...

i like this

Anonymous said...

Thanks Jason - just upgraded to 11g and needed this.

Anonymous said...

Awesome. You are a lifesaver. Thanks a ton!

Anonymous said...

Nice post

sap support costs said...

I exactly followed the same steps that you have told above but still getting error of missing files. I even created the files on the same path and downloaded all the files also. Please help me out and suggest some solution.

Jason Vogel said...

What is your exact error message?

Anonymous said...

installed client 10g....done everything what u have suggested....still getting error message while logging in pl/sql developer...it says ' SQL*NET not properly installed'..please help

Jason Vogel said...

Get your base PL/SQL Developer and Instant Client configuration working first. Then, trying pulling in tnsping and the messages files.

Anonymous said...

i dont know what i have done wrong...i want to start afresh..please tell me what i need to do to connect pl/sql developer and oracle client....what i need to install to perform this task...my requirement is working on a database with pl/sql developer where i can do all types pl/sql things....

Jason Vogel said...

Does Oracle SQL-Plus work? You need to get your Instant Client working, first, period.

Anonymous said...

Good job! How did you know which DLLs to copy... Congratulations

Jason Vogel said...

I enumerated most of the DLLs through trial-and-error [fail].

tomjenger said...

Thx very much... needed this.!!