We have a requirement to generate a awr report on the basis of two dates instead of snapshot id. I followed this metalink document
Script to Generate AWR Reports for All snap_ids Between 2 Given Dates [ID 1378510.1]. Which generate report on the given date. But the problem is it generate multiple reports.
For Example if i give 10-Aug -2013 01:00 till 13-Aug-2013 02:00 it takes all the snap shot id between this date iterate it snap_id + and generate a seperate report for each snapshot
so i end up having multiple reports . What i wanted is a consolidated report where in the the snapshot id of the begin time and the snapshot id of the end time is taken and a single report is generated.
Please find the code which i am following. Any help will be highly appreciated
gen_batch calls pcreport script and pass the snapshot id for report generation.
gen_batch.sql
set echo off heading off feedback off verify off
select 'Please enter dates in DD-MON-YYYY HH24 format:' from dual;
select 'You have entered:', '&&BEGIN_DATE', '&&END_DATE' from dual;
set pages 0 termout off
spool batch.sql
SELECT DISTINCT '@pcreport '
||b.snap_id
||' '
||e.snap_id
||' '
|| TO_CHAR(b.end_interval_time,'YYMMDD_HH24MI_')
||TO_CHAR(e.end_interval_time,'HH24MI')
||'.txt' Commands,
'-- '||TO_CHAR(b.end_interval_time,'YYMMDD_HH24MI') lineorder
FROM dba_hist_snapshot b,
dba_hist_snapshot e
WHERE b.end_interval_time>=to_date('&BEGIN_DATE','DD-MON-YYYY HH24')
AND b.end_interval_time<=to_date('&END_DATE','DD-MON-YYYY HH24')
AND e.snap_id =b.snap_id+1
ORDER BY lineorder
/
spool off
set termout on
select 'Generating Report Script batch.sql.....' from dual;
select 'Report file created for snap_ids between:', '&&BEGIN_DATE', '&&END_DATE', 'Check file batch.sql' from dual;
set echo on termout on verify on heading on feedback on
pcreport.sql
define num_days = 0;
define report_type = 'text'
column inst_num new_value inst_num
column dbname new_value dbname
column dbid new_value dbid
SELECT d.dbid dbid ,
d.name db_name ,
i.instance_number inst_num ,
i.instance_name inst_name
FROM v$database d,
v$instance i;
column begin_snap new_value begin_snap
column end_snap new_value end_snap
column report_name new_value report_name
SELECT &1 begin_snap
FROM dual;
SELECT &2 end_snap
FROM dual;
SELECT name
||'_'
||'&3' report_name
FROM v$database;
@@?/rdbms/admin/awrrpti
run as sysdba
SQL> @gen_batch
SQL> set echo off heading off feedback off verify off
Please enter dates in DD-MON-YYYY HH24 format:
once complete than run
SQL> @batch