Re: Backup Script

Thank you, Anurag.

I wish I knew what the hell all of this means!

(Just joking. I can see immediately that it is a recipe for Sara Lee Cheese Cake with Pecan topping).

I will inwardly digest.

Slowly.

Best Regards
HJR "Anurag" <avdbi_at_hotmail.com> wrote in message news:uj7bv3gnr4pp9b_at_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

> >

> >

>

>