Oracle批量导出AWR报告

工作需求：项目中需要把生产库中所有的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 <<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
}