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

联系:手机/微信(+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. 惜分飞 说:
    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. 惜分飞 说:

    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