How to connect QGIS to an Oracle DB (SDE)?

Question

I want to access an Oracle Database (with SDE) from Qgis 2.4.

I have tried many different options from within the window Create a new Oracle Connection but I always get an error message (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor or similar messages). I know however that the DB is working and listening. I know the correct host, the correct port and I have valid username / password.

The IT support here gave me 2 (independent) pieces of advice but I'm not sure how to implement them with Qgis:

• "Does your client (Qgis) know about the correct tns_admin directory?"
• "Make sure that LDAP is listed first: NAMES.DIRECTORY_PATH = (LDAP, TNSNAMES,ONAMES)"

Anyone has an idea?

Thanks a lot in advance!

PS: sorry if my title is not very explicit. I'm not sure how to best formulate it.

---

EDIT [30.09.2014]

I have moved a little bit forward and I have edited the file tnsnames.ora located under C:\OraClient11g\product11円.2.0\client_1\NETWORK\ADMIN:

name_of_my_db =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = *********)(PORT = 1568))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = name_of_my_db)
 )
)

Will Qgis read this file or does it require its own copy of it? If yes, where should it be located? If no, how does it know where tnsnames.ora is stored? Should there be an entry in the registry?

By the way, my sqlnet.ora file (also located in C:\OraClient11g\product11円.2.0\client_1\NETWORK\ADMIN) looks like this:

# sqlnet.ora Network Configuration File: C:\oracle\product10円.2.0\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.
################
# Filename......: sqlnet.ora
# Name..........: $Id: sqlnet.ora,v 1.3 2004年11月22日 13:47:17 oracle Exp $
# Date..........: $Source: /data/repository/cvsroot/ORACLE/env_sqlnet/indus/sqlnet.ora,v $
#
################
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES,ONAMES)
SQLNET.CRYPTO_SEED = "************************"
NAMES.PREFERRED_SERVERS =
 (ADDRESS_LIST =
 (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
 (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
 (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
 (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1568))
 (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1529))
 (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1527))
 (ADDRESS = (COMMUNITY = ************************)(PROTOCOL = TCP)(Host = ************************)(Port = 1528))
 )
NAMES.DEFAULT_DOMAIN = ************************
SQLNET.EXPIRE_TIME = 0

---

EDIT 2 [30.09.2014]

According to my DB Admin, I shouldn't even need the tnsnames.ora, because the connection should work through LDAP... Not sure whether or not this information brings anything relevant for my problem?

• user30184: Have you already read this gis.stackexchange.com/questions/240/… and this gis.stackexchange.com/questions/73414/…?
• Stéphane Henriod: Yes I have, thanks! However, the first link is quite old and refers to older versions of Qgis, when the Oracle connection was not yet compiled by default. I assume many things have changed since. The second link is more recent and I tried to follow what it says but without success. I guess it's a syntax problem from my side

Answer 1

It looks like EZConnect can't connect for whatever reason (3a works/3b doesn't). You can try adding "ezconnect" to NAMES.DIRECTORY_PATH in the sqlnet.ora file. Hopefully that will make 3b from @Albert Godfrind's answer work.

The LDAP connection may fail from QGIS because you have something like the Oracle Instant Client somewhere in the path before your C:\OraClient11g\ client. In that case, sqlplus and tnsping would get loaded from the C:\OraClient11g\ client, while QGIS would load the other client that is missing the sqlnet.ora file. You can set the the variables ORACLE_HOME and TNS_ADMIN to tell the Oracle client to load the admin directory that it specifies, rather than the default relative path. Try setting these variable to force Oracle to use the same admin dir that sqlplus is using. Start QGIS from the command line after setting:

SET ORACLE_HOME=C:\OraClient11g\product11円.2.0\client_1
SET TNS_ADMIN=C:\OraClient11g\product11円.2.0\client_1\NETWORK\ADMIN

NOTE: I'm not sure if TNS_ADMIN is going to be just for TNS Names looks up, or if the client will load the sqlnet.ora file and use LDAP. Technically, I think ORACLE_HOME will be enough, and you don't actually need the TNS_ADMIN variable or tns_names.ora file.

---

I got this to work with TNS_NAMES by setting the TNS_ADMIN variable, then starting QGIS 2.4.0. I created the connection by only setting the Database, Username and Password values in the "Create a New OGR Data connection" dialog.

The version of QGIS that I have does have the instant client included in the bin folder, so you wither need to set TNS_ADMIN to use tns name conenctions, or create the Network/admin/ folders in the QGIS bin folders and copy your tnsnames.ora and sqlnet.ora file into it.

It looks like if you set the Host values in the connection dialog, QGIS tries to use an ezconnect connection.

