Linux shell 腳本清除歸檔日志

　　有些時候，資料庫處于同步資料的狀态，archive歸檔日志不停的增長會導緻磁盤空間被大量耗用，以至于歸檔空間占滿，而是導緻資料庫執行個體挂起。對于這種情形，可以使用一個shell腳本來定時自動清除這些歸檔日志，使資料庫能正常運作。

1.清除歸檔日志shell腳本

#!/bin/bash

#定義運作環境

. ~/.bash_profile

ARCH_MAX_VALUE=75

DEL_lOGS_INST=49

KEEP_ARCH_DAYS=5

echo "================"

echo `date +%Y%m%d%t%T`

echo ""

#擷取目前歸檔使用率

function getArchUsage() {

ARCH_USED=`

sqlplus -S /nolog << EOF

connect / as sysdba

set echo off feedback off heading off underline off

select PERCENT_SPACE_USED from v\\$flash_recovery_area_usage where FILE_TYPE='ARCHIVED LOG';

exit;

EOF`

}

#删除N天前的歸檔

function delArchLogDaysAgo() {

rman target / nocatalog log /dev/null <<EOF

crosscheck archivelog all;

delete noprompt force archivelog until time 'sysdate-$KEEP_ARCH_DAYS';

exit

EOF

function getArchLogSeq() {

sqlplus -s /nolog <<EOF

connect /as sysdba

spool /tmp/applied.log

set pagesize 0

set head off

set feedback off

set linesize 120

select thread#||':'||min(sequence#+$DEL_lOGS_INST) from v\$archived_log where status='A' and first_time<sysdate-1/3 group by thread#;

spool off

function CompareArchMaxValue() {

getArchUsage

#比較歸檔使用率與門檻值

if [ $ARCH_USED -ge $ARCH_MAX_VALUE ];then

echo "\n"`date +%Y%m%d%t%T` Event_Message: flash_recovery_area_usage is $ARCH_USED% larger than $ARCH_MAX_VALUE%.Waiting to delete archive log ...

getArchLogSeq

DelArchLogs

#如果歸檔使用率小于門檻值，輸出目前歸檔使用率，結束該腳本

else

echo "\n"`date +%Y%m%d%t%T` Event_Message: flash_recovery_area_usage is $ARCH_USED% lower than $ARCH_MAX_VALUE%.

fi

#删除歸檔到指定sequence

function DelArchLogs() {

while read line

do

thread=`echo $line |awk -F ":" '{print $1}'`

sequence=`echo $line |awk -F ":" '{print $2}'`

echo "thread:$thread sequence:$sequence"

$ORACLE_HOME/bin/rman target / nocatalog <<EOF

#crosscheck archivelog all;

delete noprompt force archivelog until sequence $sequence thread $thread;

echo "\n"`date +%Y%m%d%t%T` Event_Message:Have deleted `echo $DEL_lOGS_INST+1|bc` archive logs.

done < /tmp/applied.log

CompareArchMaxValue

############################

#

#Main()

delArchLogDaysAgo