creating awr reports manually

When working with the third parties on the same database there happens some confusion. The operational dba 's are responsible from the database performance and other standard operational duties like backup, security, architecture .. and so on. Vendor dba 's are also responsible to achieve SLA contract requirements and promised performance values. Both side should be causious not to overlap the other side 's duties. Here i tried to answer one of the requests of our vendor dba by supplying hourly AWR reports directly in their directories on the UNIX environment. I think they can follow database activitiy better from now on.


### creating the awr report ###
ORACLE_HOME=/oracle/product/10.2.0; export ORACLE_HOME
ORACLE_SID=montc1; export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin; export PATH

$ORACLE_HOME/bin/sqlplus "/ as sysdba" << EOF
declare
i_begin_snap_id number;
i_end_snap_id number;
i_db_id number;
i_inst_num number;
begin

--select the db_id and the instance_number of the instance,
--these values are going to be used to create the awr_report
select DBID, INSTANCE_NUMBER into i_db_id, i_inst_num from dba_hist_database_instance where rownum=1;

--find the snap_id of the time being
select SNAP_ID into i_end_snap_id from dba_hist_snapshot where trunc(end_interval_time,'HH24') = trunc(sysdate,'HH24') order by SNAP_ID desc;

--set the begin snap_id of the previous hour as we are generation hourly awr reports
i_begin_snap_id := i_end_snap_id - 1;

--generate and write the report to a table
insert into avea_sysmon.temp_awr_output select output from table (SYS.DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(i_db_id, i_inst_num, i_begin_snap_id, i_end_snap_id));
commit;

end;
/
EOF

### creating report name ###
report_name=/moneta_home/avea_data/awr/awr_report_`date | awk '{print $3"_"$2"_"$6"_"$4}'`.html
export report_name

### spooling previously created report to the file specified ###
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" << EOF
set linesize 1500;
set echo off;
set heading off;
set termout on;

spool $report_name;
select s_output from avea_sysmon.temp_awr_output;
truncate table avea_sysmon.temp_awr_output;
spool off;
EOF

### delete the older reports ###
ctl=`ls -l /moneta_home/avea_data/awr | grep .html | wc -l`
if [ $ctl -gt 110 ]
then
ls -l /moneta_home/avea_data/awr | grep .html | head -10 | awk '{print "/moneta_home/avea_data/awr/"$9}' | xargs rm
#else
#echo "dont delete anything"
fi


After i tested the script activity of the script, i used crontab to schedule this script as a job.

$ crontab -l
00,10,20,30,40,50 * * * * /oracle/epeker/check_arch.sh > /oracle/epeker/check_arch.log 2>&1
20 * * * * /oracle/awr_reports/get_awr.sh > /oracle/awr_reports/get_awr.log 2>&1

The awr reports of the specified time are now available on application directories.

$ ls -lrt /moneta_home/avea_data/awr
total 1053328
-rw-r--r--   1 oracle   dba      5229793 Nov  6 14:20 awr_report_6_Nov_2008_14:20:45.html
-rw-r--r--   1 oracle   dba      4911565 Nov  6 15:20 awr_report_6_Nov_2008_15:20:08.html
-rw-r--r--   1 oracle   dba      4865032 Nov  6 16:20 awr_report_6_Nov_2008_16:20:12.html
-rw-r--r--   1 oracle   dba      6112427 Nov  6 17:20 awr_report_6_Nov_2008_17:20:06.html
-rw-r--r--   1 oracle   dba      5057170 Nov  6 18:20 awr_report_6_Nov_2008_18:20:03.html
-rw-r--r--   1 oracle   dba      5031651 Nov  6 19:20 awr_report_6_Nov_2008_19:20:02.html
-rw-r--r--   1 oracle   dba      4798984 Nov  6 20:20 awr_report_6_Nov_2008_20:20:03.html
-rw-r--r--   1 oracle   dba      5016641 Nov  6 21:20 awr_report_6_Nov_2008_21:20:02.html
-rw-r--r--   1 oracle   dba      4895053 Nov  6 22:20 awr_report_6_Nov_2008_22:20:05.html
-rw-r--r--   1 oracle   dba      5228292 Nov  6 23:20 awr_report_6_Nov_2008_23:20:09.html
-rw-r--r--   1 oracle   dba      5018142 Nov  7 00:20 awr_report_7_Nov_2008_00:20:03.html
-rw-r--r--   1 oracle   dba      5072180 Nov  7 01:20 awr_report_7_Nov_2008_01:20:10.html