天萃荷净
运维DBA反映监控到Oracle数据库的pmon和LISTENER进程负载均比较高,查看alert日志文件发现动态注册监听太多,分析原因为REMOTE_LISTENER设置不当导致
1.Oralce平台系统版本相关信息
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.oracleplus.com" from dual;
www.oracleplus.com
-------------------
2012-05-30 11:07:12
2.Oracle 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;
3.Oracle 进程负载较高的解决方案
alter system set remote_listener = 'remote_rac' scope=memory sid = 'AHUNICOM1';
alter system set remote_listener = '' scope=memory sid = 'AHUNICOM1';
--然后重启节点,pmon和监听恢复正常
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【案例】Oracle进程异常 pmon和LISTENER进程负载均比较高的解决办法