Linux ora-12514多实例,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

SNMPOFF

监听程序参数文件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.

已连接到空闲例程。