论坛 › Oracle数据库管理 › 部署awr自动生成脚本

seedog123 发表于 2012-5-7 10:42:30

部署awr自动生成脚本

脚本如下：

AWR_FORMAT=html
NUM_DAYS=1

MAX_SNAP_ID=`sqlplus -S / as sysdba << EOF
set heading off trimspool on feedback off
SELECT trim(max(SNAP_ID)) FROM DBA_HIST_SNAPSHOT;
EOF`

MIN_SNAP_ID=`expr $MAX_SNAP_ID - 1`

dbname=`sqlplus -S / as sysdba << EOF
set heading off trimspool on feedback off
select instance_name from v\\$instance;
EOF`

timeid=`sqlplus -S / as sysdba << EOF
set heading off trimspool on feedback off
select extract(hour from  begin_interval_time)||'_'||extract(hour from  end_interval_time) from DBA_HIST_SNAPSHOT where
SNAP_ID=(select trim(max(SNAP_ID)) from DBA_HIST_SNAPSHOT);
EOF`


AWR_LOG=/u01/awrrpt_$dbname_`date '+%Y%m%d'`_$timeid.html
echo $AWR_LOG

echo -e "$AWR_FORMAT\n$NUM_DAYS\n$MIN_SNAP_ID$MAX_SNAP_ID\n$AWR_LOG\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrrpt.sql) > /dev/null




我想让awr输出的格式是：awrrpt_数据库实例名_当前日期_最后2个snap号截取的小时数.html

$ echo $AWR_LOG
/u01/awrrpt_20120515_ 6_7.html

问题：
没有显示出dbname
6前面有个空格

am196 发表于 2012-5-7 17:21:59

我这边是定时发送ADDM报告！

seedog123 发表于 2012-5-7 17:24:18

回复 2# 的帖子

am196  参考一下可以么

Maclean Liu(刘相兵 发表于 2012-5-7 19:20:50

请自行修改 以下脚本：#!/bin/bash
# edit by maclean  2012-05-07 www.oracledatabase12g.com
export ORACLE_BASE=
export ORACLE_HOME=
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=
sqlplus -s /nolog <<EOF
connect / as sysdba
set echo off
set veri off
set feedback off
set termout on
set heading off
set linesize 1500
set termout off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE DB_UNIQUE_NAME varchar2(20)
exec select max(snap_id) -1  into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id)     into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select DB_UNIQUE_NAME  into :DB_UNIQUE_NAME from v\$database ;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
alter session set nls_date_format='YYYY-MM-DD';
column filename new_val filename
select 'awr_'||:DB_UNIQUE_NAME||'_'||sysdate||'_'||:BgnSnap||'_'||:EndSnap||'.html' filename  from dual ;
spool &filename;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_html (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF

seedog123 发表于 2012-5-7 19:50:51

回复 4# 的帖子

好的，，我马上改改。。

lxhbww 发表于 2012-5-8 08:22:10

如果是每周三的8-12点自动收集，这起止snap号要怎么写？

yobyin 发表于 2012-5-10 09:50:53

根据时间查出snapid 了。

justinleisure 发表于 2014-7-18 21:11:29

脚本可以方便的批量生成awr报告

liupzmin 发表于 2014-7-23 12:47:32

留着看看

ora_ted 发表于 2014-7-26 11:20:48

参考参考

查看完整版本: 部署awr自动生成脚本