Saturday, November 15, 2008

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

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

--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));


### 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;

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

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/ > /oracle/epeker/check_arch.log 2>&1
20 * * * * /oracle/awr_reports/ > /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


  1. Could you please provide the description of table "avea_sysmon.temp_awr_output"

  2. The table has only one varchar column and can be created by running the following script


    hope this helps,

  3. similar kind of post here may be useful for readers :