部署awr自动生成脚本

seedog123

脚本如下：

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

[oracle@du awr]$ echo $AWR_LOG
/u01/awrrpt_20120515_ 6_7.html

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

am196

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

seedog123

am196  参考一下可以么

Maclean Liu(刘相兵

请自行修改 以下脚本：

1. #!/bin/bash
   
2. # edit by maclean  2012-05-07 www.oracledatabase12g.com
   
3. export ORACLE_BASE=
   
4. export ORACLE_HOME=
   
5. export PATH=$ORACLE_HOME/bin:.:$PATH
   
6. export ORACLE_SID=
   
7. sqlplus -s /nolog <<EOF
   
8. connect / as sysdba
   
9. set echo off
   
10. set veri off
    
11. set feedback off
    
12. set termout on
    
13. set heading off
    
14. set linesize 1500
    
15. set termout off
    
16. VARIABLE BgnSnap NUMBER
    
17. VARIABLE EndSnap NUMBER
    
18. VARIABLE DID NUMBER
    
19. VARIABLE INST_NUMBER number
    
20. VARIABLE DB_UNIQUE_NAME varchar2(20)
    
21. exec select max(snap_id) -1  into :BgnSnap from dba_hist_snapshot ;
    
22. exec select max(snap_id)     into :EndSnap from dba_hist_snapshot ;
    
23. exec select DBID into :DID from v\$database;
    
24. exec select DB_UNIQUE_NAME  into :DB_UNIQUE_NAME from v\$database ;
    
25. exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
    
26. alter session set nls_date_format='YYYY-MM-DD';
    
27. column filename new_val filename
    
28. select 'awr_'||:DB_UNIQUE_NAME||'_'||sysdate||'_'||:BgnSnap||'_'||:EndSnap||'.html' filename  from dual ;
    
29. spool &filename;
    
30. SELECT output FROM TABLE (dbms_workload_repository.awr_report_html (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
    
31. spool off
    
32. exit
    
33. EOF

复制代码

seedog123

好的，，我马上改改。。

lxhbww

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

yobyin

根据时间查出snapid 了。

justinleisure

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

liupzmin

留着看看

ora_ted

参考参考