ORACLE 监听错误（ora-12514）的解决

今天在本机上连接rman时抛出12514的错误：

C:\Documents and Settings\Administrator>rman nocatalog target sys/sys@colin

恢复管理器: Release 10.2.0.1.0 - Production on 星期二 7月 17 09:36:34 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN-00571:

RMAN-00569: ERROR MESSAGE STACK FOLLOWS

RMAN-00571:

RMAN-00554: 内部恢复管理器程序包初始化失败

RMAN-04005: 目标数据库中存在错误:

ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

这个错误很常见，造成这个错误的原因也比较多，根据在线文档上的错误描述为：

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action:

- Wait a moment and try to connect a second time.

- Check which services are currently known by the listener by executing: lsnrctl services

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.

笔者这边遇到的情况为，每次关闭数据库重新启动后都会抛这样的错误。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

C:\Documents and Settings\Administrator>sqlplus sys/sys@colin as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 7月 17 11:30:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:

ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

察看监听文件的配置为：

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (PROGRAM = extproc)

    )      ----注意这里缺少了相应得数据库服务

)

LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = COLIN)(PORT = 1521))

    )

)

下面呢就利用net manager为监听添加相应得数据库服务了

注：上面是一个添加数据库服务的图片

服务添加完成后，保存下，再看监听的配置：

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = colin)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (SID_NAME = colin)

    )               -------相应的数据库服务添加上了

)

LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = COLIN)(PORT = 1521))

    )

)

好了下步就是要重新装入下监听器

C:\Documents and Settings\Administrator>lsnrctl reload

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 17-7月 -2007 12:0

2:25

Copyright (c) 1991, 2005, Oracle. All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

命令执行成功

我们再来显示下监听的状态：

C:\Documents and Settings\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 17-7月 -2007 12:0

2:56

Copyright (c) 1991, 2005, Oracle. All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

LISTENER 的 STATUS

------------------------

别名                      LISTENER

版本                      TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ

ction

启动日期                  17-7月 -2007 09:39:35

正常运行时间              0 天 2 小时 23 分 21 秒

跟踪级别                  off

安全性                    ON: Local OS Authentication

SNMP                      OFF

监听程序参数文件          D:\oracle\product\10.2.0\db_1\network\admin\listener.o

ra

监听程序日志文件          D:\oracle\product\10.2.0\db_1\network\log\listener.log

监听端点概要...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=colin)(PORT=1521)))

服务摘要..

服务 "PLSExtProc" 包含 1 个例程。

例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "colin" 包含 1 个例程。

例程 "colin", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

命令执行成功

再关闭数据库，然后再连接数据库。Ok，no problem了~

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

C:\Documents and Settings\Administrator>sqlplus sys/sys@colin as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 7月 17 12:03:06 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

已连接到空闲例程。