Locating Oracle’s Network Configuration Files

This content has been relocated to edstevensdba.wordpress.com.

4 Comments


  1. // Reply

    Your article is not bad, however you should mention a few topics.
    Nowadays we have to deal with 32-bit and 64-bit Environment. Oracle Client can be 32 bit or 64 bit. Thus the Registry hive for a 32-bit Oracle Client at 64-bit Windows is “HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN”

    Then you mention, you get all your findings with SQL*Plus. Some time ago I also started such investigations, but I gave up. The way how tnsnames.ora file is located vary and can be different for any application or driver. For example, ODP.NET Managed Driver (Oracle Data Provider for .NET) does not use the Registry entries at all. See also this post: http://stackoverflow.com/questions/28280883/determining-locatation-of-relevant-tnsnames-ora-file/28283924#28283924

    In order to make any application working I recommend following:

    1) Define location of tnsnames.ora by Environment variable “TNS_ADMIN”
    2) Define location of tnsnames.ora by Registry key “HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN” and/or “HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN”
    3) In case your location is different to “%ORACLE_HOME%network\admin” then create a symbolic link at this location pointing to your tnsnames.ora file


    1. // Reply

      “Your article is not bad, however”

      Sounds like “damnation by faint praise” 😉

      The intent of the article was to illustrate the fundamental concepts and provide an introduction to some follow-on articles. I would hope the reader would be able to take those fundamentals, read the follow-on articles, and extrapolate to their specific environment.

      Hardly a week goes by that I don’t have to deal with 32-bit/64-bit issues on Windows clients, but those questions don’t change the fundamentals. Trying to specifically address all the possible permutations of 32-bit vs. 64-bit, plus registry vs. system environment variables, plus tns_admin, plus use of symbolic links, plus use of ifile directives would quickly mushroom into a small book in and of itself.

      I also did not address the fact that jdbc-thin clients don’t even use the tns client software and thus do not use tnsnames at all. Nor did I address the use of ldap instead of tnsnames, nor the use of hard-coded connection string specification.

      It appears from the SO article you reference that you yourself said

      “Obviously there is no fix search, it varies for different providers/drivers. Maybe it also depends on the Oracle version.”

      I’m quite certain that at some point it can vary by Oracle version, as I have noticed over the years (I started with Oracle 7.3 on Windows 3.11) that the reference to TNS_ADMIN in the registry seemed to move from HLKM\sofware\oracle to HKLM\software\oracle\KEY_OraClientWhatever, though that seems to have stabilized since 9i so shouldn’t be an issue for anyone who is staying reasonably current. Who knows, perhaps it can also be in HKLM\software\oracle\\OLEDB or in HKLM\software\oracle\ODP.NET, again with different scope of influence. Throw in the same possibilities under HKLM\SOFTWARE\Wow6432Node and the possible permutations become almost endless. But the overview I gave seems to suffice for virtually every case I’ve ever encountered.

      I used sqlplus as my demonstration target for several reasons, but the biggest reason is that, again, my intent was to provide an overview of the fundamental process, not to plumb every possibility.

      I did devote an entire section to the TNS_ADMIN variable, but again chose not to explore every possible permutation. For a multi-home environment, such as a mixed 32-bit/64-bit Windows installation, I agree with your recommendation of TNS_ADMIN to point to a central tnsnames.ora. This was pointed out in the final paragraph under “Conclusion”, just before “Further reading”. And in that I pointed out that when using TNS_ADMIN, one does not even need to put tnsnames in any ORACLE_HOME at all; that, indeed, it might be preferable to deliberately put it in a ‘neutral’ directory. And that being the case, there is no need to create any symbolic links, which would be just another unnecessary layer of complexity – one more “moving part” to have to follow-up on.



Leave a Reply

Your email address will not be published. Required fields are marked *