awr自动捕获脚本（html格式）

  对于想将awr以html格式保存下来的用户，可以参考这个脚本。

 

:<-- Create table
create table IAWR$AWR_REPOSITY
(
  SNAP_ID   NUMBER,
  CONTENT   CLOB
);

 

--load_awr.ctl
LOAD DATA
INFILE 'load_awr.dat'
INTO TABLE iawr$awr_reposity
APPEND
FIELDS TERMINATED BY ','
(
snap_id char(30),
name filler char(500),
content LOBFILE(name) TERMINATED BY EOF)

CMMT

 

#########################################################
###define var

 

source ~ora10g/.bash_profile;
cd ~ora10g/some_shell/iawr;

 

v_num_days=2;
v_report_type='html';
v_report_name="${ORACLE_BASE}/awrrpt_repos";
v_now_day=$(date -d "1 days ago" "+%F");
declare -i v_pre_idx;
v_pre_idx=0;

 

v_awr_uname='iawr';
v_awr_pwd='iawr';
v_sys_uname='sys';
v_sys_pwd='tiger';
v_awr_cfile='load_awr.ctl';

 

mkdir -p "${v_report_name}";

#########################################################
###get daily snapid list

 

v_snapid_list=$(${ORACLE_HOME}/bin/sqlplus -S ${v_sys_uname}/${v_sys_pwd} AS SYSDBA <set feedback off echo off verify off pagesize 0 heading off linesize 1000;
WITH t1 AS
(
  SELECT dhs.snap_id,row_number() over(ORDER BY dhs.snap_id) AS rn
  FROM   dba_hist_snapshot dhs
  WHERE  dhs.begin_interval_time BETWEEN trunc(SYSDATE - 1) - 1.5 / 24 AND
         trunc(SYSDATE) - 1 / 86400
),t2 AS
(
  SELECT trim(sys_connect_by_path(snap_id, ' ')) str,connect_by_isleaf lf
  FROM   t1
  START  WITH rn = 1
  CONNECT BY PRIOR snap_id = snap_id - 1
)
SELECT str FROM t2 WHERE lf=1;
EXIT;
EOF
);

 

#########################################################
###get report iteration

 

cat /dev/null > load_awr.dat;

 

for v_idx in ${v_snapid_list}
  do

  if [ ${v_pre_idx} -ne 0 ]
    then
      ${ORACLE_HOME}/bin/sqlplus -S ${v_sys_uname}/${v_sys_pwd} as sysdba <      define num_days=${v_num_days};
      define report_type=${v_report_type};
      define begin_snap=${v_pre_idx};
      define end_snap=${v_idx};
      define report_name="${v_report_name}/${v_now_day}_${v_idx}.html";
@${ORACLE_HOME}/rdbms/admin/awrrpt.sql;
exit;
EOF

 

echo ${v_idx}','"${v_report_name}/${v_now_day}_${v_idx}.html" >> load_awr.dat;
  fi;

  v_pre_idx=${v_idx};

  done;

 

#########################################################
###load awr html file into database

 

${ORACLE_HOME}/bin/sqlldr userid="${v_awr_uname}/${v_awr_pwd}" control=${v_awr_cfile};

 

#########################################################
###remove report file from disk

 

cd ${v_report_name};

find . -regextype egrep -regex ".+$(date -d '1 days ago' '+%F').+\.html" -type f | xargs rm;