TNS_ADMIN, IFILE - replace oracle names server for free!

Oracle names server until several years ago, represented very nice and handy Oracle service which allows DBA to centralize and share any of Oracle service names infrastructure around the network.

Even thought there was some other benefits, onames server was not free and after all represented another service in infrastructure which DBA has to maintain. But benefit was more then obvious-centralized management of all oracle naming services so that change in just one place reflect changes to all clients immediately. For instance, if you move database from one server to another, DBA have to changed just one entry with namesctl and all users would access moved database like nothing happened. Pretty cool especially if your database is accessed by several hundreds of clients!
Because I do not want so to evoke past times, I want to share my experience how to achieve similar functionality but with no extra servers, payment or installation.

The Solution

Mine solution is based on two important resources that Oracle supports:

1. existence of TNS_ADMIN environment variable (Linux or Windows), which represent pointer to directory where network configuration files are located
   
2. existence of ifile entry in any oracle configuration file

TNS_ADMIN

To implement common TNS_ADMIN, you must create directory on some server that any client can access from the network. In that directory set read rights (to ensure unexpected modifications). If you look in previous picture, DBA would use the same server where Oracle names server was planned to be.
Let us suppose that this server has "pcdamir" name and created shared dir name is "tns_share". In that directory (\\pcdamir\tns_share) place tnsnames.ora and sqlnet.ora files that should be readable and valid to all the clients in network. Here is an example of one correct listing, looking from clients side:

E:\>dir \\pcdamir\tns_share
 Volume in drive \\pcdamir\tns_share has no label.
 Volume Serial Number is 084F-8D27

 Directory of \\pcdamir\tns_share

02.06.2010.  21:02    <dir>          .
02.06.2010.  21:02    <dir>          ..
17.05.2010.  21:35               231 sqlnet.ora
05.05.2010.  20:24               499 tnsnames.ora
               2 File(s)            730 bytes
               2 Dir(s)  11.702.665.216 bytes free

E:\>

Next step is to define on all client computers (just once!) TNS_ADMIN environment variable as

TNS_ADMIN=\\pcdamir\tns_share

In Windows it should be best to do that through system variables (so any user on that PC can use this settings) or in Linux as part of ".bash_profile" file.
With TNS_ADMIN you ensure that all clients will look in "\\pcdamir\tns_share" directory when looking for any Oracle network configuration entry.

Check from client side existence of TNS_ADMIN variable. Here is an example for Windows clients:

E:\>echo %TNS_ADMIN%
\\pcdamir\tns_share
E:\>

IFILE

If your all clients can use the same network settings, then you can forget about ifile option and you can start testing your "onames replacement". However, from mine experience, it is almost impossible to satisfy all the clients in network with same tnsnames.ora or sqlnet.ora configuration files. These unsatisfied clients are usually advanced users (developers, DBAs, IT managers etc.) which know what configuration files are and in many cases represent a fewer number of clients. Here is when ifile comes to play.
On "pcdamir" (where common share is defined) edit tnsnames.ora and sqlnet.ora and put in the end following entries:
tnsnames.ora

IFILE=c:\tns_private\tnsnames_private.ora

sqlnet.ora

IFILE=c:\tns_private\sqlnet_private.ora

Placed ifile represent soem kind of pointer to other file where some additional definition may be found. That definition may be:

• new entry (for instance new tns_name which is needed only for that pc)
• replacement for existing entry (tns_name with same value but different definition if user on this PC wants to have different value against all others)

As you see usage is pretty straightforward and allow any combination that you might need. As we said previously, that these entries are really needed for advanced users, so DBA should only sent to them some notes like:

1. create directory c:\tns_private
2. In that directory place (even empty ones!) tnsnames_private.ora and sqlnet_private.ora files
3. In these files place entries that would satisfy your need for different configuration against other (common)

The test

Let us show on tnsnames.ora entries how it works. Let us suppose that in common tnsnames.ora we have content:

# tnsnames.ora Network Configuration File: c:\oracle\product\11.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XE=
  (DESCRIPTION=
     (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)
     )
    (CONNECT_DATA= (SID = XE)
    )
  )

IFILE=c:\tns_private\tnsnames_private.ora

So if you try to tnsping xe, you'll get result:

E:\>tnsping xe

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-LIP-2010 21:44:45

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
\\pcdamir\tns_share\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)) (CONNECT_DATA= (SID = XE)))
OK (10 msec)

E:\>

As you see in line 8, there should be "\\pcdamir\tns_share" reference which shows that TNS_ADMIN has correct value that shows valid common network directory.
Now let us create c:\tns_private directory, tnsnames_private.ora file in it.

C:\tns_private>dir
 Volume in drive C has no label.
 Volume Serial Number is 084F-8D27

 Directory of C:\tns_private

02.06.2010.  21:53    <dir>          .
02.06.2010.  21:53    <dir>          ..
02.06.2010.  21:54               290 tnsnames_private.ora
               1 File(s)            290 bytes
               2 Dir(s)  11.702.571.008 bytes free

C:\tns_private>

where content of tnsnames_private.ora is:

C:\tns_private>more tnsnames_private.ora
# tnsnames.ora Network Configuration File: c:\oracle\product\11.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XE.MY_DOMAIN.HR=
  (DESCRIPTION=
     (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)
     )
    (CONNECT_DATA= (SID = XE)
    )
  )

C:\tns_private>

As you see I have added the same instance with different tns name (I have to because of my other NAMES.DEFAULT_DOMAIN settings). And this entry is also valid:

C:\tns_private>tnsping XE.MY_DOMAIN.HR

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-LIP-2010 22:01:13

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
\\pcdamir\tns_share\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL = TCP)(HOST = pcdamir)(PORT = 1528)) (CONNECT_DATA= (SID = XE)))
OK (20 msec)

C:\tns_private>

The best thing is when you need to change some setting from common tnsnames.ora, then you just add in your tnsnames_private.ora file. Because this file is executed after all definition in tnsnames.ora fiel (this is why I said to place at the end), your new entry should be that one that win. Here is an example for one tnsnames_private.ora file:

# tnsnames.ora Network Configuration File: c:\oracle\product\11.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XE=
  (DESCRIPTION=
     (ADDRESS= (PROTOCOL = TCP)(HOST = serverx)(PORT = 1521)
     )
    (CONNECT_DATA= (SID = XE)
    )
  )

and the result is:

C:\tns_private>tnsping XE

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-LIP-2010 22:01:13

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
\\pcdamir\tns_share\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL = TCP)(HOST = serverx)(PORT = 1521)) (CONNECT_DATA= (SID = XE)))
OK (0 msec)

C:\tns_private>

As you see now your client see totally different instance then all other clients in network and the best of all you do not interfere with their settings at all!

The End

Regardless I have shown examples only for tnsnames.ora entries, the same situation is with sqlnet.ora file. This si also very handsome if on some client pc db is installed and you need special settings in one moment.

Another thing. If you want ensure that your entries in common tnsnames.ora or sqlnet.ora configuration files has absolute priority over any client entry, place ifile definition part in them at the beginning (not at the end)!

Cheers!