ITPub博客

首页 > 数据库 > Oracle > AWR自动生成,定时推送

AWR自动生成,定时推送

原创 Oracle 作者:pennymeng 时间:2020-07-22 16:47:46 0 删除 编辑

AWR自动生成,定时推送


1. 首先编辑AWR报告生成脚本:autoawrrpt.sh


#!/bin/bash

# ********************************

# * dba_oracle_awr.sh

# ********************************

# Usage: dba_oracle_awr.sh -s [instance_name]

# -f [from time]

# -t [to time]

# -p [report type, html or text]

# -h [oracle home]

# -n [tns admin]

#

# time format: 'yyyymmdd'.

# E.g 20110304 means  Mar 04, 2011

#

#

# **********************

# get parameters

# **********************

while getopts ":s:f:t:p:h:n" opt

do

case $opt in

s) instance=$OPTARG

;;

f) from=$OPTARG

;;

t) to=$OPTARG

;;

p) type=$OPTARG

type=$(echo $type|tr "[:upper:]" "[:lower:]")

;;

h) oracle_home=$OPTARG

;;

n) tns_admin=$OPTARG

;;

'?') echo "$0: invalid option -$OPTARG">&2

exit 1

;;

esac

done

if [ "$instance" = "" ]

then

echo "instance name(-s) needed"

echo "program exiting..."

exit 1

fi

if [ "$from" = "" ]

then

echo "from time (-f} needed"

echo "program exiting..."

exit 1

fi

if [ "$to" = "" ]

then

echo "to time (-t) needed"

echo "program exiting..."

exit 1

fi

if [ ${oracle_home} = "" ]

then

echo "oracle home (-h) needed"

echo "program exiting..."

exit 1

fi

sqlplus="${oracle_home}/bin/sqlplus"

echo $sqlplus

if [ "$type" = "" ]

then

type="html"

fi

# ********************

# trim function

# ********************

function trim()

{

local result

result=`echo $1|sed 's/^ *//g' | sed 's/ *$//g'`

echo $result

}

# *******************************

# read interchange ID & passwd

# *******************************

#read_act()

#{

#echo "interchange ID: "

#read user

#echo "password: "

#stty -echo

#read pswd

#stty echo

#}

# *******************************

# get begin and end snapshot ID

# *******************************

define_dur()

{

begin_id=`$sqlplus -s /nolog<<EOF

conn /as sysdba

set pages 0

set head off

set feed off

select max(SNAP_ID) from DBA_HIST_SNAPSHOT where

BEGIN_INTERVAL_TIME<=to_date($from,'yyyymmdd');

EOF`

ret_code=$?

if [ "$ret_code" != "0" ]

then

echo "sqlplus failed with code $ret_code"

echo "program exiting..."

exit 10

fi

end_id=`$sqlplus -s /nolog<<EOF

conn /as sysdba

set pages 0

set head off

set feed off

select min(SNAP_ID) from DBA_HIST_SNAPSHOT where

END_INTERVAL_TIME>=to_date($to,'yyyymmdd');

spool off

EOF`

ret_code=$?

if [ "$ret_code" != "0" ]

then

echo "sqlplus failed with code $ret_code"

echo "program exiting..."

exit 10

fi

begin_id=$(trim ${begin_id})

end_id=$(trim ${end_id})

# echo "begin_id: $begin_id end_id: $end_id"

}

# *******************************

# generate AWR report

# *******************************

generate_awr()

{

awrsql="${oracle_home}/rdbms/admin/awrrpt.sql"

if [ ! -e $awrsql ]

then

echo "awrrpt.sql does not exist, exiting..."

exit 20

fi

tmp1_id=${begin_id}

#echo "begin_id is: $begin_id"

#echo "tmp1_id is: $tmp1_id"

#echo "end_id is: $end_id"

while [ ${tmp1_id} -lt ${end_id} ]

do

let tmp2_id=${end_id}

if [ $type = "text" ]

then

report_name=/home/oracle/report/"awrrpt_${instance}_${from}.txt"

else

report_name=/home/oracle/report/"awrrpt_${instance}_${from}.html"----report名称按需修改

fi

#echo $report_name

$sqlplus -s "/as sysdba">/dev/null<<EOF

set term off

define report_type=$type

define num_days=1

define begin_snap=${tmp1_id}

define end_snap=${tmp2_id}

define report_name=${report_name}

@${oracle_home}/rdbms/admin/awrrpt.sql

exit;

EOF

tmp1_id=${tmp2_id}

done

}

# *******************************

# main routing

# *******************************

#read_act

define_dur

generate_awr



2. 以下为:awr.sh内容(按需设置)

#!/bin/sh

source /home/oracle/.bash_profile

b=`date -d last-day +%Y%m%d`

a=$(date +"%Y%m%d")

echo $b

echo $a


/home/oracle/report/autoawrrpt.sh -s orcl(实例名) -f $b -t $a -p HTML -h /home/app/oracle/product/11.2.0

echo "AWRRPT $b"(报告名) |mail -a /home/oracle/report/"awrrpt_orcl_$b.html"(autoawrrpt.sh里面设置的报告名称) -s "orcl-AWRRPT-$b" test@qq.com


3. liunx 系统设置定时任务

30 00 * * * /home/oracle/report/awr.sh


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69976387/viewspace-2706263/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP, Oracle 11g OCM, MySQL 5.7 OCP, A member of OCMU Oracle User Group, Certificate of Aptech Certified System Master

注册时间:2020-06-03

  • 博文量
    17
  • 访问量
    8400