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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：连续两次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和监听恢复正常

One thought on “连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常”

  1. 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
    回复
  2. 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 ()
    回复

发表评论

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

1 × 1 =