— The following anonymous PL/SQL code can be used to generate a script which can be run to generate AWR report for one day. Currently the script runs to create report for yesterday’s report. It creates a report between each snap.
set serveroutput on
set feedback off
spool rpt.sql
set termout off
set linesize 200
set trimspool on
DECLARE
dbid v$database.dbid%TYPE;
dbname v$database.name%TYPE;
-- snap history get snap ids
CURSOR l_cur IS
SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot
WHERE begin_interval_time >= trunc(SYSDATE) – 1 and end_interval_time <= trunc(SYSDATE) + 1/24
ORDER BY begin_interval_time;
l_rec l_cur%ROWTYPE;
l_prev_rec l_cur%ROWTYPE;
l_begin_date VARCHAR2(30);
l_end_date VARCHAR2(30);
BEGIN
SELECT dbid, name INTO dbid, dbname FROM v$database;
OPEN l_cur;
FETCH l_cur INTO l_rec;
WHILE l_cur%FOUND
LOOP
l_prev_rec := l_rec;
FETCH l_cur INTO l_rec;
IF l_cur%found THEN
l_begin_date := to_char(l_prev_rec.BEGIN_INTERVAL_TIME, 'YYYYMMDD_HH24MI');
l_end_date := to_char(l_prev_rec.end_interval_time, 'YYYYMMDD_HH24MI');
dbms_output.put_line('-- ' || l_begin_date
|| ' – ' || l_end_date);
dbms_output.put_line('define inst_num = 1;');
dbms_output.put_line('define num_days = 0;');
dbms_output.put_line('define inst_name = ''' || dbname || ''';');
dbms_output.put_line('define db_name = ''' || dbname || ''';');
dbms_output.put_line('define dbid = ' || dbid || ';');
dbms_output.put_line('define report_type = ''html'';');
dbms_output.put_line('define begin_snap = ' || l_prev_rec.snap_id);
dbms_output.put_line('define end_snap = ' || l_rec.snap_id);
dbms_output.put_line('define report_name = /tmp/awr_' || dbname
|| '_' || l_begin_date || '_' || l_end_date || '.html');
dbms_output.put_line('@?/rdbms/admin/awrrpti');
END IF;
END LOOP;
CLOSE l_cur;
END;
/
Sample Output from rpt.sql:
-- 20111125_0000 – 20111125_0100
define inst_num = 1;
define num_days = 0;
define inst_name = ‘TESTDB’;
define db_name = ‘TESTDB’;
define dbid = 2547329494;
define report_type = ‘html’;
define begin_snap = 384
define end_snap = 385
define report_name = /tmp/awr_TESTDB_20111125_0000_20111125_0100.html
@?/rdbms/admin/awrrpti
-- 20111125_0100 – 20111125_0200
define inst_num = 1;
define num_days = 0;
define inst_name = ‘TESTDB’;
define db_name = ‘TESTDB’;
define dbid = 2547329494;
define report_type = ‘html’;
define begin_snap = 385
define end_snap = 386
define report_name = /tmp/awr_TESTDB_20111125_0100_20111125_0200.html
@?/rdbms/admin/awrrpti
alexlima.com 