Determining location of relevant tnsnames.ora file

by

According Oracle these locations are searched for tnsnames.ora, resp. sqlnet.ora and ldap.ora:

  1. Oracle Net files in present working directory (PWD/CWD)
  2. TNS_ADMIN defined sessionally or by user-defined script
  3. TNS_ADMIN defined as a global environment variable
  4. TNS_ADMIN defined in the registry
  5. Oracle Net files in %ORACLE_HOME/network|net80\admin (Oracle default location)

However, I am not sure whether each application/driver follows this list. I got this list from Oracle Document 111942.1 referring to Oracle 9i, so it might be outdated.

In Database Net Services Administrator’s Guide the order is

  1. TNS_ADMIN defined by environment variable
  2. TNS_ADMIN defined in the registry (if TNS_ADMIN environment variable is not present)
  3. %ORACLE_HOME%/network/admin directory (if TNS_ADMIN environment variable is not present)

I would recommend to define an environment variable for TNS_ADMIN and use only one tnsnames.ora file. In order to be on the safe side, check also your registry values.

If your files are not located in %ORACLE_HOME%\network\admin, I recommend to create a symbolic link for it – just to be on the very safe side, e.g. mklink /d %ORACLE_HOME%\network\admin c:\Oracle\common\settings\admin

Another note, you don’t have to “play” with your tnsnames.ora file. With Process Monitor from Microsoft Sysinternals you can monitor each file access, i.e. the filter would be Path contains tnsnames

Update

When I run a test on my machine I get following order:

  1. Environment variable TNS_ADMIN
  2. Registry Key HKEY_CURRENT_USER\SOFTWARE\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN

  3. Registry Key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN, resp. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN

    -> Only if TNS_ADMIN Environment variable is not set.

  4. %ORACLE_HOME%\network\admin
  5. Current directory (which can be different to directory where your application is located)
  6. Folder where your application is located

Update 2

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

For example, the Oracle HTTP Server reads TNS_ADMIN setting from opmn.xml config file.

Another example, for ODP.NET Managed Driver (Oracle.ManagedDataAccess) beta version, I found this order at Oracle Managed and TNS Names :

  1. data source alias in the ‘dataSources’ section under <oracle.manageddataaccess.client> section in the .NET config file (i.e. machine.config, web.config, user.config).
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.
  4. data source alias in the tnsnames.ora file present at %TNS_ADMIN%
    (where %TNS_ADMIN% is an environment variable setting).
  5. data source alias in the tnsnames.ora file present at %ORACLE_HOME%\network\admin
    (where %ORACLE_HOME% is an environment variable setting).

In official documentation (12c Release 4 (12.1.0.2.4)) it says:

  1. data source alias in the dataSources section under <oracle.manageddataaccess.client> section in the .NET config file (i.e. machine.config, web.config, user.config).
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file. Locations can consist of either absolute or relative directory paths.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.

However, based on some tests I made with ODP.NET Managed Driver (4.121.2.0) it takes %ORACLE_HOME%\network\admin and TNS_ADMIN Environment variable into account. Locks like the documentation is not 100% correct.

More Related Contents:

  1. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  2. Oracle: Import CSV file
  3. When or Why to use a “SET DEFINE OFF” in Oracle Database
  4. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  5. Sql*plus always returns exit code 0?
  6. ORA-12170: TNS:Connect timeout occurred
  7. SQL*Plus how to accept text variable from prompt?
  8. Convert epoch to date in sqlplus / Oracle
  9. oracle SQL plus how to end command in SQL file?
  10. How can I issue a single command from the command line through sql plus?
  11. How to View Oracle Stored Procedure using SQLPlus?
  12. How do I escape a reserved word in Oracle?
  13. How to put more than 1000 values into an Oracle IN clause [duplicate]
  14. ORA-30926: unable to get a stable set of rows in the source tables
  15. How to best split csv strings in oracle 9i
  16. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  17. Using the “IN” clause with a comma delimited string from the output of a replace() function in Oracle SQL
  18. Oracle JDBC and Oracle CHAR data type
  19. Does Oracle have an equivalent of SQL Server’s table variables?
  20. Reason why oracle is case sensitive?
  21. Faster alternative in Oracle to SELECT COUNT(*) FROM sometable
  22. SQL Fiddle Output Error
  23. oracle PL/SQL how to calculate range ip for IPv6 cidr
  24. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  25. How to list active / open connections in Oracle?
  26. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  27. How can I solve ORA-00911: invalid character error?
  28. How to call an Oracle function with a Ref Cursor as Out-parameter from C#?
  29. How to Register Pluggable Database(PDB) with new created LISTENER
  30. ORA-12704: character set mismatch

Leave a Comment