TNS_ADMIN / TNSNAMES location failover

Question

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?

score 3 · Answer 1 · answered Oct 18 '18 at 19:43

[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.

TNS_ADMIN / TNSNAMES location failover

1 Answers1