Oracle – ORA-12514: TNS:listener does not know of service (The service is running and listed by lsnrctl)

databaseodbcoracleoracle11gUbuntu

I'm trying to access an Oracle database using ODBC in PHP. (Apache 2, PHP 5, ODBC driver and Oracle database 11.2 are installed on Ubuntu Server 14.04 in VirtualBox.)

I've managed to install Oracle database and I think I've configured tnsnames.ora and ODBC driver:

  • I'm able to login using sqlplus and execute SQL;
  • tnsping works;
  • I actually installed 12.1 version of ODBC driver when database version is 11.2, but I think it works fine.

But when I call odbc_connect in PHP, I get the following error:

Warning: odbc_connect(): SQL error: [unixODBC][Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor , SQL state S1000 in SQLConnect in /var/www/html/index.php on line 61

I believe driver works fine because it's mentioned in that error. When I tried calling odbc_connect without driver installed, I had another error.

If I specify @DB when using sqlplus, I get some strange errors (listings down there).

Configuration

This is how tnsnames.ora configured:

# tnsnames.ora Network Configuration File:

MYNAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = TCP)(PROTOCOL = TCP)(HOST = database)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = XE))
  )

DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

DB and EXTPROC_CONNECTION_DATA sections were here when I started, but I also added MYNAME to test whether I should use (CONNECT_DATA = (SID = XE)) instead of (SERVICE_NAME = ORCL).

sqlplus connect errors

I'm unable to login like sqlplus dbuser@DB:

sqlplus dbuser@DB

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 17 21:22:41 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12641: Authentication service failed to initialize

If I change SQLNET.AUTHENTICATION_SERVICES from ALL to NONE, I'm unable to login using sqlplus at all:

sqlplus dbuser@DB

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 17 21:22:41 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

sqlplus dbuser

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 17 21:22:41 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

Relevant SO questions

ORA 12514 error:TNS listener error

  1. Check if a database instance is up and running
  2. Check the output of the lsnrctl service command and see what services are registered.
  3. Check if you have SERVICE_NAME correctly specified when connecting to the instance.
  4. If it happens that database instance stared before listener did, sometimes you just need to wait a little or you can execute alter system register in order to register the instance.
  1. Up and running;
  2. My service seems is listed;
  3. Specified correctly;
  4. Doesn't help.

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor for existing service

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Tried both (local_listener solution).

The Question

So I'm missing something in tnsnames.ora? Or there is some other problem I'm encountering?

Update 1

lsnrctl status:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 18-FEB-2016 20:27:56

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                18-FEB-2016 20:21:31
Uptime                    0 days 0 hr. 6 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           ORCL
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/database/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

sqlplus dbuser (when SQLNET.AUTHENTICATION_SERVICES is set to ALL):

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 18 20:26:50 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

Best Answer

OK, I'm an idiot.

My /etc/odbc.ini contained something like that:

[DB]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 12g ODBC driver
DSN = DB
EXECSchemaOpt = 
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = localhost/DRORACLE
SQLGetData extensions = F
Translation DLL = 
DisableRULEHint = T
UserID = dbuser
StatementCache=F
CacheBufferSize=20

I've copy-pasted all that somewhere and edited a few key-value pairs in order to make it work on my machine.

For the last few days I was working with Oracle Database on some other operating systems. For example, I've downloaded Oracle DB Developer VM image from Oracle site, where the Database is already installed and working fine.

So, I looked at that record in odbc.ini and decided to try something simpler (knowning what is working fine on other machines):

[DB2]
Driver = Oracle 12g ODBC driver
DSN = DB
ServerName = database
UserID = dbuser
Password = dbuser

I tried isql -v DB2 and it worked!

The Answer

The reason ODBC driver gave me the error is invalid ServerName!

ServerName = localhost/DRORACLE

vs.

ServerName = database

database == localhost in my case, just another line in /etc/hosts.

Actually, I got an error message from isql -v DB, when I just changed ServerName in that first record, but that was only because that record was missing Password field.

I should add that I'm using SQLNET.AUTHENTICATION_SERVICES = (NONE) here. I'm not sure exactly why, but it shows ORA-12641: Authentication service failed to initialize if I use (ALL) there. Doesn't matter for me, so you probably should figure it out yourself, if you would be configuring that.

Related Topic