I’ve recently begun a new contract migrating a Solaris 9i database to Oracle 11gR2 on Windows, in the Azure cloud. I hate windows with a vengeance and this hasn’t made me change my opinion!
One of the planned improvements is to have everyone using a standard, central
tnsnames.ora file for alias resolution. A good plan, and the company has incorporated my own tnsnames checker utility to ensure that any edits are valid and don’t break anything.
I found that the
tnsnames.ora in my local Oracle Client install, was not working. Here’s what I had to do to fix it.
In my local
tnsnames.ora, I had something like the following:
(Server names etc have been obfuscated to protect the innocent!)
However, using the above caused
tnsping commands, or connection attempts to time out or simply fail:
tnsping barney TNS Ping Utility for 64-bit Windows: Version 184.108.40.206.0 - Production on 19-MAY-2 016 12:16:42 ... TNS-03505: Failed to resolve name
If the standard
tnsnames.ora file was copied locally, and
IFILE‘d, then it all just worked as expected.
The problem is simple, Oracle isn’t fond of
IFILEing files from networked drives. So, to get around this, I needed to map a network drive instead, and use the drive specifier in my
First map a persistent network drive to be my (new)
Y: drive. This should be reconnected at logon until further notice. Note that this mapping uses my current credentials to make the connection.
net use Y: \\servername\share_name /PERSISTENT:YES
And in my
tnsnames.ora, I now have this:
And now, it all just works!
C:\Users\ndunbar\Downloads>tnsping barney TNS Ping Utility for 64-bit Windows: Version 220.127.116.11.0 - Production on 19-MAY-2 016 12:21:23 ... Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=bedrock) (PORT=1521)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=barney))) OK (160 msec)