Ubuntu Oracle Instant Client tnsnames.ora

1
votes

I had recently set up Oracle Instant Client on Ubuntu 18.04 and am trying to connect to a database located on a different server through sqlplus with the command: 

sqlplus username@orcl

I had edited my tnsnames.ora file in the following format:

ORCL= 
 (DESCRIPTION= 
   (ADDRESS=(hostname)(PORT=1521))
   (CONNECT_DATA= 
     (SERVICE_NAME=service_name)))

And after I put in the password it get the error message:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

If I were to manually type in the connection data such as 

sqlplus username/password@orcl:portnumber/service_name

I would be about to connect, but I wish to not type all of the connection data and leave it to just username@host

I am not sure what is causing the error and don't know if I need to edit any files on the server side where the actual database is hosted to allow for such a connection. Thank you

2
oraclesqlplustnsnamesoracleclient
You missed the port, see valid example at orafaq.com/wiki/Tnsnames.ora - Wernfried Domscheit
Also maybe this is obvious, but in your example you're using ORCL= so your command should be sqlplus username@ORCL - kfinity
Sorry, just updated my question. I do use a port and have been trying to connect to the actual database name. I just used vague keywords when presenting the example in the question. I still get the same error - Bobby Bob

2 Answers

0
votes

If it fails like described the service_name for the entry in the tnsnames.ora file is wrong.

I would make a copy of the tnsnames.ora file to a folder and rename the entry from ORCL to something unique for my test

oracle@befb83f389c6:~$ mkdir tst oracle@befb83f389c6:~$ cp product/11.2.0/xe/network/admin/tnsnames.ora tst/ oracle@befb83f389c6:~$ vim tst/tnsnames.ora oracle@befb83f389c6:~$ cat tst/tnsnames.ora

tnsnames.ora Network Configuration File:

ONLYFORME = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = befb83f389c6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

at this point tnsping to the name ONLYFORME will fail as the client does not know about the copy of the tnsnames.ora file

oracle@befb83f389c6:~$ tnsping ONLYFORME

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 06-OCT-2018 12:18:51

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

Used parameter files:

TNS-03505: Failed to resolve name

to compensate we set the TNS_ADMIN env varaiable

oracle@befb83f389c6:~$ export TNS_ADMIN=/u01/app/oracle/tst/ oracle@befb83f389c6:~$ tnsping ONLYFORME

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 06-OCT-2018 12:19:16

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = befb83f389c6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) OK (0 msec) oracle@befb83f389c6:~$

now tnsping to ONLYFORME works

connecting with sqlplus using this name also works fine

oracle@befb83f389c6:~$ sqlplus iasim@ONLYFORME

SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 6 12:22:43 2018

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

Enter password:

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

to reproduce the error youre getting we might put in an unknown service name

oracle@befb83f389c6:~$ cat tst/tnsnames.ora

tnsnames.ora Network Configuration File:

ONLYFORME = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = befb83f389c6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = UNKNOWN_XE) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

oracle@befb83f389c6:~$ sqlplus iasim@ONLYFORME

SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 6 12:25:17 2018

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

Enter password: ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Enter user-name:

at this point we know which tnsnames.ora file it is that resolves the tnsname we're using (ONLYFORME) and we know where the error is

hope this helps...

0
votes

assuming that you don't want to type password;

this method is usually done by dba's who logs as ssh user into that particular server/host directly, wherein their password is stored as hashed-private/public-keys (Not from remote machines)

the simpler way is to use like sqlplus username/password@remote_host_ip:1521/orcl better yet create alias in your kshrc/bashrc file ( and source them ) for the above so you get what you need with fewer keystrokes.

eg: nano .bashrc alias tosql='sqlplus username/password@remote_host_ip:1521/orcl'