We keep 6 weeks of history in the AWR on our databases, but I want to capture some information for long-term trending. What I really want to do is capture some metrics and put them in some database tables to use to generate reports, but I have not had time to build the scripts to do that. So, instead I built a simple set of scripts to capture an AWR for the previous month. Since we have 6 weeks of history if I run my report in the first week of a month all the days of the previous month should still be in the AWR. I have just finished building this script so I can not promise that there is value in keeping monthly AWR reports but I thought it was worth sharing it. Maybe something in the code will be useful to someone. Here is the script:
-- Has to be run in the first week of the month so the entire -- previous month is available. We keep 6 weeks of awr history. -- setup columns for snapshots column bsnap1 new_value bsnap1s noprint; column esnap1 new_value esnap1s noprint; column filenm new_value filenms noprint; -- get snap id for first day of previous month select min(snap_id) bsnap1 from dba_hist_snapshot where extract(month from END_INTERVAL_TIME)= extract(month from (sysdate-to_number(to_char(sysdate,'DD')))) and STARTUP_TIME= (select max(STARTUP_TIME) from dba_hist_snapshot where extract(month from END_INTERVAL_TIME)= extract(month from (sysdate-to_number(to_char(sysdate,'DD'))))); -- get snap id for last day of previous month select max(snap_id) esnap1 from dba_hist_snapshot where extract(month from END_INTERVAL_TIME)= extract(month from (sysdate-to_number(to_char(sysdate,'DD')))); -- get html file name select name|| '_'|| to_char(extract(month from (sysdate-to_number(to_char(sysdate,'DD')))))|| '_'|| to_char(extract(year from (sysdate-to_number(to_char(sysdate,'DD')))))|| '.html' filenm from v$database; -- get awr report define report_type='html'; define begin_snap = &bsnap1s; define end_snap = &esnap1s; define report_name = '&filenms'; define num_days = 0; @@$ORACLE_HOME/rdbms/admin/awrrpt.sql undefine report_type undefine report_name undefine begin_snap undefine end_snap undefine num_days
If the database bounced during the previous month we get the last set of snapshots after the last bounce.
I am not sure whether this approach will give us any benefits but I think it may help to show how to use queries to pick begin and end snapshots and then run an AWR report.
The tricky part of the code is this:
extract(month from (sysdate-to_number(to_char(sysdate,'DD'))))
It just returns the previous month as a number. It is August now so here is what it returns today:
SQL> select 2 extract(month from (sysdate-to_number(to_char(sysdate,'DD')))) 3 prev_month 4 from dual; PREV_MONTH ---------- 7
sysdate-to_number(to_char(sysdate,’DD’)) is the last day of the previous month:
SQL> select sysdate-to_number(to_char(sysdate,'DD')) last_day 2 from dual; LAST_DAY --------- 31-JUL-15
– Bobby
