awr 自動mail

1. 各節點 AWR 自動生成 script. 設定(awrrun/autoawr.sql)

cat /data/run/awrrun

#!/bin/sh

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;
@/data/run/autoawr.sql;
exit
!

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';
begin
  :rpt_options := &NO_OPTIONS;
end;
/

variable dbid number;
variable inst_num number;
variable bid number;
variable eid number;
begin
--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;
end;
/

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 xxx.xxx.xxx.xxx
user oracle oracle*delldb
cd /data/awrrpt
lcd /var/ftp/
bin
prompt
mget *
bye
!

cat /data/run/delll6_autoftp

ftp -n <open xxx.xxx.xxx.xxx
user oracle oracle*l6db
cd /data/awrrpt
lcd /var/ftp/
bin
prompt
mget *
bye
!

4. 中心節點啟用 sendmail 設定(/etc/hosts,/etc/resolv.conf,/etc/mail/sendmail.cf)及服務啟動

cat /etc/hosts

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

cat /etc/resolv.conf

search localdomain
nameserver 10.109.131.132(DNS IP)

cat /etc/mail/sendmail.cf

# "Smart" relay host (may be null)

#DS[10.150.7.22]

DS[10.17.48.12] (SMTP IP)

#service sendmail restart
#service xinetd restart

 

5. 中心節點 mail 傳送設定(mail.txt/mailsend.sh)

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 ( ftp://10.109.196.141 ) to download them for a look . for a dba , please analyze the daily awr report.

cat /data/run/mailsend.sh

#!/bin/sh
#cd /data/awrrptbak/
#maillist=chi-gang.dong@foxconn.com,andy.y.xu@foxconn.com,qi.wang@foxconn.com,pcebg-it-dba@foxconn.com,maggie.q.wan@foxconn.com,jonkey.gf.wang@foxconn.com,wison.wt.he@foxconn.com,gavin.zh.zheng@foxconn.com,alex.lj.yeh@foxconn.com,jack.s.li@foxconn.com,sailing.hx.wang@foxconn.com,ivan.segovia@foxconn.com,arturo.rios@foxconn.com,harrison.han@foxconn.com,liaoyuan.shu@foxconn.com,wei.w.chen@foxconn.com,Steven.pg.lu@foxconn.com,patrick.sw.xu@foxconn.com,edwin.j.tan@foxconn.com,zhi-yuan.zhong@foxconn.com,mei-kai.pan@foxconn.com,demon.jh.jin@foxconn.com,mark.cy.liu@foxconn.com,edgarsosamarin@foxconn.com,Felipe.Pasillas@foxconn.com,Ivan.Valenciana@foxconn.com,sergio.aguila@foxconn.com

maillist=andy.y.xu@foxconn.com,qi.wang@foxconn.com,pcebg-it-dba@foxconn.com,maggie.q.wan@foxconn.com,de-jin.zhou@foxconn.com,shu-yang.duan@foxconn.com,ao.ga.geng@foxconn.com,will.wy.chu@foxconn.com,zheng-bang.liu@foxconn.com,sky.qy.chen@foxconn.com,pceg-wsj-it@foxconn.com,joe.liu@foxconn.com,win.yx.huang@foxconn.com,kinglion.hx.liu@foxconn.com

#maillist=de-jin.zhou@foxconn.com,pcebg-it-dba@foxconn.com

#maillist=ivan.segovia@foxconn.com
#maillist=chi-gang.dong@foxconn.com,qi.wang@foxconn.com
#d=`date --date='1 days ago' "+%d"`
#filedate=`date +%Y%m`$d
filedate=`date --date='1 days ago' '+%Y%m%d'`
#attachment1=/data1/logbak/mxdell1_awrrpt_1_$filedate.html
#attachment2=mxdell1_awrrpt_1_$filedate.html
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/mailsend.sh 1>/data/run/log/mailsend.log 2>/data/run/log/mailsend.bad
0 4 * * * sh /data/run/mailclear.sh 1>/data/run/log/mailclear.log 2>/data/run/log/mailclear.bad

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25850100/viewspace-703982/，如需轉載，請註明出處，否則將追究法律責任。