oracle12c 12505,Oracle 11g侦听器因ORA-12514和ORA-12505错误而失败

最新推荐文章于 2021-07-02 16:02:44 发布

淡定地坚强

最新推荐文章于 2021-07-02 16:02:44 发布

阅读量228

点赞数

文章标签： oracle12c 12505

我在开发机器上本地运行Oracle 11g实例,可以通过SqlPlus直接连接到本地实例：

c:\>sqlplus ace

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 11:50:20 2013

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

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

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", status UNKNOWN, 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

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)