資料庫監控

########### dp backup ###########################
30 19 * * * sh /home/oracle/export.sh > /home/oracle/export.log
########### rman backup ####################################
00 12 * * 0 sh /home/oracle/incr0.sh > /home/oracle/incr0.log
00 21 * * 1,2,3,4,5,6 sh  /home/oracle/incr1.sh >  /home/oracle/incr1.log
########### delete log #############################
30 04 * * * sh /home/oracle/script/delete_archvelog.sh >/home/oracle/script/delete.log
############# Daily monitor to Server ##########################
51 23 * * * df -h >/exp/sys/disk
51 23 * * * free -m >/exp/sys/mem
0 0 * * * iostat -xt 300 144 > /exp/sys/cpudiskam
0 12 * * * iostat -xt 300 144 > /exp/sys/cpudiskpm
52 23 * * * sh /exp/sys/check.sh
15 12 * * * sh /exp/sys/checkcpuam.sh
15 00 * * * sh /exp/sys/checkcpupm.sh
*/10 * * * * sh /exp/sys/session.sh
備份指令碼
[oracle@testdb2 dbs]$ cat /home/oracle/export.sh
#!/bin/sh
#add date:212/07/30
#function:using data pump backup databae
export ORACLE_SID=fktestdb2
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
action_time=`date +%Y_%m_%d`
del_time=`date -d "10 day ago"  +%Y_%m_%d`
echo action time:$action_time
filename=fktestdb2_$action_time.dump
logname=fktestdb2_$action_time.log
expdp system/syspass content=all full=y  directory=dump dumpfile=$filename logfile=$logname
cd /u03/orabackup/dump
tar -czvf fktestdb2_$action_time.tar.gz $filename $logname
mv *.gz *.log /u09/dump
rm $filename $logname
#del dump data
path="/u09/dump"
echo "delete  dump data 10 day ago"
rm -rf $path/fktestdb2_$del_time.tar.gz
rm -rf $path/fktestdb2_$del_time.log
echo "delete 7 dump data OK!
全備
[oracle@testdb2 dbs]$ cat /home/oracle/incr0.sh
export ORACLE_SID=fktestdb2
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
rman target / << EOF
backup current controlfile format '/u09/backup/fktestdb2/control/%d_%s_%p_%c.ctl';
sql 'alter system switch logfile';
backup incremental level 0 database plus archivelog delete input;
exit
EOF
[oracle@testdb2 dbs]$ cat /home/oracle/incr1.sh
export ORACLE_SID=fktestdb2
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
rman target / << EOF
backup current controlfile format '/u09/backup/fktestdb2/control/%d_%s_%p_%c.ctl';
sql 'alter system switch logfile';
backup incremental level 1 database plus archivelog delete input;
exit
EOF
刪除歸檔日誌
[oracle@testdb2 dbs]$ cat /home/oracle/script/delete_archvelog.sh
#!/bin/bash
export ORACLE_SID=fktestdb2
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
rman target / << EOF
delete force noprompt archivelog all completed before 'sysdate-3';
delete force noprompt backupset completed before 'sysdate-6';
exit;
EOF
#delete archivelog on standby database completed  before 14 days
rman target sys/syspass@testdb << EOF
delete force noprompt archivelog all completed before 'sysdate-7';
exit;
EOF

#delete archivelog on standby database completed  before 14 days
rman target sys/syspass@test2sb << EOF
delete force noprompt archivelog all completed before 'sysdate-7';
exit;
EOF

硬碟監控
[oracle@testdb2 dbs]$ cat /exp/sys/check.sh
rm -rf /exp/sys/disk_* /exp/sys/mem_* /exp/sys/cpu_* /exp/sys/disk.sql /exp/sys/mem.sql /exp/sys/cpu.sql
cat /exp/sys/disk|grep -v r|awk '{print $1}' >/exp/sys/disk1
cat /exp/sys/disk|grep -v r|awk '{print $2}' >/exp/sys/disk2
cat /exp/sys/disk|grep -v r|awk '{print $3}' >/exp/sys/disk3
cat /exp/sys/disk|grep -v r|awk '{print $4}' >/exp/sys/disk4
cat /exp/sys/disk|grep -v r|awk '{print $5}' >/exp/sys/disk5
cat /exp/sys/disk|grep -v r|awk '{print $6}' >/exp/sys/disk6
cat /exp/sys/disk2|sed s/^/,\'\/g>/exp/sys/disk_2
cat /exp/sys/disk3|sed s/^/,\'\/g>/exp/sys/disk_3
cat /exp/sys/disk4|sed s/^/,\'\/g>/exp/sys/disk_4
cat /exp/sys/disk5|sed s/^/,\'\/g>/exp/sys/disk_5
cat /exp/sys/disk6|sed s/^/,\'\/g>/exp/sys/disk_6
cat /exp/sys/disk_2|sed s/$/\'\/g>/exp/sys/disk_22
cat /exp/sys/disk_3|sed s/$/\'\/g>/exp/sys/disk_33
cat /exp/sys/disk_4|sed s/$/\'\/g>/exp/sys/disk_44
cat /exp/sys/disk_5|sed s/$/\'\/g>/exp/sys/disk_55
cat /exp/sys/disk_6|sed s/$/\'\/g>/exp/sys/disk_66
cat /exp/sys/disk1|sed s/^/insert\ into\ checkdisk\ values\(\'10\.182\.15\.85\',\'/g>/exp/sys/disk_1
cat /exp/sys/disk_1|sed s/$/\'\/g>/exp/sys/disk_11
paste /exp/sys/disk_11 /exp/sys/disk_22 >/exp/sys/disk_21
paste /exp/sys/disk_21 /exp/sys/disk_33 >/exp/sys/disk_31
paste /exp/sys/disk_31 /exp/sys/disk_44 >/exp/sys/disk_41
paste /exp/sys/disk_41 /exp/sys/disk_55 >/exp/sys/disk_51
paste /exp/sys/disk_51 /exp/sys/disk_66 >/exp/sys/disk_61
cat /exp/sys/disk_61|sed s/$/\)\;/g >>/exp/sys/disk.sql

