AWR 修改为自动化脚本

把交互式的awr 修改成自动化脚本

1. 查看参数 show parameter statistics;
    statistics_level                     string      TYPICAL 
    statistics_level 默认是typical，在10g中表监控是激活的，非特殊情况，否则不要修改。如果STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：
            ASH(Active Session History)
            ASSM(Automatic Shared Memory Management)
            AWR(Automatic Workload Repository)
            ADDM(Automatic Database Diagnostic Monitor)

2. /opt/app/oracle/product/10G/rdbms/admin/awrrpt.sql
调用awrrpti.sql， 需要的是修改awrrpti.sql 程序，如何修改
在awrrpti.sql 里面有详细的描述
========================================
Rem    NOTES
Rem      Run as SYSDBA.  Generally this script. should be invoked by awrrpt,
Rem      unless you want to pick a database other than the default.
Rem
Rem      If you want to use this script. in an non-interactive fashion,
Rem      without executing the script. through awrrpt, then
Rem      do something similar to the following:
Rem
Rem      define  inst_num     = 1;
Rem      define  num_days     = 3;
Rem      define  inst_name    = 'Instance';
Rem      define  db_name      = 'Database';
Rem      define  dbid         = 4;
Rem      define  begin_snap   = 10;
Rem      define  end_snap     = 11;
Rem      define  report_type  = 'text';
Rem      define  report_name  = /tmp/swrf_report_10_11.txt
Rem      @@?/rdbms/admin/awrrpti
==============================
-- ***************************************************
--   Customer-customizable report settings
--   Change these variables to run a report on different statistics
-- ***************************************************
-- The default number of days of snapshots to list when displaying the
-- list of snapshots to choose the begin and end snapshot Ids from.
--
--   List all snapshots
-- define num_days = '';
--
--   List no (i.e. 0) snapshots
-- define num_days = 0;
--
-- List past 3 day's snapshots
-- define num_days = 3;
--
-- Reports can be printed in text or html, and you must set the report_type
-- in addition to the report_name
--
-- Issue Report in Text Format
--define report_type='text';
--
-- Issue Report in HTML Format
--define report_type='html';

-- Optionally, set the snapshots for the report.  If you do not set them,
-- you will be prompted for the values.
--define begin_snap = 545;
--define end_snap   = 546;

-- Optionally, set the name for the report itself
--define report_name = 'awrrpt_1_545_546.html'

-- ***************************************************
--   End customer-customizable settings
-- **************************************************

我想收集系统在批处理时间的处理情况：（对于RAC库，只要是在不同的实例上执行就可以，脚本无需修改）
select snap_interval, retention from dba_hist_wr_control
    保留30天， 间隔时间是1小时
======================================================================================
cp awrrpt.sql auto_awrrpt.sql
cp awrrpti.sql auto_awrrpti.sql

auto_awrrpt.sql 仅仅修改为调用auto_awrrpt，既由@@awrrpti 改为@@auto_awrrpti
auto_awrrpti.sql 主要的修改有：
--   List no (i.e. 0) snapshots
   define num_days = 0;

 

-- Issue Report in HTML Format
   define report_type='html';

-- Optionally, set the snapshots for the report.  If you do not set them,
-- you will be prompted for the values.
--define begin_snap = 545;
--define end_snap   = 546;
prompt "================================================="
col begin_snap new_value begin_snap for 999999 ;
col end_snap new_value end_snap for 999999 ;

select min(snap_id) begin_snap,
max(snap_id) end_snap
from dba_hist_snapshot
where instance_number = &inst_num
and to_char(end_interval_time, 'YYYYMMDDHH24MISS') between to_char(sysdate-1, 'YYYYMMDD')||'174000' and to_char(sysdate-1, 'YYYYMMDD')||'2330
00';

---- 获取批处理时间段的snap_id

--define report_name = 'awrrpt_1_545_546.html'
  col report_name new_value report_name noprint;
  select 'awrrpt_'||&inst_num||'_'||&begin_snap||'_'||&end_snap||'.html' report_name from dual;

定时执行脚本：
sqlplus "/as sysdba"<@auto_awrrpt.sql
!

把脚本定制到crontab 中

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21634752/viewspace-704112/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21634752/viewspace-704112/