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 byhttp://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;
---------------------------------------------------------------------------
Hi Jayendra,
ReplyDeleteThank you for your post, this helped me a lot.
I was looking for daily and weekly auto AWR generation scripts, I have used these scripts and tweaked for my requirement. And they work great.
Thank you.
Nice to see my script helped you.
DeleteHi, Do you have another version of this script to gen awr in txt format? Thanks for sharing
ReplyDeleteThọ Nguyễn
ReplyDeleteI do not have text version of script but
You can generate text report with function dbms_workload_repository.awr_report_text instead of DBMS_WORKLOAD_REPOSITORY.awr_report_html
Can we generate awr,addm,ash report on weekly basis.any script
ReplyDelete