Oracle批量导出AWR报告

项目中需要把生产库中所有的AWR报告dump出来，然后导入到方便测试的数据库中。在测试库中的AWR报告需要根据dbid和实例名逐个导出

工作需求：项目中需要把生产库中所有的AWR报告dump出来，然后导入到方便测试的数据库中。在测试库中的AWR报告需要根据dbid和实例名逐个导出，如果遇到很多再加上RAC系统，会很麻烦。在网上找了一些脚本，发现没有适合自己的，所以就自己学习了一个存储过程来实现这样的功能。

说明：在$Oracle_HOME/rdbms/admin/awrrpti.sql中可以看到，，生成AWR报告主要使用DBMS_WORKLOAD_REPOSITORY.awr_report_html包。至于喜欢玩哪种方式，要看个人偏好。

Oracle AWR报告生成与查看

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

Oracle AWR报告生成步骤

存储过程实现代码：

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 < set pages 0 termout off verify off feedback off
select DBID from v\$database;
exit
EOF
}

Instnum() {
sqlplus -S $CONNECTSTR < set pages 0 termout off verify off feedback off
select instance_number from v\$instance;
exit
EOF
}

Instname() {
sqlplus -S $CONNECTSTR < 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 < 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 < 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
}