Need script to compress & move Oracle archive logs.

[Gulam Mohiuddin]

Need script to compress & move Oracle archive logs to another host.

I need your help and tips for creating a Unix shell script to maintain archive log space for Oracle database. This script will be running though cron for every 10 minutes.

Monitor /arch01 mount point for every 10 minutes on hostA

If /arch01 mount point’s disk space is more than 80% used

Then compress 5 oldest archive logs

Move the compressed oldest archive log to another hostB using rcp at hostB:/arch02.

Maintain a log of all moved archive logs

Thanks,

Gulam.

[Steven E. Protter]

Shalom Gulam,

This is a substantially complex script you are asking for.

Bill McNamara created a thread for syadmin scripts that probably contains the components you need for this job.

If I find the link for that thread before I finish writing my recommendations, I'll add it.

First, I recommend you compress and move all logs but the active one. You never know when activity is going to get heavy and thus defeat your script. Going for the five oldest under certain circumstances makes the script harder to spec and test.

Links:
http://forums2.itrc.hp.com/service/forums/questionanswer.do?threadId=51050

Check his profile, there are two other good threads. BTW, google found this stuff, HP's search engine, not so much.

SEP

[Moderator edit: The above link is no longer valid.]

[Paul F. Carlson]

As SEP mentioned, this would be a fairly complex script to write. If you know perl, it could be substantially easier.

If you are indeed proficient at perl, take a look at the Time::Local module and it's functions. I've written scripts similar to what you are looking for (although not for archive logs) in the past using these functions and epoch time values. Going into detail here would just be too much.

[Rick Garland]

The script you are asking for is a complex script. Has many conditions to be satisfied.

Here is a PERL script that will do part of what you are seeking. Modify at will to include/remove those items you so desire. Hopefully this will give you a start of how to proceed with a script of your definitions.

It will not keep track every 5 min nor does it monitor disk space, but on a regular schedule it will compress/remove the arch logs based on time, which you can define in the script. It will count those compressions/removals. You can modify the script to keep a log for you as well.


If the arch logs are kept in control to begin with, then you will have predictable changes and can account for these changes in the script.

[Volker Borowski · Accepted solution]

OK,

unfortunately I only have a linux system handy right now, but the ideas should be adoptable:

1) watch the filesystem

Check out the corresponding column for the disk percentage like this for /home in this case. On my Linux box it is in column 5. For HP-UX adjust by changing $5 and "df -k"

df -k | awk '/home/ {print substr($5,1,length($5)-1)}'

Use this to check the filesystem i.e.

if [ $(df ......) -gt 80 ]
then
# process archive files
fi

2) # process archive files
List the files sorted by time i.e if this is the contents:

$ ls -lrt # l= ELL for "long"
total 0
-rw-rw-r-- 1 sapr sapr 0 Mar 6 21:22 arch01
-rw-rw-r-- 1 sapr sapr 0 Mar 6 21:22 arch02
-rw-rw-r-- 1 sapr sapr 0 Mar 6 21:22 arch03
-rw-rw-r-- 1 sapr sapr 0 Mar 6 21:23 arch04
-rw-rw-r-- 1 sapr sapr 0 Mar 6 21:23 arch05

use

$ ls -1rt | head -3 # 1=ONE for single column
arch01
arch02
arch03

To get the list of guys to process
The section could look like this:

for name in $(ls -1rt | head -3)
do
compress $name
if rcp $name.Z hostB:/arch02
then
echo $name processed at $(date) >> /var/log/somewhere.log
echo rm $name.Z
echo $name deleted at $(date) >> /var/log/somewhere.log
else
echo $name rcp failed at $(date) >> /var/log/somewhere.log
echo "alert, archiver stuck possible" | mailx ....
fi
done

Test the Script very carefully and delete the "echo" in front of the "rm" after you have succesfully checked out the correct evauation of the returncode from rcp (Give wrong destination or revoke write permission on target to simulate a faulty copy).

This should give you some basic ideas!

But again: Keep in mind that your archive logs are VERY important for your database, so you may never delete them unless you are sure they are stored at a safe place. So test all thinkable errors that might occur in a "rcp" and check if the returncode is correctly evaluated.

possible pittfalls:
- other files than archives in this directory
- archive is filling up too speedy to get the compress done, may be the evaluation of the "compress" returncode should be included, because if the compress fails, no .Z file would be transferable ...
- ...

Happy Scripting
Volker

[James A. Donovan]

This is a script we use...the names have been changed to protect the guilty...it compresses all but the most recent log...

#!/bin/sh

