ORA-12505在Ubuntu上用于OracleXE11g

Question

我在连接本地Oracle数据库时遇到了一些问题。

我已经完成了基于以下指南的安装：安装-甲骨文-11g-r2-快车

本教程运行良好，但安装之后，我无法使用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

不使用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> 

当我试图使用SQLDeveloper连接时，我得到以下错误：

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

，我错过了什么？

listener.ora按@Mihai:的请求更改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)

@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

Answer 1

首先，请确保XE数据库已注册到侦听器。

来自侦听器的输出：

$ 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

如果列表中没有服务，请弹出侦听器。

$ lsnrctl stop
$ lsnrctl start

以用户sys的身份通过IPC登录到数据库。(不与侦听器联系)

$ sqlplus / as sysdba
SQL>-- tell the listener to pick up this particular database service.
SQL>alter system register;

如果您的数据库服务仍未列出，请为静态服务配置listener.ora文件。

静态服务

Answer 2

看看甲骨文中的tnsnames.ora，home.You中应该有这样的东西：

XE=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =somehost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XE)
    )
  )

编辑

在SQLDeveloper中，该错误意味着您试图在SID上连接，将其更改为service_name。

​

或者，在tsanames中，将SERVICE_NAME = XE更改为SID = XE