環球新消息丨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.

已連接到空閑例程。