ORA-12514:TNS:侦听器不知道服务（服务正在运行，并由lsnrctl列出）

Question

我试图在PHP中使用ODBC访问Oracle数据库。（Apache2、PHP5、ODBC驱动程序和Oracle数据库11.2安装在VirtualBox中的Ubuntu服务器14.04上。）

我已经安装了Oracle数据库，我想我已经配置了tnsnames.ora和ODBC驱动程序：

• 我可以使用sqlplus登录并执行SQL；
• tnsping工作；
• 当数据库版本为11.2时，我实际上安装了12.1版本的ODBC驱动程序，但我认为它可以正常工作。

如果我在使用sqlplus时指定@db，我会得到一些奇怪的错误（下面的列表）。

以下是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 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

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

ORA 12514错误：TNS侦听器错误

1. 检查数据库实例是否已启动并正在运行
2. 检查lsnrctl service命令的输出，看看注册了哪些服务。
3. 检查连接到实例时是否正确指定了service_name。
4. 如果数据库实例在listener之前出现，有时只需稍等，或者执行Alter system register就可以注册实例。

1. 启动并运行；
2. 我的服务似乎已列出；
3. 正确指定；
4. 没有帮助。

ORA-12514 TNS:侦听器当前不知道现有服务的连接描述符中请求的服务

ORA-12514:TNS:侦听器当前不知道连接描述符中请求的服务

两者都尝试了(local_listener解决方案）。

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

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，它成功了！

ServerName = localhost/DRORACLE

ServerName = database

database==localhost在我的例子中，只是/etc/hosts中的另一行。

实际上，当我在第一条记录中更改servername时，从ISQL-V DB得到了一条错误消息，但这只是因为该记录缺少password字段。

我应该补充一点，我在这里使用的是sqlnet.authentication_services=(NONE)。我不确定确切的原因，但它显示ORA-12641:如果我在那里使用(ALL)，身份验证服务无法初始化。对我来说无关紧要，所以如果你要配置它，你可能应该自己弄清楚。