Windows (VBS) and UNIX (Shell) scripts to delete applied archive logs

In a dataguard configuration, archive logs should stay on production archive log directory until they are copied and applied to the standby database. Then they should be backed up and removed in order to prevent archive log directories from getting full. Following scripts are written by me to automate this procedure:

Unix:
There should be a file "$HOME/stby/archive_last_deleted " which is used to keep sequence number of the last deleted archive log file. At first you can create it with vi and write 1 in it.

Following scheduled script "$HOME/stby/archclean.sh" runs on standby site to delete applied archived logs:

#/bin/ksh

if [ "$hosttype" == "SunOS" ]
then
userid=`/usr/xpg4/bin/id -u -n`
else
userid=`/usr/bin/id -u -n`
fi
. ~$userid/.profile

sqlplus '/ AS SYSDBA' > $HOME/stby/out/archclean.out1 <
set lines 2000
select 'MAKEGREP' GREP,count(1) from V\$MANAGED_STANDBY where PROCESS='MRP0' and SEQUENCE# <> 0;
exit;
EOF
IS_APPLY_RUNNING=`cat $HOME/stby/out/archclean.out1|grep MAKEGREP|awk '{print $2}'`
date >> $HOME/stby/out/archclean.log
if [[ $IS_APPLY_RUNNING -eq 1 ]];then
echo "Apply process (MRP0) is running..." >> $HOME/stby/out/archclean.log
sqlplus '/ AS SYSDBA' > $HOME/stby/out/archclean.out1 <
set lines 2000
SELECT 'MAKEGREP' GREP,SEQUENCE#-1 FROM V\$MANAGED_STANDBY where PROCESS in ('MRP0') and SEQUENCE# <> 0;
EOF
MAX_SEC_TO_DELETE=`cat $HOME/stby/out/archclean.out1|grep MAKEGREP|awk '{print $2}'`
LAST_DELETED=`cat $HOME/stby/archive_last_deleted`
NEXTTODELETE=$((LAST_DELETED+1))
echo "Archive Sequence Range to Clean: $NEXTTODELETE - $MAX_SEC_TO_DELETE" >> $HOME/stby/out/archclean.log
ARCHIVE_FORMAT=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_FORMAT|cut -d= -f2`
ARCHIVE_DIR=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_DIR|cut -d= -f2`
while [[ $MAX_SEC_TO_DELETE -gt $NEXTTODELETE ]];do
NEXTARCHIVE=`awk 'BEGIN {str="'$ARCHIVE_FORMAT'" ; sub("SEQUENCE","'$NEXTTODELETE'",str) ; print str}'`
FILECOUNT=`ls -l $ARCHIVE_DIR|grep $NEXTARCHIVE|grep ".arc"|wc -l 2>/dev/null`
if [[ $FILECOUNT -eq 1 ]];then
echo "removing $ARCHIVE_DIR/$NEXTARCHIVE" >> $HOME/stby/out/archclean.log
/usr/bin/rm -f $ARCHIVE_DIR/$NEXTARCHIVE
fi
echo $NEXTTODELETE>$HOME/stby/archive_last_deleted
NEXTTODELETE=$(($NEXTTODELETE+1))
done
else
echo "Apply process (MRP0) is not running..." >> $HOME/stby/out/archclean.log
fi

At production site my backup procedure updates "$HOME/stby/backupseq" with the last backed up archive sequence number. There should be a file "$HOME/stby/archive_last_deleted " which is used to keep sequence number of the last deleted archive log file. At first you can create it with vi and write 1 in it. My following scheduled script "$HOME/stby/archcleanprod.sh" deletes all archived which are backed up and applied to standby database:

#/bin/ksh

if [ "$hosttype" == "SunOS" ]
then
userid=`/usr/xpg4/bin/id -u -n`
else
userid=`/usr/bin/id -u -n`
fi
. ~$userid/.profile

