Tag Archives: sqldeveloper

tnsnames.ora, keeping your connections under control


There are a lot of ancient things that prove to be still valuable today.
Recently I came across something, which I thought I could or should share here.
Your tnsnames.ora file, centrally managed in a distributed environment.

This solution applies whether you are working with the Oracle Instant Client or with the full blown setup of the client software.

Imagine you have an environment with a reasonably big bunch of PC’s. Wether you are running Linux or Windows, or if you are running some Terminal Server Forrest, where deploying, packaging and launching applications cost an arm and a leg. To keep consistency across the environment, shortcuts are not an option.
I have seen the most exotic solutions, distributing a tnsnames.ora file. Pushing versions of tnsnames.ora out to workstations, have login-procedures check and copy files… Basically every trick in the book will, at one time, get you in the situation where you have a client, connecting to a database, containing something other than the end user expects… With the appropriate consequences…
It is my firm believe that the best way still is not to distribute the file.

The coolest thing (therefore) would be to have something (obviously extremely simple), containing all your database connection aliases.
Of course you can than add each and any every facility to maintain and update this file. Be it version control or automatic deployment. Basically this one copy of your file is the source of all truth, which of course adds some importance to the file itself.

(Re)introducing the tnsnames-parameter ifile=

With this parameter you can define an instance or form of tnsnames.ora file, and store it anywhere you can reach from the endpoint, where your Oracle client is installed.

the most simple version of using ifile = here would be:
ifile = <mount_point>/path/central-file.ora

you can multiplex your tnsnames.ora:
ifile = <mount_point_1>/path/central1.oraifile = <mount_point_2>/path/central2.ora

You can nest tnsnames.ora as needed:
ifile = <mount_point>/path/general-file.ora
In general-file.ora:
ifile = <group-specific_mount_point>/path/specific.ora

These options give you a very good set of opportunities to organize your redirection-setup the way you need it.
To me, having this centralized tnsnames-setup, brings advantages in connection troubleshooting as there are no occurances of file-discrepancies.
ifile = works and you get connected… or it doesn’t, no chance of missing that last update.

Hope this helps…