经常碰到数据库因为归档日志堆积而造成磁盘空间满的情况,这里如果备库归档不备份的话,则需要定期删除。
下面的脚本在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.
下面的脚本在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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/628922/viewspace-1181660/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/628922/viewspace-1181660/