oracle数据库监听频道异常,数据库监听不定期出现异常故障处理

故障现象：

数据库监听不定期出现异常,从应用tnsping数据库，时间花费很长甚至连不上

故障分析处理过程：

从应用主机tnsping如下：

racdb1_scenemon$tnsping racdb_new

TNS Ping Utility for Solaris: Version

11.2.0.3.0 - Production on 19-2月

-2014 15:46:23

Copyright (c) 1997, 2011, Oracle. All rights

reserved.

已使用的参数文件:

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.11.1)(PORT =

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

(LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb)

(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY =

5))))

OK (60000 毫秒)

从数据库服务器上查看监听状态：

$ lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.3.0 -

Production on 19-FEB-2014 15:45:58

Copyright (c) 1991, 2011, Oracle. All rights

reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

TNS-12535: TNS:operation timed out

TNS-12560: TNS:protocol adapter error

TNS-00505: Operation timed out

将监听进程予以kill并重启监听：

$ ps -ef |grep tns

oracle 18504 18468 0 15:45:15 pts/10 0:00 grep

tns

oracle 24037 1 0 Nov 28 ? 6:08

/oracle/app/asm/11.2.0/grid/bin/tnslsnr LISTENER -inherit

oracle 9488 1 3 Jan 24 ? 4447:11

/oracle/app/db/product/11.2.0/db/bin/tnslsnr LISTENER -inherit

$

$ kill -9 9488

$ lsnrctl start

LSNRCTL for Solaris: Version 11.2.0.3.0 -

Production on 19-FEB-2014 15:48:22

Copyright (c) 1991, 2011, Oracle. All rights

reserved.

Starting

/oracle/app/db/product/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 11.2.0.3.0 -

Production

Log messages written to

/oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml

Listening on:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))

Connecting to

(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Solaris: Version

11.2.0.3.0 - Production

Start Date 19-FEB-2014 15:48:22

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Log File

/oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))

The listener supports no services

The command completed successfully

检查listener.log如下：

17-FEB-2014 16:02:26 *

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn)))

* (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41603)) * establish * racdb *

0

17-FEB-2014 16:02:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn)))

* (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41659)) * establish * racdb *

12518

TNS-12518: TNS:listener could not hand off

client connection

TNS-12560: TNS:protocol adapter error

TNS-00530: Protocol adapter error

Solaris Error: 24: Too many open files

<<<<<<<<<

17-FEB-2014 16:02:26 *

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn)))

* (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41652)) * establish * racdb *

0

17-FEB-2014 16:02:26 *

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn)))

* (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41669)) * establish * racdb *

0

17-FEB-2014 16:02:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn)))

* (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41682)) * establish * racdb *

12518

TNS-12518: TNS:listener could not hand off

client connection

TNS-12560: TNS:protocol adapter error

TNS-00530: Protocol adapter error

Solaris Error: 24: Too many open files

<<<<<<<<<<

Too many open files意味着Maximum Number Of Open Files Per Process达到了上限。因此listener hang住的原因可能是该limit设置过小，进一步查看硬件限制如下：

$ ulimit -Ha

time(seconds) unlimited

file(blocks) unlimited

data(kbytes) unlimited

stack(kbytes) unlimited

coredump(blocks) unlimited

nofiles(descriptors) 65536

memory(kbytes) unlimited

查看软件限制如下：

$ ulimit -Sa

time(seconds) unlimited

file(blocks) unlimited

data(kbytes) unlimited

stack(kbytes) 8192

coredump(blocks) unlimited

nofiles(descriptors) 256

memory(kbytes) unlimited

查看/etc/system如下：

*ident  "@(#)system     1.18

97/06/27 SMI" /* SVR4 1.5 */

*

* SYSTEM SPECIFICATION FILE

*

* moddir:

*

*       Set the search path for modules.  This has a format similar to the

*       csh path variable. If the module isn't

found in the first directory

*       it tries the second and so on. The

default is /kernel /usr/kernel

*

*       Example:

*               moddir: /kernel /usr/kernel

/other/modules

* root device and root

filesystem configuration:

*

*       The following may be used to override

the defaults provided by

*       the boot program:

*

*       rootfs:

Set the filesystem type of

the root.

*

*       rootdev:        Set the root device.  This should be a fully

*                       expanded physical

pathname.  The default is the

*                       physical pathname of the

device where the boot

*                       program resides.  The physical pathname is

*                       highly platform and

configuration dependent.

*

*       Example:

*               rootfs:ufs

*

rootdev:/sbus@1,f8000000/esp@0,800000/sd@3,0:a

*

*       (Swap device configuration should be specified

in /etc/vfstab.)

* exclude:

*

*       Modules appearing in the moddir path

which are NOT to be loaded,

*       even if referenced. Note that `exclude'

accepts either a module name,

*       or a filename which includes the

directory.

*

*       Examples:

*               exclude: win

*               exclude: sys/shmsys

* forceload:

*

*       Cause these modules to be loaded at boot

time, (just before mounting

*       the root filesystem) rather than at

first reference. Note that

*       forceload expects a filename which

includes the directory. Also

*       note that loading a module does not

necessarily imply that it will

*       be installed.

*

*       Example:

*               forceload: drv/foo

* set:

*

*       Set an integer variable in the kernel or

a module to a new value.

*       This facility should be used with

caution.  See system(4).

*

*       Examples:

*

*       To set variables in 'unix':

*

*               set nautopush=32

*               set maxusers=40

*

*       To set a variable named 'debug' in the

module named 'test_module'

*

*               set test_module:debug = 0x13

* Begin FJSVssf (do not

edit)

set ftrace_atboot = 1

set kmem_flags = 0x100

set kmem_lite_maxalign = 8192

* End FJSVssf (do not edit)

forceload: drv/fjpfca

* Begin MDD root info (do

not edit)

rootdev:/pseudo/md@0:0,0,blk

* End MDD root info (do not

edit)

*** Begin EMCpower added

lines *** DO NOT EDIT BELOW THIS LINE ***

forceload: drv/emcpsf

forceload: drv/sd

forceload: drv/ssd

forceload: drv/emcp

forceload: misc/emcpgpx

forceload: misc/emcpmpx

forceload: misc/emcpvlumd

forceload: misc/emcpxcrypt

forceload: misc/emcpdm

forceload: misc/emcpioc

set emcp:bPxEnableInit=1

*** End EMCpower added

lines *** DO NOT EDIT ABOVE THIS LINE ***

exec_user_stack = 1

set noexec_user_stack_log =

1

查看/etc/project如下：

system:0::::

user.root:1::::

noproject:2::::

default:3::::

group.staff:10::::

user.oracle:100::oracle::

综上所述，从listener.log的输出看，nofiles的soft limit过小，只有256，这会导致Solaris Error: 24: Too many open files错误。从/etc/system和/etc/project，我们没有看到设置了soft limit。

故障处理小结及后续建议

将oracle用户的soft limit提升为至少1024，然后重新oracle用户登录，检验ulimit合格后，重新启动数据库和监听。

具体解决办法如下：

1、在/etc/system增加以下行

set rlim_fd_max=65536

set rlim_fd_cur=4096

2、重新登录ORACLE并检验oracle用户的限制

su – oracle

ulimit -Ha

ulimit –Sa

3、重新启动数据库和监听