grab awr report

sedikit automasi gather awr report dgn bantuan ksh…. cukup inputan jumlah hari saja, script ini akan mengambil semua awr report dari sysdate mundur sejumlah hari yg ditentukan…..

#!/bin/ksh

if [ $# -ne 5 ]
then
 echo “penggunaan awrsimple.sh <jumlah hari> <lokasi directory awr> <nomor instance> <username> <tns alias>”
 exit
fi

num_days=$1
awrloc=$2
instnum=$3
username=$4
tns=$5

exec 4>/dev/tty

function getpass
{
        typeset prompt=$1
        typeset backspace=$(echo  \\b\\c)
        typeset enter=$(echo \\r\\c)
        typeset savesetting=$(stty -g)
        typeset keystroke password n i reading result
        n=0
        echo “${prompt}”\\c >&4
        stty -echo -icrnl -icanon min 1 time 0
        reading=1
        while ((reading)) ; do
                keystroke=$(dd bs=1 count=1 2>/dev/null)
                case $keystroke in
                $enter)
                        reading=0
                        ;;
                $backspace)
                        if ((n)) ; then
                                echo “${backspace} ${backspace}”\\c >&4
                                ((n=n-1))
                        fi
                        ;;
                *)
                        echo \*\\c >&4
                        data[n]=$keystroke
                        ((n=n+1))
                esac
        done
        stty “$savesetting”
        echo >&4
        result=”"
        i=0
        while  ((i<n)) ; do
                result=”${result}${data[i]}”
                ((i=i+1))
        done
        echo $result
        return 0
}
passwd=$(getpass “password : “)

sqlplus -s $username/$passwd@$tns as sysdba <<EOF > log1.txt

set serveroutput on
set lines 125
set pages 1000
set feedback off
set echo off
set ver off
col report_name form a40
col betime form a5
col edtime form a5
col instart_fmt form a20
col snapdat form a20

spool awrsimple.sql

DECLARE
 CURSOR c1 IS
select to_char(s.startup_time,’dd Mon “at” HH24:mi:ss’)  instart_fmt
     , di.instance_name                                  inst_name
     , di.db_name                                        db_name
     , s.snap_id                                         snap_id
     , to_char(s.end_interval_time,’dd Mon YYYY HH24:mi’) snapdat
     , to_char(s.end_interval_time,’DDmonRR’) repdate
     , to_char(s.end_interval_time,’HH24′) snaphour
     , s.snap_level                                      lvl
  from dba_hist_snapshot s
     , dba_hist_database_instance di
     , v\$database da
     , v\$instance ins
 where s.dbid              = da.dbid
   and di.dbid             = da.dbid
   and s.instance_number   = ins.instance_number
   and di.instance_number  = ins.instance_number
   and ins.instance_number = $instnum
   and di.dbid             = s.dbid
   and di.instance_number  = s.instance_number
   and di.startup_time     = s.startup_time
   and s.end_interval_time >= decode( $num_days
                                   , 0   , to_date(’31-JAN-9999′,’DD-MON-YYYY’)
                                   , 3.14, s.end_interval_time
                                   , to_date((select to_char(max(end_interval_time),’dd/mm/yyyy’)
       from dba_hist_snapshot),’dd/mm/yyyy’) - ($num_days-1))
 order by di.db_name, di.instance_name, s.snap_id;

    v_report_name varchar2(100);
    v_db_name     varchar2(30);
    v_beid        varchar2(3);
    v_snap_begin  number(8);
    v_snapdate    varchar2(8);
    v number(3) := 1;
BEGIN
    select name into v_db_name from v\$database;
    FOR I IN C1 LOOP

      if v > 1 then

        dbms_output.enable(1000000);
        dbms_output.put_line(’define num_days=’||$num_days);
        dbms_output.put_line(’define report_type=html’);
        dbms_output.put_line(’define begin_snap=’||v_snap_begin);
        dbms_output.put_line(’define end_snap=’||i.snap_id);
        v_report_name:=’awr_’||lower(v_db_name)||’$instnum\_’||v_snapdate||’_'||v_beid||’-'||i.snaphour||’.html’;
        dbms_output.put_line(’define report_name=$awrloc’||v_report_name);
        dbms_output.put_line(‘@?/rdbms/admin/awrrpt.sql’);

      end if;
      v := v + 1;
      v_beid := i.snaphour;
      v_snap_begin := i.snap_id;
      v_snapdate := i.repdate;
    END LOOP;
END;
/
spool off

@awrsimple.sql

set echo on
set feed on
set ver on
exit;
EOF