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.
Best Answer
We did have similar issue with Oracle Spatial. In our case we need to adding more permissios for geom-columns (or whatever column you use to store geometries).
It seems that other GIS desktop applications are not so strict.
More information from QGIS Developer email list.