今天某数据库突然报如下错误：
TNS-12570 TNS:packet reader failure

想观察以下监听程序状态，但是一直没有响应：
[oracle@test-rpt2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-OCT-2013 19:12:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-rpt2)(PORT=1521)))
....无响应

[oracle@test-rpt2 ~]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-OCT-2013 19:13:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-rpt2)(PORT=1521)))

....无响应

[oracle@test-rpt2 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-OCT-2013 19:14:54

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-rpt2)(PORT=1521)))
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 104: Connection reset by peer
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-01153: Failed to process string:
NL-00313: null pointer or null size
[oracle@test-rpt2 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-OCT-2013 19:20:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-rpt2)(PORT=1521)))
....无响应

因此通过ps命令检查了监听相关进程在OS层面的状态,有两个进程，依次kill后重启监听程序：

[oracle@test-rpt2 ~]$ ps -ef |grep ls
oracle 967 27778 0 14:36 ? 00:00:00 /oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
oracle 14191 29226 0 19:22 pts/4 00:00:00 grep ls
oracle 27778 1 0 Sep11 ? 00:01:54 /oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
[oracle@test-rpt2 ~]$ ps -ef |grep ls
oracle 967 27778 0 14:36 ? 00:00:00 /oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
oracle 14193 29226 0 19:22 pts/4 00:00:00 grep ls
oracle 27778 1 0 Sep11 ? 00:01:54 /oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
[oracle@test-rpt2 ~]$ kill -9 967
[oracle@test-rpt2 ~]$ ps -ef |grep ls
oracle 14783 29226 0 19:22 pts/4 00:00:00 grep ls
oracle 27778 1 0 Sep11 ? 00:01:54 /oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
[oracle@test-rpt2 ~]$ kill -9 27778
[oracle@test-rpt2 ~]$ ps -ef |grep ls
oracle 15599 29226 0 19:22 pts/4 00:00:00 grep ls

[oracle@test-rpt2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-OCT-2013 19:21:16

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

[oracle@test-rpt2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-OCT-2013 19:22:49

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

Starting /oracle/product/10.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/product/10.2.0/network/admin/listener.ora
Log messages written to /oracle/product/10.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-rpt2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-rpt2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 07-OCT-2013 19:22:51
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /oracle/product/10.2.0/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-rpt2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle@test-rpt2 ~]$ ps -ef|grep ls
oracle 9294 26127 0 19:52 pts/3 00:00:00 grep ls
oracle 15773 1 0 19:22 ? 00:00:00 /oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit

再次连接时发现连接错误提示号变成如下：
C:UsersAdministrator>sqlplus /nolog
SQL> conn system/testrpt@testrpt
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

针对ORA-12514错误，在listener.ora文件里增加了静态监听，并重新加载监听：

[oracle@test-rpt2 admin]$ more listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = testrpt)
(ORACLE_HOME = /oracle/product/10.2.0)
(SID_NAME = testrpt)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test-rpt2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

[oracle@test-rpt2 ~]$ sqlplus / as sysdba

sys@testrpt> alter system register;

System altered.

但是问题依然存在，而且偶尔出现ORA-12560错误：
SQL> conn system/testrpt
ERROR:
ORA-12560: TNS: 协议适配器错误试了一下其他的客户端连接发现正常。

找台同事的电脑连接，发现连接成功，看来ORA-12514错误时跟我的客户端有关，仔细检查一下本地的tnsnames.ora文件发现里头两个重复testrpt网络别名，而且其中一个所指定service_name刚好不是testrpt，把这个多余的删除后可以正常连接。

C:UsersAdministrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 10月 8 11:36:43 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn system/testrpt@testRPT
已连接。

C:UsersAdministrator>tnsping testrpt
TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 08-10月-
2013 11:36:37
Copyright (c) 1997, 2006, Oracle. All rights reserved.
已使用的参数文件:
D:oracleproduct10.2.0db_1networkadminsqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 128.129.99.24)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testrpt)))
OK (20 毫秒)