解決PL/SQL Developer串連資料庫時出現 “ORA-12541:TNS:無監聽程式”錯誤

最後更新：2016-07-23 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

標籤：

在用PL/SQL Developer串連資料庫時出現
“ORA-12541:TNS:無監聽程式”錯誤。

1、檢查listener.log日誌
發現下面錯誤：
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-9月 -2008 10:25:26

系統參數檔案為D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
寫入D:/oracle/product/10.2.0/db_1/network/log/listener.log的日誌資訊
寫入D:/oracle/product/10.2.0/db_1/network/trace/listener.trc的跟蹤資訊
追蹤層級當前為0

以 pid=1704 開始
監聽: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=//./pipe/EXTPROC1ipc)))
監聽該對象時出錯: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.5.0.5)(PORT=1521)))
TNS-12545: 因目標主機或對象不存在, 串連失敗
TNS-12560: TNS: 協議適配器錯誤
TNS-00515: 因目標主機或對象不存在, 串連失敗
32-bit Windows Error: 49: Unknown error
不再監聽: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=//./pipe/EXTPROC1ipc)))

2、查看Oracle的listener是否啟動
C:/Documents and Settings/mengzhaoliang>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-9月 -2008 10:5
0:44

正在串連到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS: 無監聽程式
TNS-12560: TNS: 協議適配器錯誤
TNS-00511: 無監聽程式
32-bit Windows Error: 2: No such file or directory
正在串連到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.5.0.5)(PORT=1521)))
TNS-12535: TNS: 操作逾時
TNS-12560: TNS: 協議適配器錯誤
TNS-00505: 操作逾時
32-bit Windows Error: 60: Unknown error

原來沒有啟動listener，用“lsnrctl start”命令也不能啟動。
C:/Documents and Settings/mengzhaoliang>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-9月 -2008 10:5
2:16

啟動tnslsnr: 請稍候...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
系統參數檔案為D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
寫入D:/oracle/product/10.2.0/db_1/network/log/listener.log的日誌資訊
監聽: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=//./pipe/EXTPROC1ipc)))
監聽該對象時出錯: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.5.0.5)(PORT=1521))
)
TNS-12545: 因目標主機或對象不存在, 串連失敗
TNS-12560: TNS: 協議適配器錯誤
TNS-00515: 因目標主機或對象不存在, 串連失敗
32-bit Windows Error: 49: Unknown error

監聽程式未能啟動。請參閱上面的錯誤訊息...

3、查看listener.ora的內容：
# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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))
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.5.0.5)(PORT = 1521))
)
)

原來原生ip發生改變後，就出現了上述問題，改變資料庫的監聽ip地址:
把(ADDRESS = (PROTOCOL = TCP)(HOST = 0.5.0.5)(PORT = 1521))
改成
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

127.0.0.1：也就是目前資料庫正在用的ip地址。

4、再次啟動oracle的listener
C:/Documents and Settings/mengzhaoliang>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-9月 -2008 10:5
4:40

啟動tnslsnr: 請稍候...

正在串連到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------
別名                      LISTENER
版本                      TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
啟動日期                  20-9月 -2008 10:54:41
正常已耗用時間              0 天 0 小時 0 分 1 秒
追蹤層級                  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=127.0.0.1)(PORT=1521)))
服務摘要..
服務 "PLSExtProc" 包含 1 個常式。
常式 "PLSExtProc", 狀態 UNKNOWN, 包含此服務的 1 個處理常式...
命令執行成功

啟動已經成功，

5.再tnsnames.ora上添加上
ORCL_127.0.0.1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

6、再次用PL/SQL Developer再次串連資料庫
出現下面錯誤：
TNS-12514: TNS: 監聽程式當前無法識別串連描述符中請求的服務

再次檢查listener.log日誌
20-9月 -2008 11:01:54 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)(CID=

(PROGRAM=D:/plsql/plsqldev.exe)(HOST=RUIFEI-EF0ADC98)(USER=mengzhaoliang))) * (ADDRESS=

(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1267)) * establish * orcl * 12514
TNS-12514: TNS: 監聽程式當前無法識別串連描述符中請求的服務

查看listener：
C:/Documents and Settings/mengzhaoliang>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-9月 -2008 11:1
1:09

正在串連到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
服務摘要..
服務 "PLSExtProc" 包含 1 個常式。
常式 "PLSExtProc", 狀態 UNKNOWN, 包含此服務的 1 個處理常式...
處理常式:
"DEDICATED" 已建立:0 已被拒絕:0
LOCAL SERVER
命令執行成功

7、用sqlplus也出現同樣錯誤：
C:/Documents and Settings/mengzhaoliang>sqlplusscott/[email protected]_127.0.0.1

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 9月 20 11:15:09 2008

ERROR:
ORA-12514: TNS: 監聽程式當前無法識別串連描述符中請求的服務

8、查看listenser狀態：
C:/Documents and Settings/mengzhaoliang>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-9月 -2008 11:2
6:42

正在串連到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER 的 STATUS
------------------------
別名                      LISTENER
版本                      TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
啟動日期                  20-9月 -2008 11:24:33
正常已耗用時間              0 天 0 小時 2 分 8 秒
追蹤層級                  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

C:/Documents and Settings/mengzhaoliang>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-9月 -
2008 11:27:43

已使用的參數檔案:
D:/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

TNS-03505: 無法解析名稱

9、查看sqlnet.ora內容：
# sqlnet.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

10.把listener.ora的內容：
# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)

改成下面的內容：

# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
# (PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = orcl))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)

11、然後關閉、再啟動listener
在cmd中執行“lsnrctl stop” 和“lsnrctl stop”命令，再次登陸正常！
C:/Documents and Settings/mengzhaoliang>sqlplus scott/[email protected]

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 9月 20 11:55:47 2008

串連到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

再次用PL/SQL Peveloper登陸就沒有問題了。

解決PL/SQL Developer串連資料庫時出現 “ORA-12541:TNS:無監聽程式”錯誤

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More