Andrew Fraser DBA

Replace all tnsnames.ora files with a single master file using TNS_ADMIN

Maintaining large numbers of client tnsnames.ora files is a pain.  Oracle Internet Directory is the recommended solution to this, but it is not an easy exercise to set up or migrate to.

An easier alternative is to set TNS_ADMIN in all client PCs to use a single common tnsnames.ora file

set TNS_ADMIN=\\network\share\path

All the client PCs need read access to that file, but don’t need write access.
One drawback of doing that is that if the network share is unavailable for any reason then all client connections will fail, so a highly resilient network share would be needed. A safer alternative is to set TNS_ADMIN to the same local disk folder on each PC

set TNS_ADMIN=C:\SecureITFolder

and rely on automated PC management tools like Microsoft System Centre to deploy the master tnsnames file out to each client PC at regular intervals.

The precedence of TNS_ADMIN in Windows is supposedly (I’ve not yet tested this):

1. In current working directory
2. TNS_ADMIN defined by the session (set tns_admin= in batch/command prompt)
3. TNS_ADMIN defined as a global environment variable (control panel > system > advanced system settings > advanced > environment variables)
4. TNS_ADMIN defined in registry (run “regedit” and look under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\…)
5. Default location (network\admin directory for that particular Oracle Home)