Unabe to resolve cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

0
votes

I am trying to access an oracle database on another machine. I am using:

  • Ubuntu Server 18.04
  • oracle client 12.2
  • python 3.6 and cx_oracle 6.4.1

I constanly get this error:

Error

File "basic_test.py", line 5, in <module>
    con =  cx_Oracle.connect('jalabe','jalabepass', cx_Oracle.makedsn('ipserver','port',None,'prod'))
cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

basic_tst.py

import cx_Oracle
# first attempt
# con =  cx_Oracle.connect('jalabe/jalabepass@ipaddress/prod')

# second attempt 
con =  cx_Oracle.connect('jalabe','jalabepass', cx_Oracle.makedsn('ipaddress','port',None,'prod'))
print(con.version)
con.close()

tnsnames.ora

DATABASE.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ipaddress)(PORT = port))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )

Other info

  • $ORACLE_HOME as /usr/lib/oracle/12.1/client64/lib/
  • File tnsnames.ora is inside /usr/lib/oracle/12.1/client64/network/admin

I have read many post without any luck. From all of the post I have read these are the ones I think are more usefull although they haven't work

I am sure it might be some character somewhere.

I don't have sqlplus installed nor tnslistener. (Could it be the cause?) (I have read that tnslistener should be in the oracle server which is why i haven't installed it)

1
oracleubuntucx-oracle

1 Answers

1
votes

Here are a few comments and suggestions to help you correct your issue:

1) Install SQL*Plus and use it to connect to your database. You should be able to connect using the connect string 'jalabe/jalabepass@host/service_name' where 'host' is the host on which the database is found and 'service_name' is the service name of the database you want to connect to. This same connect string should work within cx_Oracle as well. You don't need to use makedsn() or tnsnames.ora (although they should also work, of course!)

2) Do not set the environment variable ORACLE_HOME when using the Instant Client.

3) On the database machine you should be able to run the command 'lsnrctl status' which will show you the status of the listener and which services it knows about. If your service isn't in the list, that's the source of the issue!

4) If you stop and restart the listener it can take a bit of time before the database will register itself with the listener. You can force that to happen more quickly by issuing the command 'alter system register' as SYSDBA in the database (or stopping/starting the database).

Hopefully that helps you out!