Tuesday, October 8, 2013

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

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
as

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''';
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define num_days=2';
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define begin_snap='||v_bid;
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define begin_snap='||v_eid;
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define report_name=''/tmp/awrrpt_'||'2'||'_'||v_bid||'_'||v_eid||'.html''';
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='@?/rdbms/admin/awrpt.sql';

UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='exit';

UTL_FILE.PUT_LINE(v_parfile,v_commands );
   
UTL_FILE.fclose(v_parfile); 


UTL_FILE.FREMOVE(v_directory,v_parfile_name);


end;
/

4.Create one shell script to execute this generated parfile.

#!/bin/sh
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4/db_1
export ORACLE_SID=pindb
PATH=$PATH:$ORACLE_HOME/bin

sqlplus system/manager <<EOF
execute SYSTEM.USP_AUTOMATE_AWRRPT;
@/hot-bkp/expdp/awrrpt_parfile.sql
EOF
exit


5.Schedule it after 12 AM to get yesterday's record.


No comments:

Post a Comment

Dear User,

Thank you very much for your kind response