cat /exp/sys/mem|grep -v r|awk '{print $1}' >/exp/sys/mem1
cat /exp/sys/mem|grep -v r|awk '{print $2}' >/exp/sys/mem2
cat /exp/sys/mem|grep -v r|awk '{print $3}' >/exp/sys/mem3
cat /exp/sys/mem|grep -v r|awk '{print $4}' >/exp/sys/mem4
cat /exp/sys/mem2|sed s/^/,\/g>/exp/sys/mem_22
cat /exp/sys/mem3|sed s/^/,\/g>/exp/sys/mem_33
cat /exp/sys/mem4|sed s/^/,\/g>/exp/sys/mem_44
cat /exp/sys/mem1|sed s/^/insert\ into\ checkmem\ values\(\'10\.182\.15\.85\',\'/g>/exp/sys/mem_1
cat /exp/sys/mem_1|sed s/$/\'\/g>/exp/sys/mem_11
paste /exp/sys/mem_11 /exp/sys/mem_22 >/exp/sys/mem_21
paste /exp/sys/mem_21 /exp/sys/mem_33 >/exp/sys/mem_31
paste /exp/sys/mem_31 /exp/sys/mem_44 >/exp/sys/mem_41
cat /exp/sys/mem_41|sed s/$/\)\;/g >>/exp/sys/mem.sql

echo 'exit' >>/exp/sys/mem.sql
echo 'exit' >>/exp/sys/disk.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
#$ORACLE_HOME/bin/sqlplus oscheck/checkdb@test @/exp/sys/cpu.sql
$ORACLE_HOME/bin/sqlplus oscheck/checkdb@test @/exp/sys/mem.sql
$ORACLE_HOME/bin/sqlplus oscheck/checkdb@test @/exp/sys/disk.sql
CPU監控
[oracle@testdb2 dbs]$ cat /exp/sys/checkcpuam.sh
sysdir="/exp/sys/"
rm -rf /exp/sys/cpuam_*  /exp/sys/cpuam3 /exp/sys/cpuam4 /exp/sys/cpuam5 /exp/sys/cpuam6 /exp/sys/cpuam7  /exp/sys/cpuam.sql
cp /exp/sys/cpudiskam /exp/sys/cpudiskam.bak
i=1
cn=5
cat $sysdir/cpudiskam.bak|while read line
do
        if [ "$i" -eq "$cn" ];
        then
                echo $line|awk '{print $1}' >>$sysdir/cpuam3
                echo $line|awk '{print $2}' >>$sysdir/cpuam4
                echo $line|awk '{print $3}' >>$sysdir/cpuam5
                echo $line|awk '{print $4}' >>$sysdir/cpuam6
                echo $line|awk '{print $6}' >>$sysdir/cpuam7
                cn=$(($cn + 20))
        fi
     i=$(($i+1))
