Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RMAN Quick clean-up
Hi. We discovered this too. If you are running on
the Unix platform you can try playing with the script
below. I don't guarantee it to be bug free since it
is hot off the press but it works for me.
Incidentally this just generates a script which you
need to run. I'm not overly confident about
automating deletes of backups at this point, so I
would rather be able to check successful tape backups
before running the delete. (Call me paranoid) :) We
are running Oracle 8.1.6 on Solaris 2.6 so this script
is based on that assumption.
Tina
#! /bin/ksh # ########################################################################### # Program Name: del_obs_bkp.ksh # #
#
# script which can be used to remove the obsolete # # backups.
#
#
#
# Author: T.Ridgley, Virtualogic, Inc.
#
#
#
# Usage: del_obs_bkup.ksh <DB NAME>
#
#
#
# Example: del_obs_bkup.ksh TEST
#
########################################################################### ##################################################### # SET GENERAL ENVIRONMENT VARIABLES # #####################################################
ORACLE_HOME=/oracle/product/816
export ORACLE_HOME
RUN_DIR=/oracle/admin/scripts/BACKUPS/RMAN/TEST
export RUN_DIR
HOME=`pwd`
export HOME
TODAY=`date '+%m%d%y'`
RUNTIME_START=`date`
MAIL_NOTIFY=tridgley_at_xyz.com
HOST_NAME=`uname -n`
export $HOST_NAME
CATALOG=CESVA
export CATALOG
#################################################### # * * * * * * * * * * * * * * * * * * * * * * * * *# #################################################### # # # P R O G R A M F U N C T I O N S # # # ##################################################### * * * * * * * * * * * * * * * * * * * * * * * * *#
#################################################### #################################################### # DISPLAY VALID INPUT FORMATS # ####################################################
show_input_format () {
echo "Usage is del_obs_bkup.ksh <DATABASE NAME>" >> $LOGFILE_NAME
echo "Program exits..." >>$LOGFILE_NAME sleep 2
}
#################################################### # SEND EMAIL NOTIFICATION OF BACKUP FAILURE # ####################################################
send_email () {
MESSAGE_TXT=`cat $RUN_DIR/MESSAGE.TXT`
echo "FROM: "$HOST_NAME > $RUN_DIR/MSGFILE
echo "WARNING...RMAN MTC "$HOST $ORACLE_SID
$MESSAGE_TXT >> $RUN_DIR/MSGFILE
echo "."
>> $RUN_DIR/MSGFILE
mailx -s SYSTEM_RMAN $MAIL_NOTIFY < $RUN_DIR/MSGFILE
}
#################################################### # VERIFY THAT INSTANCE IS RUNNING # ####################################################
check_instance_running () {
echo "Checking to see if instance is running" >> $LOGFILE_NAME
ps -ef |grep ora_pmon_$DBNAME | grep -v grep > $RUN_DIR/inst_ck.out
if [ -s $RUN_DIR/inst_ck.out ]
then
RUNNING=TRUE
else
RUNNING=FALSE
fi
rm -f $RUN_DIR/inst_ck.out
}
#################################################### # GENERATE REPORT OF OBSOLETE BACKUPS # ####################################################
report_obsolete_info () {
echo 'Capturing obsolete backup information' >> $LOGFILE_NAME echo 'report obsolete;' >
$ORACLE_HOME/bin/rman target / catalog
rman/rman@$CATALOG < $RUN_DIR/report_obsolete.sql >
$RUN_DIR/report_obsolete$DBNAME.log
}
#################################################### # GENERATE REPORT OF OBSOLETE BACKUPS # ####################################################
parse_obsolete_info () {
echo 'Parsing obsolete backup information' >> $LOGFILE_NAME
grep 'Backup Set' $RUN_DIR/report_obsolete$DBNAME.log > $RUN_DIR/backupset.out
if [ ! -s $RUN_DIR/backupset.out ]
then
echo 'NO OBSOLETE BACKUPS REQUIRE REMOVAL AT THIS TIME' >> $LOGFILE_NAME
echo 'PROGRAM STOPS...' >> $LOGFILE_NAME exit 0
awk ' { print $3 } ' $RUN_DIR/backupset.out > $RUN_DIR/numbers.out
rm -f $RUN_DIR/backupset.out
}
#################################################### # GENERATE DELETE OBSOLETE BACKUP SCRIPT # ####################################################
gen_delete_obsolete_script () {
echo 'Building script to remove obsolete backups...'
NUM=`cat $RUN_DIR/numbers.out`
echo '#! /bin/ksh '
>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'ORACLE_HOME=/oracle/product/816 '
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'export ORACLE_HOME '
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'RUN_DIR=/oracle/admin/scripts/BACKUPS/RMAN/TEST'
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'export RUN_DIR'
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'DBNAME='$DBNAME
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'export DBNAME'
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'CATALOG='$CATALOG
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'export CATALOG'
>>
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'allocate channel for maintenance type disk;'
> $RUN_DIR/remove_obsolete.sql
for I in $NUM
do
echo 'change backupset '$I 'delete;'
>> $RUN_DIR/remove_obsolete.sql
done
echo $ORACLE_HOME'/bin/rman target / catalog
rman/rman@'$CATALOG' <
'$RUN_DIR'/remove_obsolete.sql >
'$RUN_DIR'/remove_obsolete.out' >>
$RUN_DIR/remove_obsolete_bkups.ksh
chmod 775 $RUN_DIR/remove_obsolete_bkups.ksh
rm -f $RUN_DIR/numbers.out
}
#################################################### # * * * * * * * * * * * * * * * * * * * * * * * * *# #################################################### # # # S T A R T V A L I D A T I O N S # # # ##################################################### * * * * * * * * * * * * * * * * * * * * * * * * *#
#################################################### #################################################### # VERIFY INPUT PARAMETERS # ####################################################
if [ $# -lt "1" ]
then
show_input_format
exit 1
fi
#################################################### # VERIFY DATABASE IS LOCAL TO THIS SERVER # ####################################################
grep $1: /var/opt/oracle/oratab > /dev/null
if [ $? -eq "1" ]
then
echo "Database selected is invalid on this
machine." >> $LOGFILE_NAME
echo "Valid databases are:" >>
$RUN_DIR/dbs.out cat $RUN_DIR/dbs.out >>
machine." > $RUN_DIR/MESSAGE.TXT echo "Valid databases are:" >>
$RUN_DIR/dbs.out >> $RUN_DIR/MESSAGE.TXT cat $RUN_DIR/dbs.out >>
echo "Database selected is valid" >> $LOGFILE_NAME
if [ -f $RUN_DIR/dbs.out ]
then
rm -f $RUN_DIR/dbs.out
fi
##################################################### SET FILE NAMES AND ENV VARIABLES FOR THIS RUN #
####################################################
DBNAME=$1
export DBNAME
LOGFILE_NAME=$DBNAME'_'$TODAY'_'del_obs_bkup.log
export LOGFILE_NAME
sleep 3
ORACLE_SID=$1; export ORACLE_SID
#################################################### # * * * * * * * * * * * * * * * * * * * * * * * * *# #################################################### # # # M A I N P R O C E S S # # # ##################################################### * * * * * * * * * * * * * * * * * * * * * * * * *#
####################################################
check_instance_running
if [ "$RUNNING" = FALSE ]
then
echo 'Program = del_obs_bkup.ksh. Instance must
be running in order to execute' >>
$LOGFILE_NAME
echo 'Program exits' >> $LOGFILE_NAME echo 'Program = del_obs_bkup.ksh. Instance must be running in order to execute' > $RUN_DIR/MESSAGE.TXT echo 'Program exits' >> $RUN_DIR/MESSAGE.TXT exit 1 else echo 'Instance running' >> $LOGFILE_NAME report_obsolete_info parse_obsolete_info gen_delete_obsolete_script