Re: RMAN Quick clean-up

From: James Howerton <jhowerton_at_uabmc.edu>
Date: Tue, 09 May 2000 13:46:16 -0500
Message-Id: <10492.105269@fatcity.com>

Thanks Tina,

Nice scripts, I'll check them out.

...JIM... >>> tlridgley_at_yahoo.com 5/9/00 12:14:14 PM >>> 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.=20 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 =20

                  #


#                                                    =20

                   #

# Program Desc: Gathers obsolete backup
information, & generates a #
# script which can be used to remove

the obsolete       #


#                   backups.                         =20

                   #


#                                                    =20

                   #


#  Author:          T.Ridgley, Virtualogic, Inc.     =20

                   #


#                                                    =20

                   #


#  Usage:           del_obs_bkup.ksh <DB NAME>       =20

                   #


#                                                    =20

                   #


#  Example:         del_obs_bkup.ksh TEST            =20

                   #


###########################################################################=





#####################################################

#        SET GENERAL ENVIRONMENT VARIABLES          #

#####################################################

ORACLE_HOME=3D/oracle/product/816
export ORACLE_HOME
RUN_DIR=3D/oracle/admin/scripts/BACKUPS/RMAN/TEST export RUN_DIR
HOME=3D`pwd`
export HOME
TODAY=3D`date '+%m%d%y'`
RUNTIME_START=3D`date`
MAIL_NOTIFY=3Dtridgley_at_xyz.com=20
HOST_NAME=3D`uname -n`
export $HOST_NAME
CATALOG=3DCESVA
export CATALOG

####################################################=20
# * * * * * * * * * * * * * * * * * * * * * * * * *#
####################################################
# #
# P R O G R A M F U N C T I O N S #
# #
####################################################
# * * * * * * * * * * * * * * * * * * * * * * * * *#
####################################################

####################################################=20
# DISPLAY VALID INPUT FORMATS #
####################################################=20

show_input_format () {

echo "Usage is del_obs_bkup.ksh <DATABASE NAME>" >> $LOGFILE_NAME

echo "Program exits..."                           >>

$LOGFILE_NAME
=20
sleep 2

}

####################################################
# SEND EMAIL NOTIFICATION OF BACKUP FAILURE #
####################################################=20

send_email () {

MESSAGE_TXT=3D`cat $RUN_DIR/MESSAGE.TXT` echo "FROM: "$HOST_NAME > $RUN_DIR/MSGFILE echo "WARNING...RMAN MTC "$HOST $ORACLE_SID $MESSAGE_TXT >> $RUN_DIR/MSGFILE=20

echo "."                                             =20

>> $RUN_DIR/MSGFILE mailx -s SYSTEM_RMAN $MAIL_NOTIFY < $RUN_DIR/MSGFILE

}
=20
####################################################=20
# VERIFY THAT INSTANCE IS RUNNING #
####################################################=20

check_instance_running () {

echo "Checking to see if instance is running"        =20
     >> $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=3DTRUE
  else
    RUNNING=3DFALSE
fi

rm -f $RUN_DIR/inst_ck.out

}

####################################################=20
# GENERATE REPORT OF OBSOLETE BACKUPS #
####################################################=20

report_obsolete_info () {

echo 'Capturing obsolete backup information'         =20
     >> $LOGFILE_NAME

echo 'report obsolete;'            >

$RUN_DIR/report_obsolete.sql

$ORACLE_HOME/bin/rman target / catalog
rman/rman@$CATALOG < $RUN_DIR/report_obsolete.sql >=20 $RUN_DIR/report_obsolete$DBNAME.log=20

}

####################################################=20
# GENERATE REPORT OF OBSOLETE BACKUPS #
####################################################=20

parse_obsolete_info () {

echo 'Parsing obsolete backup information'           =20
     >> $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...'                         =20
        >> $LOGFILE_NAME
     exit 0=20

awk ' { print $3 } ' $RUN_DIR/backupset.out > $RUN_DIR/numbers.out

rm -f $RUN_DIR/backupset.out

}
####################################################
# GENERATE DELETE OBSOLETE BACKUP SCRIPT #=09
####################################################

