经验丰富的老员工希望能够每天为数据库生成 1 个 AWR 报告,以便于后期分析数据库的性能变化,手动生成太麻烦,查了一下资料,发现可以自动生成,过程如下。
数据库环境:11gR2 RAC(双节点)
AWR 报告:由于是 RAC 数据库,希望生成每个节点的报告及全局报告,时间段为:第一天的 0 点~ 第二天的 0 点。
(1)在 Oracle 服务器上创建路径
- server2$[/home/oracle] mkdir awrreport
(2)创建 directory 并授权(使用 sys 账户执行)
- create or replace directory DIR_AWRREPORT as '/home/oracle/awrreport';
- grant read,write on directory DIR_AWRREPORT to mydba; -- mydba为具有dba权限的用户
- grant select on v_$database to mydba;
- grant select on dba_hist_snapshot to mydba;
- grant execute on sys.dbms_workload_repository to mydba;
(3)编写脚本(使用监控账户 mydba 执行)
- CREATE OR REPLACE PROCEDURE auto_awrreport
- AS
- /*
- 用途 :自动生成AWR报告
- 创建人 :gegeman
- 创建日期:2017-12-08
- */
- start_snap number;
- end_snap number;
- rpt_interval number := 24; --报告间隔(小时)
- start_time varchar2(14);
- end_time varchar2(14);
- awr_file utl_file.file_type;
- v_dbid number;
- v_dbname varchar2(20);
- begin
- --确定数据库名称与id
- select dbid,name into v_dbid,v_dbname from v$database;
- ---查询起始的快照id
- select max(snap_id) into end_snap from dba_hist_snapshot;
- start_snap := end_snap-rpt_interval;
- ---格式化快照时间
- select to_char(end_interval_time-rpt_interval/24, 'yyyymmddhh24'),to_char(end_interval_time, 'yyyymmddhh24')
- into start_time,end_time
- from dba_hist_snapshot
- where snap_id = end_snap and instance_number = 1;
- --实例1报告
- awr_file := utl_file.fopen('DIR_AWRREPORT',lower(v_dbname)||'_1_'||start_time||'_'||end_time||'.html', 'a',32767);
- for awr_info in (select output from table(dbms_workload_repository.awr_report_html(v_dbid,1,start_snap,end_snap,0))) loop
- UTL_FILE.put_line(awr_file,awr_info.output);
- end loop;
- utl_file.fclose(awr_file);
- --实例2报告
- awr_file := utl_file.fopen('DIR_AWRREPORT',lower(v_dbname)||'_2_'||start_time||'_'||end_time||'.html', 'a',32767);
- for awr_info in (select output from table(dbms_workload_repository.awr_report_html(v_dbid,2,start_snap,end_snap,0))) loop
- UTL_FILE.put_line(awr_file,awr_info.output);
- end loop;
- utl_file.fclose(awr_file);
- --全局报告
- awr_file := utl_file.fopen('DIR_AWRREPORT',lower(v_dbname)||'_global_'||start_time||'_'||end_time||'.html', 'a',32767);
- for awr_info in (select output from table(dbms_workload_repository.awr_global_report_html(l_dbid => v_dbid,l_inst_num => '',l_bid => start_snap,l_eid => end_snap,l_options => 0))) loop
- UTL_FILE.put_line(awr_file,awr_info.output);
- end loop;
- utl_file.fclose(awr_file);
- end auto_awrreport;
(4)授予监控用户执行 procedure 权限(使用 sys 账户)
- grant execute on mydba.auto_awrreport to mydba;
(5)创建 job,每天晚上 12:30 执行(监控账户)
- declare
- job1 number;
- begin
- sys.dbms_job.submit(job => job1,
- what => 'auto_awrreport;',
- next_date => sysdate,
- interval => 'TRUNC(SYSDATE+1) + 30/(24*60)',
- instance => 2
- );
- end;
来源: http://www.linuxidc.com/Linux/2017-12/149483.htm