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

Question

我正在尝试在 PHP 中使用 ODBC 访问 Oracle 数据库。 (Apache 2、PHP 5、ODBC 驱动程序和 Oracle 数据库 11.2 安装在 VirtualBox 的 Ubuntu Server 14.04 上。)

我已经成功安装了 Oracle 数据库，并且我想我已经配置了 tnsnames.ora 和 ODBC 驱动程序:

我可以使用 sqlplus 登录并执行 SQL；

tnsping 工程；

当数据库版本为 11.2 时，我实际上安装了 12.1 版本的 ODBC 驱动程序，但我认为它工作正常。

但是当我在 PHP 中调用 odbc_connect 时，出现以下错误:

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

我相信驱动程序可以正常工作，因为该错误中提到了它。当我尝试调用 odbc_connect没有安装驱动程序，我有另一个错误。

如果我指定 @DB使用 sqlplus 时，我收到一些奇怪的错误(下面的列表)。

配置

这是 tnsnames.ora 的配置方式:

# 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和 EXTPROC_CONNECTION_DATA当我开始时，部分就在这里，但我还添加了 MYNAME测试我是否应该使用 (CONNECT_DATA = (SID = XE))而不是 (SERVICE_NAME = ORCL) .

sqlplus 连接错误

我无法像 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

如果我改变 SQLNET.AUTHENTICATION_SERVICES来自 ALL至 NONE ，我根本无法使用 sqlplus 登录:

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

相关的 SO 问题

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.

启动并运行；

我的服务似乎已列出；

正确指定；

没有帮助。

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

两种都试过了( local_listener 解决方案)。

问题

所以我在 tnsnames.ora 中遗漏了一些东西？还是我遇到了其他问题？

更新 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 (当 SQLNET.AUTHENTICATION_SERVICES 设置为 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>

Answer 1

好吧，我是个白痴。

我的 /etc/odbc.ini包含类似的东西:

[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

我已经将所有内容复制粘贴到某个地方并编辑了一些键值对，以使其在我的机器上工作。

在过去的几天里，我在其他一些操作系统上使用 Oracle 数据库。例如，我从 Oracle 站点下载了 Oracle DB Developer VM 镜像，该站点已经安装了数据库并且工作正常。

所以，我在 odbc.ini 中查看了该记录。并决定尝试一些更简单的东西(知道在其他机器上运行良好):

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

我试过 isql -v DB2它奏效了!

答案

ODBC 驱动程序给我错误的原因是无效的 服务器名 !

ServerName = localhost/DRORACLE

对比

ServerName = database

database == localhost就我而言，只是 /etc/hosts 中的另一行.

实际上，我收到了来自 isql -v DB 的错误消息，当我刚刚更改 ServerName在第一个记录中，但这只是因为该记录丢失了 Password field 。

我应该补充一点，我正在使用 SQLNET.AUTHENTICATION_SERVICES = (NONE)这里。我不确定为什么，但它显示 ORA-12641: Authentication service failed to initialize如果我使用 (ALL)那里。对我来说没关系，所以如果你要配置它，你可能应该自己弄清楚。