Cannot connect using tnsnames.ora but can connect by substituting its details

Question

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.

Answer 1

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.