No doubt this is incredibly old hat and I’ve just missed it somewhere along the line, but with a nod and a wink from Andrew Fraser at work and using my more stable server connection, here’s a small thing I didn’t know.
On Unix and Linux, you can have a personal .tnsnames.ora (note the leading dot) file in your home directory that over-rides the tnsnames.ora files in the usual locations.
[oracle@ISP4400 ~]$ cd ~
[oracle@ISP4400 ~]$ cat .tnsnames.ora
DOUG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST92)
)
)
TEST1020 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1020)
)
)
[oracle@ISP4400 ~]$ echo $TNS_ADMIN
[oracle@ISP4400 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST1020 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1020)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST92)
)
)
So I have DOUG as an alias to the 9.2 database and TEST1020 in my personal .tnsnames.ora , plus TEST92 (which points to the same instance as DOUG) and TEST1020 in the tnsnames.ora in $ORACLE_HOME/network/admin
I can connect to all three aliases :-
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@DOUG
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:21 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@TEST92
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:26 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@TEST1020
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:32 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
But TEST1020 is in both tnsnames files, so what happens if there’s an error in the definition in .tnsnames.ora? I’ll point it to a non-existent service.
[oracle@ISP4400 ~]$ cat .tnsnames.ora
DOUG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST92)
)
)
TEST1020 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DUMMY)
)
)
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@TEST1020
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:13:31 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
My point is that, if you don’t know that the user has their own .tnsnames.ora file in their home directory, you would go and look at the copy in TNS_ADMIN in $ORACLE_HOME/network/admin and the TNS entry would look fine. That could be confusing.
Yep that one has caught me out before. I spent a whole morning trying to work out why a user couldn’t connect to a database until I trussed the sqplus session and found it opening a $HOME/.tnsnames.ora
Annoying
Andy
hm… is there similar .sqlnet.ora? 🙂
“is there a similar .sqlnet.ora?”
According to Note:1006387.6: “SQL*NET V2 DIRECTORY SEARCH CRITERIA ON UNIX,” yes.
For sqlnet.ora, the search path is $HOME/.sqlnet.ora, $TNS_ADMIN, $ORACLE_HOME/network/admin
Very cool piece of info Doug. Thanks!
Hello,
keep in mind on unix there’s an additional directory (i think i remember /etc/tnsnames.ora on tru64, the documentation states /var/opt/oracle for sun) where this file could reside. See the full search order for tnsnames.ora at http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14212/config_concepts.htm#sthref245 .
Best regards,
Martin
Thanks, Martin.
That’s what I meant by ‘the tnsnames.ora files in the usual locations.’, as I was only really talking about the .tnsnames.ora, but a useful reminded nonetheless
I suppose that this theoretically could represent a security “issue” — if one wanted to be super careful you’d place a .tnsnames.ora in everyone’s home directory that points to the global one, and remove user write permissions (or something).
“I suppose that this theoretically could represent a security “issue” “
I’m surprised no-one else mentioned that. Of course it isn’t really a security issue because people can connect to whatever they want if they can be bothered constructing a connect string, but it does act as part of an overall strategy.
tnsnames.ora has some funny things that can happen if you’re using the IFILE parameter in it too. Virtually every Oracle version I’ve tested this with behaves differently (except for 10gr2 that does it all 100%). Try i.e. the following:
Create an entry (call it DB1) in tnsnames.ora, and add an ifile= to point to another text file that would contain DB2. Fire up sqlplus and you’ll see that you can connect to both entries just fine. Remaining in the sqlplus session, edit one of the entries (i.e. rename DB1 to DB3) and you won’t be able to connect to it anymore.
In other words, including an IFILE parameter in tnsnames.ora disables the on-the-fly re-reading of the tns aliases.
Now to get the issue even more confusing, remove the IFILE from tnsnames.ora and restart sqlplus. Now, remaining in the sqlplus session, edit tnsnames.ora and re-add the IFILE. Surprisingly (on some platforms – behaviour is really totally inconsistent among versions and platforms, I’ve elaborated this somewhat on my blog http://ora.kicks-ass.net/blog), now everything works again. You can add, remove, modify any entry you like and sqlplus will happily re-read the file on the fly.
tnsnames.ora can be the source for quite some confusion 🙂
Stefan
Stefan,
They’re really useful comments and very closely related to what we’re trying to achieve at work. Many thanks,
Doug
How do you create TNS alias names in oracle 10g
That might be a better question for one of the many forums, after perhaps having a read of some of the documentation or a quick search on Google?
I can not get my .tnsnames.ora file to work. I am on HP-UX version 11:31. Oracle 10.2.0.4. It only reads from the tnsnames.ora file that in the standard location : $ORACLE_HOME/network/admin.
Any thoughts?
Sorry, really no idea.
I tried this on Windows and it doesn’t work. Oracle client will find exactly one tnsnames.ora file and use only those entries. You can put a TNS_ADMIN registry value in the Oracle_Home key and point to a different tnsnames.ora file, and if it doesn’t exist it will then roll to the normal place, but it won’t scan both for entries.