投稿
  1. 首页
  2. 软件运维

PLSQL链接数据库提示无监听程序

穿越宇宙的少女 • 2023-5-14 • 软件运维 • 阅读 13

PLSQL链接数据库提示无监听程序,第1张

方法一：

在oracle_home下找到lsnrctl.exe 输入 start

方法二

可能认不到实例名

在cmd下运行

set oracle_sid=自己数据库的实例名（大多数orcl）

网上还有很多解决办法

也许不见得管用，这种问题具体问题具体分析比较好。介绍的这2种您的机器还不行。就去修改一个TNSNAMES.ORA。

***************************************************************

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

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

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = TESTDB)

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

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

Listener 改为上面的样子(ORACLE_SID为TESTDB，如果实际不是，自己改一下）.

然后lsnrctl stop

lsnrctl start

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/mengzhaoliang>lsnrctl status

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

0: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/mengzhaoliang>lsnrctl start

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

2: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:5

4: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 - 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

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/mengzhaoliang>sqlplusscott/mzl@ORCL_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:2

6: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 - 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

监听端点概要...

(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/mengzhaoliang>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/mengzhaoliang>sqlplus scott/mzl@orcl

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 Peveloper登陆就没有问题了。

完毕!

通过以上步骤我的问题还没解决，然后重启了一下OracleOraDb10g_home1TNSListener服务就行了

先看下服务，实例和监听是否都起来了。如果是吧network/admin下的东西清除了(TR...ORA文件可以先复制出来)，然后用net ass 那个工具重新配置个监听就好了(这个方式省的你路径找来找去的)

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/yw/11238446.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
穿越宇宙的少女 穿越宇宙的少女 一级用户组
0 0
上一篇 2023-05-14
下一篇 2023-05-14

发表评论

登录后才能评论

评论列表（0条）

保存