Recently, I faced a TNS resolution problem at a customer. The reason was a bad environment setting: The customer called the service desk because of a DBLINK pointing to a bad database.
The users were supposed to be redirected to a development database, and the DBLINK was redirecting to a validation database instead. The particularity of the environment is that development and validation databases are running on the same server, but on different Oracle homes, each home having its own tnsnames.ora. Both tnsnames.ora contain common alias names, but pointing on different databases. Not exactly best practice, but this is not the topic here.
The problem started with some issues to reproduce the case. Our service desk was not able to reproduce the situation without understanding that the customer was trying to access the database remotely via a development tool (through the listener), while we were connected locally on the server.
Let me present the case with my environment.
First, this is the database link concerned by the issue:
SQL> select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ---------- -------------------- ------------------------------ ---------- --------- PUBLIC DBLINK DBLINK MYDB 21-MAR-14
And this is the output when we try to display the instance name through the DBLINK, when connected locally:
SQL> select instance_name from v$instance@DBLINK; INSTANCE_NAME ---------------- DB2
The user is redirected on the remote database, as expected. Now, let’s see what happens when connected using the SQL*Net layer:
[oracle@srvora01 ~]$ sqlplus system@DB1 SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 10:07:45 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance@DBLINK; INSTANCE_NAME ---------------- DB1
Here we can see that the user is not redirected to the same database (here, for demonstration puproses, on the database itself).
The first thing to check is the TNS_ADMIN variable, if it exists:
[oracle@srvora01 ~]$ echo $TNS_ADMIN /u00/app/oracle/product/11.2.0/db_3_0/network/admin
There is the content of the tnsnames.ora file on that location:
[oracle@srvora01 ~]$ cat /u00/app/oracle/product/11.2.0/db_3_0/network/admin/tnsnames.ora DB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = srvora01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB1) ) ) MYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = srvora01)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DB2) ) )
Clearly, we have a problem with the TNS resolution. The local connection resolves the MYDB alias correctly, while the remote connection resolves a different database with the alias. In this case, we have two solutions:
- The tnsnames.ora is not well configured: this is not the case, as you can see above
- Another tnsnames.ora file exists somewhere on the server and is used by remote connections
To confirm that the second hypothesis is the good one, we can use the strace tool:
SQL> set define #
SQL> select spid from v$process p join v$session s on p.addr=s.paddr and s.sid=sys_context('userenv','sid');
SPID
------------------------
5578
SQL> host strace -e trace=open -p #unix_pid & echo $! > .tmp.pid
Enter value for unix_pid: 5578
SQL> Process 5578 attached - interrupt to quit
SQL> select instance_name from v$instance @ DBLINK;
open("/u00/app/oracle/product/11.2.0/db_3_0/network/admin/tnsnames.ora", O_RDONLY) = 8
open("/etc/host.conf", O_RDONLY) = 8
open("/etc/resolv.conf", O_RDONLY) = 8
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 8
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 8
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 8
open("/etc/passwd", O_RDONLY|O_CLOEXEC) = 10
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 10
open("/etc/hostid", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 10INSTANCE_NAME
----------------
DB2
The DBLINK is resolved using the file /u00/app/oracle/product/11.2.0/db_3_0/network/admin/tnsnames.ora.
Now, when connected remotely:
SQL> set define #
SQL> select spid from v$process p join v$session s on p.addr=s.paddr and s.sid=sys_context('userenv','sid');
SPID
------------------------
6838
SQL> host strace -e trace=open -p #unix_pid & echo $! > .tmp.pid
Enter value for unix_pid: 6838
SQL> Process 6838 attached - interrupt to quit
SQL> select instance_name from v$instance@DBLINK;
open("/u00/app/oracle/network/admin/tnsnames.ora", O_RDONLY) = 8
open("/etc/host.conf", O_RDONLY) = 8
open("/etc/resolv.conf", O_RDONLY) = 8
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 8
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 8
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 8
open("/etc/passwd", O_RDONLY|O_CLOEXEC) = 9
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 9
open("/etc/hostid", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/etc/hosts", O_RDONLY|O_CLOEXEC) = 9INSTANCE_NAME
----------------
DB1
Here the DBLINK is resolved with the file /u00/app/oracle/network/admin/tnsnames.ora.
Two different tnsnames.ora files are used according to the connection method! If we query the content of the second tnsnames.ora, we have an explanation for our problem:
[oracle@srvora01 ~]$ cat /u00/app/oracle/network/admin/tnsnames.ora MYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = srvora01)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DB1) ) )
It is not clearly documented by Oracle, but the database session can inherit the environment variables in three different ways:
- When you connect locally to the server (no SQL*Net, no listener), the Oracle session inherits the client environment
- When you connect remotely to a service statically registered on the listener, the Oracle session inherits the environment which started the listener
- When you connect remotely to a service dynamically registered on the listener, the Oracle session inherits the environment which started the database
In our case, the database was restarted with the wrong TNS_NAMES value set. Then, the database registered this value for remote connections. We can check this with the following method:
[oracle @ srvora01 ~]$ ps -ef | grep pmon
oracle 3660 1 0 09:02 ? 00:00:00 ora_pmon_DB1
oracle 4006 1 0 09:05 ? 00:00:00 ora_pmon_DB2
oracle 6965 3431 0 10:44 pts/1 00:00:00 grep pmon
[oracle @ srvora01 ~]$ strings /proc/3660/environ | grep TNS_ADMIN
TNS_ADMIN=/u00/app/oracle/network/admin
Note that we can get the value for TNS_ADMIN using the dbms_system.get_env.
The solution was to restart the database with the correct TNS_ADMIN value:
[oracle @ srvora01 ~]$ echo $TNS_ADMIN /u00/app/oracle/product/11.2.0/db_3_0/network/admin [oracle@srvora01 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 10:46:03 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 1570009088 bytes Fixed Size 2228704 bytes Variable Size 1023413792 bytes Database Buffers 536870912 bytes Redo Buffers 7495680 bytes Database mounted. Database opened. [oracle@srvora01 ~]$ ps -ef | grep pmon oracle 4006 1 0 09:05 ? 00:00:00 ora_pmon_DB2 oracle 7036 1 0 10:46 ? 00:00:00 ora_pmon_DB1 oracle 7116 3431 0 10:46 pts/1 00:00:00 grep pmon [oracle@srvora01 ~]$ strings /proc/7036/environ | grep TNS_ADMIN TNS_ADMIN=/u00/app/oracle/product/11.2.0/db_3_0/network/admin
The value for TNS_ADMIN is now correct.
[oracle@srvora01 ~]$ sqlplus system @ DB1 SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 10:47:21 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved.Enter password: Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance @ DBLINK; INSTANCE_NAME ---------------- DB2
Remote connections are now using the right tnsnames.ora.
I hope this will help you with your TNS resolution problems.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)