Schedule AWR Report Generation

set echo off set head off set feed off

spool /orasys/home/oracle/Kishi/Logs/Awr_report_daily.lst; select ‘Define begin_snap= ‘|| snap_id from dba_hist_snapshot where EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=9 and EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’YYYY’) and EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’MM’) and EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’DD’);

SELECT ‘Define end_snap= ‘|| snap_id from dba_hist_snapshot where EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=18 and EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’YYYY’) and EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’MM’) and EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’DD’);

select ‘define  report_type  = ‘ || ”’html”’ from dual; select ‘define  report_name  = /orasys/home/oracle/Kishi/Logs/AWR_REPORT_Daily.html’ from dual; select ‘define  inst_name    = ‘ || INSTANCE_NAME from v$instance; select ‘define  db_name    = ‘ || name from v$database; select ‘define  dbid = ‘ || dbid from v$database; select ‘define inst_num = ‘ || INSTANCE_NUMBER from v$instance; select ‘define  num_days     = 3’ from dual; select ‘@$ORACLE_HOME/rdbms/admin/awrrpti.sql’ from dual; spool off;