0

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.

  • The entry is PROD.DOMAIN, you're trying @prod ? – Philᵀᴹ Dec 15 '15 at 8:16
  • Yes, this format works presumably because service name is "prod". All other instances in tnsnames can be referenced in this way. – Joe Creighton Dec 15 '15 at 14:37
  • Check file /local/share/oracle/network/sqlnet.ora. If default_domain is DOMAIN then you can skip it. Otherwise you have to specify it. – Wernfried Domscheit Dec 15 '15 at 15:56
  • sqlnet.ora is not referenced in the Oracle doc and there is no such file provided for me by the Oracle DBAs in /local/share/oracle/network (making me believe it shouldn't be required but I am researching it now :) – Joe Creighton Dec 15 '15 at 16:27
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.