适用于 Ubuntu 上的 Oracle XE 11g 的 ORA-12505
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20017638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-12505 for Oracle XE 11g on Ubuntu
提问by Vegard
I have some problems connecting to my local Oracle database.
我在连接到本地 Oracle 数据库时遇到了一些问题。
I have done the installation based on this guide: installing-oracle-11g-r2-express
我已经根据本指南完成安装：installing-oracle-11g-r2-express
The tutorial worked out fine, but after the installation I am unable to connect to database using SQLDeveloper or 'sqlplus system/password@XE'
该教程运行良好，但安装后我无法使用 SQLDeveloper 或“sqlplus system/password@XE”连接到数据库
sqlplus system/password@XE
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 16 11:57:06 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
connecting without using XE works:
不使用 XE 进行连接工作：
:~$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 16 11:54:54 2013
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>
when I try to connect using SQLDeveloper I get the following error:
当我尝试使用 SQLDeveloper 进行连接时，出现以下错误：
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
What am I missing?
我错过了什么？
listener.ora as requested by @Mihai:changed 20.11.2012 12:06
@Mihai 要求的 listener.ora：已更改 20.11.2012 12:06
# listener.ora Network Configuration File:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = <name_of_my_computer>)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
$ lsnrctl status as requested by @BjarteBrandt:
@BjarteBrandt 要求的 $ lsnrctl 状态：
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 18-NOV-2013 12:52:26
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-NOV-2013 12:48:06
Uptime 0 days 0 hr. 4 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/<name_of_my_computer>/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<name_of_my_computer>)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
采纳答案by Bjarte Brandt
Fist, please make sure the XE database did register to the listener.
首先，请确保 XE 数据库确实注册到侦听器。
output from my listener:
我的听众的输出：
$ lsnrctl status
$ lsnrctl 状态
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
If no services are in your list, please bounce the listener
如果您的列表中没有任何服务，请退回监听器
$ lsnrctl stop
$ lsnrctl start
Login to the database over IPC as user sys. (does not contact the listener)
以 sys 用户身份通过 IPC 登录到数据库。（不联系听者）
$ sqlplus / as sysdba
SQL>-- tell the listener to pick up this particular database service.
SQL>alter system register;
If your database service still is not listed, please configure you listener.ora file for a STATIC service.
如果您的数据库服务仍未列出，请为 STATIC 服务配置您的 listener.ora 文件。
回答by Mihai
Look at tnsnames.ora in your Oracle home.You should have something like this:
查看您的 Oracle 主页中的 tnsnames.ora。您应该有这样的内容：
XE=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =somehost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = XE)
)
)
Edit
编辑
In SQLDeveloper,that error means you are trying to connect on SID,change that to service_name.
在 SQLDeveloper 中，该错误意味着您正在尝试连接 SID，将其更改为 service_name。
OR, in tsanames change SERVICE_NAME = XE to SID = XE
或者，在 tsanames 中将 SERVICE_NAME = XE 更改为 SID = XE
