2

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?

  • 1
    Do you have the FULL Oracle Client installed? – Mapperz Feb 21 '14 at 15:00
  • yep, I've installed the full client. – JTPort Feb 21 '14 at 15:02
2

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)
    )
  )

enter image description here

2

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 server:1521/service

For example

tnsping 192.168.56.102:1521/orcl121

TNS Ping Utility for Linux: Version 12.1.0.1.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:

sqlplus scott/tiger@192.168.56.102:1521/orcl121

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

lsnrctl status

which should give something like:

LSNRCTL for Linux: Version 12.1.0.1.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 12.1.0.1.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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.