sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs

Let’s take the following setup

C:\tmp>type dir1\sqlnet.ora

C:\tmp>type dir1\tnsnames.ora

C:\tmp>type dir2\sqlnet.ora

C:\tmp>type dir2\tnsnames.ora

You set TNS_ADMIN to dir1 and your current directory is dir2.

Let’s try TNSPING.EXE first

C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>tnsping db

TNS Ping Utility for 64-bit Windows: Version - Production on 25-NOV-2013 15:47:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
OK (0 msec)

TNSPING.EXE is using the sqlnet.ora in %TNS_ADMIN% directory (EXAMPLE.COM domain) and the tnsnames.ora in the current directory (db02)

Let’s try with sqlplus

C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>sqlplus -L system@db

SQL*Plus: Release Production on Mon Nov 25 16:01:15 2013

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from global_name;


SQLPLUS.EXE is using the sqlnet.ora in the current directory (EXAMPLE.ORG) and the tnsnames.ora in the current directory (db02)

This does not reproduce on Linux

Author: Laurent Schneider

Oracle Certified Master

6 thoughts on “sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies”

  1. Interesting. I think a key there is “This does not reproduce on Linux”. Have you explored possible interactions with the registry? Perhaps tns_admin set in registry vs. as a session environment variable? I started down that road once, but the possible permutations just got too complex. One more reason one of my favorite tag lines is “My momma always said Windows was like a box of chocolates.”

  2. There is certainly a great deal to find out
    about this issue. I like all the points you’ve made.

    Also visit my website :: watch breaking bad online (Marilyn)

  3. Spot on with this write-up, I honestly believe that this
    site needs a great deal more attention. I’ll probably
    be back again to read more, thanks for the information!

    Here is my website web page (Lino)

Leave a Reply