oracle自动导出awr报告,自动生成awr报告脚本

大家好！

我们知道awr报告每小时自动收集一次，但是不会自动产生awr报告，这里Aning和大家分享一个自动生成awr报告的脚本：

1.shell脚本，report4.sh 这是每4小时生成一次awr报告的脚本

#!/bin/bash

ORACLE_BASE=/u01/app/oracle

export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_HOME

ORA_CRS_HOME=/u01/app/11.2.0/grid

export ORA_CRS_HOME

ORACLE_SID=rac1

export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORA_CRS_HOME/bin

export PATH

export ORACLE_UNQNAME=rac

datetime=`date  +%Y%m%d%H`

cd /home/oracle/hourly_backup

$ORACLE_HOME/bin/sqlplus /nolog

@/home/oracle/backup/script/awrrpt_rac1_4.sql

exit

!

/bin/gzip -9 /home/oracle/hourly_backup/report_$datetime.html

2.shell脚本调用的生成awr报告的sql脚本，awrrpt_rac1_4.sql

set echo off;

set veri off;

set feedback off;

set termout on;

set heading off;

variable rpt_options number;

define NO_OPTIONS = 0;

rem according to your needs, the value can be 'text' or 'html'

define report_type='html';

begin

:rpt_options := &NO_OPTIONS;

end;

/

variable dbid number;

variable inst_num number;

variable bid number;

variable eid number;

variable day number;

begin

select max(snap_id)-5 into :bid from dba_hist_snapshot;

select max(snap_id) into :eid from dba_hist_snapshot;

select dbid into :dbid from v$database;

select instance_number into :inst_num from v$instance;

select to_number(to_char(sysdate,'yyyymmddhh24')) into :day from dual;

end;

/

column ext new_value ext noprint

column fn_name new_value fn_name noprint;

column lnsz new_value lnsz noprint;

select 'txt' ext from dual where lower('&report_type') = 'text';

select 'html' ext from dual where lower('&report_type') = 'html';

select 'awr_report_text' fn_name from dual where lower('&report_type') = 'text';

select 'awr_report_html' fn_name from dual where lower('&report_type') = 'html';

select '80' lnsz from dual where lower('&report_type') = 'text';

select '1500' lnsz from dual where lower('&report_type') = 'html';

set linesize &lnsz;

column report_name new_value report_name noprint;

--select 'sp_'||:bid||'_'||:eid||'.'||'&ext' report_name from dual;

select 'report_'||:day||'.'||'&ext' report_name from dual;

set termout off;

spool &report_name;

select output from table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options ));

spool off;

set termout on;

clear columns sql;

ttitle off;

btitle off;

repfooter off;

undefine report_name

undefine report_type

undefine fn_name

undefine lnsz

undefine NO_OPTIONS

3.在crontab 里面添加执行时间

crontab -l 10 4,8,12,16,20,0 * * * /home/oracle/backup/script/report4.sh

上述脚本是没4小时生成一次awr报告，大家可以根据自己需要修改生成时间