[GIS] Error in the connection to Oracle Spatial from QGIS

oracle-spatialqgis

I am not being able to stablish the connection to an Oracle Spatial server in QGIS.

Apparently I have the connection configured accordingly with QGIS documentation, but it stills gives me an error:

ORA-12514:TNS_listener does not currently know service requested in connect descriptor

Here are my connection parameters as set in QGIS:

QGIS connection parameters

I have used the same parameters that I have set in the connection settings in SQL Developer (that works fine):

connection parameters SQL Developer

I have tried to use tnsping command to check the connection parameters and it is successful, regardless use:

tnsping [server_ip]:[port_number]/[SID]

or

tnsping [tnsname]

where [tnsname] is defined in the tnsnames.ora file with the same connection parameters indicated above:

[tnsname] = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = XXX.XXX.XX4.95) (PORT = 1521)) ) (CONNECT_DATA = (SID = anza1) (SERVER = DEDICATED) ) )

I am really running out of options… Can anyone help?

NOTE: I'am using QGIS 2.0.1-Dufour

Best Answer

The fact that tnsping completes is no indication that the database is actually accessible. All it does is tell that the TNS listener is up and running and listening on the port you expect. To illustrate that, just try something like this:

tnsping XXX.XXX.XX4.95:1521/whatever

and you will still get a successful result.

A better way is to test using sqlplus, for example:

$ sqlplus scott/tiger@localhost:1521/whatever
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 9 09:43:10 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Your problem is most likely because you are mixing up the SID (= a physical identifier of a particular database instance) and the service (= a logical identifier that can be common to all instances of a database). Connections should really be done via a service name.

Some tools (like sqldeveloper) clearly offer you the choice to connect via a SID or a service names. Others, like QGIS are not so clear: what does "base de dados" means ? Is it supposed to be a service name ? Or a SID ? Probably a service name, considering the error message you get.

First try and connect using sqlplus:

sqlplus <user>/<pass>@XXX.XXX.XX4.95:1521/anza1

Here anza1 means a service name. If that fails too like QGIS, then your problem is just that the database has a service name different than anza1. A very common cause is that the service name for your database is a fully qualified dns name, for example anza1.db.oracle.com. So you may want to try that as a service name for sqlplus (and then also QGIS).

sqlplus /@XXX.XXX.XX4.95:1521/anza1.db.oracle.com

You can find out the services your database advertises by looking at the service_names parameter (must be DBA to do that):

SQL> show parameter service

NAME                 TYPE       VALUE
-------------------- ---------- ------------------------------
service_names        string     orcl121,my_service,whatever.oracle.com

Here I can connect to the database using any of the offered service names.

You can also ask the listener about the services it knows about for each database instance:

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-APR-2016 10:07:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                09-APR-2016 09:55:13
Uptime                    0 days 0 hr. 12 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/spatial-workshop/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=spatial-workshop)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=spatial-workshop)(PORT=5500))(Security=(my_wallet_directory=/home/oracle/app/oracle/admin/orcl121/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "my_service" has 1 instance(s).
  Instance "orcl121", status READY, has 1 handler(s) for this service...
Service "orcl121" has 1 instance(s).
  Instance "orcl121", status READY, has 1 handler(s) for this service...
Service "orcl121XDB" has 1 instance(s).
  Instance "orcl121", status READY, has 1 handler(s) for this service...
Service "whatever.oracle.com" has 1 instance(s).
  Instance "orcl121", status READY, has 1 handler(s) for this service...
The command completed successfully

As you can see, I can connect to that database using any of the offered services.

You can change the names of the services offered by doing this (as DBA):

SQL> alter system set service_names = 'my_service,whatever.oracle.com';
System altered.

That has an immediate effect.

Related Question