我在开发机器上本地运行Oracle 11g实例,可以通过sqlPlus直接连接到本地实例：
c:\>sqlplus ace sql*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 11:50:20 2013 Copyright (c) 1982,2010,Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta sql> select count(*) from my_table ; COUNT(*) ---------- 5297
但我无法通过监听器连接到它：
c:\>sqlplus -L "user/pw@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))" sql*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 11:52:40 2013 Copyright (c) 1982,Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor SP2-0751: Unable to connect to Oracle. Exiting sql*Plus
类似地,如果我通过sqlDeveloper连接,我会收到一个错误(尽管ORA-12505,TNS：监听器当前不知道连接描述符中给出的SID).
这个实例一直稳定并且工作正常一年或更长时间,直到今天,周一早上.我们的企业IT部门有时会在周末推出新的政策和更新,所以我假设情况发生了变化,但我无法弄清楚是什么.
我已多次重启服务和监听器,监听器日志没有提供任何线索.
听众似乎很好：
c:\>lsnrctl status LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Beta on 11-MAR-2013 11:55:33 Copyright (c) 1991,Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Beta Start Date 11-MAR-2013 11:17:30 Uptime 0 days 0 hr. 38 min. 3 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\FBC305BB46560\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machine.domain.com)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc",status UNKNOWN,has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc",has 1 handler(s) for this service... The command completed successfully
1521端口似乎没问题：
c:\>netstat -an -O | find /i "1521" TCP 0.0.0.0:1521 0.0.0.0:0 LISTENING 4368 TCP 169.243.90.109:55307 159.185.207.100:1521 ESTABLISHED 12416 TCP [::]:1521 [::]:0 LISTENING 4368
(PID 4368是TNSLSNR.exe进程.)
此外,我可以tnsping到XE服务：
c:\>tnsping xe TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Beta on 11-MAR-2013 12:27:47 Copyright (c) 1997,Oracle. All rights reserved. Used parameter files: C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = machine.domain.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) OK (210 msec)
listenerr.ora文件：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = machine.domain.com)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
另外,我不知道它是否相关,我似乎无法访问https://127.0.0.1:8080/apex上的顶点(即使权限看起来很好).
那么我还应该在哪儿看？
更新所需信息：
sql> show parameter service_names NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string XE sql> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string
Update2：正如@miracle173正确指出的那样,听众并不好.使用更新的’local_listener’参数现在显示额外信息：
Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machine.domain.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc",has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "xe",status READY,has 1 handler(s) for this service... Service "xe" has 1 instance(s). Instance "xe",has 1 handler(s) for this service... The command completed successfully
因此,感谢@YasirArsanukaev在他投入的时间,我找到了一个有效的解决方案,但我无法解释.
原文链接：https://www.f2er.com/oracle/205355.html关于LOCAL_LISTENER的想法,我正在读this other answer,它说：
The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null,which according to the documentation is equivalent to hostname:1521.
所以我试图ping我自己的主机名,但不能 – 它看起来像一些IPv6问题,收到一般的失败消息.
所以我接受了那个答案的建议
sql> alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both; sql> alter system register;
它现在有效,大概是因为它可以解析localhost引用,它解析了实际的主机名失败.
