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…


3 thoughts on “tnsnames.ora, keeping your connections under control

  1. Hi Jan,
    I’ve learnt something new today: That IFILE can be nested with tnsnames.ora, too! Back in 2011 when I put my article on this topic together, I had the experience that this wasn’t possible; you could only have on “IFILE layer” with tnsnames.ora (wrt 3 in init.ora).
    But I just confirmed that in my 12.1.0.2 environment.
    Did you use nested IFILEs in earlier releases as well?

    Cheers,
    Uwe

    1. Hi Uwe,
      I have actually always been able to avoid using nested IFILES by doing mointpoint-provisioning-tricks around the target file, and or playing with the combination of multiple ifile-registrations in the source and specific aliasses in the tnsnames.ora itself.
      I would have to test if nested IFILE would fail in older versions, but I don’t actually feel there should be a reason for that.
      Nb. most of my experience dates back to versions 8 / 10.
      Cheers,
      Jan
      Nb. looking forward to meeting you at DOAG!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.