首页 > Linux操作系统 > Linux操作系统 > awr 自动 mail 发送设置

awr 自动 mail 发送设置

原创 Linux操作系统 作者:golden_zhou 时间:2011-05-25 17:57:36 0 删除 编辑

1. 各节点 AWR 自动生成 script. 设置(awrrun/autoawr.sql)

cat /data/run/awrrun


cd  /data/awrrpt

ORACLE_HOME=/u01/product/oracle;export ORACLE_HOME
ORACLE_SID=delll10;export ORACLE_SID

$ORACLE_HOME/bin/sqlplus /nolog<connect / as sysdba;

find  /data/awrrpt/delll10*.html  -mtime  +1 -exec rm -f {} \;

cat /data/run/autoawr.sql

rem autoawr.sql

set echo off;
set veri off;
set feedback off;
set termout on;
set heading off;

variable rpt_options number;

define NO_OPTIONS = 0;
-- define ENABLE_ADDM = 8;

rem according to your needs, the value can be 'text' or 'html'
define report_type='html';
  :rpt_options := &NO_OPTIONS;

variable dbid number;
variable inst_num number;
variable bid number;
variable eid number;
--select max(snap_id)-24 into :bid from dba_hist_snapshot;
  select min(snap_id) into :bid from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd') = (select max(to_char(begin_interval_time,'yyyymmdd'))from dba_hist_snapshot) order by snap_id;
  select max(snap_id) into :eid from dba_hist_snapshot;
  select dbid into :dbid from v$database;
  select instance_number into :inst_num from v$instance;

column ext new_value ext noprint
column fn_name new_value fn_name noprint;
column lnsz new_value lnsz noprint;

select 'txt' ext from dual where lower('&report_type') = 'text';
select 'html' ext from dual where lower('&report_type') = 'html';
select 'awr_report_text' fn_name from dual where lower('&report_type') = 'text';
select 'awr_report_html' fn_name from dual where lower('&report_type') = 'html';
select '80' lnsz from dual where lower('&report_type') = 'text';
select '1500' lnsz from dual where lower('&report_type') = 'html';

set linesize &lnsz;

column report_name new_value report_name noprint;

--select 'awrrpt_1'||:bid||'_'||:eid||'.'||'&ext' report_name from dual;
--select instance_name||'_awrrpt_'||instance_number||'_'||:bid||'_'||:eid||'.'||'&ext' report_name from v$instance;
select 'webdb_'||instance_name||'_awrrpt_'||instance_number||'_'||b.timestamp||'.'||'&ext' report_name from v$instance a ,(select to_char(begin_interval_time,'yyyymmdd') timestamp from dba_hist_snapshot where snap_id = :bid) b;
set termout off;
spool &report_name;

select output from table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid,:eid,:rpt_options ));
spool off;
set termout on;
clear columns sql;
ttitle off;
btitle off;
repfooter off;
undefine report_name
undefine report_type
undefine fn_name
undefine lnsz
undefine NO_OPTIONS

2. 各节点开启 FTP 服务及权限设置

chown -R oracle.dba /var/ftp
chmod -R 775 /var/ftp

#service vsftpd status
vsftpd (pid 5113) is running...

3. 中心节点 AWR 收集及 FTP 服务(delll10_autoftp/delll6_autoftp)

cat /data/run/delll10_autoftp

ftp -n <open
user oracle oracle*delldb
cd /data/awrrpt
lcd /var/ftp/
mget *

cat /data/run/delll6_autoftp

ftp -n <open
user oracle oracle*l6db
cd /data/awrrpt
lcd /var/ftp/
mget *

4. 中心节点启用 sendmail 设置(/etc/hosts,/etc/resolv.conf,/etc/mail/及服务启动

cat /etc/hosts

# Do not remove the following line, or various programs
# that require network functionality will fail.               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6  wsjdelll10sty  wsjdelll10sty.

cat /etc/resolv.conf

search localdomain
nameserver IP)

cat /etc/mail/

# "Smart" relay host (may be null)



#service sendmail restart
#service xinetd restart


5. 中心节点 mail 发送设置(mail.txt/

cat /data/run/mail.txt

Dear all:                                                                                                                          

  This mail is automatically generated by wsj delll10 and delll6 DB, which is about the database's detailed perfomance monitor report!

  You can click the link ( ) to download them for a look . for a dba , please analyze the daily awr report.

cat /data/run/

#cd /data/awrrptbak/,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
#d=`date --date='1 days ago' "+%d"`
#filedate=`date +%Y%m`$d
filedate=`date --date='1 days ago' '+%Y%m%d'`
title='WSJ Oracle Databases Performance Report - '$filedate' !'
#title='DFMS RAC database & B2B RAC Core database performance report-'$filedate'!'
#(cat /data/run/mail.txt && uuencode $attachment1 $attachment2)|mailx -s "$title" $maillist
(cat /data/run/mail.txt )|mailx -s "$title" $maillist


6. AWR 文件清理(/data/run/mailclear)

cat /data/run/mailclear

echo "">/var/spool/mail/oracle

7. 定制任务(crontab -e)

30 0 * * * sh /data/run/awrrun 1>/data/run/log/autoawr.log 2>/data/run/log/autoawr.bad

0 2 * * * sh /data/run/delll6_autoftp 1>/data/run/log/delll6_autoftp.log 2>/data/run/log/delll6_autoftp.bad
5 2 * * * sh /data/run/delll10_autoftp 1>/data/run/log/delll10_autoftp.log 2>/data/run/log/delll10_autoftp.bad
10 2 * * * sh /data/run/webdb_autoftp 1>/data/run/log/webdb_autoftp.log 2>/data/run/log/webdb_autoftp.bad

0 3 * * * sh /data/run/autoclearawr 1>/data/run/log/autoclearawr.log 2>/data/run/log/autoclearawr.bad

0 3 * * * sh /data/run/ 1>/data/run/log/mailsend.log 2>/data/run/log/mailsend.bad
0 4 * * * sh /data/run/ 1>/data/run/log/mailclear.log 2>/data/run/log/mailclear.bad

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

下一篇: net send message alert
请登录后发表评论 登录


  • 博文量
  • 访问量