Data Guard 自动删除归档日志的脚本

Data Guard 备库的归档日志 需要手工删除，主库上若有rman备份，可在backup命令中加delete。

$ cat delarch.sh
#!/bin/bash
# Program:
#       This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
# History:
# 2016/09/21 sxhua First release


source /home/oracle/.bash_profile



ArgNum=1
retention=$1
script=`basename $0`
dir=/tmp
tmpf=$dir/.$script.tmp
LogFile=/home/oracle/maintain/delete_arch.log

echo "" >> $LogFile
echo "" >> $LogFile
echo "####  `date +'%Y-%m-%d %H:%M:%S'` START  ####" >> $LogFile
echo "" >> $LogFile


# basename dirname
usage()
{ #usage
echo " USAGE: `basename $0` $retention"  >> $LogFile
exit 2
}



if [ ! $# -eq $ArgNum ];then
echo " "
echo " Incorrect parameter" >> $LogFile
usage
fi



# primary: CURRENT  standby: STANDBY
function GetDBRole
{
sqlplus -S /nolog <<EFF
<eof
connect / as sysdba
set head off
set feedback off
set pages 0
select controlfile_type from v\$database;
exit
EFF
return
}



# get archived log list for standby database
function GetLogListForStandby
{
sqlplus -S /nolog <<EFF       > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0
select name from (
  select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
         count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn
   from v\$archived_log a
  where a.completion_time< sysdate - $retention
      and a.resetlogs_id in (
        select i.resetlogs_id from v\$database_incarnation i where status = 'CURRENT')
)
where rn=1 and cn=1
order by sequence#;

exit
EFF
return
}



# get archived log list for primary database
function GetLogListForPrimary
{
sqlplus -S /nolog <<EFF      > $tmpf
connect / as sysdba
set head off
set feedback off
set pages 0

select name from (
   select name,sequence#,row_number() over(partition by a.sequence# order by name) rn,
          sum(backup_count) over(partition by a.sequence# ) bk_cnt,
          count(decode(applied,'YES',1,null)) over (partition by a.sequence#) cn
     from v\$archived_log a
    where a.completion_time< sysdate - $retention
        and completion_time and a.resetlogs_id in (
          select i.resetlogs_id from v\$database_incarnation i where status = 'CURRENT')
   )
where rn=1 and cn=1 and bk_cnt>0
order by sequence#;

exit
EFF
return
}



# delete control file recoder
function DelControlFileRecorder
{
rman target / <<eof
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit;
EOF

}




# check database role
DBROLE=`GetDBRole`

NUM=0

if [ $DBROLE = "CURRENT" ];then
echo "It's a primary database ......" >> $LogFile
# get archived log list for primary
GetLogListForPrimary

elif [ $DBROLE = "STANDBY" ];then
echo "It's a standby database ......" >> $LogFile
# get archived log list for standby
GetLogListForStandby
fi

echo "deleting archived log files ......" >> $LogFile

if [ -n $tmpf ]; then
for ARCH in `cat $tmpf`;do
if [ -f $ARCH ];then
NUM=`expr $NUM + 1`
rm -f $ARCH
fi
done
fi

rm -f $tmpf

echo "finished deleting $NUM files" >> $LogFile



# delete control file recoder
echo "deleting control file archivelog recoder ......" >> $LogFile
DelControlFileRecorder

echo ""  >> $LogFile
echo "####   `date +'%Y-%m-%d %H:%M:%S'` END   ####"  >> $LogFile
echo ""  >> $LogFile





0 */8 * * * /home/oracle/maintain/delete_arch.sh 1 > /dev/null 2>&1
</eof
</eof

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/807718/viewspace-2125290/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/807718/viewspace-2125290/