Oracle 10g:Automate AWR reports to get in mail attachment.

October 08, 2013

Automate Automatic Workload Repository Report in a mail

Every time go to database and collect reports is old fashioned way.Now, Lets do some automated work for oracle workload reports.

This is required when number of databases and also we all lazy to avoid see reports everyday.So it is better to make one schedule who will send us yesterdays all working day awr report in one single mail where just need to see if anything problematic in off -office hours.

Simple and easy way creating one single procedure and schedule by scheduler.

lets do some coding.

1.Create directory for parameters file for awrpt input parameters.

--Report Type,

--Num days

--Begin snapshot id

--End snapshot id

--Reporrt Name

This is the mandatory report parameter needs to generate awr report.

2.Create stored procedure.

--Change directory name

--Get host_command from one of blog post.

--set report path

CREATE OR REPLACE PROCEDURE SYSTEM.USP_AUTOMATE_AWRRPT

v_dbid varchar2(20);

v_INSTANCE_NUMBER varchar2(20);

v_bid varchar2(20);

v_eid varchar2(20);

v_parfile UTL_FILE.file_type;

v_parfile_name VARCHAR2(50);

v_commands VARCHAR2(100);

v_location VARCHAR2(50);

v_directory VARCHAR2(50);

begin

--snapshot details

select DBID,INSTANCE_NUMBER,MIN(SNAP_ID) bid,MAX(SNAP_ID) eid

INTO v_dbid,v_INSTANCE_NUMBER,v_bid,v_eid

from

DBA_HIST_SNAPSHOT

where

trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-1)

Group by

DBID,INSTANCE_NUMBER ;

--Get Directorty location

Select

DIRECTORY_NAME,DIRECTORY_PATH INTO v_directory,v_location

from

dba_directories

where

DIRECTORY_NAME='DATA_PUMP_DIR';

v_parfile_name:='awrrpt_parfilesql';

v_parfile := UTL_FILE.fopen(v_directory, v_parfile_name, 'w', 32767);

v_commands:='define report_type=''html''';