oracle数据库进程pmon,【案例】Oracle进程异常 pmon和LISTENER进程负载均比较高的解决办法...

天萃荷净

运维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进程负载均比较高的解决办法