## This script will compress archive logs for multiple databases having multiple archivelog locations
## assumes archive logs written to /archX/$ORACLE_SID and names end in .arc

## Script exits if ${LogFile} exists, so that multiple instance of the script do not run simultaneously...

LogFile=/tmp/$0.log

if [ -f ${LogFile} ]
then
exit
else
SID_LIST="mydb1 mydb2"
for SID in ${SID_LIST}
do
ORACLE_SID=${SID}
for x in 1 2
do
ArchDest=/arch${x}/${SID}
LogExcl=`ls -lt ${ArchDest}/*.arc | head -1 | awk '{print $9}'`
for File in `ls ${ArchDest}/*.arc | grep -v ${LogExcl}`
do
echo 'Compressing Archive Log file' ${File} >> ${LogFile}
/usr/contrib/bin/gzip ${File} 1> ${LogFile} 2>> ${LogFile}
/usr/bin/mailx -s "Archive Logs compressed" < ${LogFile}
fi
done
done
done
rm -f ${LogFile}
fi

[Volker Borowski]

Jim,

watch it, if you use several archiver processes (two is default since oracle 9 I think) and get heavy load on your box. You might have arc1 starting to archive before arc0 has finished writing and thus the script could start processing a file that is still been written to, if just ignore one file ! You should ignore at least the number of files that matches your number of archiver processes.

I rethought my suggestion and would recommend to use "fuser" on the archive log prior compressing to ensure the archiver process has really finished !

Volker

[rariasn]

echo "Conectado a BD $ORACLE_SID"
LOG_ARCHIVE_DEST=/oracle-arch/cass/log
TMP_ARCHIVE_DEST=/oracle-temp/cass
CONTROLDEST=/oracle-arch/cass/ctl

# Modificado borrado de $TMP_ARCHIVE_DEST/arch*.dbf. Este borrado se
# realiza una vez copiados a cinta y comprobado que la copia es O.K.



for FILE in $LOG_ARCHIVE_DEST/*.dbf
do
[ -f $FILE ] || continue
NAME=`/usr/bin/basename $FILE`
echo "Copiando $FILE a $TMP_ARCHIVE_DEST/$NAME..."
/usr/bin/cp -p $FILE $TMP_ARCHIVE_DEST/$NAME
if [ $? != 0 ] ; then
echo "Error fallo al copiar archive log '$FILE'"
continue
fi
echo "Comparando $FILE con $TMP_ARCHIVE_DEST/$NAME..."
/usr/bin/cmp -s $FILE $TMP_ARCHIVE_DEST/$NAME
RES="$?"
echo "$FILE"
echo "$TMP_ARCHIVE_DEST/$NAME: $RES"
if [ $RES -eq 0 ]
then
echo "Borrando $FILE..."
/sbin/rm $FILE
else
echo "No se ha borrado $FILE"
fi
done

/sbin/rm -f $CONTROLDEST/*.bak
svrmgrl << EOF >/var/apps.real/oracle/Omniback/copia_logs.log 2>&1
connect internal
alter database backup controlfile to '$CONTROLDEST/ctrlCASS.bak';
exit;


- Next, backup logs and list files.

[Yogeeraj_1]

hi Gulam,

i agree with volker, you should watchout for the archive to have finished archiving before compressing and moving the file.

however, i would prefer to use RMAN.

Yes. RMAN backup the archive log files. Then compress and send it away! or just backup it using NFS.

clean and simple.

kind regards
yogeeraj

[Arturo Galbiati]

Hi gulam,
from a lot of year I'm using two script for this. First script check_dump.ksh checks for the space on directory udump, bdump, sdump and archive, compresses files and send aletr by mail and pager.
Second script purge_archlogs.ksh remove the old arhive logs. I use two script because before to remove teh archive log files I backup them twice per day in the mornign and in the evening. usually I use 2 days as retention period to be sure to have the archive log file sstore on two different tapes. It's very important to be sure to have a valid backup pior to remove teh archive log otherwise in case of datbase crash you will be in a big trouble.
Let me know if you are interested to these scripts and I'll attach them.
I'd like to avoid to post not necessary scripts.
HTH,
Art

[Gulam Mohiuddin]

Thanks Arturo.

I appreciate if you could share your scripts with us.

Regards,

Gulam.

[Arturo Galbiati]

Hi Gulam,
I attached the scripts as they are. Most probably you need to chnage them a bit to adapt to your environment.
HTH,
Art

This is the check_dump.ksh script

[Arturo Galbiati]

and this is the purge_archlogs.ksh script