Script to delete arch logs from the Standby database host after applied

#!/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

7 thoughts on “Script to delete arch logs from the Standby database host after applied

  1. 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🙂

  2. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s