Trying to connect an oracle 10g database to QGIS. I'm getting the error below:
ORA - 12154: TNS:Could not resolve the connect identifier specified
Anybody ever seen this error, or could provide some guidance?
Check the tnsnames.ora file in the location c:\app\user\product\10.x.x\client\network\admin\tnsnames.ora
(if there is more than 1 products you may have to do it to both or 'de-install' oracle and then re-install the full client.)
There should be the database connections listed
DBANAME = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = IPADDRESS)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DBSERVICE) ) )
The error means that the listener that serves your database(s) does not know of the database name you specify in your connection string. The connection details you need to specify are: - IP address or DNS name of the server where the database runs - the port number of the listener (1521 by default) - the SID or service name of your database. Not sure which one QGIS expects.
1) Verify that you can connect to the listener (you probably can, otherwise you would have a different error) Still, double check that you are indeed connecting to the right listener by using the tnsping command:
tnsping 192.168.56.102:1521/orcl121 TNS Ping Utility for Linux: Version 184.108.40.206.0 - Production on 21-FEB-2014 23:04:32 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora Used EZCONNECT adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orcl121))(ADDRESS= (PROTOCOL=TCP)(HOST=192.168.56.102)(PORT=1521))) OK (0 msec)
That confirms that the listener is up and running, but it does not confirm that it can connect to your database.
2) Verify that you can connect to your database using sqlplus. For example:
If that succeeds, then check that you are passing the right parameters to QGIS. If not, then the issue is not with QGIS but just with database connectivity.
3) Go on the server and check the state if the listener
which should give something like:
LSNRCTL for Linux: Version 220.127.116.11.0 - Production on 21-FEB-2014 23:10:42 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 18.104.22.168.0 - Production Start Date 19-FEB-2014 18:17:17 Uptime 2 days 4 hr. 53 min. 25 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=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=spatial-workshop)(PORT=1521))) (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 "orcl121" has 2 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... The command completed successfully
Verify that the service name shown is the same you use for your connections. If you do not see any of the "service" lines, then that means that your database is not registered with this listener. Check that it is up and running. If not, start it up. If it is, then it means that it somehow failed to register with the listener. The usual steps for this are:
1) The listener starts
2) The database starts and registers with the listener.
If you do the steps in the reverse sequence:
1) The database starts (but the listener is not up yet)
2) The listener starts. At that point it knows nothing about the database
3) Sometimes later, the database attempts to register with the listener. It does that automatically every 60 seconds until it registers.
Sometimes that does not work and that usually means a problem in the database configuration. A simple cure may be to just stop and start the database. You can also try a manual registration:
alter system register;
If that does not work, you need to check some of the database connection parameters. But rather than going through a full explanation, I suggest you to read http://edstevensdba.wordpress.com/2011/03/19/ora-12514/ and http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/ for a good summary of this problem and how to solve it.