- 论坛徽章:
- 0
|
前段时间用shell写了个脚本获取awr_report..今天重新整理了下.
改用存储过程来获得awr_report,用shell分析alert_log,并将他们一并发送到自己的mail中.
方法记录:
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目录里.
过程开始:
- create or replace procedure auto_awr_reports
- as
- l_snap_start NUMBER ;
- l_snap_end NUMBER ;
- l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR';
- l_last_snap NUMBER := NULL;
- l_dbid v$database.dbid%TYPE;
- l_instance_number v$instance.instance_number%TYPE;
- l_file UTL_FILE.file_type;
- l_file_name VARCHAR(50);
- BEGIN
- SELECT dbid
- INTO l_dbid
- FROM v$database;
- SELECT instance_number
- INTO l_instance_number
- FROM v$instance;
- SELECT snap_id
- INTO l_snap_start
- FROM DBA_HIST_SNAPSHOT
- WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24') = TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 09';
- SELECT snap_id
- INTO l_snap_end
- FROM DBA_HIST_SNAPSHOT
- WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24') = TO_CHAR(SYSDATE-1,'YYYY-MM-DD')||' 17';
- FOR cur_snap IN (SELECT snap_id
- FROM dba_hist_snapshot
- WHERE instance_number = l_instance_number
- AND snap_id BETWEEN l_snap_start AND l_snap_end
- ORDER BY snap_id)
- LOOP
- IF l_last_snap IS NOT NULL THEN
- l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w', 32767);
- FOR cur_rep IN (SELECT output
- FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap,cur_snap.snap_id)))
- LOOP
- UTL_FILE.put_line(l_file, cur_rep.output);
- END LOOP;
- UTL_FILE.fclose(l_file);
- END IF;
- l_last_snap := cur_snap.snap_id;
- END LOOP;
- EXCEPTION
- WHEN OTHERS THEN
- IF UTL_FILE.is_open(l_file) THEN
- UTL_FILE.fclose(l_file);
- END IF;
- RAISE;
- END;
- /
复制代码
过程结束.
3.在系统里创建shell脚本,shell脚本里使用了mutt来发送附件.要有sendmail支持发邮件..
内容如下:
脚本开始:
- #!/bin/bash
- # ORACLE ENV.
- # 自己定义你的oracle环境变量.因为要用cron来执行这个脚本.必须包含oracle的环境变量.
- #
- #
- export_path=/oracle/my_script/report_file
- # ANALYZE ORACLE ALERT LOG.
- # export file /oracle/my_script/report_file/analyze_$(date +%F).log
- #
- export LANG=C
- my_str=$(date -d "-1 day" "+%a %b %d")
- alert_log_file=/oracle/app/diag/rdbms/cecis/cecis/trace/alert_cecis.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
- #
- $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" "写上你的接收EMail地址" $awr_files < /etc/sysconfig/network-scripts/ifcfg-eth0 && rm -rf /oracle/my_script/report_file/*
- #
- # end
- exit
复制代码
脚本结束.
4.在cron中建立这个脚本的启动时间.每天晚上3点.
$ crontab -l
0 3 * * * /oracle/my_script/analyze_alter_log.sh
这样就能每天在mail里查看前一天的每小时的awr和告警日志信息了.
[ 本帖最后由 Seker 于 2009-1-6 13:19 编辑 ] |
|