oracle 不产生awr快照,awr快照无法生成

现网数据库，无法生成awr快照，数据库版本为11.1.0.7.0,操作系统为AIX 6.1

1、snapshot 未生成时mmon trc中显示

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

2、查看alert日志，在线日志切换非常频繁，在线日志每个成员的大小为2G，共16组，无报错

Tue Dec 03 10:01:02 2013

Thread 1 advanced to log sequence 29504 (LGWR switch)

Current log# 17 seq# 29504 mem# 0: /dev/rvdata_ora_red39

Current log# 17 seq# 29504 mem# 1: /dev/rvdata_ora_red40

Tue Dec 03 10:02:27 2013

Thread 1 advanced to log sequence 29505 (LGWR switch)

Current log# 21 seq# 29505 mem# 0: /dev/rvdata_ora_red41

Current log# 21 seq# 29505 mem# 1: /dev/rvdata_ora_red42

Tue Dec 03 10:03:10 2013

Thread 1 advanced to log sequence 29506 (LGWR switch)

Current log# 22 seq# 29506 mem# 0: /dev/rvdata_ora_red43

Current log# 22 seq# 29506 mem# 1: /dev/rvdata_ora_red44

Tue Dec 03 10:03:57 2013

Thread 1 advanced to log sequence 29507 (LGWR switch)

Current log# 23 seq# 29507 mem# 0: /dev/rvdata_ora_red45

Current log# 23 seq# 29507 mem# 1: /dev/rvdata_ora_red46

3、杀掉mmon进程，mmon进程自动拉起后，观察awr快照仍旧没有生成

$ ps -ef | grep ora_mmon | grep -v grep | awk '{print $2}' | xargs kill -9

4、AWR report not getting generated hourly可参考如下链接文章

https://forums.oracle.com/thread/2153562

If the system is so over-loaded that it takes over 15 minutes to gather statistics or other MMON tasks,

this error is expected.It is a functionality enhancement in 11g, as it prevents MMON from locking resources

those other processes might be waiting for. In 10g , mmon slaves are allowed to run indefinitely.

This is at database level, as many users are in contention for resources when load is high. This is a new code

that will kill the MMON slave if it takes too long to complete so that normal sessions do not have to wait for

these resources.

Solution

If the load on the system is extremely high, this is expected, and error can be ignored.

从日志看，存在大量的Slave action has been temporarily suspended，属于11g新特性，当系统处于overload状态时，MMON收集统计信息超过15分钟，则会终止该任务(10g会无限延期)。所以该问题是系统性能不足导致。

5、既然不能自动生成awr快照，那就手动创建快照，编写SHELL脚本，部署定时任务，暂时解决awr快照无法生成问题，找出AWR报告中有问题的SQL，由业务部件进行优化

设置每小时的15,30,45分钟生成awr快照，awr快照自动生成时间为每小时，只所以这样设置，是为了便于查看awr快照何时自动生成

$ cat /home/oracle/fly/shell/snapshot_create.sh

#!/bin/ksh

export ORACLE_BASE=/u01/app/oracle

export ORACLE_SID=ora11g

export ORACLE_HOME=/u01/app/oracle/products

/u01/app/oracle/products/bin/sqlplus /nolog<

conn /as sysdba

begin

dbms_workload_repository.create_snapshot();

end;

/

quit

EOF

$ crontab -l

15,30,45 * * * * /home/oracle/fly/shell/snapshot_create.sh