Thursday, May 14, 2009

Toad for Oracle TNS Names Path and Editor issues on Windows XP

Brief Description -- Symptoms/Quirks:
Couldn't use TNSNames to connect, and TNSNames Editor was greyed out


Today I ran into much tnsnames related frustration configuring and getting information out of Toad for Oracle on Windows XP.

The Actual Problem:

Issue appears to be due to Toad looking for tnsnames.ora in the main client directory (as in InstantClient) as opposed to the \network\admin directory (for a full client install). Resolved by setting TNS_ADMIN environment variable.


How to:
An Oracle Client install is required before installing Toad (for tnsnames connectivity). The upshot is that the small amount of documentation that there is, applies to the Oracle Instant Client and not to the Full Client, and Toad help does not appear to have been updated to reflect changes in the latest release. My problems turned out to be minor issues, but since the documentation is scant and makes a lot of assumptions, I've listed basic install/configuration info below.

These steps apply to Toad for Oracle 9.7.2 and Oracle Client 11.1 on Windows XP
  • If you do not already have an Oracle Client installed download the Instant Client
    • It will also be helpful to download some of the associated extensions to the package such as SqlPlus and ODBC
  • If you do not already have source for a licensed version of Toad, download the evaluation version
  • Install and/or configure the Oracle Client
    • If using InstantClient
      • Extract the .zip file to a local directory (there are no requirements around the top level location, use one that makes sense to you, but do not change the name of the subdirectories it exports).
      • Verify that the tnsnames.ora is in the working directory of the base of the extracted directory.
        • If it doesn't exist and this is a new install create a text file 'tnsnames.ora'
        • If it doesn't exist and you have a full client install, or an existing InstantClient install, locate the tnsnames.ora (default location is %ORACLE_HOME%\network\admin) and make a note of it.
    • For both the full client and the InstantClient you will need to set environment variables
      • Right click 'My Computer' and select 'Properties'
      • Select the 'Advanced' tab and click the 'Environment Variables' button
      • In 'System Variables' locate and select the PATH variable.
      • Click the 'Edit' button and verify the existence of, or append the client directory location to the end of the path.
        • To add, append a semi colon followed by the path to the client directory (e.g. ;c:\instantclientdir)
      • If you are using the full client, and the tnsnames.ora file is in a location other than the working directory, select 'New' to add a system variable 'TNS_ADMIN'
        • In 'Variable Name' enter TNS_ADMIN
        • In 'Variable Value' enter the directory location of the tnsnames.ora file. (e.g. c:\instantclientdir\network\admin)
      • Verify paths are set (both Instant Client and full Client)
        • In a cmd window type
          • echo %PATH%
          • This should return your normal path with the location of the client files appended.
      • Verify TNS_ADMIN variable (Full Client and clients with existing tnsnames.ora)
          • In a cmd window type
            • Echo %TNS_ADMIN%
            • This should return the location of the tnsnames.ora
  • Install Toad
      • From the Toad install media or download kick off setup
        • No changes from defaults are required during the actual install, choose components and file locations as best suits your environment and licensing.
  • Configure/Verify Toad
    • With the Oracle Client installed and configured, Toad should be able to locate the settings it needs
    • To verify select 'Session' new connection. In the 'Installed Clients' box (bottom right)
      • Verify that a client is listed
      • Click the elipses button to the right and verify that there are no errors listed (they will be red).
        • If values are incorrect, it is most likely an incorrect path in the environment variables.
          • From a cmd window type
            • env
            • Check that the path and TNS_ADMIN variable are correct, and verify there aren't stray oracle client variables with incorrect values (e.g. an incorrect ORACLE_HOME). Be very careful about changing these values if you have an existing client install.
            • Missing values not related to the ORACLE_HOME are not an issue if you have client only install (you don't need a SID etc. unless you have a local database).
    • To verify/configure the TNSNames Editor Utility:
      • On the 'Utilities' menu select TNSNames Editor.
        • If it is greyed out on the menu there is still a path issue (see above)
        • If you created a blank file it will be empty and entries can be added either by editing text directly or by generating entries from direct connections.
          • To add an entry click the 'Add Service' button (looks like a shiny blank sheet)
          • Enter connection information.
            • To enter the SID as SID instead of Service, check 'Use Oracle 8 and previous identification (SID)'
    • To add Oracle Utilities to Toad
      • On the 'View' menu select 'Toad Options'
      • In the left hand pane, Select 'Executables'
      • Enter the path for any Oracle Utilities you have installed or downloaded.
Thanks to John P. from Quest who provided some as listed links below:

3 comments:

  1. Hi Caroline,

    I'm sorry to hear you were having problems connecting Toad to your Oracle database.

    Toad actually makes several attempts to connect based on where it thinks the Oracle client dll is located. This information is published in the Toad User Guide located in the Toad installation directory under the Docs folder.

    Toad does not depend on having an Oracle Client in order to connect to the server. Toad supports Instant Client, Direct and LDAP. Information regarding how to configure and use these alternative methods is described in the Toad User Guide.

    Toad also now carries out a compatibility check to ensure that, if you use an Oracle client, it's version is approriate compared to the version of server you are connecting to. This check is also part of the Toad Advisor (Help | Toad Advisor).

    For help and assistance on using Toad, might I suggest the following websites:
    www.toadworld.com (main Toad portal with technical articles, blogs, video clips, etc)
    www.asktoad.com (Toad FAQ wiki)
    http://tech.groups.yahoo.com/group/toad/ (Toad development community).

    Kind regards,

    John Pocknell
    Toad Product Manager
    Quest Software

    ReplyDelete
  2. Thanks John! It's great to see that you're actively out looking to provide help and information!

    My post was intended to be specific to tnsnames functionality, so I should have been more specific in my original sentence regarding Toad requiring an oracle client. I've updated the post to reflect that.

    I included a step by step just to be thorough.

    ReplyDelete
  3. Thank you for this instruction set. I am trying to instantiate a RAC connection from HP's BAC application and am having a very hard time. Thanks to your efforts on this page I was able to finally validate my tnsnames syntax. I basically have no experience with Oracle and your instruction set has helped me keep my knot from fraying.

    S. McG.

    ReplyDelete