Oracle AWR报告自动生成异常

监控平台收集不到wrh$_tablespace_space_usage表数据。

awr报告没有任何快照信息。

alter日志发现报错：Suspending MMON slave action kewrmafsa_ for 82800 seconds

MMON进程trace文件报错如下：

Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
- Slave action had prior policy violations.
Unknown return code: 101

尝试手动生成AWR快照。

exec  dbms_workload_repository.create_snapshot();

执行卡住。

观察v$session，会话卡在下述SQL；

insert into wrh$_sql_bind_metadata (dbid, con_dbid, snap_id, sql_id, name, position, dup_position, datatype, datatype_string, character_sid, precision, scale, max_length ) SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */ :dbid, :srcdbid con_dbid, :lah_snap_id, bnd.sql_id, name, position, dup_posi

mos（2226216.1）查看资料：

给出以下解决方案：

原因：View v$sqlbind_capture corresponds to fixed table X$KQLFBC table which is mainly used to store variables associated with the binding of data.  This error can be noticed in large databases using large amount of binding variables.

措施：

1. Collect statistics on following fixed table:

SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');

PL/SQL procedure successfully completed.

Or

2. Restarting the database will release of X$KQLFBC table data

Or

3. Flush shared_pool on a regular basis

这个库负载很低我选择了alter system flush shared_pool;

清空share pool后，再次执行exec  dbms_workload_repository.create_snapshot()，执行成功。

然后重启了MMON。

如下操作：

alter system enable restricted session;
alter system disable restricted session;

一小时后观察，发现awr并未自动生成，还存在问题。

继续查找mos（2294282.1）发现类似的问题，执行下述操作：

And execute the following command to check MMON suspension status:

sqlplus / as sysdba
SQL> oradebug unit_test kebm_dmp_slv_attrs kewrmafsa_

Status: 3
Flags: 0
Runtime limit: 900
CPU time limit: 300
Violations: 3
Suspended until: 1412107412 <<<---- not zero indicates that MMON is still suspended.

And execute the following command to lift MMON suspension status if necessary.