# Usage: daily_awr.sh
#
#
# Check format of command line
if [ "$#" != 1 ]
then
echo "Usage: daily_awr.sh
exit 1
fi
# set environment
. ~/.profile > /dev/null
. ~/dba/lib/include > /dev/null
ORACLE_SID=$1
export ORACLE_SID
oracle_running_locally_check
MAIL_LIST=
sqlplus -s '/ as sysdba' <
column begin_snap new_value begin_snap
column end_snap new_value end_snap
column report_name new_value report_name
column full_report_name new_value full_report_name
column instance_number new_value instance_number
column dbid new_value dbid
column output format a80
select dbid from v\$database;
select instance_number from v\$instance;
select min(snap_id) as begin_snap,
max(snap_id) as end_snap
from dba_hist_snapshot
where dbid = &dbid
and instance_number = &instance_number
and snap_level >= 1 -- 5
and begin_interval_time between (trunc(sysdate) + 3.9/24) and (trunc(sysdate) + 16.2/24);
select '$LOG_DIR/sp_' || to_char(sysdate, 'MMDDYY') || '_4am-4pm.html' as report_name
from dual;
select '&&report_name' as full_report_name
from dual;
set feedback off termout off
spool &full_report_name
set verify off pages 0
select output
from table
(
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML
(
&dbid,
&instance_number,
&begin_snap,
&end_snap
)
)
/
spool off
! uuencode &&full_report_name &&full_report_name | $MAILX -s "${ORACLE_SID} DB Statspack Report 4am to 4pm" $MAIL_LIST
quit
EOF
