Oracle批量导出AWR报告

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

可能您还喜欢以下内容：

oracle10g登录em后,提示"java.lang.Exception: Exception in sending Request :: null" ORACLE查看并修改最大连接数 关于Oracle修改IP地址引起的问题 ORACLE中TO_DATE用法 Oracle性能调优原则 oracle 11g 卸载方法之官方deinstall工具完全使用手册 Oracle 11G Client 客户端安装步骤（图文详解） oracle 11g完美导出到oracle10g 功能强大的数据库管理工具Navicat Premium 微软发布SQL Server 2012 Service Pack 2 常用SQL查询语句 SQL Server导出表到EXCEL文件的存储过程 SQL SERVER 2008字符串函数大全 SQL Server备份与灾难性恢复计划制定 SQL Server 定时自动备份 SQL Server 2008-建立分区表(Table Partition) 毫秒级百万数据分页存储过程[转] 如何比较两个数据表 SQL栏目树的代码【转】 6个有用的MySQL语句

取消Oracle数据库密码期限和用户被锁定 oracle数据库导入导出 Windows Server 2008 R2 SP1上安装Oracle 11G R2 CentOS 6.5_x64安装Oracle 11g R2 在RedHat 5下安装Oracle 10g详解 RedHat Linux5.5下Oracle 11g安装图解 Windows Server2008R2安装Oracle Database 11g Release 2图解 mysql事务与mysql储存引擎 MySQL数据库查询优化 常用的几种嵌入式数据库 MySQL性能优化详解 MySQL性能优化的最佳20+条经验【转】 T-SQL经典语句 使用为公开的存储过程sp_cursoropen实现分页 Sql Server查询性能优化之走出索引的误区【转】 MySQL数据库性能优化 SQL端口改变后的远程连接和数据库连接 sql常用字段类型 oracle中的伪列rownum 微软新数据库向Oracle宣战

手机扫描二维码访问：