[Shell] 自動生成oracle awr report並mail出來

tolilong發表於2016-03-07
Shell script如下:
[mon@oracle6 source]$ more gen_oracle_awr 
#!/bin/bash
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=xxxxxxx
AWRHOME=/home/mon/awr

#interval=8

maxmin=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF # >> /home/mon/log/gen_oracle_awr.log #if general output to log,the maxmin will not get the value.
conn mon/mon;
set heading off pagesize 0 feedback off verify off echo off;
select max(snap_id),min(snap_id) from dba_hist_snapshot where begin_interval_time>=sysdate-1 and begin_interval_time<=sysdate-1+8/24;
exit;
EOF`

#if [ -z "$maxmin" ]; then
#   echo "no rows returned from database"
#   exit 0
#else
#   echo $maxmin
#fi

max=`echo $maxmin | awk '{print $1}'`
min=`echo $maxmin | awk '{print $2}'`
#echo $max
#echo $min

rm -rf $AWRHOME/awrrpt_1_${min}_${max}.html

$ORACLE_HOME/bin/sqlplus -s /nolog << EOF >> /dev/null
conn / as sysdba;
@?/rdbms/admin/awrrpt.sql;
html
2
$min
$max
$AWRHOME/awrrpt_1_${min}_${max}.html
EOF

cat $AWRHOME/awrrpt_1_${min}_${max}.html | mutt -s "$ORACLE_SID Database AWR Report" -e "set content_type=text/html" tolilong@163.com -a $AWRHOME/awrrpt_1_${min}_${max}.html


新增crontab job
###############general awr#######################################
15 15 * * * /home/mon/source/gen_oracle_awr > /dev/null 2>&1


注意點:
1.maxmin=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF # >> /home/mon/log/gen_oracle_awr.log的時候,不能輸出到log,否則maxmin獲取不到資料
2.以mon賬號執行的時候conn / as sysdba會提示ORA-01031: insufficient privileges沒有許可權
在os上執行如下命令即可了。
[root@oracle6 source]# usermod -G dba mon
3.ORA-12547: TNS:lost contact
[oracle@topaz bin]$ ls -l oracle
-rwxrwxr-x 1 oracle oinstall 210824714 May  4  2014 oracle
[oracle@topaz bin]$ 
[oracle@topaz bin]$ chmod 6751 oracle
[oracle@topaz bin]$ ls -l oracle

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2051017/,如需轉載,請註明出處,否則將追究法律責任。

相關文章