Monday, November 2, 2009

Automated AWR reports in Oracle 10g/11g


Many Oracle DBA's are aware of power of Oracle AWR (Automated Workload Repository) feature. If you have license for it then using it's statistic reports may be very useful to find present hot spots as well one in the history in whole database. Interval retention of snapshot generation is controlled with history period and time between two snapshots.

To retrieve how many days retention is setup, use this query:
SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;
Normally DBA control these settings by the predefined Oracle package dbms_workload_repository, by calling it's procedure
dbms_workload_repository.modify_snapshot_settings
In next example, retention period will be defined as 30 days long (43200 min) and the interval between each snapshot is 15 min (every 15 minutes one snapshot will be taken). Those settings are enough and satisfactory for most of the today database configurations:
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(43200, 15);

But however long retention policy is, most of DBAs run AWR statistic from time to time, manually. This is true in two cases:
  1. When something "strange" happened in their database (fire fighter DBA).
  2. Some of us run from time to time to see if anything "is strange", what ever that means (proactive DBA).
On the other hand when nothing "strange" happened for a longer time, many of data (reports) are lost because new data push old ones out of repository, loosing many important information how "healthy" database looks like and behave... This is also sometimes very interesting part indeed!

To overcome all mentioned, following solution gives you chance to automate collecting statistic and save them in plain html file, which could be stored and analyzed later with no chance of lost of any moment of life of your database.
/* ---------------------------------------------------------------------------
 Filename: create_awr_report_for_database.sql
 CR/TR#  :
 Purpose : In directory defined with v_dir, create awr reports for ALL instances (RAC)
           time for analyse is 07-18, so put in crontab to run at 18:16 on daily basis
          
 Date    : 09.09.2009.
 Author  : Damir Vadas

 Remarks : Run as privileged user

 Changes (DD.MM.YYYY, Name, CR/TR#):
 --------------------------------------------------------------------------- */

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) := '/home/oracle';
  v_dir         VARCHAR2(256) ;
  
  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(begin_interval_time,'HH24') = '18';
  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 to_char(begin_interval_time,'HH24') = '07'
     AND snap_id < v_end;
  dbms_output.put_line('begin snap_id '||v_begin);
    
  SELECT to_char(begin_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(begin_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 xx_some_temp_dir 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('XX_SOME_TEMP_DIR', '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 xx_some_temp_dir');
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 xx_some_temp_dir');
    EXCEPTION
      WHEN OTHERS THEN
        null;
    END;
END;
/

To run this script there are two minor requirements/constraints that must be obeyed prior running it:
  1. From my point of view, the most important time to monitor database is 07-18 hours, but you may change it as you wish.
  2. "xx_some_temp_dir" id dinamically created with v_dir value. So create directory privilege must be available to user which run this script. Keep in mind that Windows has different path definition against Linux (i.e. c_dir CONSTANT VARCHAR2(256) := 'c:\'; ). Change any of those values to apply your situation and configuration.

Last step of automation is to place this script in crontab (or windows schedule) and run it on daily basis at 18:16 (or later).
16 18 * * * . /home/oracle/MY_DB.env && sqlplus -s "/ as sysdba" @/u01/rman_backup/scripts/create_awr_report_for_database.sql

Result will be placed in v_dir directory, one file per day, giving you opportunity to analyze them whenever you like and need. Here is an example for RAC database MY_DB with 4 instances:


Last (but not least) benefit of this approach is that your retention period may be smaller-7 days would be perfectly fine for most of the cases, because there is statistic already recorded for the whole past period. As previously said, you define it like:
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(10080, 15);

If you need whole day monitoring (let us say with night shift as well) my suggestion is to modify script to run against different period, assume 18:00-07:00. As you can see, automatically, result will be saved in different file name in the same directory. Dividing monitoring on two parts is, from my point of view, really necessary, and enables DBA to view database in two very different situations OLTP against night (batch) time. Time that in database life really differ in numbers, statistic values and logical interpretation of them.

Cheers!

22 comments:

  1. Mate ... This is awesome ... Thanks for the post ... But it would be great if you give bit more explanation and also bit detailed script...


    Cheers

    ReplyDelete
  2. Hi bb,

    Which part is not clear? Please reply here or in private...

    Good wishes in 2010...

    Cheers!
    Damir

    ReplyDelete
  3. Damir,

    Thanks for sharing this great tip.
    However initially I can not make it work, I always got "ORA-29280: invalid directory path", even I just put /tmp in the utl_file.fopen('/tmp', ...). Later I did:
    create directory awrdir as '/tmp/awr_reports';
    utl_file.fopen('AWRDIR', ..)

    Then it works. Not sure if a DIRCTORY object is neccessary for utl_file to work in 10g. Based on your post, it seems it works just fine with path name ...

    Regards,

    Denis

    ReplyDelete
  4. Denis,
    you are right...
    ...
    81 IF UTL_FILE.is_open(v_file) THEN
    82 UTL_FILE.fclose(v_file);
    83 END IF;
    84 END;
    85 /
    end snap_id 481
    begin snap_id 470
    v_start_date 100114_0700
    v_end_date 1800
    v_instance.instance_name=xe
    ORA-29280: invalid directory path

    PL/SQL procedure successfully completed.

    SQL>

    So:

    create directory "some_name as "v_dir" ;
    is necessary needed.

    THX for pointing this out. I have put in original code....

    ReplyDelete
  5. Why are the date formats different for start date and end dates?

    ReplyDelete
  6. @Sapna,

    could you please be more specific?
    I do not follow you.

    Rg,
    Damir

    ReplyDelete
  7. This script is garbage, consolidates everything into one report which is pretty much useless. Amateur scritp for amateur dba's.

    ReplyDelete
  8. @walter,
    First this is global way, you can always fine grained to hours or smaller period you like-but always a start is from the top.
    I have found in situations if anything happens now there is one strong perspective in the past in a quick way.
    Also gives you a picture if DBA change or someone other comes "to play" at once.
    But thank you for your honest words ... maybe you can give us your written thoughts on this. This would really interest me a lot.
    Rg,
    Damir

    ReplyDelete
  9. @Walter,
    You are RUDE. People offer a solution. You can take it or leave it. A we DBAs build on each other work and build
    up each other. You are no DBA
    ming

    ReplyDelete
    Replies
    1. @Walter

      If you feel the provided info is useless, please give the right solution over here.

      Else stop doing this stupid thing in public forums.

      Thanks,
      Manis

      Delete
  10. Thank you !!!!!!!

    ReplyDelete
  11. I ran your script w/mods and got this:
    SQL> @create_awr_report_for database
    end snap_id 189
    begin snap_id 157
    v_start_date 140319_0630
    v_end_date 2230
    ORA-01780: string literal required
    PL/SQL procedure successfully completed.

    ReplyDelete
    Replies
    1. show me full log of sqlplus output....everything.

      Delete
    2. before that, look carefully at mine reply January 16, 2010 at 1:11 AM.

      Delete
    3. Hi Damir

      I am still getting the invalid directory path error even after mentioning the paths correctly

      SQL> @awr1.sql
      end snap_id 60102
      begin snap_id 60091
      v_start_date 141208_0700
      v_end_date 1800
      v_instance.instance_name:dwld1
      ORA-29280: invalid directory path

      PL/SQL procedure successfully completed.


      Original code :

      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) := '/home/oracle';
      v_dir VARCHAR2(256) := '/tmp/awr_reports';

      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(begin_interval_time,'HH24') = '18';
      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 to_char(begin_interval_time,'HH24') = '07'
      AND snap_id < v_end;
      dbms_output.put_line('begin snap_id '||v_begin);

      SELECT to_char(begin_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(begin_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||'''');
      -- lets 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;
      /

      Delete
    4. ORA-29280: invalid directory path, 4 errors
      0) Is Oracle directory value passed correctly to script?
      1) Check on OS level that this directory exists
      2) Check that user you are running this script has read, write grants on that Oracle directory
      3) Chack that oracle user can read and write to OS directory defined by Oracle dir value


      I'm pretty sure that it is 1). So check to replace 'awr' to 'AWR' (capital letters).

      Hope this helps
      Damir

      Delete
  12. Hey Damir, Thanks for your help, it worked out with your advise, by making awr to AWR, but my doubt is , why it is not working with small case awr

    ReplyDelete
  13. Hi Damir,

    I am still having this "ORA-29280: invalid directory path" error. Where in this script do I replace 'awr' to 'AWR'? I have set
    v_dir VARCHAR2(256) := '/u01/app/oracle/admin/OFD1/scripts/monitor/awr_reports';

    This is the location of my script and intended location of reports.

    Is the "Original code" above correct - posted by Anonymous on December 9, 2014 at 5:34 AM ?

    Thanks

    ReplyDelete
  14. I got the script working.

    Do you have a script to automate ADDM reports such the above?
    Thanks

    ReplyDelete
  15. I will post in the future something like this. Keep connected.

    ReplyDelete
  16. Hello Damir,
    This reate_awr_report_for_database.sql has been working for me on my smaller databases for over a year. But when migrated to a bigger 8TB datase it is failing with

    ORA-29285: file write error
    ORA-06512: at line 67
    ORA-29285: file write error

    Line 67 is
    execute immediate('create or replace directory awrdir as '''||v_dir||'''');

    I have over a TB of space in v_dir and the directory is being created and dropped as expected.
    The .html output is produced with zero size - no data.

    Googling, I am of the impression that the file being assembled may be larger that expected by the output. Can the number 32767 on line 71 that starts with "v_file := UTL_FILE.fopen .......32767 " by increased or what do you think the problem is?

    Thanks
    Mathias

    ReplyDelete
  17. Any advise on how to get around this error?
    ORA-20019: Database/Instance 4009464705/1 was re-started during specified snapshot interval 39576-39587. Yes, the database was re-started sometime between 0700-1800.

    Also, is it a big change to have this run like awrgrpt where it generates AWR for all the nodes as a whole instead of one for each node?

    ReplyDelete