** This process requires an Oracle Diagnostic license
Oracle’s AWR repository is extremely useful, providing excellent reports on the performance of your database instances. Separating the performance report data from the source database means you can keep your performance data for as long as you like without impacting space usage on the source database.
That’s a positive, because I’ve often found that I didn’t have the space to store long term (>6 mo) performance data. The following procedure addresses that by automatically creating daily AWR reports to either store on disk for long term analysis, or perhaps send as a daily email report. As you accumulate these reports over time, you can compare them to determine long term changes in performance and usage. You can also use unix commands such as “grep” or “awk” to compare data across your reports. For instance you can pull out the db_time metric to determine your system load over time.
I usually set up the process with a non-system account.
— The following direct grants are required.
grant select on dba_hist_snapshot to dvdba;
grant select on gv_$database to dvdba;
grant execute on DBMS_WORKLOAD_REPOSITORY to dvdba;
create directory awr_dir as ‘/home/oracle/awr’;
grant all on directory awr_dir to dvdba;
Here is the procedure:
create or replace procedure dvdba.awrtofile
(days_ago number default 1, starttime number default 7, endtime number default 18 ) is
if endtime <= starttime then
SELECT MIN (snap_id), MAX (snap_id)
INTO bid, eid
end_interval_time >= (starttime/24 + trunc(sysdate-days_ago-prior_day))
and end_interval_time <= (endtime/24 + 0.0068 + trunc(sysdate-days_ago))
SELECT dbid, inst_id, db_unique_name INTO dbid, inst_id, db_unique_name
FOR c1_rec IN
FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(dbid,inst_id, bid, eid, 8 )))
WHEN UTL_FILE.INVALID_OPERATION THEN
dbms_output.put_line(‘File could not be opened or operated on as requested.’);
WHEN OTHERS THEN
One of the virtues of a script like this is that it gives you fine control over your reporting period. Here is how you would execute the procedure for yesterday, from 7 am until 6 pm:
To create reports for the last 30 days (which requires a 30-day retention policy for AWR data), run the following:
for x in 1..30 loop
Here is a schedule entry to create reports daily for both a day window (7am to 6pm) and a night window (11pm to 6am):
— Job defined entirely by the CREATE JOB procedure.
job_name => ‘DV_WRITE_AWR_TO_DISK’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘dvdba.awrtofile(1,7,18); dvdba.awrtofile(1,23,6);’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=daily; byhour=7;byminute=0’,
end_date => NULL,
enabled => TRUE,
comments => ‘Task to create daily awr report and write them to the AWR_DIR.’);
SELECT * FROM dba_scheduler_job_log WHERE job_name = ‘DV_WRITE_AWR_TO_DISK’;
Useful scripts to address minor “paper cut” irritations are only one of the ways Datavail’s experienced database professionals can make your life easier. Contact us to learn what we can do for you.
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.