解决一次Listener挂掉的问题

By On 2010/06/04 · Leave a Comment · In Oracle

在一个大型系统开发环境，有人反映无法连接到数据库，经检查判断是listener的问题，找到对应的进程杀掉后重启listener 就解决了。

开始怀疑是只是单个session的问题：

[oracle@erpdevdb bdump]$ sqlplus banping/banping

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 17 10:23:07 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> select machine from v$session;

MACHINE
----------------------------------------------------------------
servicedesk
localhost.localdomain
x-9ee5775925
erpdevdb
erp-app
WORKGROUP\DEV00
WORKGROUP\ERP_DEV003

WORKGROUP\DEV00
erp-app
servicedesk
servicedesk
localhost.localdomain
erpdevdb

......

39 rows selected.

SQL> select sql_id from v$session where machine='x-9ee5775925';

SQL_ID
-------------
2vwg9dh3v6rkg

SQL> select sql_text from v$sql where sql_id='2vwg9dh3v6rkg';

no rows selected

SQL> select distinct user from v$session;

USER
------------------------------
banping

后来意识到是监听的问题，查看监听果然没有反应：

[oracle@erpdevdb bdump]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-MAY-2010 10:31:40

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

查找到listener 的进程并杀死进程：

[oracle@erpdevdb admin]$ ps -ef|grep ora

......

oracle   13720 28474  0 May15 ?        00:00:00 /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

[oracle@erpdevdb admin]$ kill -9 13720

重新启动监听：

[oracle@erpdevdb bdump]$ lsnrctl stat

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-MAY-2010 10:49:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=erpdevdb)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@erpdevdb bdump]$
[oracle@erpdevdb bdump]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-MAY-2010 10:49:44

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=erpdevdb)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
LSNRCTL> start
Starting /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                17-MAY-2010 10:49:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdevdb.banping.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
LSNRCTL> exit

[oracle@erpdevdb admin]$ ps -ef|grep tnslsnr
oracle    5021     1  0 10:49 ?        00:00:00 /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

查看sqlnet.log记录：

Fatal NI connect error 12537, connecting to:
(LOCAL=NO)

VERSION INFORMATION:
TNS for Linux: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production
Time: 17-MAY-2010 10:49:23
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

查看listener.log 记录

17-MAY-2010 10:49:23 * (CONNECT_DATA=(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM)(UR=A)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45772)) * establish * +ASM * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe
17-MAY-2010 10:49:23 * service_update * erpoptm * 0
17-MAY-2010 10:49:23 * service_update * erpwh * 0
17-MAY-2010 10:49:23 * service_update * devdb * 0
17-MAY-2010 10:49:23 * service_update * erptest * 0
17-MAY-2010 10:49:23 * service_update * papererpdb * 0
17-MAY-2010 10:49:23 * service_update * erpstudydb * 0
17-MAY-2010 10:49:23 * service_update * +ASM * 0
17-MAY-2010 10:49:23 * service_update * erpoptm * 0
17-MAY-2010 10:49:23 * service_update * erpwh * 0
17-MAY-2010 10:49:23 * service_update * devdb * 0
17-MAY-2010 10:49:23 * service_update * papererpdb * 0
17-MAY-2010 10:49:23 * service_update * erpstudydb * 0
17-MAY-2010 10:49:23 * ping * 0
17-MAY-2010 10:49:23 * service_update * erptest * 0
17-MAY-2010 10:49:23 * service_update * +ASM * 0
17-MAY-2010 10:49:23 * ping * 0
17-MAY-2010 10:49:23 * ping * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SID=erptest)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45907)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM)(UR=A)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45905)) * establish * +ASM * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=erptest)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45863)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SID=erptest)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45774)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM)(UR=A)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46043)) * establish * +ASM * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=erptest)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46005)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=erptest)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46084)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SID=erptest)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46045)) * establish * erptest * 0
17-MAY-2010 10:49:23 * service_update * +ASM * 0

TNSLSNR for Linux: Version 10.2.0.1.0 - Production on 17-MAY-2010 10:49:52

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

System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Trace information written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/trace/li

 
分享这篇文章
Facebook Twitter Delicious StumbleUpon Digg

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性： <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>