Oracle TNSNAMES – Cannot Connect Using tnsnames.ora but Can Connect by Substituting Details

oracletnsnames

I'm setting up a minimal Oracle Instant Client on a non-Oracle Linux host from zip files (as per https://docs.oracle.com/database/121/SQPUG/apd.htm#SQPUG157) and have installed

instantclient-basiclite-linux.x64-12.1.0.2.0.zip
instantclient-sqlplus-linux.x64-12.1.0.2.0.zip

(note: no tnsping is available).

I've added the following environment variables:

ORACLE_SID=prod; export ORACLE_SID
ORACLE_HOME=/local/share/bin/oracle_client/instantclient_12_1; export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/sqlplus; export SQLPLUS
LD_LIBRARY_PATH=$ORACLE_HOME:${LD_LIBRARY_PATH}; export LD_LIBRARY_PATH
TNS_ADMIN=/local/share/oracle/network; export TNS_ADMIN

The $TNS_ADMIN/tnsnames.ora file is provided by an Oracle host (I cannot change it), is readable from this non-Oracle host, and contains no syntax errors (it is in use by Oracle hosts). The specific entry looks much like this:

PROD.DOMAIN =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = prod.domain)(PORT = 1234))
  )
 (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = prod)
 )
)

That said, I cannot connect on the command line using the preferred method for us:

$SQLPLUS user@prod
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 14 14:45:22 2015

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

Enter password: 
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

suggesting that tnsnames is not being found or parsed or… ? I believe I have everything in place as per "Connecting to a Database with SQL*Plus Instant Client" in the referenced document at the top.

Note that I can connect if I use the host name itself:

$SQLPLUS user@\"//prod.domain:1235/prod\"

or the details from tnsnames itself (proving good syntax):

$SQLPLUS user@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=prod.domain)(PORT=1234)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prod)))"

Any ideas?

My current workaround is the third method using Perl to parse tnsnames into a set of one-liners and grep for the instance I want but it'd be nice to know why this isn't working and how to fix it.

Thanks.

Best Answer

Thank you to Wernfried Domscheit for pointing me to sqlnet.ora. This file was not referenced in the documentation I was using so I had no idea it existed. After visiting an Oracle host and finding the file in $ORACLE_HOME/network/admin, I copied it to $TNS_ADMIN dir and minimized it through testing to the one setting (no others were needed) Wernfried referenced:

# this setting = my local domain
NAMES.DEFAULT_DOMAIN = DOMAIN

and my preferred connection method of

$SQLPLUS user@prod

succeeded. I will speak with the Oracle DBAs about this file and it's proper inclusion in this shared dir.

Related Question