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. [email protected]:~/ [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. [email protected]:~/ [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. [email protected]:~/ [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. [email protected]:~/ [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.
[email protected]:~/ [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.
[email protected]:~/ [NASTY] export TNS_ADMIN=/home/oracle/tns1 [email protected]:~/ [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 [email protected]:~/ [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 [email protected]:~/ [NASTY] export TNS_ADMIN=/home/oracle/tns3 [email protected]:~/ [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 [email protected]:~/ [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/[email protected]:1521/NASTY_RW Connected. SQL> CREATE DATABASE LINK orcl CONNECT TO system IDENTIFIED BY manager USING 'ORCL'; Database link created. SQL> SELECT 1 FROM [email protected]; SELECT 1 FROM [email protected] * 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/[email protected]:1521/NASTY_SITE1_DGMGRL Connected. SQL> SELECT 1 FROM [email protected]; SELECT 1 FROM [email protected] * 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/[email protected](DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zam42)(PORT = 1521)) (CONNECT_DATA = (SID=NASTY))) Connected. SQL> SELECT 1 FROM [email protected]; SELECT 1 FROM [email protected] * 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 [email protected]; 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/[email protected](DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zam42)(PORT = 1521)) (CONNECT_DATA = (SID=NASTY))) Connected. SQL> SELECT 1 FROM [email protected]; SELECT 1 FROM [email protected] * 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 [email protected]; SELECT 1 FROM [email protected] * 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 [email protected]; 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/[email protected](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/[email protected]:1521/NASTY_RW Connected. SQL> SELECT 1 FROM [email protected]; SELECT 1 FROM [email protected] * ERROR at line 1: ORA-12541: TNS:no listener SQL> CONNECT system/[email protected]:1521/NASTY_SITE1_DGMGRL Connected. SQL> SELECT 1 FROM [email protected]; SELECT 1 FROM [email protected] * ERROR at line 1: ORA-12541: TNS:no listener SQL> CONNECT system/[email protected](DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zam42)(PORT = 1521)) (CONNECT_DATA = (SID=NASTY))) Connected. SQL> SELECT 1 FROM [email protected]; SELECT 1 FROM [email protected] * ERROR at line 1: ORA-12541: TNS:no listener SQL> CONNECT system/manager Connected. SQL> SELECT 1 FROM [email protected]; 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
