Alex Lima

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

Published by

Alex Lima

July 6, 2009

#!/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 responses to “Script to delete arch logs from the Standby database host after applied”

Jurijs Velikanovs

July 6, 2009

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

LikeLike

Reply
1. Alex Lima
  
  July 6, 2009
  
  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
  
  LikeLike
  
  Reply
  1. Edgar Chupit
    
    July 7, 2009
    
    Hi Alex,
    
    Just show them Yury proposed solution as another reason for considering RMAN even in NetApp environment :)
    
    LikeLike
Alex Lima

July 10, 2009

Hi Edgar,

Not sure I understood your comment..
Alex

LikeLike

Reply
syed

August 5, 2010

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

LikeLike

Reply
Pablo

August 12, 2010

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

LikeLike

Reply
Farrukh Rizvi

May 30, 2013

Thanks Syed Zulfiqar

LikeLike

Reply