检查数据库发现客户有一套核心的ADG库smon进程负载异常,单进程一直持有cpu 100%

[oracle@q9adg01 trace]$ top -c
top - 14:00:14 up 83 days, 21:39,  4 users,  load average: 10.34, 11.55, 11.25
Tasks: 1162 total,   3 running, 1157 sleeping,   0 stopped,   2 zombie
Cpu(s):  1.7%us,  1.2%sy,  0.0%ni, 86.2%id, 10.7%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  264253752k total, 200445076k used, 63808676k free,   757684k buffers
Swap: 33554424k total,        0k used, 33554424k free,  6529220k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5707 oracle    25   0  150g  20m  16m R 99.9  0.0  14273:00 ora_smon_q9db1
 5285 oracle    16   0 13564 1952  820 R 31.5  0.0   0:02.49 top -c
 5713 oracle    18   0  150g  20m  17m S  5.3  0.0 410:01.33 ora_asmb_q9db1
 5821 oracle    15   0  150g  23m  17m S  5.3  0.0   4883:29 ora_lck0_q9db1
 7596 oracle    15   0  150g  69m  37m S  5.3  0.0   5368:28 ora_pr00_q9db1
[oracle@q9adg02 ~]$ top -c
top - 14:00:03 up 84 days, 19:36,  3 users,  load average: 6.46, 6.96, 6.76
Tasks: 1045 total,   5 running, 1040 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.8%us,  1.0%sy,  0.0%ni, 93.4%id,  3.7%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  264253752k total, 196879216k used, 67374536k free,   425320k buffers
Swap: 33554424k total,        0k used, 33554424k free,  4727836k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
11615 oracle    25   0  150g  16m  14m R 100.0  0.0  14272:55 ora_smon_q9db2
18173 oracle    16   0  150g  73m  37m D 18.6  0.0  24:33.91 oracleq9db2 (LOCAL=NO)
 6561 oracle    15   0  150g  31m  25m R 12.2  0.0   0:48.50 oracleq9db2 (LOCAL=NO)

数据库版本和patch信息

14:18:05 sys@Q9DB>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>  SELECT INST_ID,DATABASE_ROLE,OPEN_MODE FROM GV$DATABASE;
   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         2 PHYSICAL STANDBY READ ONLY WITH APPLY
         1 PHYSICAL STANDBY READ ONLY WITH APPLY
SQL >select inst_id,STARTUP_TIME from gv$instance;
   INST_ID STARTUP_T
---------- ---------
         2 01-NOV-13
         1 01-NOV-13
[oracle@q9adg01 trace]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch  lspatches
16056266;Database Patch Set Update : 11.2.0.3.6 (16056266)
16315641;Grid Infrastructure Patch Set Update : 11.2.0.3.6 (16083653)

SYSAUX表空间增加数据文件

SQL> select ts# from v$tablespace where name='SYSAUX';
       TS#
----------
         2
SQL> select file#,name,creation_time from v$datafile where ts#=2;
     FILE# NAME                                               CREATION_
---------- -------------------------------------------------- ---------
         3 +DATA/q9db/datafile/sysaux.1412.818566605          12-MAR-08
       151 +DATA/q9db/datafile/sysaux.1431.818566885          26-MAR-12
       221 +DATA/q9db/datafile/sysaux.828.818547945           16-APR-12
      1744 +DATA/q9db_adg/datafile/sysaux.2050.835459505      29-DEC-13

核对数据库确实在2013年12月29日对SYSAUX表空间增加了数据文件而且未重启数据库,触发Bug 16427872 Standby SMON spins on CPU after add/drop SYSAUX datafile on primary

Bug 16427872 Standby SMON spins on CPU after add/drop SYSAUX datafile on primary
在12.1.0.1中修复,在未修复前增加/删除sysaux的数据文件后,通过重启实例来解决该问题