This post was originally published on this site

I like to have my Oracle Net config consistent and without surprises. So for me it is a best practice to have ONE central TNS_ADMIN directory on the database server, which is set in all my shells. But a lot of processes might ignore that in case instances, sessions etc. are started from different environments, e.g. srvctl. So I also have symlinks in /network/admin to the central location. Well, there is one exception for Grid Infrastructure setups. Here my master is the GI_HOME/network/admin
and everything else symlinks to that. Why? Well – GI agents tend to adapt listener.ora file, and when they do that, they rename a former listener.ora, which is not what you wanted, in case it was a symlink.

There are also valid arguments for separating TNS_ADMIN settings. E.g. every databases has its own settings, maybe because you want other TNS alias resolution for different databases. e.g. PROD database uses a db link to alias ORCL, which is resolved differently for database TEST on the same server, so points to another database.
So in case you clone PROD to TEST you do not need to adapt the link to point to another database as this is already done cause of different name resolution.

Yes, valid approaches. But now follow some warnings what can happen in case you want to have different TNS_ADMINs on the same server.

I prepared the following tnsnames.ora’s in 4 different directories for an experiment:

1.
oracle@zam42:~/ [NASTY] cat /home/oracle/tns1/tnsnames.ora
# TNS-12545: Connect failed because target host or object does not exist
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP )(HOST = unknownhost )(PORT = 1521 ))
    (CONNECT_DATA =
      (SERVICE_NAME = UNKNOWN_SERVICE )
    )
  )
2.
oracle@zam42:~/ [NASTY] cat /home/oracle/tns2/tnsnames.ora
# TNS-12541: TNS:no listener
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP )(HOST = zam42 )(PORT = 1561 ))
    (CONNECT_DATA =
      (SERVICE_NAME = UNKNOWN_SERVICE )
    )
  )
3.
oracle@zam42:~/ [NASTY] cat /home/oracle/tns3/tnsnames.ora
# ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP )(HOST = zam42 )(PORT = 1521 ))
    (CONNECT_DATA =
      (SERVICE_NAME = UNKNOWN_SERVICE )
    )
  )
4.
oracle@zam42:~/ [NASTY] cat /home/oracle/tns4/tnsnames.ora
# ORA-00000: normal, successful completion
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP )(HOST = zam42 )(PORT = 1521 ))
    (CONNECT_DATA =
      (SERVICE_NAME = NASTY_RW )
    )
  )

And one listener.ora in the first directory.

oracle@zam42:~/ [NASTY] cat /home/oracle/tns1/listener.ora
LISTENER =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL=IPC )(KEY=LISTENER ))
        (ADDRESS =
            (PROTOCOL = TCP )
            (HOST = zam42 )
            (PORT = 1521 )
        )
    )

SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (ENVS="TNS_ADMIN=/home/oracle/tns2" )
            (GLOBAL_DBNAME = NASTY_SITE1_DGMGRL )
            (SID_NAME      = NASTY )
            (ORACLE_HOME   = /u00/app/oracle/product/19 )
        )
    )

Guess what: my db server is zam42, port is 1521 and the instance is called NASTY.

This is what happens in case you use the 4 different definitions for the ORCL alias,
depending on which environment you use:
tns1 – TNS-12545 – because a host is defined which cannot be resolved
tns2 – TNS-12541 – because a port is specified where no listener is bound
tns3 – ORA-12514 – because an unknown service is specified
tns4 – ORA-00000 – valid connect descriptor

This is how I setup the environment now:
I start the listener with environment 1.
Environment 2 is used when connections via static service NASTY_SITE1_DGMGRL are established (setting ENVS=) .
The entire instance I start from a bequeath session with environment 3.
Afterwards I set my environment on the server to environment 4.
Well kind of nasty, but here it is.

oracle@zam42:~/ [NASTY] export TNS_ADMIN=/home/oracle/tns1
oracle@zam42:~/ [NASTY] lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-SEP-2020 20:34:05

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
The command completed successfully
oracle@zam42:~/ [NASTY] lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-SEP-2020 20:34:08

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u00/app/oracle/product/19/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /home/oracle/tns1/listener.ora
Log messages written to /u00/app/oracle/diag/tnslsnr/zam42/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zam42)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                04-SEP-2020 20:34:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/tns1/listener.ora
Listener Log File         /u00/app/oracle/diag/tnslsnr/zam42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zam42)(PORT=1521)))
Services Summary...
Service "NASTY_SITE1_DGMGRL" has 1 instance(s).
  Instance "NASTY", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@zam42:~/ [NASTY] export TNS_ADMIN=/home/oracle/tns3
