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
#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/25198367/viewspace-696263/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25198367/viewspace-696263/