Oracle批量导出AWR报告
工作需求：项目中需要把生产库中所有的AWR报告dump出来，然后导入到方便测试的数据库中。在测试库中的AWR报告需要根据dbid和实例名逐个导出，如果遇到很多再加上RAC系统，会很麻烦。在网上找了一些脚本，发现没有适合自己的，所以就自己学习了一个存储过程来实现这样的功能。<p></p><p>说明：在$ORACLE_HOME/rdbms/admin/awrrpti.sql中可以看到，生成AWR报告主要使用<span style="background-color:rgb(240,240,240)">DBMS_WORKLOAD_REPOSITORY.awr_report_html包。至于喜欢玩哪种方式，要看个人偏好。
存储过程实现代码：
create or replace directory AWR_REPORTS_DIR as '/u01/awr/'; DECLARE
l_snap_start NUMBER := 14632; l_snap_end NUMBER := 14643; l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR';
l_last_snap NUMBER := NULL; l_dbid NUMBER := 813977229; l_file UTL_FILE.file_type; l_file_name VARCHAR(50); cursor cur_inum is SELECT instance_number FROM dba_hist_snapshot WHERE dbid = l_dbid GROUP BY instance_number ORDER BY instance_number;
BEGIN
for l_instance_number in cur_inum loop l_last_snap := NULL; FOR cur_snap IN (SELECT snap_id FROM dba_hist_snapshot WHERE instance_number = l_instance_number.instance_number AND snap_id BETWEEN l_snap_start AND l_snap_end ORDER BY snap_id) LOOP IF l_last_snap IS NOT NULL THEN l_file := UTL_FILE.fopen(l_dir, 'awr_' ||l_instance_number.instance_number||'_'|| l_last_snap || '_' || cur_snap.snap_id || '.html', 'w', 32767);
FOR cur_rep IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number.instance_number, l_last_snap, cur_snap.snap_id))) LOOP UTL_FILE.put_line(l_file, cur_rep.output); END LOOP; UTL_FILE.fclose(l_file); END IF; l_last_snap := cur_snap.snap_id; END LOOP; end loop;
EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END; / |
shell 脚本实现参考：
#!/bin/sh # version 1.0 created by sprilich 20121101 # version 1.2 edited by sprilich 20121214 # set the environment #ORACLE_SID=eupdb #ORACLE_HOME=/u01/oracle/product/db10gr2 ORACLE_SID=portaldb1 ORACLE_HOME="$ORACLE_HOME" PATH=$ORACLE_HOME/bin:$PATH CONNECTSTR=" / as sysdba" #BEGIN_ID="223" #END_ID="226" BEGIN_TIME="20140504_00:00:00" END_TIME="20140506_12:00:00" #FTPSERVERIP="10.193.16.86" #FTPUSER="ftpuser" #FTPPASS="1qaz2wsx" export ORACLE_SID export ORACLE_HOME export PATH
function Dbid { sqlplus -S $CONNECTSTR <<EOF set pages 0 termout off verify off feedback off select DBID from v\$database; exit EOF }
Instnum() { sqlplus -S $CONNECTSTR <<EOF set pages 0 termout off verify off feedback off select instance_number from v\$instance; exit EOF }
Instname() { sqlplus -S $CONNECTSTR <<EOF set pages 0 termout off verify off feedback off select instance_name from v\$instance; exit EOF }
function Snap_id_like_time { sqlplus -S $CONNECTSTR <<EOF set pages 0 termout off verify off feedback off; select SNAP_ID from dba_hist_snapshot where to_char(end_interval_time,'hh24') in (9,10,11,12,14,15,17) and trunc(end_interval_time,'mi')> trunc(sysdate-1) and instance_number in (select instance_number from v\$instance) order by snap_id; exit EOF }
function Snap_id_between_time { sqlplus -S $CONNECTSTR <<EOF set pages 0 termout off verify off feedback off; select SNAP_ID from dba_hist_snapshot where trunc(end_interval_time,'hh')>= trunc(to_date('$BEGIN_TIME','YYYYMMDD_HH24:MI:SS'),'hh') and trunc(end_interval_time,'hh')<= trunc(to_date('$END_TIME','YYYYMMDD_HH24:MI:SS'),'hh') and instance_number in (select instance_number from v\$instance) order by snap_id; exit EOF }
function Snap_id_between_id { sqlplus -S $CONNECTSTR <<EOF set pages 0 termout off verify off feedback off; select SNAP_ID from dba_hist_snapshot where snap_id>= to_number($BEGIN_ID) and snap_id<= to_number($END_ID) and instance_number in (select instance_number from v\$instance) order by snap_id; exit EOF }
echo "==========++++++++++++++===========";
CMDPM=`echo $1 | awk '{print tolower($1)}'` case $CMDPM in bi | -bi | byid) SNAP_ID=`Snap_id_between_id` ;; bt | -bt | bytime) SNAP_ID=`Snap_id_between_time` ;; lt | -lt | liketime) SNAP_ID=`Snap_id_like_time` ;; *) #SNAP_ID=`Snap_id_like_time` echo "please usage like : $0 -bt" ;; esac
echo "$SNAP_ID"; echo "==========++++++++++++++===========";
#Lftp to sftpServer .lftp Just fo Linux. function Lftp_awr_report { cd . for VAR in *.htm* do lftp -u ${FTPUSER},${FTPPASS} sftp://${FTPSERVERIP}<<EOF cd /u01/docdata/olm/xh/121.160 put ${VAR} bye EOF done; }
#FTP to ftpServer function Ftp_awr_report { cd /tmp HOSTNAME=`hostname`; LOCALDIR="olm/xh/`grep ${HOSTNAME} /etc/hosts|awk '{print $1;}'|head -1`"; ftp -n ${FTPSERVERIP} <<EOF passive prompt user ${FTPUSER} ${FTPPASS} cd ${LOCALDIR} binary mput *.html ascii mput out222*.log bye EOF }
function Create_awr_report { for snap_id_line in $SNAP_ID ; do bid="$eid" eid="$snap_id_line" if [ "$bid" != "" -a "$eid" != "" ] ; then sqlplus -S $CONNECTSTR <<EOF set echo off; set veri off; set feedback off; set termout on; set heading off; set trimspool on; set linesize 1500; set termout off; column report_name new_value report_name noprint; select name1 || name2 as report_name from (select a.snap_id as begin_snap_id,a.end_interval_time as begin_time, to_char(a.end_interval_time, 'yyyymmdd_') || '`Instname`'||to_char(a.end_interval_time, '_hh24')|| to_char(a.end_interval_time, 'mi') || '-' as name1 from dba_hist_snapshot a where a.snap_id = $bid and a.instance_number = `Instnum`) t1, (select b.snap_id as end_snap_id,b.end_interval_time as end_time, to_char(b.end_interval_time, 'hh24') || to_char(b.end_interval_time, 'mi') || '.' || 'html ' name2 from dba_hist_snapshot b where b.snap_id = $eid and b.instance_number = `Instnum`) t2 where rownum < 2 and end_snap_id - begin_snap_id < 3 and end_time-begin_time<INTERVAL '2' HOUR; set termout off; spool &report_name; select output from TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(`Dbid`,`Instnum`,$bid, $eid,0 )); spool off; set termout on; clear columns sql; ttitle off; btitle off; repfooter off; exit EOF fi done }
Create_awr_report; #Ftp_awr_report; |
国外哥们儿写的SQL参考：
REM http://flashdba.com/database/useful-scripts/awr-generator/ REM AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR Reports REM REM Creates an output SQL script which, when run, will generate all AWR Reports REM between the specificed start and end snapshot IDs, for all instances REM REM For educational purposes only - no warranty is provided REM Test thoroughly - use at your own risk REM
set feedback off set echo off set verify off set timing off
-- Set AWR_FORMAT to "text" or "html" define AWR_FORMAT = 'text' define DEFAULT_OUTPUT_FILENAME = 'awr-generate.sql' define NO_ADDM = 0
-- Get values for dbid and inst_num before calling awrinput.sql
set echo off heading on column inst_num heading "Inst Num" new_value inst_num format 99999; column inst_name heading "Instance" new_value inst_name format a12; column db_name heading "DB Name" new_value db_name format a12; column dbid heading "DB Id" new_value dbid format 9999999999 just c;
prompt prompt Current Instance prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name from v$database d, v$instance i; -- Call the Oracle common input script to setup start and end snap ids @@?/rdbms/admin/awrinput.sql
-- Ask the user for the name of the output script prompt prompt Specify output script name prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt This script produces output in the form of another SQL script prompt The output script contains the commands to generate the AWR Reports prompt prompt The default output file name is &DEFAULT_OUTPUT_FILENAME prompt To accept this name, press <return> to continue, otherwise enter an alternative prompt
set heading off column outfile_name new_value outfile_name noprint; select 'Using the output file name ' || nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') , nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') outfile_name from sys.dual;
set linesize 800 set serverout on set termout off
-- spool to outputfile spool &outfile_name
-- write script header comments prompt REM Temporary script created by awr-generator.sql prompt REM Used to create multiple AWR reports between two snapshots select 'REM Created by user '||user||' on '||sys_context('userenv', 'host')||' at '||to_char(sysdate, 'DD-MON-YYYY HH24:MI') from dual;
set heading on
-- Begin iterating through snapshots and generating reports DECLARE
c_dbid CONSTANT NUMBER := :dbid; c_inst_num CONSTANT NUMBER := :inst_num; c_start_snap_id CONSTANT NUMBER := :bid; c_end_snap_id CONSTANT NUMBER := :eid; c_awr_options CONSTANT NUMBER := &&NO_ADDM; c_report_type CONSTANT CHAR(4):= '&&AWR_FORMAT'; v_awr_reportname VARCHAR2(100); v_report_suffix CHAR(5);
CURSOR c_snapshots IS select inst_num, start_snap_id, end_snap_id from ( select s.instance_number as inst_num, s.snap_id as start_snap_id, lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id from dba_hist_snapshot s where s.dbid = c_dbid and s.snap_id >= c_start_snap_id and s.snap_id <= c_end_snap_id ) where end_snap_id is not null order by inst_num, start_snap_id;
BEGIN
dbms_output.put_line(''); dbms_output.put_line('prompt Beginning AWR Generation...');
dbms_output.put_line('set heading off feedback off lines 800 pages 5000 trimspool on trimout on');
-- Determine report type (html or text) IF c_report_type = 'html' THEN v_report_suffix := '.html'; ELSE v_report_suffix := '.txt'; END IF;
-- Iterate through snapshots FOR cr_snapshot in c_snapshots LOOP -- Construct filename for AWR report v_awr_reportname := 'awrrpt_'||cr_snapshot.inst_num||'_'||cr_snapshot.start_snap_id||'_'||cr_snapshot.end_snap_id||v_report_suffix;
dbms_output.put_line('prompt Creating AWR Report '||v_awr_reportname ||' for instance number '||cr_snapshot.inst_num||' snapshots '||cr_snapshot.start_snap_id||' to '||cr_snapshot.end_snap_id); dbms_output.put_line('prompt');
-- Disable terminal output to stop AWR text appearing on screen dbms_output.put_line('set termout off');
-- Set spool to create AWR report file dbms_output.put_line('spool '||v_awr_reportname);
-- call the table function to generate the report IF c_report_type = 'html' THEN dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html(' ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));'); ELSE dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text(' ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));'); END IF;
dbms_output.put_line('spool off');
-- Enable terminal output having finished generating AWR report dbms_output.put_line('set termout on');
END LOOP;
dbms_output.put_line('set heading on feedback 6 lines 100 pages 45');
dbms_output.put_line('prompt AWR Generation Complete');
-- EXCEPTION HANDLER?
END; /
spool off
set termout on
prompt prompt Script written to &outfile_name - check and run in order to generate AWR reports... prompt
--clear columns sql undefine outfile_name undefine AWR_FORMAT undefine DEFAULT_OUTPUT_FILENAME undefine NO_ADDM undefine OUTFILE_NAME
set feedback 6 verify on lines 100 pages 45 |
可能您还喜欢以下内容：
手机扫描二维码访问：
