1

Is there a way to have multiple tnsnames.ora locations defined? We currently use a server path like TNS_ADMIN=\\192.168.1.100\infosys\oracle\tnsnames but are in a situation this week where that server had to be taken offline for a couple days.

I've tried stacking locations similar to PATH variable but it doesn't work:

> set TNS_ADMIN=z:\Common\oracle;\\192.168.1.100\infosys\oracle\tnsnames
> mctnsping ora-server

McTnsping Utility by Michel Cadot: Version 2018.08.17 on 18-OCT-2018 11:16:33
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.

*** TNS-03514: Failed to find tnsnames.ora file

Single location is fine:

> set TNS_ADMIN=Z:\Common\oracle
> mctnsping ora-server

Used parameter files:
Z:\Common\oracle\tnsnames.ora
...snip...    
Attempting to contact ora-server:1521
OK (201 msec)

How to have an alternate location without touching every client to change TNS_ADMIN?

Improve this question
1

1 Answer 1

Reset to default
3 
[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))" > ~/tnsnames1.ora
[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))" > ~/tnsnames2.ora
[oracle@o71 ~]$ cat tnsnames1.ora
TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))
[oracle@o71 ~]$ cat tnsnames2.ora
TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))

I changed the HOST and SERVICE_NAME values for demonstrating how name resolution still works after making one of the files inaccesible.

Now include them with IFILE:

[oracle@o71 ~]$ echo -e "IFILE=/home/oracle/tnsnames1.ora\nIFILE=/home/oracle/tnsnames2.ora" > ~/tnsnames.ora
[oracle@o71 ~]$ cat ~/tnsnames.ora
IFILE=/home/oracle/tnsnames1.ora
IFILE=/home/oracle/tnsnames2.ora
[oracle@o71 ~]$ export TNS_ADMIN=/home/oracle

Test:

[oracle@o71 ~]$ tnsping tns1

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 18-OCT-2018 21:37:00

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))
OK (10 msec)

tnsping picked up the last occurence of the same TNS alias. Now make this second file inaccessible:

[oracle@o71 ~]$ mv /home/oracle/tnsnames2.ora /home/oracle/tnsnames2.ora.corrupt

Test again:

[oracle@o71 ~]$ tnsping tns1

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 18-OCT-2018 21:37:19

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))
OK (0 msec)

TNS resolution still works, but from the first file (HOST, SERVICE_NAME).

With this method you basically enumerate multiple files containing TNS aliases using the IFILE parameter, which was not designed for this, but it is something.

Improve this answer
1
  • +1 for the information and education. However I can't use it here as it appears IFILE support is incomplete, at least on Windows. For our primary client app, ArcGIS Desktop, IFILE only works if the target .ora files are on the local client system. Network sources fail using both mapped drive and UNC paths. For mctnsping, using IFILE always fails.
    – matt wilkie
    Oct 22, 2018 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.