By default snapshots of the relevant data are taken every hour and retained for 7 days. Most of the times database running in default AWR settings. DBA's generates the awr report when something happened in their database or periodically to check if anything is wrong in databases
Some times databases are running smooth without any problems. Due to the default retention of AWR snapshot setting we may loose some good information about database.When problem occurs in the database we may need to compare the bad time report (when problem occurred) vs good time report (when db running smooth). In this case we don't have any information about database when running without any problem or we don't have same period snapshot AWR report to compare it with current reports.
To avoid this situation DBA can automate the AWR reports generation using below script.
It automatically generates the awr report of last one hour and no manual intervention is required by DBAs onece it is schedule in cronjob
Crontab Entry
05 * * * * sh /oracle/AWR/generate_awr.sh ORCL >> /oracle/AWR/awr_rpt.log
Cronjob to be executed every hour at 05 mins on daily basis and shell output is redirected to log file
SCRIPT STARTS HERE
##############################################################################
1 ) generate_awr.sh
#------------------------------------------------------------------------------
#!/usr/bin/ksh
# File: generate_awr.sh
# Description:
# shell script to run under the UNIX "cron" utility to automatically generate Oracle "AWR" reports in HTML against the database accessed via passed parameter
# Usage :
# sh generate_awr.sh oracle_sid
# Parameters:
# One parameter to be passed. Oracle_sid to be passed as parameter.
# Environment variables are captured from /etc/oratab entry.
dbSid=${1}
export oraSid=${dbSid}
#echo $oraSid
export ORACLE_SID=`grep "$oraSid:" /etc/oratab|cut -d: -f1 -s`
export ORACLE_HOME=`grep "$oraSid:" /etc/oratab|cut -d: -f2 -s`
export ORAENV_ASK=NO
#. oraenv ${dbSid}
unset TWO_TASK
export PATH=$PATH:$ORACLE_HOME/bin
#echo $ORACLE_SID
#echo $ORACLE_HOME
#echo $PATH
sqlplus -S " / as sysdba" @/oracle/AWR/create_awr_report_for_database.sql
# Finish
#------------------------------------------------------------------------------
2) create_awr_report_for_database.sql
/* ---------------------------------------------------------------------------
Original script by
http://damir-vadas.blogspot.in/2009/11/automated-awr-reports-in-oracle-10g11g.html
Filename: create_awr_report_for_database.sql
Purpose : In directory defined with v_dir, create awr reports for last one hour snapshots, so put in crontab to run at every hour on daily basis
Remarks : Run as privileged user
# If No report generated in report folder (v_dir) create directory manually once before running the script.
--------------------------------------------------------------------------- */
set serveroutput on
set linesize 166
set pagesize 600
set trimout on
DECLARE
CURSOR c_instance
IS
SELECT instance_number, instance_name
FROM gv$instance
ORDER BY 1;
c_dir CONSTANT VARCHAR2 (256) := 'oracle/AWR';
v_dir VARCHAR2 (256) := 'oracle/AWR/daily_awr';
v_dbid v$database.dbid%TYPE;
v_dbname v$database.name%TYPE;
v_inst_num v$instance.instance_number%TYPE := 1;
v_begin NUMBER;
v_end NUMBER;
v_start_date VARCHAR2 (20);
v_end_date VARCHAR2 (20);
v_options NUMBER := 8; -- 0=no options, 8=enable addm feature
v_file UTL_FILE.file_type;
v_file_name VARCHAR (50);
BEGIN
-- get database id
SELECT dbid, name
INTO v_dbid, v_dbname
FROM v$database;
-- get end snapshot id
SELECT MAX (snap_id)
INTO v_end
FROM dba_hist_snapshot
WHERE TO_CHAR (END_INTERVAL_TIME, 'yyyy/mm/dd hh24') <= to_char(sysdate,'yyyy/mm/dd hh24');
DBMS_OUTPUT.put_line ('end snap_id ' || v_end);
-- get start snapshot id
SELECT MAX (snap_id)
INTO v_begin
FROM dba_hist_snapshot
WHERE snap_id < v_end;
DBMS_OUTPUT.put_line ('begin snap_id ' || v_begin);
SELECT TO_CHAR (END_INTERVAL_TIME, 'YYMMDD_HH24MI')
INTO v_start_date
FROM dba_hist_snapshot
WHERE snap_id = v_begin AND instance_number = v_inst_num;
DBMS_OUTPUT.put_line ('v_start_date ' || v_start_date);
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_end_date
FROM dba_hist_snapshot
WHERE snap_id = v_end AND instance_number = v_inst_num;
DBMS_OUTPUT.put_line ('v_end_date ' || v_end_date);
-- Thanx to Yu Denis Sun - we must have directory defined as v_dir value!
execute immediate('create or replace directory awrdir as '''||v_dir||'''');
-- let's go to real work...write awrs to files...
FOR v_instance IN c_instance
LOOP
DBMS_OUTPUT.put_line (
'v_instance.instance_name:' || v_instance.instance_name
);
v_file :=
UTL_FILE.fopen (
'AWRDIR',
'awr_'
|| v_instance.instance_name
|| '_'
|| v_instance.instance_number
|| '_'
|| v_start_date
|| '_'
|| v_end_date
|| '.html',
'w',
32767
);
FOR c_report
IN (SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html (
v_dbid,
v_instance.instance_number,
v_begin,
v_end,
v_options
)))
LOOP
UTL_FILE.PUT_LINE (v_file, c_report.output);
END LOOP;
UTL_FILE.fclose (v_file);
END LOOP;
EXECUTE IMMEDIATE ('drop directory AWRDIR');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
IF UTL_FILE.is_open (v_file)
THEN
UTL_FILE.fclose (v_file);
END IF;
BEGIN
EXECUTE IMMEDIATE ('drop directory AWRDIR');
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;
/
exit;
---------------------------------------------------------------------------
