Wednesday, March 23, 2011

generate AWR automatic

hmm so , there was a performance problem in my prod server,
which pushed me to create a baseline of whole month and we never had it. To create that i needed AWR report of every day , every hour which made it even hard for me to take snapshot of it.

To create AWR manually needs a lot of times and obviously LABOR.

so i created my favorite script which is derived from BASH shell , where i had aix 5.1 and never had bash 3.x , which dont allowed `seq` in for loop.

so i wrote in (ksh). which is as below.

#!/bin/ksh
if [ $# != 4 ]
then
echo "Syntax awr_generation.sh dbid instanceId startsnapid endsnapid"
exit 1
fi
l_dbid=$1
l_instid=$2
l_start_snapid=$3
let l_end_snapid=$4-1
i=${l_start_snapid}
until [[ $i = $l_end_snapid ]];
do
let l_next_snapid=$i+1;
l_awr_log_file="/home/oracle/awr/awrrpt_${2}_${i}_${l_next_snapid}.log"
sqlplus -s / as sysdba << EOF set head off set pages 0 set lines 132 set echo off set feedback off spool $l_awr_log_file SELECT output FROM TABLE (dbms_workload_repository.awr_report_html
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOF
let i=i+1
done