今天某資料庫突然報如下錯誤：
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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-rpt2)(PORT=1521)))
....無響應

[@more@]

[oracle@test-rpt2 ~]$ lsnrctl reload

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

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

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

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

[oracle@test-rpt2 ~]$ lsnrctl start

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

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

> 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>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 毫秒)

TNS-12570 TNS:packet reader failure

相關文章