Hard override entry in tnsnames.ora

I have a set of shell scripts and sqlplus commands.

These connect to Oracle DB_ONE and DB_TWO.

I am upgrading DB_ONE.

For my testing I override the DB_ONE entry in a local tnsnames.ora. There exists a global tnsnames.ora with all connections in it.

export TNS_ADMIN=/path/to/local/tnsnames:/path/to/global/tnsnames

This way, I am able to connect to DB_ONE on my_new.server and DB_TWO on some.other.server as expected.

However, if I break my_new.server, sqlplus automatically connects to DB_ONE on original.server. So it fails silently and fails over to the connection in the global tnsnames file. I would like this connection to fail completely.

Is there a way to have a 'hard' override such that sqlplus will only try a DB_ONE connection from the local tnsnames.ora, whilst being free to try DB_TWO connections from all tnsnames.ora files?

My local tnsnames.ora

DB_ONE=
        (DESCRIPTION=
                (ADDRESS_LIST=
                        (ADDRESS=
                        (PROTOCOL=TCP)
                        (PORT=1524)
                        (HOST=my_new.server)
                )
        )
        (CONNECT_DATA=
                (SERVICE_NAME=DB_ONE)
        )
        )

Global tnsnames.ora which I cannot change

DB_ONE=
        (DESCRIPTION=
                (ADDRESS_LIST=
                        (ADDRESS=
                        (PROTOCOL=TCP)
                        (PORT=1524)
                        (HOST=original.server)
                )
        )
        (CONNECT_DATA=
                (SERVICE_NAME=DB_ONE)
        )
        )

DB_TWO=
        (DESCRIPTION=
                (ADDRESS_LIST=
                        (ADDRESS=
                        (PROTOCOL=TCP)
                        (PORT=1524)
                        (HOST=some.other.server)
                )
        )
        (CONNECT_DATA=
                (SERVICE_NAME=DB_TWO)
        )
        )

Solution

This is not valid:

export TNS_ADMIN=/path/to/local/tnsnames:/path/to/global/tnsnames

TNS_ADMIN is a single directory path, not a searchable list like $PATH or $LD_LIBRARY_PATH etc. The documentation mentions that:

If the TNS_ADMIN environment variable is not set, then Oracle Net will check the ORACLE_HOME/network/admin directory.

It doesn't say so, but it also defaults to checking the network/admin directory if the TNS_ADMIN variable does not point to a valid directory, and as your colon-seperated list isn't a valid directory path, it will use the tnsnames.ora under $ORACLE_HOME/network/admin.

That means your local 'override' file is never being used, and you were accessing which ever instance DB_ONE points to in the global file. It's not that the TNS entry from the second file is used if the first fails - that mechanism just doesn't exist. (You can have failover within a file but that's different).

Assuming you have connection strings using a TNS alias like user/pwd@DB_ONE and you can't change those for your testing, your only real option is to make a complete copy of the global file and just edit the entry for DB_ONE:

cp /path/to/global/tnsnames/tnsnames.ora /path/to/local/tnsnames/
edit /path/to/local/tnsnames/tnsnames.ora
export TNS_ADMIN=/path/to/local/tnsnames

Or as @ibre5041 mentioned in a comment, as you're on Linux you could skip the TNS_ADMIN environment variable and use ~/.tnsnames.ora for your local copy.

As you mention that won't reflect any changes made to the global file, but presumably once you've finished your testing you can trash your local file or revert to the global TNS_ADMIN anyway.