{"id":782,"date":"2006-05-15T12:00:00","date_gmt":"2006-05-15T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=782"},"modified":"2006-05-15T12:00:00","modified_gmt":"2006-05-15T12:00:00","slug":"tnsnames-ora","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/05\/15\/tnsnames-ora\/","title":{"rendered":".tnsnames.ora"},"content":{"rendered":"<p>No doubt this is incredibly old hat and I&#8217;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&#8217;s a small thing I didn&#8217;t know.<\/p>\n<p>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.<\/p>\n<pre>[oracle@ISP4400 ~]$ cd ~<br\/>[oracle@ISP4400 ~]$ cat .tnsnames.ora<br\/>DOUG =<br\/>(DESCRIPTION =<br\/> (ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))<br\/> (CONNECT_DATA =<br\/>   (SERVER = DEDICATED)<br\/>   (SERVICE_NAME = TEST92)<br\/> )<br\/>)<br\/>TEST1020 =<br\/>(DESCRIPTION =<br\/> (ADDRESS_LIST =<br\/>   (ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))<br\/> )<br\/> (CONNECT_DATA =<br\/>   (SERVICE_NAME = TEST1020)<br\/> )<br\/>)<br\/><p><\/p><br\/>[oracle@ISP4400 ~]$ echo $TNS_ADMIN<br\/><p><\/p><br\/>[oracle@ISP4400 ~]$ cat $ORACLE_HOME\/network\/admin\/tnsnames.ora<br\/># tnsnames.ora Network Configuration File: \/home\/oracle\/product\/10.2.0\/db_1\/network\/admin\/tnsnames.ora<br\/># Generated by Oracle configuration tools.<br\/><p><\/p><br\/>TEST1020 =<br\/>(DESCRIPTION =<br\/> (ADDRESS_LIST =<br\/>   (ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))<br\/> )<br\/> (CONNECT_DATA =<br\/>   (SERVICE_NAME = TEST1020)<br\/> )<br\/>)<br\/><p><\/p><br\/>TEST92 =<br\/>(DESCRIPTION =<br\/> (ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))<br\/> (CONNECT_DATA =<br\/>   (SERVER = DEDICATED)<br\/>   (SERVICE_NAME = TEST92)<br\/> )<br\/>)<\/pre>\n<p>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<\/p>\n<p>I can connect to all three aliases :-<\/p>\n<pre>[oracle@ISP4400 ~]$ sqlplus testuser\/testuser@DOUG<br\/><p><\/p><br\/>SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:21 2006<br\/><p><\/p><br\/>Copyright (c) 1982, 2005, Oracle.  All rights reserved.<br\/><p><\/p><br\/><br\/>Connected to:<br\/>Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production<br\/>With the Partitioning, OLAP and Oracle Data Mining options<br\/>JServer Release 9.2.0.4.0 - Production<br\/><p><\/p><br\/>SQL&gt; exit<br\/>Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production<br\/>With the Partitioning, OLAP and Oracle Data Mining options<br\/>JServer Release 9.2.0.4.0 - Production<br\/>[oracle@ISP4400 ~]$ sqlplus testuser\/testuser@TEST92<br\/><p><\/p><br\/>SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:26 2006<br\/><p><\/p><br\/>Copyright (c) 1982, 2005, Oracle.  All rights reserved.<br\/><p><\/p><br\/><br\/>Connected to:<br\/>Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production<br\/>With the Partitioning, OLAP and Oracle Data Mining options<br\/>JServer Release 9.2.0.4.0 - Production<br\/><p><\/p><br\/>SQL&gt; exit<br\/>Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production<br\/>With the Partitioning, OLAP and Oracle Data Mining options<br\/>JServer Release 9.2.0.4.0 - Production<br\/>[oracle@ISP4400 ~]$ sqlplus testuser\/testuser@TEST1020<br\/><p><\/p><br\/>SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:32 2006<br\/><p><\/p><br\/>Copyright (c) 1982, 2005, Oracle.  All rights reserved.<br\/><p><\/p><br\/><br\/>Connected to:<br\/>Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production<br\/>With the Partitioning, OLAP and Data Mining options<br\/><p><\/p><br\/>SQL&gt; exit<br\/>Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production<br\/>With the Partitioning, OLAP and Data Mining options<\/pre>\n<p>But TEST1020 is in both tnsnames files, so what happens if there&#8217;s an error in the definition in .tnsnames.ora? I&#8217;ll point it to a non-existent service.<\/p>\n<pre>[oracle@ISP4400 ~]$ cat .tnsnames.ora<br\/>DOUG =<br\/>(DESCRIPTION =<br\/>  (ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))<br\/>  (CONNECT_DATA =<br\/>    (SERVER = DEDICATED)<br\/>    (SERVICE_NAME = TEST92)<br\/>  )<br\/>)<br\/><p><\/p><br\/>TEST1020 =<br\/>(DESCRIPTION =<br\/>  (ADDRESS_LIST =<br\/>    (ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))<br\/>  )<br\/>  (CONNECT_DATA =<br\/>    (SERVICE_NAME = DUMMY)<br\/>  )<br\/>)<br\/><p><\/p><br\/>[oracle@ISP4400 ~]$ sqlplus testuser\/testuser@TEST1020<br\/><p><\/p><br\/>SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:13:31 2006<br\/><p><\/p><br\/>Copyright (c) 1982, 2005, Oracle.  All rights reserved.<br\/><p><\/p><br\/>ERROR:<br\/>ORA-12514: TNS:listener does not currently know of service requested in connect<br\/>descriptor<br\/><p><\/p><br\/><br\/>Enter user-name:<\/pre>\n<p>My point is that, if you don&#8217;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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>No doubt this is incredibly old hat and I&#8217;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&#8217;s a small thing I didn&#8217;t know. On Unix and Linux, you can have a personal .tnsnames.ora (note the leading&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2006\/05\/15\/tnsnames-ora\/\">Continue reading <span class=\"screen-reader-text\">.tnsnames.ora<\/span><\/a><\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-782","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1063,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/21\/10g-default-installation-and-ofa\/","url_meta":{"origin":782,"position":0},"title":"10g Default Installation and OFA","date":"August 21, 2006","format":false,"excerpt":"or ... 'what's that all about?!?!'Last year, I blogged about some of the variations on Optimal Flexible Architecture that I've seen on my travels. I'm gradually coming round to having the service group for clustered applications as the top directory level (e.g.\/myapp1\/oradata\/\/myapp1\/oraindex\/\/myapp2\/oradata\/\/myapp2\/oraindex\/but I'm still not convinced. If people ever failed\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":910,"url":"http:\/\/orcldoug.com\/blog\/2005\/11\/08\/cost-based-oracle-and-isp4400\/","url_meta":{"origin":782,"position":1},"title":"Cost-based Oracle and ISP4400","date":"November 8, 2005","format":false,"excerpt":"My copy of Jonathan Lewis' CBO book should be with me tomorrow \ud83d\ude09In the meantime, as I expect to be blogging pretty often about the process of setting up the Intel ISP4400 that I bought to use for experiments at home, I thought I'd better hive it off into a\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1328,"url":"http:\/\/orcldoug.com\/blog\/2007\/10\/25\/which\/","url_meta":{"origin":782,"position":2},"title":"Which?","date":"October 25, 2007","format":false,"excerpt":"No, I don't mean the UK consumer organisation, but a simple *nix command. It's so simple that I've hesitated about blogging about it, but when I keep sharing a small snippet of information and people don't know it and find it useful, it's probably worth mentioning here.If you work on\u2026","rel":"","context":"With 19 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1044,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/04\/tracing-session-activity-over-a-remote-database-link\/","url_meta":{"origin":782,"position":3},"title":"Tracing session activity over a remote database link","date":"August 4, 2006","format":false,"excerpt":"Yesterday someone asked me how to trace a session that selects from a view in a remote database via a link. If they activated the trace on the local instance, they wouldn't see the bulk of the work which was happening on the remote instance - just a bunch of\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1102,"url":"http:\/\/orcldoug.com\/blog\/2006\/10\/08\/10gr2-on-solaris-10\/","url_meta":{"origin":782,"position":4},"title":"10gR2 on Solaris 10","date":"October 8, 2006","format":false,"excerpt":"I was surprised to realise that it's nearly 7 months since I bemoaned the lack of availability of 10gR2 on Solaris 10 (x86). Well, it's been out a few weeks now and I've been waiting since then for a free weekend to install it on the ISP4400. One of the\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":926,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/24\/intel-isp4400\/","url_meta":{"origin":782,"position":5},"title":"Intel ISP4400","date":"October 24, 2005","format":false,"excerpt":"I've wanted to carry on investigating parallel execution for a while but, unless you happen to be working on a reasonably large data warehouse with a few spare test environments kicking around, it's difficult. Without wishing to reopen the whole debate about the value of single user test scripts (which\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=782"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/782\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=782"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}