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\product\11.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\product\11.2.0\client_1\NETWORK\ADMIN) looks like this:

# sqlnet.ora Network Configuration File: C:\oracle\product\10.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?

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\product\11.2.0\client_1
SET TNS_ADMIN=C:\OraClient11g\product\11.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.

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 ?

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.