gen_delete_obsolete_script () {

echo 'Building script to remove obsolete backups...'

NUM=3D`cat $RUN_DIR/numbers.out`

echo '#! /bin/ksh  '                                 =20

>=20
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'ORACLE_HOME=3D/oracle/product/816 ' =20 >>=20
$RUN_DIR/remove_obsolete_bkups.ksh

echo 'export ORACLE_HOME '                           =20

>>=20
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'RUN_DIR=3D/oracle/admin/scripts/BACKUPS/RMAN/TEST' >>=20
$RUN_DIR/remove_obsolete_bkups.ksh

echo 'export RUN_DIR'                                =20

>>=20
$RUN_DIR/remove_obsolete_bkups.ksh

echo 'DBNAME=3D'$DBNAME                                =20

>>=20
$RUN_DIR/remove_obsolete_bkups.ksh

echo 'export DBNAME'                                 =20

>>=20
$RUN_DIR/remove_obsolete_bkups.ksh

echo 'CATALOG=3D'$CATALOG                              =20
 >> $RUN_DIR/remove_obsolete_bkups.ksh
echo 'export CATALOG'                                =20

>>=20
$RUN_DIR/remove_obsolete_bkups.ksh
echo 'allocate channel for maintenance type disk;' =20 > $RUN_DIR/remove_obsolete.sql
for I in $NUM
do=20
echo 'change backupset '$I 'delete;' =20 >> $RUN_DIR/remove_obsolete.sql
done

echo $ORACLE_HOME'/bin/rman target / catalog rman/rman@'$CATALOG' <=20
'$RUN_DIR'/remove_obsolete.sql >
'$RUN_DIR'/remove_obsolete.out' >>=20
$RUN_DIR/remove_obsolete_bkups.ksh

chmod 775 $RUN_DIR/remove_obsolete_bkups.ksh

rm -f $RUN_DIR/numbers.out

}

####################################################=20
# * * * * * * * * * * * * * * * * * * * * * * * * *#
####################################################=20
# #
# S T A R T V A L I D A T I O N S #
# #
####################################################=20
# * * * * * * * * * * * * * * * * * * * * * * * * *#
####################################################=20

####################################################=20
# VERIFY INPUT PARAMETERS #
####################################################=20

if [ $# -lt "1" ]
  then
    show_input_format
    exit 1
fi
=20
####################################################=20
# VERIFY DATABASE IS LOCAL TO THIS SERVER #
####################################################=20

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:"                      =20
                          >>=20

$LOGFILE_NAME
grep '/oracle/' /var/opt/oracle/oratab >

$RUN_DIR/dbs.out         =20
    cat $RUN_DIR/dbs.out                             =20
                          >>=20

$LOGFILE_NAME
sleep 3
echo "Database selected is invalid on this

machine."     > $RUN_DIR/MESSAGE.TXT
    echo "Valid databases are:"                      =20
                          >>=20

$RUN_DIR/MESSAGE.TXT
grep '/oracle/' /var/opt/oracle/oratab >

$RUN_DIR/dbs.out                    >>=20
$RUN_DIR/MESSAGE.TXT
    cat $RUN_DIR/dbs.out                             =20
                          >>=20

$RUN_DIR/MESSAGE.TXT
    send_email
    rm -f $RUN_DIR/dbs.out
    exit 1
  else

    echo "Database selected is valid"                =20
               >> $LOGFILE_NAME

if [ -f $RUN_DIR/dbs.out ]
then
rm -f $RUN_DIR/dbs.out
fi

####################################################=20
# SET FILE NAMES AND ENV VARIABLES FOR THIS RUN #
####################################################=20

DBNAME=3D$1
export DBNAME
LOGFILE_NAME=3D$DBNAME'_'$TODAY'_'del_obs_bkup.log export LOGFILE_NAME
sleep 3
ORACLE_SID=3D$1; export ORACLE_SID

####################################################=20
# * * * * * * * * * * * * * * * * * * * * * * * * *#
####################################################=20
# #
# M A I N P R O C E S S #
# #
####################################################=20
# * * * * * * * * * * * * * * * * * * * * * * * * *#
####################################################=20

check_instance_running
  if [ "$RUNNING" =3D FALSE ]
    then
      echo 'Program =3D del_obs_bkup.ksh. Instance must be running in order to execute' >>=20 $LOGFILE_NAME

      echo 'Program exits'                           =20
                                   >>=20
$LOGFILE_NAME
      echo 'Program =3D del_obs_bkup.ksh.  Instance must
be running in order to execute'     >=20
$RUN_DIR/MESSAGE.TXT
      echo 'Program exits'                           =20
                                   >>=20
$RUN_DIR/MESSAGE.TXT
        exit 1
    else
      echo 'Instance running'                        =20
                                       =20
>> $LOGFILE_NAME
      report_obsolete_info
      parse_obsolete_info
      gen_delete_obsolete_script

James Howerton <jhowerton_at_uabmc.edu> wrote: > DBA's, >=20 > Does anyone have a quick method for removing > obsolete backup sets and archivelogs? I'm spending > way too much time doing this each week. >=20 > Thanks >=20 > ....JIM... >=20 >=20 > -- > Author: James Howerton > INET: jhowerton_at_uabmc.edu=20 >=20 > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists >
> To REMOVE yourself from this mailing list, send an > E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing).

Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/=20
--=20
Author: Tina Ridgley
INET: tlridgley_at_yahoo.com=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Received on Tue May 09 2000 - 13:46:16 CDT