oracle@zam42:~/ [NASTY] echo "STARTUP FORCE" | sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 4 20:34:14 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             520093696 bytes
Database Buffers          536870912 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
oracle@zam42:~/ [NASTY] export TNS_ADMIN=/home/oracle/tns4

And now let us start or experiments.

1. connection using dynamic service NASTY_RW

# sqlplus /nolog
SQL> CONNECT system/manager@zam42:1521/NASTY_RW
Connected.
SQL> CREATE DATABASE LINK orcl CONNECT TO system IDENTIFIED BY manager USING 'ORCL';

Database link created.

SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

For a dynamic service we get the TNS_ADMIN of the pmon, smon etc. so it depends on environment when the instance was started. Note: In case of multitenant all connections for applications should use dynamic services.

2. connection using static service NASTY_SITE1_DGMGRL

SQL> CONNECT system/manager@zam42:1521/NASTY_SITE1_DGMGRL
Connected.
SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12541: TNS:no listener

For the static service we get the TNS_ADMIN as defined in the ENVS block of listener.ora as desired. If it is not defined in the ENVS block, it is inherited from the environment in which the listener was started, which would lead to a TNS-12545 in that scenario.

3. connection using SID=

SQL> CONNECT system/manager@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zam42)(PORT = 1521)) (CONNECT_DATA = (SID=NASTY)))
Connected.
SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Same rules as for the dynamic service as also the SID is dynamically registered. It would be different in case you have dynamic service registration disabled. Later we will test that.

4. bequeath connection

SQL> CONNECT system/manager
Connected.
SQL> SELECT 1 FROM dual@orcl;

         1
----------
         1

Well that was not of much surprise and the only way of getting the db link connection.

5. I said I will test what happens if I change dynamic service registration not to happen at all, so I set local_listener to an invalid port.

SQL> ALTER SYSTEM SET local_listener='zam42:1522';

System altered.

Now let us connect via SID=

SQL> CONNECT system/manager@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zam42)(PORT = 1521)) (CONNECT_DATA = (SID=NASTY)))
Connected.
SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12541: TNS:no listener

Now we are in enviroment 2, which is cause of the ENVS in the static service definition, which is now used as the dynamic service registration has disappeared.
Can you still follow?

6. once more bequeath

SQL> CONNECT system/manager
Connected.
SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

WTF?
Set back the local listener correctly.

SQL> ALTER SYSTEM SET local_listener='zam42:1521';

System altered.

SQL> CONNECT system/manager
Connected.
SQL> SELECT 1 FROM dual@orcl;

         1
----------
         1

Can it be even nastier?
Lets get to level 2.
Now we do a restart the instance via remote connection using SID=NASTY or static service NASTY_SITE1_DGMGRL. The picture will change.

SQL> CONNECT sys/manager@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zam42)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = NASTY_SITE1_DGMGRL))) AS SYSDBA
Connected.
SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             520093696 bytes
Database Buffers          536870912 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL> CONNECT system/manager@zam42:1521/NASTY_RW
Connected.
SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12541: TNS:no listener


SQL> CONNECT system/manager@zam42:1521/NASTY_SITE1_DGMGRL
Connected.
SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12541: TNS:no listener


SQL> CONNECT system/manager@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zam42)(PORT = 1521)) (CONNECT_DATA = (SID=NASTY)))
Connected.
SQL> SELECT 1 FROM dual@orcl;
SELECT 1 FROM dual@orcl
                   *
ERROR at line 1:
ORA-12541: TNS:no listener


SQL> CONNECT system/manager
Connected.
SQL> SELECT 1 FROM dual@orcl;

         1
----------
         1

Well, at least now we are more consistent.

Summary

It is very challenging to have multiple TNS_ADMIN in place for listeners and databases instances. There is a high risk for messing things up. You need to be very careful. I am still a big fan of having ONE TNS_ADMIN definition on the server setting it wherever I can, and symlinking from OH/network/admin for everything which bypasses that. I would be happy to read your thoughts on that.

Mathias