#!/usr/bin/ksh
##################################################################################################################
#
# This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
#
##################################################################################################################
script=`basename $0`
export ORACLE_SID=$1
dir=/oracle/EPW/${ORACLE_SID}/temp/arch
tmpf=$dir/.$script.tmp
logcount=50
function GetAppliedLogfileSequenceNumber
{
sqlplus -S /nolog <<EOF > $tmpf
connect / as sysdba
set head off
set pages 0
select max(sequence#) from v\$archived_log where applied = ‘YES’;
select resetlogs_id from v\$database_incarnation where status = ‘CURRENT’;
exit
EOF
return
}
if [ -d $dir ]
then
cd $dir
GetAppliedLogfileSequenceNumber
if [ -s $tmpf ]
then
count=`cat $tmpf | awk ‘{print $1}’ | sed -n ‘1p’;`
db_incarnation=`cat $tmpf | awk ‘{print $1}’ | sed -n ‘3p’;`
if [ ${#count} -ne 0 ]
then
let count=$count-$logcount
if ((count <= 0))
then
echo “$script: log count is set to (non)zero no log(s) to remove”
exit 0
fi
else
exit 0
fi
else
echo “$script: no archive log(s) to remove”
exit 0
fi
while [ -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc ]
do
rm -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
#ls ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
rcode=$?
if ((rcode != 0))
then
echo “$script: cannot remove: ${ORACLE_SID}_${count}_1_${db_incarnation}.arc”
exit 1
else
let count=$count-1
fi
done
rm -f $tmpf
else
print “$script: $dir no such file or directory”
exit 1
fi
exit 0
An Oracle Spin by Alex Lima 
Hi Alex,
It is a nice script. Thank you for publishing it.
Just wonder if you have considered to use RMAN for deleting applied archivelogs?
Instead of doing “while … rm -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc …” just run RMAN delete command.
Other option to use in DataGuard environments might be ARCHIVELOG DELETION POLICY. This options eliminates any scripts on DR site 😉
Yury
Hi Yury,
Thanks for your comments.. The reason I am not using RMAN is because on this shop they use netapp snapshot for backups and they don’t like RMAN there.
So, i was trying to come up with a solution without it, that said I had not use the ARCHIVELOG DELETION POLICY, I will definitely look into that.
Thanks a alot.
Keep in touch.. Where are you located?
Alex Lima
Hi Alex,
Just show them Yury proposed solution as another reason for considering RMAN even in NetApp environment 🙂
Hi Edgar,
Not sure I understood your comment..
Alex
Hi Alex,
Thanks for sharing the script.
Here is an windows batch script to delete the applied archive logs from primary and standby servers.
Remove applied archive logs from primary and standby servers
Syed
Hello, it’s a very nice script but i don’t understand what does a variable logcount..do you explain me because if the max sequence is for example 300, the variable count never will < 0 and if i run the script yesterday and i try run script again today, should not be archivelogs, so the script would not pass for "if ((count <= 0))
then
echo “$script: log count is set to (non)zero no log(s) to remove”
exit 0
fi
else
exit 0
fi
else
echo “$script: no archive log(s) to remove”
exit 0
fi
Thanks Syed Zulfiqar