AWR Data ETL and AWR Warehouse

为了更好的绘制数据库的性能曲线并对数据库的历史性能指标数据进行跟踪对比,我采用了将AWR的基础数据进行导出、传输到目标端、导入目标库的方式,并使用tanel poder的perfsheet工具进行相应指标的绘图。后读到Oracle cloud control 12c从12.1.0.4之后提供了awr warehouse这个功能,oracle帮
我们实现了这awr的ETL过程,所以后续采用了这个新功能,减少了很多人工工作。
手工完成的脚本参考了oracle的awrextr.sql和awrload.sql脚本,并进行了修改适合自己的场景。

源端
awr_get.sh

#!/bin/bash
export PATH=/usr/bin:/usr/sbin:/usr/local/bin:$PATH
. ~/.bash_profile
for i in NameList
do
ORACLE_SID=$i
export ORACLE_SID
cd /oracle/scripts/db_maintain/perfsheet
/orasoft/oracle/product/11.2.0/db_1/bin/sqlplus -s / as sysdba <"/oracle/scripts/db_maintain/perfsheet/awr_get.sql"
done

awr_get.sql

declare
  dmpfile varchar2(15);
  dmpdir  varchar2(13);
  bid     number;
  eid     number;
  dbid    number;
begin
  select name into dmpfile from v$database;
  dmpdir := 'DATA_PUMP_DIR';
  select min(snap_id) into bid from dba_hist_snapshot where begin_interval_time > sysdate - 7;
  select max(snap_id) into eid from dba_hist_snapshot where begin_interval_time > sysdate - 7;
  select dbid into dbid from v$database;

  dbms_swrf_internal.awr_extract(dmpfile => dmpfile,
                                 dmpdir  => dmpdir,
                                 bid     => bid,
                                 eid     => eid,
                                 dbid    => dbid);
  dbms_swrf_internal.clear_awr_dbid;
end;
/

目标端
awrload.sh

#!/bin/bash
export PATH=/usr/bin:/usr/sbin:/usr/local/bin:$PATH
. ~/.bash_profile
export ORACLE_SID=instance_name
/orasoft/oracle/product/11.2.0/db_1/bin/sqlplus -s / as sysdba <"/oracle/scripts/db_maintain/awrload.sql"

awrload.sql

declare
  schname varchar2(20);
  dmpfile varchar2(20);
  dmpdir  varchar2(20);
  
  sql_drop_awr_stage varchar2(100);
  sql_create_awr_stage varchar2(100);
  sql_grant varchar2(100);

  cursor cur_fname is
    select fname from dbinfo;
  fname_record dbinfo.fname%type;
begin
  schname := 'AWR_STAGE';
  dmpdir  := 'DUMP_AWR';
  open cur_fname;
  loop
    fetch cur_fname
      into fname_record;
    exit when cur_fname%notfound;
    sql_drop_awr_stage := 'drop user AWR_STAGE cascade';
    execute immediate sql_drop_awr_stage;
    sql_create_awr_stage := 'create user AWR_STAGE identified by AWR_STAGE default tablespace SYSAUX temporary tablespace TEMP';
    execute immediate sql_create_awr_stage;
    sql_grant := 'alter user AWR_STAGE quota unlimited on SYSAUX'; 
    execute immediate sql_grant;
    
    dbms_swrf_internal.awr_load(schname => schname,
                                dmpfile => fname_record,
                                dmpdir  => dmpdir);
    dbms_swrf_internal.move_to_awr(schname => schname);
    dbms_swrf_internal.clear_awr_dbid;
  end loop;
  close cur_fname;
end;
/

awr warehouse的安装需求请参考Master Note on AWR Warehouse (Doc ID 1907335.1)

此条目发表在AWR, OEM12c分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s