连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常

发表于 2012 年 5 月 30 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

平台系统版本相关信息

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter cluster;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string      192.168.16.11

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;

www.xifenfei.com
-------------------
2012-05-30 11:07:12

pmon和监听负载
pmon和LISTENER进程负载均比较高

  PID     %CPU ResSize    Char Command    
10617230  72.9  143924 21014  ora_pmon_ahunicom1  
22675560  49.9  142000  1547  oracleahunicom1 (LOCAL=NO)
 5243206  30.6   49728  2579  /oracle10/app/product/db/10.2.0/bin/tnslsnr LISTENER -inherit

监听日志
每秒钟很多类此pmon注册监听信息

27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0

通过这两点可以确定是因为pmon在不停的动态注册监听导致监听日志,pmon,listener进程异常

查询MOS[ID 982068.1]
问题原因

After altering the value of the parameter REMOTE_LISTENER, 
excessive CPU is seen for the TNS listener process (TNSLSNR) and the listener.log file grows rapidly. 
Alert log confirms the REMOTE_LISTENER parameter in the SPFILE was altered.
Listener.log shows continuous service_update triggered from PMON to the TNS listener, 100's per second. 
REMOTE_LISTENER had been set to null twice

查询alert日志,果真发现:
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH SID='AHUNICOM1';
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;

解决方案

alter system set remote_listener = 'remote_rac' scope=memory sid = 'AHUNICOM1';
alter system set remote_listener = '' scope=memory sid = 'AHUNICOM1';
--然后重启节点,pmon和监听恢复正常
此条目发表在 Oracle 监听 分类目录。将固定链接加入收藏夹。

《连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常》有 2 条评论

  1. 惜分飞 说：
    2012 年 5 月 30 日11:21 
    Hdr: 9235880 11.1.0.7 NET 11.1.0.7 PRODID-115 PORTID-226 6836609
Abstract: ALTER OF  REMOTE_LISTENER PARAMETER CAUSES SERVICE_UPDATE SPIN

*** 12/22/09 08:38 am ***

PROBLEM: 
11.1.0.7 four Node RAC exadata system, Linux x86-64

Altering the value of REMOTE_LISTENER with a short delay inbetween, 

1.ALTER SYSTEM SET remote_listener='' SCOPE=MEMORY 
2.Wait few minutes
3.ALTER SYSTEM SET remote_listener='' SCOPE=SPFILE 
or
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH
      
Causes PMON to to send service updates continuously to the TNS listener. 
In the region of 300+ updates within a second.
This causes CPU increase for the TNS listener process and the listener.log to 
fill quickly.

DIAGNOSTIC ANALYSIS: 

Alert.log

Wed Dec 16 11:32:45 2009
ALTER SYSTEM SET remote_listener='' SCOPE=MEMORY;
Wed Dec 16 11:34:23 2009
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;

PMON Trace via event 10257 

*** 11:34:24.394
kmmlrl: update remote_listener
kmmlrl: 56 processes
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0

Listener.log

16-DEC-2009 11:34:24 * service_update * dmis0021 * 0
16-DEC-2009 11:34:24 * service_update * dmis0021 * 0
16-DEC-2009 11:34:24 * service_update * dmis0021 * 0
16-DEC-2009 11:34:24 * service_update * dmis0021 * 0
16-DEC-2009 11:34:24 * service_update * dmis0021 * 0
16-DEC-2009 11:34:24 * service_update * dmis0021 * 0
16-DEC-2009 11:34:24 * service_update * dmis0021 * 0
16-DEC-2009 11:34:24 * service_update * dmis0021 * 0

WORKAROUND: 
If the commands are run close together, within seconds, problems has yet to 
reproduce.
Run ALTER SYSTEM command with SCOPE=BOTH straight away and the problem does
not reproduce.

RELATED BUGS: 
None found

REPRODUCIBILITY: 
Customer can reproduce on 2 databases, one production, one test, on same RAC 
cluster.
Unable to reproduce inhouse, tested 11.1.0.6/11.1.0.7 standalone/RAC and 
Exadata systems

TEST CASE: 
Unable to reproduce inhouse

STACK TRACE: 

#0  0x0000003bd62c8fdf in poll () from /lib64/libc.so.6
#1  0x000000000348dbbf in ntevpque ()
#2  0x000000000348a0b8 in ntevque ()
#3  0x000000000341fb3c in nsevwait ()
#4  0x0000000000a29503 in ksnwait ()
#5  0x0000000007943125 in ksliwat ()
#6  0x0000000007941050 in kslwaitctx ()
#7  0x0000000000ac29ca in ksuclnwt ()
#8  0x0000000000ac16bf in ksucln ()
#9  0x0000000001c37b5b in ksbrdp ()
#10 0x0000000001dcc2dd in opirip ()
#11 0x0000000001134ec2 in opidrv ()
#12 0x000000000183f2c2 in sou2o ()
#13 0x0000000000974eb5 in opimai_real ()
#14 0x0000000001844879 in ssthrdmain ()
#15 0x0000000000974d5f in main ()
  2. 惜分飞 说：
    2012 年 5 月 30 日11:20

    After REMOTE_LISTENER change, excessive CPU seen for TNS listener process

    Applies to:
Oracle Net Services - Version: 11.1.0.6 - Release: 11.1
Information in this document applies to any platform.
11.1 databases onwards

Symptoms
After altering the value of the parameter REMOTE_LISTENER, 
excessive CPU is seen for the TNS listener process (TNSLSNR) and the listener.log file grows rapidly. 
Alert log confirms the REMOTE_LISTENER parameter in the SPFILE was altered.
Listener.log shows continuous service_update triggered from PMON to the TNS listener, 100's per second. 
REMOTE_LISTENER had been set to null twice

Listener.log example section
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0
16-DEC-2009 11:34:24 * service_update * orcl01 * 0

PMON trace
*** 2009-12-16 11:34:24.394
kmmlrl: update remote_listener
kmmlrl: 56 processes
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0
kmmlrl: update remote_listener
kmmlrl: nsgr update returned 0
kmmlrl: nsgr register returned 0

Alert log
Wed Dec 16 11:32:45 2009
ALTER SYSTEM SET remote_listener='' SCOPE=MEMORY;
Wed Dec 16 11:34:23 2009
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;

Changes
REMOTE_LISTENER parameter value changed to null value

Cause
Bug 9235880Alter of REMOTE_LISTENER parameter causes service update spinn

Solution
Bug is being worked on by development. 
Do not set REMOTE_LISTENER to Null if already Null

Workarounds
If REMOTE_LISTENER is required to by Null, set to value before setting to Null
ALTER SYSTEM SET remote_listener='example.com' SCOPE=BOTH;
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;
Or set to a false value
ALTER SYSTEM SET remote_listener='example2.com' SCOPE=BOTH;
Example2 not being a valid net-service-name entry