Archive Log Deletion Script in SAP & Linux Environment with Oracle

Dear All,

Need your help on the below:


Application: SAP R/3
Primary: 2 Node RAC
Standby: Single Instance
Oracle: 10.2.0.2.0
OS: RHEL 4 (Update 5)

We need to create a shell script to delete the archive log files from Archive Destination {/oracle/SID/oraarch} based on two conditions:


1. The Archive log files must be shipped and applied to the Local/Remote Standby. {Can be checked by "select max(sequence#) from v$archived_log where applied='YES'"}

2. The Archive log files must be successfully backed up by brarchive utility.

Brarchive utility creates a logfile "/oracle/SID/saparch/archSID.log" which has information about the backed up archive log files.

The script should delete the archive log files or move to some location only if both the conditions above are TRUE.

Regards,
Paresh

so whats the problem?

Quote:

---

Originally Posted by davey23uk

so whats the problem?

---

The problem is can any of you EXPERTS help me in creating a shell script for my requirement, since i am bad at it :D.

Like in Linux, how do i read a particular file and compare to find out if the backup is indeed done, Some kind of logic to incorporate both the conditions in a single script.

What I have been able to find out is the attached script which covers the first condition.

Thanks,
Paresh

well what have you come up with so far - no-one is going to do your work for you

Well if you are using SAP . why dont you use SAP tool itself.

i am sure you must be having SAP tool BRARCHIVE.
Use that it can delete logfiles after making sure on its own that it has been backed up.

I am not sure about standby though.
But i think you can write script using brarchive to backup and delete only those archives which are applied.

Quote:

---

Originally Posted by davey23uk

well what have you come up with so far - no-one is going to do your work for you

---

Thanks Davey for your prompt response

Quote:

---

Originally Posted by paragp1981

Well if you are using SAP . why dont you use SAP tool itself.

i am sure you must be having SAP tool BRARCHIVE.
Use that it can delete logfiles after making sure on its own that it has been backed up.

I am not sure about standby though.
But i think you can write script using brarchive to backup and delete only those archives which are applied.

---

Thanks Parag, would check in the tool brarchive.

A piece of script which covers the first condition:

#!/bin/ksh
typeset username=$1
function readSqlstmt {
typeset stmt=$1

echo "
set feedback off
set verify off
set heading off
set pagesize 0
whenever sqlerror exit 1
whenever oserror exit 2
$stmt;
exit
" | sqlplus -S "/as sysdba" >> test.log
}


readSqlstmt "SELECT name from v\$archived_log WHERE applied = 'YES'" | while read u
do
echo $u
# Here you can print first to see if you are getting the correct list
# then you can simply remove
#
# rm $u
done

Found this script in one of the Oracle Forums where the expert says he believes in sharing his work for good.