12c awr_report_html,awr自动捕获脚本（html格式）

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

:

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

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 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;