Imagine the scene. You arrive in the office on Christmas Eve. Everyone’s in a good mood. The tree has been decorated fabulously this year. Mince pies and mulled wine are being served in the canteen and all your colleagues are in a festive mood. (The boss even comes in to work wearing a santa hat).
Suddenly the atmosphere is shattered, as you try to log on to your database to carry out those last minute Christmas checks and see the error TNS-0535: Failed to resolve name. It’s like opening the fridge door on Christmas morning and finding that the dog’s eaten all the turkey!
How can you fix this problem? You know that it’s caused by not being able to find an entry for the database in your tnsnames.ora file. It must have been that last-minute install of the latest Oracle version, that re-set your default Oracle home. You’re now pointing to a different tnsnames.ora file. (A bit like Santa looking in the wrong address book when trying to deliver your presents).
Rather than hunting down the file on your PC - (though you know it’s under %ORACLE_HOME%/network/admin) - you think to yourself - hey it’s Christmas! Let’s enter into the Christmas spirit and all share the same file. We can all have the same entries and life will be much simpler.
You decide that you’re not ready to implement Oracle Internet Directory (OID) in the company yet. (Who needs the icing-topped mince pies, when the plain ones are fine). You also rule out setting up an Oracle Names Server, as this is being phased out. (No-one wants to eat last year’s mince pies).
Following this approach, you create a master copy of the TNSNAMES.ora file in a folder called TNS_SHARED on a mapped network drive with the letter “S” (i.e. “S” for Santa) and then decide to create a TNS_ADMIN system variable which points to that directory on the network. (e.g. If on Windows XP, right-click on the “My Computer” icon - Properties - Advanced - Environment Variables - then under the “System Variables” section - New - for the “Variable Name” you enter: TNS_ADMIN and for the “Variable Value” you enter S:\TNS_ADMIN - OK,OK,OK).
You make sure that the shared file has read-only permissions, otherwise people can over-write it - either manually, or using tools such as Net Configuration Assistant, Net Manager or the TNSNAMES Editor in TOAD. For additional security, you also check that anyone who isn’t authorised to use the databases has no access to the file. (Some people aren’t allowed mince pies at Christmas).
You setup the same configuration for your colleagues and then peace on earth - or at least in the office - returns again.
Later on in the day, some people start to complain though. A few people have databases that they use for testing or development work, which they don’t want everyone to have access to. This means that you can’t put them in the central tnsnames.ora file. (As usual at Christmas, some people are happy with plain mince pies, some want custard and others want cream).
To resolve this issue, you could tell them to use a local tnsnames.ora file on their PC, in the directory C:\LOCAL_TNS. i.e. TNS_ADMIN is still set to use the one on the Santa drive (the “S” drive) - but at the end of the central file, you just add the line IFILE=C:\LOCAL_TNS\TNSNAMES.ORA. This means that people can still use the central file, but are also able to put whatever additional entries they need in their local file, without affecting other users. (Effectively you’re saying that they have access to the shared mince pies, but they can also have their own secret stash as well).
To make life even easier for your users, you can also check the folowing:
a) Check that you don’t have entries in your tnsnames.ora file that point to old versions of databases. For example, if you’ve just upgraded from 8i to 11g, then check that the connection strings point to the 11g databases and not the old 8i ones. (Everyone hates stale mince pies).
b) Remove any references to databases that no longer exist. (Check that someone hasn’t eaten the pies).
c) If you have too many entries in the file then it can take longer to resolve the names. (You try eating a large mince pie all in one go). If you have a large number of entries, then you should probably start to think about implementing OID.
d) Different people call the same databases by different names. A developer might call a database by its name/SID, whereas a business user could just call it the “live” database. To make everyone’s life easier and to make sure that the file doesn’t have loads of duplicate entries, you can just add any aliases to one connection string.
In the example that follows, you can see that some people connect to the reindeer database on the LAPLAND server by using the “ORCL10″ string. Business users know it as “LIVE” and other people use “FATHERCHRISTMAS” to get to the reindeer database in LAPLAND.
(ADDRESS = (PROTOCOL = TCP)(HOST = LAPLAND)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = reindeer)
A mince pie is also known as a Wayfarers pie or a shred pie.
e) You may find it useful to configure connect time failover, so that if the first address isn’t available, then the client will automatically try an alternative address, as shown below.
In this example, if we can’t find brandy in the cupboard, then we’ll automatically check the fridge.
Now that you’ve sorted out the office, it’s time to sort out those other important details - like trying to get all the presents that you’ve forgotten to buy people. There’s always the late night petrol station if you run out of time. Looks like a lot of your relatives will be getting things for the car again this year!
I’ll end this blog with a small word of warning. If you look on the internet, lots of websites will tell you that eating mince pies on 25th of December is still illegal in England. This has been the case since Oliver Cromwell banned Chrismas pudding, mince pies and anything to do with gluttony. The law has never been rescinded and was voted the fourth “most ridiculous British law” in a 2007 poll by UKTV Gold.
The moral of this tale is that you should not try to connect to any databases on Christmas day. Stay at home and eat some mince pies instead.