（轉）：oracle、filesystem、backup日常巡檢指令碼

1.總指令碼xunjian.code

export PS1="`hostname`$"'[$PWD] '
umask 022
cd /u02/xunjian
#######################xunjian check#################################################

#----1. filesystem check 檔案系統檢查
    rm -f /u02/xunjian/devdb2_xunjian.txt
    echo "
 1.filesystem check" >> /u02/xunjian/devdb2_xunjian.txt
 head -11 /u02/xunjian/devdb1_xunjian.txt >> /u02/xunjian/devdb2_xunjian.txt

    echo "###############182.22.3.111######################" >> /u02/xunjian/devdb2_xunjian.txt
    echo "
 182.22.3.111 filesystem check" >> /u02/xunjian/devdb2_xunjian.txt

            filesystem='/usr /var /tmp /home /opt /u01 /u02'
            for fs in $filesystem
            do
              size=`df -k $fs|grep $fs|awk '{ print $4 }'`
              lowlimit=`more fslimite.txt|grep $fs|awk '{ print $2 }'`
              s=`echo $size|tr -d "%"`
              l=`echo $lowlimit|tr -d "%"`
#        if [ $s -lt $l ]
#        then
                echo $fs "now:" $size "normal:"$lowlimit >> /u02/xunjian/devdb2_xunjian.txt
#        fi
            done
#    ssh devdb1

#----2. database status check 資料庫狀態檢查
     echo "
 2.database status check" >> /u02/xunjian/devdb2_xunjian.txt
         sqlplus -ssystem/devdb1234@devdb1@/u02/xunjian/database_check.code >> /u02/xunjian/devdb2_xunjian.txt
         sqlplus -ssystem/devdb1234@devdb2@/u02/xunjian/database_check.code >> /u02/xunjian/devdb2_xunjian.txt

#----3. database tablespace check 資料庫空間大小檢查
    echo "
 3.database tablespace check " >> /u02/xunjian/devdb2_xunjian.txt
         sqlplus -ssystem/devdb1234@devdb1@/u02/xunjian/tablespace_check.code >> /u02/xunjian/devdb2_xunjian.txt

#----4. TSM check 磁帶庫日誌檢查
    echo "
 4.TSM status check" >> /u02/xunjian/devdb2_xunjian.txt
    cd /u02/expdata
    backdate=`date +%Y%m%d`
    more /u02/expdata/dsmc.log | grep dmp |grep $backdate|awk '$2 == "B" {print $3 "\t" $4 "\t" $7 }' >> /u02/xunjian/devdb2_xunjian.txt
  
#----5. delete dmp file check 只保留最近2天的dmp檔案
    echo "
 5. delete already archivelog dmp file check" >> /u02/xunjian/devdb2_xunjian.txt
    cd /u02/expdata/backup
   find /u02/expdata/backup -mtime +2 \( -name 'sg*' -o -name 'nj*' \) -exec rm -f {} \;--保留最近2天的檔案，檔名還有sg或者nj的
    ls -l -t |grep dmp|awk '{print $6$7 "\t" $8 "\t" $9 "\t" $5"k" }'>> /u02/xunjian/devdb2_xunjian.txt

#----6. rman full backup check rman日誌的檢查
    tail -9 /u02/xunjian/devdb1_xunjian.txt >> /u02/xunjian/devdb2_xunjian.txt
    echo "

    182.22.3.110 check finished
    182.22.3.111 check finished" >> /u02/xunjian/devdb2_xunjian.txt

2.資料庫狀態檢查database_check.code


-- +----------------------------------------------------------------------------+
-- |                           - INSTANCE OVERVIEW -                            |
-- +----------------------------------------------------------------------------+

prompt   Instance Overview
COLUMN host_name   format a20
set linesize 300
 
SELECT  host_name         ,
        instance_name     ,
        instance_number   ,
        version           ,
        TO_CHAR(startup_time,'mm/dd/yyyy HH24:MI:SS') start_time,
        status            ,
        DECODE(archiver, 'FAILED', archiver,archiver)  archiver,
        DATABASE_STATUS
FROM v$instance
ORDER BY instance_number;

exit

3.資料庫空間大小檢查tablespace_check.code


-- +----------------------------------------------------------------------------+
-- |                           - TABLESPACE OVERVIEW -                            |
-- +----------------------------------------------------------------------------+
 
prompt   TABLESPACE OVERVIEW
COLUMN tablespace_name   format a20
COLUMN autoextensible    format a15
COLUMN total             format a15
COLUMN used              format a15
COLUMN free              format a15    
COLUMN used_percent      format a25
set linesize 300
set pagesize 200
 
select a.tablespace_name, a.autoextensible,
       total_m||'M' total,
       (case when total_m-free_m < 1 then '0' else null end)||to_char(round(total_m-free_m,2))||'M' used ,
       round(free_m,2)||'M' free,
       100*round((total_m-free_m)/total_m,4)||'%' used_percent
  from
  (
  select a.tablespace_name, a.autoextensible,sum(a.bytes)/1024/1024  total_m
  from dba_data_files a
  group by a.tablespace_name, a.autoextensible
  )a,
  (select  b.tablespace_name, sum(b.bytes)/1024/1024 free_m
   from dba_free_space b
   group by b.tablespace_name
  ) b
  where a.tablespace_name = b.tablespace_name
  order by autoextensible asc;
 
  exit

4. 邏輯備份exp指令碼

export ORACLE_SID=devdb2
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$PATH
export NLS_LANG=american_america.ZHS16GBK
export PS1="`hostname`$"'[$PWD] '
umask 022
#######################exp devdb data#######################################################

 backdate=`date +%Y%m%d%H%M`
 begintime=`date +%Y-%m-%d:%H-%M`

cd /u02/expdata/backup
expleiz/leiz@devdb2file=/u02/expdata/backup/leiz_$backdate.dmp log=/u02/expdata/backup/leiz_$backdate.log wner=leiz


5. tsm磁帶庫備份

cd /usr/tivoli/tsm/client/ba/bin/

dsmc incremental -subdir=yes "/u02/expdata/backup/*"  >> /u02/expdata/dsmc.log
echo "######################`date`##############################################" >> /u02/expdata/dsmc.log
dsmc q backup -inactive -subdir=yes "/u02/expdata/backup/*" >> /u02/expdata/dsmc.log

6.傳送郵件

VA_HOME=/usr/java14/bin
MAILTO_HOME=/usr/local/mailto
MAILFROM_USER=leiz@163.com.cn
MAILFROM_PASS=12345
MAILSERVERIP=172.22.3.113
MAILTO_USER=leiz@163.com.cn

PATH=$JAVA_HOME:$PATH
cd $MAILTO_HOME
java -cp ./activation.jar:./dsn.jar:./imap.jar:./mail.jar:./mailapi.jar:./pop3.jar:./s
mtp.jar:./mailto.jar mail.Mailto $MAILFROM_USER $MAILFROM_PASS $MAILSERVERIP $MAILTO_U
SER "$1" $2 $3