備庫自動刪除已應用的歸檔日誌

經常碰到資料庫因為歸檔日誌堆積而造成磁碟空間滿的情況，這裡如果備庫歸檔不備份的話，則需要定期刪除。
下面的指令碼在AIX6.1上執行透過：
REPORT="/tmp/delArch.log"
logtokeep=40----想保留的歸檔Seq個數
#source related profile
. /home/oracle/.profile_11g
echo "*******delete applied archivelog*********\n" > $REPORT
### Get Max sequence# applied from Primary database ###
applied_seq1=`sqlplus -silent /nolog < connect / as sysdba
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select max(sequence#) from v\\$archived_log where applied = 'YES' and REGISTRAR='RFS';
exit;
EOSQL`

### Calculate the archive log to delete ###
arch_to_del1=$(($applied_seq1-$logtokeep))
if [ -z "$arch_to_del1" ]; then
echo "No rows returned from database" >> $REPORT
exit 0
fi
#begin deleting, 如果是RAC，需要加上thread
echo "delete noprompt archivelog until sequence $arch_to_del1;"|rman target / >> $REPORT

Notes，V$ARCHIVED_LOG檢視幾個column解釋:
columns V$ARCHIVED_LOG.REGISTRAR and APPLIED
If REGISTRAR=’RFS’ and APPLIEDis NO, then the log has arrived at the standby but has not yet been applied.
If REGISTRAR=’RFS’ and APPLIED is YES, the log has arrived and been applied at the standby database.

column V$ARCHIVED_LOG.DELETED
Indicates whether an RMAN DELETE command has physically deleted the archived log file from disk (YES) or not (NO)

RMAN EXPIRED
Removes only files whose status in the repository is EXPIRED.
RMAN marks backups and copies as expired when you run a CROSSCHECK command and the files are absent or inaccessible.
To determine which files are expired, run a LIST EXPIRED command.

RMAN NOPROMPT
Beginning in Oracle9i, RMAN’s default behavior is to prompt for confirmation when you run DELETE EXPIRED.
In prior releases, RMAN did not prompt.