本文探讨了在使用Oracle数据库时遇到的通过服务名连接失败的问题，并提供了详细的故障诊断步骤和解决方案，包括检查sqlnet.ora、tnsnames.ora、listener.ora配置文件，分析错误日志以及监听器状态等。
为什么我的数据库无法通过服务名连接？现象和相关参数文件如下，请大家帮忙看下
SQL> conn system/system@fox
ERROR:
ORA-12547: TNS: 丢失连接
等待很长时间后出现ORA-12547: TNS: 丢失连接 这个错误，但是通过如下方式却可以连接
C:\Documents and Settings\user>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 10月 29 19:19:54 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>
SQL> conn system/system
已连接。
SQL>
tnsping正常，我把相关参数文件和log文件贴如下：
sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES= (NTS)
tnsnames.ora:
FOX=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = FOX)
(SERVER = DEDICATED)
)
)
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = FOX)
(SID_NAME = FOX)
( ORACLE_HOME = C:\ oracle\product\10.2.0\db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
)
监听器状态：
C:\Documents and Settings\user>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:5
8:16
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 28-10月-2008 20:58:11
正常运行时间 0 天 0 小时 0 分 5 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
监听程序日志文件 C:\oracle\product\10.2.0\db_1\network\log\listener.log
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
服务摘要..
服务 "FOX" 包含 1 个例程。
例程 "FOX", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功
tnsping 没问题
log文件：
sqlnet.log
***********************************************************************
Fatal NI connect error 12560, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Time: 28-10月-2008 20:44:22
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS: 协议适配器错误
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12560, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Time: 28-10月-2008 20:48:14
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS: 协议适配器错误
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
listener.log：
Listener completed notification to CRS on stop
28-10月-2008 20:27:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:27:13
Copyright (c) 1991, 2005, Oracle. All rights reserved.
系统参数文件为C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
写入C:\oracle\product\10.2.0\db_1\network\log\listener.log的日志信息
写入C:\oracle\product\10.2.0\db_1\network\trace\listener.trc的跟踪信息
跟踪级别当前为0
以 pid=4704 开始
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
28-10月-2008 20:27:14 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
28-10月-2008 20:27:19 * ping * 0
28-10月-2008 20:27:23 * (CONNECT_DATA=(SERVICE_NAME=FOX)(SERVER=DEDICATED)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=FOX-D963D6D74B0)(USER=user))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=3071)) * establish * FOX * 0
28-10月-2008 20:27:27 * service_register * fox * 0
28-10月-2008 20:28:24 * service_update * fox * 0
28-10月-2008 20:33:12 * service_update * fox * 0
28-10月-2008 20:34:51 * service_update * fox * 0
28-10月-2008 20:38:06 * ping * 0
28-10月-2008 20:38:09 * service_update * fox * 0
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on stop
28-10月-2008 20:38:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:38:40
Copyright (c) 1991, 2005, Oracle. All rights reserved.
系统参数文件为C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
写入C:\oracle\product\10.2.0\db_1\network\log\listener.log的日志信息
写入C:\oracle\product\10.2.0\db_1\network\trace\listener.trc的跟踪信息
跟踪级别当前为0
以 pid=5436 开始
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
28-10月-2008 20:38:42 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
28-10月-2008 20:38:44 * ping * 0
28-10月-2008 20:38:49 * (CONNECT_DATA=(SERVICE_NAME=FOX)(SERVER=DEDICATED)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=FOX-D963D6D74B0)(USER=user))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=3293)) * establish * FOX * 0
28-10月-2008 20:39:22 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
28-10月-2008 20:39:38 * service_register * fox * 0
28-10月-2008 20:43:32 * service_update * fox * 0
28-10月-2008 20:44:22 * (CONNECT_DATA=(SERVICE_NAME=FOX)(SERVER=DEDICATED)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=FOX-D963D6D74B0)(USER=user))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=3357)) * establish * FOX * 0
28-10月-2008 20:46:38 * service_died * fox * 12547
TNS-12547: TNS: 丢失连接
28-10月-2008 20:46:51 * ping * 0
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on stop
28-10月-2008 20:46:55 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:47:21
郁闷看不出来是什么问题，已经2天了，help!!
SQL> conn system/system@fox
ERROR:
ORA-12547: TNS: 丢失连接
等待很长时间后出现ORA-12547: TNS: 丢失连接 这个错误，但是通过如下方式却可以连接
C:\Documents and Settings\user>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 10月 29 19:19:54 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>
SQL> conn system/system
已连接。
SQL>
tnsping正常，我把相关参数文件和log文件贴如下：
sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES= (NTS)
tnsnames.ora:
FOX=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = FOX)
(SERVER = DEDICATED)
)
)
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = FOX)
(SID_NAME = FOX)
( ORACLE_HOME = C:\ oracle\product\10.2.0\db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
)
监听器状态：
C:\Documents and Settings\user>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:5
8:16
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 28-10月-2008 20:58:11
正常运行时间 0 天 0 小时 0 分 5 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
监听程序日志文件 C:\oracle\product\10.2.0\db_1\network\log\listener.log
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
服务摘要..
服务 "FOX" 包含 1 个例程。
例程 "FOX", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功
tnsping 没问题
log文件：
sqlnet.log
***********************************************************************
Fatal NI connect error 12560, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Time: 28-10月-2008 20:44:22
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS: 协议适配器错误
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12560, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Time: 28-10月-2008 20:48:14
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS: 协议适配器错误
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
listener.log：
Listener completed notification to CRS on stop
28-10月-2008 20:27:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:27:13
Copyright (c) 1991, 2005, Oracle. All rights reserved.
系统参数文件为C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
写入C:\oracle\product\10.2.0\db_1\network\log\listener.log的日志信息
写入C:\oracle\product\10.2.0\db_1\network\trace\listener.trc的跟踪信息
跟踪级别当前为0
以 pid=4704 开始
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
28-10月-2008 20:27:14 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
28-10月-2008 20:27:19 * ping * 0
28-10月-2008 20:27:23 * (CONNECT_DATA=(SERVICE_NAME=FOX)(SERVER=DEDICATED)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=FOX-D963D6D74B0)(USER=user))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=3071)) * establish * FOX * 0
28-10月-2008 20:27:27 * service_register * fox * 0
28-10月-2008 20:28:24 * service_update * fox * 0
28-10月-2008 20:33:12 * service_update * fox * 0
28-10月-2008 20:34:51 * service_update * fox * 0
28-10月-2008 20:38:06 * ping * 0
28-10月-2008 20:38:09 * service_update * fox * 0
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on stop
28-10月-2008 20:38:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:38:40
Copyright (c) 1991, 2005, Oracle. All rights reserved.
系统参数文件为C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
写入C:\oracle\product\10.2.0\db_1\network\log\listener.log的日志信息
写入C:\oracle\product\10.2.0\db_1\network\trace\listener.trc的跟踪信息
跟踪级别当前为0
以 pid=5436 开始
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
28-10月-2008 20:38:42 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
28-10月-2008 20:38:44 * ping * 0
28-10月-2008 20:38:49 * (CONNECT_DATA=(SERVICE_NAME=FOX)(SERVER=DEDICATED)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=FOX-D963D6D74B0)(USER=user))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=3293)) * establish * FOX * 0
28-10月-2008 20:39:22 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
28-10月-2008 20:39:38 * service_register * fox * 0
28-10月-2008 20:43:32 * service_update * fox * 0
28-10月-2008 20:44:22 * (CONNECT_DATA=(SERVICE_NAME=FOX)(SERVER=DEDICATED)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=FOX-D963D6D74B0)(USER=user))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=3357)) * establish * FOX * 0
28-10月-2008 20:46:38 * service_died * fox * 12547
TNS-12547: TNS: 丢失连接
28-10月-2008 20:46:51 * ping * 0
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
不再监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Listener completed notification to CRS on stop
28-10月-2008 20:46:55 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=user))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-10月-2008 20:47:21
郁闷看不出来是什么问题，已经2天了，help!!
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
