Re: Backup Script

From: Anurag <avdbi_at_hotmail.com>
Date: Tue, 16 Jul 2002 01:32:16 -0400
Message-ID: <uj7bv3gnr4pp9b@corp.supernews.com>

Howard,

Just some minor comments ...

:)
Anurag

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:ah01n8$ala$1_at_lust.ihug.co.nz...
> I have here a hot backup shell script which a student of one of my backup
> and recovery courses knocked up during the brief interludes when I wasn't
> waffling on about one thing or another.
>
> I can follow the logic, and I *think* it seems OK.
>

----- lines snipped -----
----------------------------------------------------------------------------
--------

> EXTRACTDATAFILESSQL="set head off
> set feedback off
> SELECT d.file_id || ' ' || d.file_name || ' ' || d.tablespace_name
> || ' ' || t.contents || ' ' || v.status || ' ' || t.status
> FROM sys.dba_data_files d, v\$datafile v, dba_tablespaces t
> WHERE d.file_id = v.file#
> AND d.tablespace_name = t.tablespace_name;
> exit"
>
> if [ -n "$PROMPTING" ]
> then
> echo "Extracting list of tablespaces and datafiles to backup"
> fi
>
> echo "$EXTRACTDATAFILESSQL" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "extracting datafiles"
>
> rm -f $TMPSQLFILE # Clean up temp file
>
> LINE=`echo "$DATAHOLD" | head -1` # Get first line of data
> DATAHOLD=`echo "$DATAHOLD" | tail +2` # Remove first line of data
> typeset -Z3 COUNT=1 # Prepare a counter
>
>
> while [ -n "$DATAHOLD" -o -n "$LINE" ] # We have data to analyse
> do
> set -- $LINE # Break up the line into variables
> if [ -n "$LINE" ]
> then
> eval DATAFILENO$COUNT=$1 # Hold datafile number
> eval DATAFILE$COUNT=$2 # Hold datafile name
> eval TABLESPACE$COUNT=$3 # Hold tablespace name
> eval TABLECONTENTS$COUNT=$4 # Hold tablespace content type
> eval DATAFILESTATUS$COUNT=$5 # Hold datafile status
> # SYSTEM, ONLINE, OFFLINE
> if [ "$6" = "READ" -a "$7" = "ONLY" ] # READ ONLY tablespace
> then
> eval TABLEREADONLY$COUNT=true
> fi
>
> COUNT=`expr $COUNT + 1`
> fi
>
> LINE=`echo "$DATAHOLD" | head -1`
> DATAHOLD=`echo "$DATAHOLD" | tail +2`
> done
>
> COUNT=`expr $COUNT - 1`
> DATAFILECOUNT=$COUNT
> unset COUNT
>
> if [ -n "$PROMPTING" ]
> then
> echo "Found $DATAFILECOUNT datafiles"
> echo
> fi

---

---
* Calling sqlplus as it is called above can expose the username password of
the backup user
  very easily to other unix users by running ps -ef|grep sqlplus
* I tend to feel that the way head and tail are used above ... is subject to
error. Consider, running
   this script where login.sql has linesize set at 40 and wrap turned on
* Feel that the creation of temp sql file is unnecessary
Here goes my version ... subject to further scrutiny (Pardon my ksh since I
code more in perl):

# Explicitly set most sqlplus settings on since a minor change in login.sql
# might cause the script to fail
EXTRACTDATAFILESSQL="
set head off feedback off verify off escape on pages 0
set lines 300 trimspool on termout on echo off
SELECT d.file_id || ' ' || d.file_name || ' ' || d.tablespace_name
|| ' ' || t.contents || ' ' || v.status || ' ' || t.status
FROM sys.dba_data_files d, v\$datafile v, dba_tablespaces t
WHERE d.file_id = v.file#
AND d.tablespace_name = t.tablespace_name;
exit"

if [ -n "$PROMPTING" ]
then
  echo "Extracting list of tablespaces and datafiles to backup"
fi

typeset -Z3 COUNT=0   # Prepare a counter

# Note that we connected to sqlplus by /nolog thus preventing
# password being revealed via ps -ef (easily)
# We then removed empty lines and "Connected" line using grep
# The output of sqlplus was then simply passed onto a while loop
print "
connect $SQLUSER/$SQLPASS
$EXTRACTDATAFILESSQL
exit
" | $SQLPLUS /nolog | grep -v '^Connected' | grep -v '^ *$' |
{
   while read LINE
   do
      (( COUNT+=1 ))  # Just another way to count
     set -- $LINE    # Break up the line into variables
     eval DATAFILENO$COUNT=$1  # Hold datafile number
     eval DATAFILE$COUNT=$2  # Hold datafile name
     eval TABLESPACE$COUNT=$3  # Hold tablespace name
     eval TABLECONTENTS$COUNT=$4  # Hold tablespace content type
     eval DATAFILESTATUS$COUNT=$5 # Hold datafile status
     # SYSTEM, ONLINE, OFFLINE
     if [ "$6" = "READ" -a "$7" = "ONLY" ] # READ ONLY tablespace
     then
       eval TABLEREADONLY$COUNT=true
     fi
  done
}

DATAFILECOUNT=$COUNT
unset COUNT

if [ -n "$PROMPTING" ]
then
  echo "Found $DATAFILECOUNT datafiles"
  echo
fi

----------------------------------------

---------lines snipped-------------


>

>

> Regards,

> HJR

>

>

Received on Tue Jul 16 2002 - 00:32:16 CDT