done
cat /exp/sys/cpuam3|sed s/^/,\/g>/exp/sys/cpuam_33
cat /exp/sys/cpuam4|sed s/^/,\/g>/exp/sys/cpuam_44
cat /exp/sys/cpuam5|sed s/^/,\/g>/exp/sys/cpuam_55
cat /exp/sys/cpuam6|sed s/^/,\/g>/exp/sys/cpuam_66
cat /exp/sys/cpuam7|sed s/^/,\/g>/exp/sys/cpuam_77
cat /exp/sys/tam|sed s/^/insert\ into\ checkcpu\ values\(\'10\.182\.15\.85\',\'/g>/exp/sys/cpuam_11
paste /exp/sys/cpuam_11 /exp/sys/cpuam_33 >/exp/sys/cpuam_31
paste /exp/sys/cpuam_31 /exp/sys/cpuam_44 >/exp/sys/cpuam_41
paste /exp/sys/cpuam_41 /exp/sys/cpuam_55 >/exp/sys/cpuam_51
paste /exp/sys/cpuam_51 /exp/sys/cpuam_66 >/exp/sys/cpuam_61
paste /exp/sys/cpuam_61 /exp/sys/cpuam_77 >/exp/sys/cpuam_71
cat /exp/sys/cpuam_71|sed s/$/\)\;/g >>/exp/sys/cpuam.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus oscheck/checkdb@test @/exp/sys/cpuam.sql
[oracle@testdb2 dbs]$ cat /exp/sys/checkcpupm.sh
sysdir="/exp/sys/"
rm -rf /exp/sys/cpupm_*  /exp/sys/cpupm3 /exp/sys/cpupm4 /exp/sys/cpupm5 /exp/sys/cpupm6 /exp/sys/cpupm7  /exp/sys/cpupm.sql
cp /exp/sys/cpudiskpm /exp/sys/cpudiskpm.bak
i=1
cn=5
cat $sysdir/cpudiskpm.bak|while read line
do
        if [ "$i" -eq "$cn" ];
        then
                echo $line|awk '{print $1}' >>$sysdir/cpupm3
                echo $line|awk '{print $2}' >>$sysdir/cpupm4
                echo $line|awk '{print $3}' >>$sysdir/cpupm5
                echo $line|awk '{print $4}' >>$sysdir/cpupm6
                echo $line|awk '{print $6}' >>$sysdir/cpupm7
                cn=$(($cn + 20))
        fi
     i=$(($i+1))
done
cat /exp/sys/cpupm3|sed s/^/,\/g>/exp/sys/cpupm_33
cat /exp/sys/cpupm4|sed s/^/,\/g>/exp/sys/cpupm_44
cat /exp/sys/cpupm5|sed s/^/,\/g>/exp/sys/cpupm_55
cat /exp/sys/cpupm6|sed s/^/,\/g>/exp/sys/cpupm_66
cat /exp/sys/cpupm7|sed s/^/,\/g>/exp/sys/cpupm_77
cat /exp/sys/tpm|sed s/^/insert\ into\ checkcpu\ values\(\'10\.182\.15\.85\',\'/g>/exp/sys/cpupm_11
paste /exp/sys/cpupm_11 /exp/sys/cpupm_33 >/exp/sys/cpupm_31
paste /exp/sys/cpupm_31 /exp/sys/cpupm_44 >/exp/sys/cpupm_41
paste /exp/sys/cpupm_41 /exp/sys/cpupm_55 >/exp/sys/cpupm_51
paste /exp/sys/cpupm_51 /exp/sys/cpupm_66 >/exp/sys/cpupm_61
paste /exp/sys/cpupm_61 /exp/sys/cpupm_77 >/exp/sys/cpupm_71
cat /exp/sys/cpupm_71|sed s/$/\)\;/g >>/exp/sys/cpupm.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus oscheck/checkdb@test @/exp/sys/cpupm.sql

session 監控
[oracle@testdb2 dbs]$ cat  /exp/sys/session.sh
rm /exp/sys/session.sql
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=fktestdb2
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus / as sysdba << eof
declare n number ;
begin
for h in (select s.PROGRAM,s.TERMINAL,s.SCHEMANAME,s.STATUS,p.spid,s.logon_time
                 from V\$session s ,v\$process p
                      WHERE s.TYPE NOT in ('BACKGROUND') and p.addr like s.paddr)
loop
insert into check_session@check_link(host,PROGRAM,TERMINAL,SCHEMANAME,STATUS,spid,logon_time,RECORD_DATE)
values ('10.182.15.85',h.PROGRAM,h.TERMINAL,h.SCHEMANAME,h.STATUS,h.spid,h.logon_time,sysdate);
end loop ;
select  count(1) into n from v\$session where type not in ('BACKGROUND');
insert into session_all@check_link
(host,session_num,record_date)
values ('10.182.15.85',n,sysdate);
end;
/
commit;
exit;
eof
ps -ef |grep oracle |grep LOCAL > /exp/sys/session
awk '{print $2}' /exp/sys/session > /exp/sys/session2
awk '{print $7}' /exp/sys/session > /exp/sys/session7
cat /exp/sys/session2|sed s/^/,\'\/g>/exp/sys/session_2
cat /exp/sys/session7|sed s/^/,\'\/g>/exp/sys/session_7
cat /exp/sys/session_2|sed s/$/\'\/g>/exp/sys/session_22
cat /exp/sys/session_7|sed s/$/\'\/g>/exp/sys/session_77
cat /exp/sys/session2|sed s/^/insert\ into\ check_temp\ values\(\'10\.182\.15\.85\',\'/g>/exp/sys/session_27
cat /exp/sys/session_27|sed s/$/\'\/g>/exp/sys/session27
paste /exp/sys/session27 /exp/sys/session_77 >/exp/sys/session277
cat /exp/sys/session277|sed s/$/\,sysdate\)\;/g >>/exp/sys/session.sql
echo 'exit' >>/exp/sys/session.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus oscheck/checkdb@test @/exp/sys/session.sql