TNS_ADMIN / TNSNAMES location failover
[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))" &gt; ~/tnsnames1.ora
[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))" &gt; ~/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" &gt; ~/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.