天天看點

kcbzps oracle_解決因改變oracle資料庫的IP位址引起的“ORA-12541:TNS:無監聽程式”

環境:OraDB10g_home

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

2.使用Net Manager 更改本地資料庫服務IP位址後抛出“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

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

reserved.

系統參數檔案為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\user>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on

20-9月 -2008 10:50:44

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

reserved.

正在連接配接到 (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\user>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on

20-9月 -2008 10:52:16

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

reserved.

啟動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:54:40

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

reserved.

啟動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=127.0.0.1)(PORT=1521)))

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

LISTENER 的 STATUS

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

别名 LISTENER

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

啟動日期 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.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=127.0.0.1)(PORT=1521)))

服務摘要..

服務 "PLSExtProc" 包含 1 個例程。

例程 "PLSExtProc", 狀态 UNKNOWN, 包含此服務的 1

個處理程式...

指令執行成功

啟動已經成功,

如果已使用Net Manager

更改本地資料庫服務IP位址(即遇到了問題2的情況),那麼問題應該已經解決,不需要執行下面的步驟了。

如果還出現錯誤,試試重新開機了一下OracleOraDb10g_home1TNSListener服務。

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\user>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on

20-9月 -2008 11:11:09

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

reserved.

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

服務摘要..

服務 "PLSExtProc" 包含 1 個例程。

例程 "PLSExtProc", 狀态 UNKNOWN, 包含此服務的 1

個處理程式...

處理程式:

"DEDICATED" 已建立:0 已被拒絕:0

LOCAL SERVER

指令執行成功

7、用sqlplus也出現同樣錯誤:

C:\Documents and

Settings\user>sqlplusscott/[email protected]_127.0.0.1

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

2008

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

reserved.

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:26:42

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 - Production

啟動日期 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.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=127.0.0.1)(PORT=1521)))

服務摘要..

服務 "PLSExtProc" 包含 1 個例程。

例程 "PLSExtProc", 狀态 UNKNOWN, 包含此服務的 1

個處理程式...

指令執行成功

C:\Documents and Settings\user>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 -

Production on 20-9月 -2008 11:27:43

Copyright (c) 1997, 2005, Oracle. All rights

reserved.

已使用的參數檔案:

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\user>sqlplusscott/[email protected]

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

2008

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

reserved.

連接配接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -

Production

With the Partitioning, OLAP and Data Mining options

SQL>

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

完畢!