• Stéphane Henriod: (1) I added EZCONNECT to NAMES.DIRECTORY_PATH but tnsping is still going with LDAP. If I only have EZCONNECT, then the tnsping simply fails (TNS-03505)
• Stéphane Henriod: (2) I launched Qgis from the command line after having set the 2 env variables but this doesn't help either...
• Stéphane Henriod: Moving forward: when I set the 2 variables (without even adding "EZCONNECT" in NAMES.DIRECTORY_PATH) I manage to get the connection working through the ODBC connection: connection to OCI:user/pass@db successful. But when I actually want to open the DB (not just test it), I get an Invalid data source error. Anf if I try to connect through Add an Oracle Spatial table, I get ORA-04043: object MDSYS.SDO_GEOMETRY does not exist. Is it possible that Qgis only understands the Oracle Spatial geometries? In my case, the geometries come from ArcSDE
• travis: Yes, if you want to access data loaded by ArcGIS/ArcSDE clients, you need to specify the SDO_GEOMETRY keyword when you load the data (in ArcMap/ArcCatalog). This will store the data using Oracle Spatail, not one the the ESRI types.
• Stéphane Henriod: I somehow can't create a feature class with SDO_GEOMETRY but this is not related to the current problem. I assume that this would however solve my problem, so thanks a lot!

Answer 2

Those sort of issues can be hard to track. My approach would be to remove QGIS and SDE from the picture and make sure the connection works with plain Oracle tools. That will also help your IT support debug the issue.

1) Do:

$ tnsping <tns_name>

Does this work ? Or does it fail the same way

2) Do:

$ tnsping <your_host>:1568/<database_service_name>

Does this work ? Or does it fail the same way

3) Do:

$ sqlplus <my_user>/<my_password>@tns_name

and

$ sqlplus <my_user>/<my_password>@<your_host>:1568/<database_service_name>

Does this work ? I.e do you get a SQL> prompt ? Or do you get the same error ?

4) Finally, do the following on the database server (only a DBA or system admin can do that)

$ lsnrctl status

That will list all databases and services on that server, along with their service names. Is the database you want to access listed ?

The common cause for the ORA-12514 error is that the database somehow did not register itself with the TNS listener on that server. That happens when the database starts up. But it the listener is not running yet when the database starts up, then the database tries at regular intervals to ping the listener and register - and that mechanism sometimes fails.

Another possibility is that the server runs multiple listeners that listen on different ports. That is probably the case in you environment since you use an unusual port (1568) rather than the default (1521). It may be that you are talking to the wrong listener.

EDIT:

About "database name" vs "service name"

To be exact, there really is no such thing as a database name: a database is really a collection of files (data files, redo logs, control files). What you commonly use as database name is really the SID, which really identifies an instance, i.e. the set of processes that accesses the database. A database can actually be accessed from multiple instances (= have multiple SIDs) when it runs in a cluster: multiple servers (aka nodes) access the database (the files must be on shared storage) and each runs a different instance (= a different SID).

An application that wants to access a database on a cluster can specify the explicit SID to access the instance on a specific node of the cluster - but that would defeat the goals of a cluster, which are to have nodes fail over transparently, and also have the load automatically distributed to all nodes of the cluster. So, that application will use a service name that is the same for all SIDs (= all instances). So connecting to a database using a service name is preferred to using SIDs, and is the most common way, even for all the databases that are single-instance (not on a RAC).

This is what happens in your case: see your TNSNAMES entry: it uses a service name:

(SERVICE_NAME = name_of_my_db)

For a single-node database (the most common case, and probably your case too), the name given when creating the database becomes the name of its (single) SID and also that of its (single) service name.

Another kind of name is that given in your TNSNAME file:

name_of_my_db =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = *********)(PORT = 1568))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = name_of_my_db)
 )

)

The first "name_of_my_db" (at the beginning) is really the name of a set of parameters that simplifies connecting to a database instance - a tns name. This is what you use when you connect like this:

$ sqlplus <my_user>/<my_password>@tns_name

The second "name_of_my_db" (in the SERVICE_NAME parameter) is the database_service_name in

$ sqlplus <my_user>/<my_password>@<your_host>:1568/<database_service_name>

About your problem

Your tests confirm that the database is up and running on the server, that the TNS listener knows about it, that it listens on the right port, that your TNS configuration is correct, and that you can connect. So that clearly isolates the problem to QGIS.

However the fact that tests (2) and (3b) failed with TNS-03505 is strange. Are you sure you used the full service name specified inside the TNS definition and that you used the proper syntax ?

In QGIS, how do you define the database connection ? Do you specify server, port and service name ? Or do you specify a TNS name ?

• Stéphane Henriod: Thanks a lot for your answer. --- The Oracle tools work without any problem (SQL Developper, SQL Navigator, SQL Plus). Even access through a Python script poses no problem. --- 1) Works 2) Doesn't work (Error TNS-03505) 3a) Works 3b) Doesn't work (Error ORA-12560) 4) Have to see with my DBA (I have no access to the server side)
• Stéphane Henriod: But what is exactly <database_service_name>? Is it simply the name of the DB?
• Albert Godfrind: See my edited answer
• Stéphane Henriod: Thanks for your detailed support! I just tried a simple tnsping my_service_name. This works and says that the LDAP was used to resolve the connection, not the TNS. So I edited sqlnet.ora to make sure that only TNSNAMES is used for the resolution. With a new tnsping my_service_name, I get all the correct parameters (host, port, service_name) but when I use those in tnsping <your_host>:1568/<database_service_name>, I get a TNS-03505 Error
• Stéphane Henriod: In Qgis, I have tried "all" options (with and without specifying server, port and service name, with the "short" name for the DB (my_db), with the fully contextualised name for the DB (my_db.server.something)...)

Answer 3

If you have the Oracle client installed on your system then you can suffice with just the database and port number. No host required.