自动生成awr报告并发送邮件

自动生成awr报告并发送邮件
第一种,不稳定

#!/bin/bash
export oracle_sid=$1
export OracLE_BASE=/Oracle/Oracle
export OracLE_HOME=/Oracle/Oracle/11.2.0.4
cd /home/Oracle/awr_static
TIME=`date -d -3hour +"%H"` #12点跑,查9点-10点的AWR
AWR_FORMAT=HTML
NUM_DAYS=1
MIN_SNAP_ID=`$OracLE_HOME/bin/sqlplus -S "/as sysdba" << EOF
set heading off trimspool on FeedBACk off
SELECT trim(max(snap_ID)) FROM DBA_HIST_SNAPSHOT where TO_CHAR(begin_interval_time,'hh24')=$TIME;
EOF`
MAX_SNAP_ID=0
MAX_SNAP_ID=`expr $MIN_SNAP_ID + 1`
AWR_LOG=/home/Oracle/awr_static/AWR-$oracle_sid-`date '+%Y%m%d_%H'`.HTML

echo -e "$AWR_FORMAT\n$NUM_DAYS\n$MIN_SNAP_ID$MAX_SNAP_ID\n$AWR_LOG\n"|($OracLE_HOME/bin/sqlplus -S "/as sysdba" @$OracLE_HOME/rdbms/admin/awrrpt.sql) > /dev/null


TO_MAIL=xxx@xx.com
echo "awr static" |/usr/bin/mutt -s "$1 AWR static" $TO_MAIL -a $AWR_LOG -a $DB_HEALTHchecK_file -R

rm *.HTML

0 12 * * 5 /bin/sh /home/Oracle/awr_static/awr_static.sh prod1

第二种,建议选择这个

#!/bin/bash
export oracle_sid=$1
export OracLE_BASE=/Oracle/Oracle
export OracLE_HOME=/Oracle/Oracle/11.2.0.4
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LANG=en_US.UTF-8
cd /home/Oracle/awr_static
TIME=`date -d -3hour +"%H"`
AWR_FORMAT=HTML
NUM_DAYS=1
MIN_SNAP_ID=`$OracLE_HOME/bin/sqlplus -S "/as sysdba" << EOF
set heading off trimspool on FeedBACk off
SELECT trim(max(snap_ID)) FROM DBA_HIST_SNAPSHOT where TO_CHAR(begin_interval_time,'hh24')=$TIME;
EOF`
MAX_SNAP_ID=0
MAX_SNAP_ID=`expr $MIN_SNAP_ID + 1`
AWR_LOG=/home/Oracle/awr_static/AWR-$oracle_sid-`date '+%Y%m%d_%H'`.HTML
DB_ID=`$OracLE_HOME/bin/sqlplus -S "/as sysdba" << EOF
set heading off trimspool on FeedBACk off
SELECT DBID FROM V\\\$DATABASE;
EOF`
INST_ID=`$OracLE_HOME/bin/sqlplus -S "/as sysdba" << EOF
set heading off trimspool on FeedBACk off
SELEct instance_number from v\\\$instance;
EOF`

$OracLE_HOME/bin/sqlplus -S "/as sysdba"<<!
set trimspool on FeedBACk off termout off pagesize 0 heading off
spool $AWR_LOG
SELECT * FROM table(DBMS_workload_repository.awr_report_HTML($DB_ID,$INST_ID,$MIN_SNAP_ID,$MAX_SNAP_ID));
spool off
exit;
!

TO_MAIL=fanboshi@uuzz.com,songqi@uuzz.com
echo "awr static" |/usr/bin/mutt -s "$1 AWR static" $TO_MAIL -a $AWR_LOG 

#rm $AWR_LOG