sqlplus / > $HOME/stby/out/archcleanprod.out1 <
set lines 2000
select 'MAKEGREP' GREP,APPLIED_SEQ#-10 from V\$ARCHIVE_DEST_STATUS where DEST_ID=2;
exit;
EOF
MAX_SEC_APPLIED=`cat $HOME/stby/out/archcleanprod.out1|grep MAKEGREP|awk '{print $2}'`
MAX_SEQ_NMBCOPY_2=`cat $HOME/stby/backupseq`
if [[ $MAX_SEQ_NMBCOPY_2 -gt $MAX_SEC_APPLIED ]];then
MAX_SEC_TO_DELETE=$MAX_SEC_APPLIED
else
MAX_SEC_TO_DELETE=$MAX_SEQ_NMBCOPY_2
fi
LAST_DELETED=`cat $HOME/stby/archive_last_deleted`
NEXTTODELETE=$((LAST_DELETED+1))
date >> $HOME/stby/archcleanprod.log
echo "Archive Sequence Range to Clean: $NEXTTODELETE - $MAX_SEC_TO_DELETE" >> $HOME/stby/archcleanprod.log
ARCHIVE_FORMAT=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_FORMAT|cut -d= -f2`
ARCHIVE_DIR=`cat $HOME/mntdir/config/dba.ini|grep -v "##"|grep ARCHIVE_DIR|cut -d= -f2`
while [[ $MAX_SEC_TO_DELETE -gt $NEXTTODELETE ]];do
NEXTARCHIVE=`awk 'BEGIN {str="'$ARCHIVE_FORMAT'" ; sub("SEQUENCE","'$NEXTTODELETE'",str) ; print str}'`
FILECOUNT=`ls -l $ARCHIVE_DIR|grep $NEXTARCHIVE|grep ".arc"|wc -l 2>/dev/null`
if [[ $FILECOUNT -eq 1 ]];then
echo "removing $ARCHIVE_DIR/$NEXTARCHIVE" >> $HOME/stby/archcleanprod.log
/usr/bin/rm -f $ARCHIVE_DIR/$NEXTARCHIVE
fi
echo $NEXTTODELETE>$HOME/stby/archive_last_deleted
NEXTTODELETE=$(($NEXTTODELETE+1))
done


windows:
There is a configuration file "E:\dba\orahome\mntdir\config\dba.xml" (you should replace { and } with the ones used in standard xml. I can not use them in this post, corrupts page)

{?xml version="1.0" encoding="utf-8" ?}
{config}
{general}
{archive_format}ARC_0628272535_001_SEQUENCE.ARC{/archive_format}
{archive_dir}E:\oracle\oradata\archive{/archive_dir}
{/general}
{stby}
{prodUser}dbuser{/prodUser}
{prodPass}password{/prodPass}
{prodTNS}PROD{/prodTNS}
{/stby}
{/config}

There is a file "E:\dba\orahome\stby\archive_last_deleted.txt" in which last deleted archived log's sequence number is kept. At first it can be set as 1.
My scheduled vbs script "E:\dba\orahome\stby\stby_archclean.vbs" uses configuration file to connect primary site, query dataguard status and delete applied archived logs at standby server:

'********************* CODE TO PARSE DBA.XML FILE ****************************
Set oXMLDoc = CreateObject("MSXML2.DOMDocument.3.0")
oXMLDoc.async = False
If Not oXMLDoc.load("mntdir\config\dba.xml") Then
Set oErr = oXMLDoc.parseError
sErrMsg = "XML Parsing Error. File: " & oErr.url & " Reason : " & oErr.reason & " Line: " & oErr.line & ", Character: " & oErr.linepos & ",Text: " & oErr.srcText
Err.Raise 999, , sErrMsg
End If
'******************************************************************************

Set WshShell = CreateObject("WScript.Shell")
pUSER = oXMLDoc.selectSingleNode("/config/stby/prodUser").text
pPASS = oXMLDoc.selectSingleNode("/config/stby/prodPass").text
ARCHIVE_FORMAT = oXMLDoc.selectSingleNode("/config/general/archive_format").text
ARCHIVE_DIR = oXMLDoc.selectSingleNode("/config/general/archive_dir").text
pTNS = oXMLDoc.selectSingleNode("/config/stby/prodTNS").text

Set objFSO = CreateObject("Scripting.FileSystemObject")
set archive_last_deleted = objFSO.OpenTextFile("stby\archive_last_deleted.txt", 1) 'for reading
DIM LAST_DELETED
if not archive_last_deleted.AtEndOfStream then
LAST_DELETED = cInt(archive_last_deleted.ReadLine)
else
WScript.Echo "Error on stby\archive_last_deleted file"
WScript.exit
end if
archive_last_deleted.close
NEXTTODELETE=LAST_DELETED+1



'ADO Connection object
Set con = CreateObject("ADODB.Connection")
con.CursorLocation = 3
If con.State = 1 Then con.close
con.Open "PROVIDER=MSDAORA;DATA SOURCE="&pTNS&";USER ID="&pUSER&";PASSWORD="&pPASS&";"

'ADO Command object
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText="select APPLIED_SEQ#-3 from v$ARCHIVE_DEST_STATUS where DEST_ID=2 "
cmd.CommandType=1
cmd.ActiveConnection=con

'ADO Recordset object
set rs=CreateObject("ADODB.Recordset")
Set rs=cmd.Execute


if rs.EOF then
WScript.Echo "Error: No APPLIED_SEQ# record for DEST_ID 2"
rs.Close
con.Close
WScript.exit
else
DIM MAX_SEC_APPLIED
rs.MoveFirst
MAX_SEC_APPLIED = cInt(rs.Fields(0))
rs.Close
con.Close
end if

WScript.Echo "Archive Sequence Range to Clean:" & cStr(NEXTTODELETE) & " - " & cStr(MAX_SEC_APPLIED)
do while MAX_SEC_APPLIED > NEXTTODELETE
WScript.Echo "removing... " & ARCHIVE_DIR & "\" & Replace(ARCHIVE_FORMAT,"SEQUENCE",cStr(NEXTTODELETE))
'WshShell.run("del /Q " & ARCHIVE_DIR & "\" & Replace(ARCHIVE_FORMAT,"SEQUENCE",cStr(NEXTTODELETE)))
objFSO.DeleteFile ARCHIVE_DIR & "\" & Replace(ARCHIVE_FORMAT,"SEQUENCE",cStr(NEXTTODELETE))
set archive_last_deleted = objFSO.OpenTextFile("stby\archive_last_deleted.txt", 2) 'for writing
archive_last_deleted.Write cStr(NEXTTODELETE)
archive_last_deleted.close
NEXTTODELETE = NEXTTODELETE+1
loop

I ran exactly the same script at prod side, however i added my backup procedure just before the delete.