oracle auto awr



方法记录:
1.在数据库中创建一个目录.这是awr的报表文件输出目录.必须有oracle用户的写权限.
CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/oracle/my_script/report_file';

2.创建获取awr_report的存储过程.
  TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 09' -- 上午九点起
  TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 17' -- 下午六点结束
  通过这两个时间的指定.将生成这个时间段之间的每一个小时一份的awr报表到awr_reports_dir目录里.
   过程开始:

1. create or replace procedure auto_awr_reports
   
2. as
   
3.   l_snap_start       NUMBER ;
   
4.   l_snap_end         NUMBER ;
   
5.   l_dir              VARCHAR2(50) := 'AWR_REPORTS_DIR';
   
6.   l_last_snap        NUMBER := NULL;
   
7.   l_dbid             v$database.dbid%TYPE;
   
8.   l_instance_number  v$instance.instance_number%TYPE;
   
9.   l_file             UTL_FILE.file_type;
   
10.   l_file_name        VARCHAR(50);
    
11. BEGIN
    
12.   SELECT dbid
    
13.   INTO   l_dbid
    
14.   FROM   v$database;
    
15.   SELECT instance_number
    
16.   INTO   l_instance_number
    
17.   FROM   v$instance;
    
18.   SELECT snap_id
    
19.   INTO   l_snap_start
    
20.   FROM   DBA_HIST_SNAPSHOT
    
21.   WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24') = TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 09';
    
22.   SELECT snap_id
    
23.   INTO   l_snap_end
    
24.   FROM   DBA_HIST_SNAPSHOT
    
25.   WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24') = TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 17';
    
26.   FOR cur_snap IN (SELECT snap_id
    
27.                    FROM   dba_hist_snapshot
    
28.                    WHERE  instance_number = l_instance_number
    
29.                    AND    snap_id BETWEEN l_snap_start AND l_snap_end
    
30.                    ORDER BY snap_id)
    
31.   LOOP
    
32.     IF l_last_snap IS NOT NULL THEN
    
33.       l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w', 32767);
    
34.       FOR cur_rep IN (SELECT output
    
35.                       FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap,cur_snap.snap_id)))
    
36.       LOOP
    
37.         UTL_FILE.put_line(l_file, cur_rep.output);
    
38.       END LOOP;
    
39.       UTL_FILE.fclose(l_file);
    
40.     END IF;
    
41.     l_last_snap := cur_snap.snap_id;
    
42.   END LOOP;
    
43. EXCEPTION
    
44.   WHEN OTHERS THEN
    
45.     IF UTL_FILE.is_open(l_file) THEN
    
46.       UTL_FILE.fclose(l_file);
    
47.     END IF;
    
48.     RAISE;
    
49. END;
    
50. /

复制代码

  过程结束.


3.在系统里创建shell脚本,shell脚本里使用了mutt来发送附件.要有sendmail支持发邮件..
内容如下:

脚本开始:

1. #!/bin/bash
   
2. # ORACLE ENV.
   
3. # 自己定义你的oracle环境变量.因为要用cron来执行这个脚本.必须包含oracle的环境变量.
   
4. #
   
5. #
   
6. export_path=/oracle/my_script/report_file
   
7. # ANALYZE ORACLE ALERT LOG.
   
8. # export file /oracle/my_script/report_file/analyze_$(date +%F).log
   
9. #
   
10. export LANG=C
    
11. my_str=$(date -d "-1 day" "+%a %b %d")
    
12. alert_log_file=/oracle/app/diag/rdbms/cecis/cecis/trace/alert_cecis.log
    
13. export_analyze_file=$export_path/analyze_$(date +%F).log
    
14. grep -A 10 "$my_str" $alert_log_file | grep -A 3 -B 1 -ni "warn\|err\|ORA-\|not" >> $export_analyze_file
    
15. #
    
16. # excute procedure AUTO_AWR_REPORTS.
    
17. # report awr_report_files at time 09:00 - 18:00
    
18. #
    
19. $ORACLE_HOME/bin/sqlplus -s "/ as sysdba" >/dev/null 2>&1  <<EOF
    
20. EXEC AUTO_AWR_REPORTS;
    
21. EXIT
    
22. EOF
    
23. #
    
24. # get all analye files
    
25. # 
    
26. awr_files=$(for i in $(ls -A $export_path) ;do echo "-a $export_path/$i";done|xargs)
    
27. #
    
28. # mail to my Email. and delete analyze files.
    
29. #
    
30. mutt -s "$(date +%F)_ORACLE_REPORT" "写上你的接收EMail地址" $awr_files < /etc/sysconfig/network-scripts/ifcfg-eth0 && rm -rf /oracle/my_script/report_file/* 
    
31. #
    
32. # end
    
33. exit

复制代码

脚本结束.

4.在cron中建立这个脚本的启动时间.每天晚上3点.
$ crontab -l
0 3 * * * /oracle/my_script/analyze_alter_log.sh
这样就能每天在mail里查看前一天的每小时的awr和告警日志信息了.

7777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777

#!/bin/bash
# ORACLE ENV.
#
if [ $# -lt 1 ]
then
  echo "Usage: $0 <ORACLE_SID> " 
  exit
else
  export ORACLE_SID=$1
fi
export_path=/home/oracle/my_script/report_file/$ORACLE_SID
# ANALYZE ORACLE ALERT LOG.
# export file /home/oracle/my_script/report_file/$ORACLE_SID/analyze_$(date +%F).log
#
my_str=$(date -d "-1 day" "+%a %b %d")
alert_log_file=/u01/app/oracle/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log
export_analyze_file=$export_path/analyze_$(date +%F).log
grep -A 10 "$my_str" $alert_log_file | grep -A 3 -B 1 -ni "warn\|err\|ORA-\|not" >> $export_analyze_file
#
# excute procedure AUTO_AWR_REPORTS.
# report awr_report_files at time 09:00 - 18:00
#
#export ORACLE_SID=soatest
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" >/dev/null 2>&1  <<EOF
EXEC AUTO_AWR_REPORTS;
EXIT
EOF
#
# get all analye files
#
awr_files=$(for i in $(ls -A $export_path) ;do echo "-a $export_path/$i";done|xargs)
#
# mail to my Email. and delete analyze files.
#
mutt -s "$(date +%F)_ORACLE_REPORT" "@" $awr_files < /etc/sysconfig/network-scripts/ifcfg-eth0 && rm -rf /oracle/my_script/report_file/$ORACLE_SID/*
#
# end