Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 30454.1- Unix script that automates warm backup of all available tablespaces ??

Re: 30454.1- Unix script that automates warm backup of all available tablespaces ??

From: Tanel Poder <change_to_my_first_name_at_integrid.info>
Date: Thu, 28 Aug 2003 19:12:20 +0300
Message-ID: <3f4e29e6_1@news.estpak.ee> 





Hi!



Which shell are you using?


I'll paste a modified version of the script here which works with bash under
linux.


The comments are in estonian, you might want to change them if the script
works for you.



Tanel.





#!/bin/bash




# Oracle poolt tehtud andbebaasi online-backup skripti modifikatsioon

# Tanel Põder 20030727

#

# Oracle andmebaasi online-backup skript

# Kasutab alter tablespace begin/end backup käske ja unixi

# käske cp või tar failide kopeerimiseks, vastavalt backupi sihtkohale

#

# Kasutamine:

#     hotbackup.sh <backupi asukoht>

#       Asukoht võib olla nii kataloog kui näiteks lindiseadme nimi

# Näide:

#     hotbackup.sh /home/oracle/backup

#       Kopeerib online backupi käigus andmefailid ja controlfaili

#       /home/oracle/backup kataloogi

#

# Skript tagastab veakoodi 1 vea puhul, 0 õnnestumise puhul ja

# saadab e-maili NOTIFYADDR muutujaga määratud aadressil

#

# Mugandatavad parameetrid:




ORAENV_ASK=NO  # Et ei kysiks ORACLE_SID vaartust - v6tab selle, mis
keskkonnas maaratud


export ORAENV_ASK



NOTIFYADDR="user_at_mail.com"


export NOTIFYADDR



if [ -z "$1" ]


then



  echo "" >&2


   echo "Usage:  hobackup.sh <target>" >&2


  echo "" >&2
   echo "          <target> - directory name or tape device for tar" >&2
   echo "" >&2




   exit 1


fi




. $ORACLE_HOME/bin/oraenv


               # set ORACLE environment


echo ""
echo "Warm database backup of $ORACLE_SID on host `hostname`"
echo "Log will be written to warm.backup.`hostname`.${ORACLE_SID}.log"
echo ""





# log file




exec >>warm.backup.`hostname`.${ORACLE_SID}.log
exec 2>&1


echo ""
echo "**** Warm database backup of $ORACLE_SID at `date` on `hostname` ****"
echo ""





# prepare backup target




TARGET=$1



export TARGET



if [ -d "$TARGET" ]


then


  echo "Target is the directory $TARGET"
elif [ -c "$TARGET" ]


then



 echo "Target is the character device $TARGET - tar will be used"
else


  echo


"ERROR: Target is neither a directory nor a character special file"
  echo ""



 echo "**** Backup ABORTED at `date` !!! ****"
  echo ""


  exit 1


fi



if


[ ! -w "$TARGET" ]


then


  echo "ERROR: Target is not writable"


  echo ""



 echo "**** Backup ABORTED at `date` !!! ****"
  echo ""


  exit 1


fi



test -d "$TARGET"


TAR=$?                         # 0 for directory, otherwise tar


export TAR


echo "TAR is [$TAR]"



# check if the database instance is up




sqlplus -SILENT "/ as sysdba" > /tmp/warm.backup.${ORACLE_SID}.tmp <<EOF

        WHENEVER SQLERROR EXIT 1
        SELECT count(*)
        FROM sys.dba_tablespaces;




EOF


EXITCODE=$?



export EXITCODE



if [ "$EXITCODE" -ne 0 ]


then

   echo "ERROR: Database instance $ORACLE_SID is not up !!!"
   echo ""
   echo "**** Backup ABORTED !!! ****"





  echo ""


   exit 1


fi



# get list of files to backup

#   (modify sqlplus line to accommodate your usr/pwd)




echo "Getting names of files to backup ..."



sqlplus -SILENT "/ as sysdba" > /tmp/warm.backup.${ORACLE_SID}.tmp <<EOF

        WHENEVER SQLERROR EXIT 1
        SET TAB OFF
        SET PAGESIZE 0
        SET LINESIZE 300
        SET FEEDBACK OFF

        SELECT  tablespace_name || ' ' ||file_name
        FROM sys.dba_data_files
        WHERE status = 'AVAILABLE'
        ORDER BY tablespace_name;




EOF


EXITCODE=$?



export EXITCODE



if [ "$EXITCODE" -ne 0 ]


then


  echo "ERROR: SQL*Plus exit code: $EXITCODE"
  cat /tmp/warm.backup.${ORACLE_SID}.tmp
  echo ""


  echo


"**** Backup ABORTED at `date` !!! ****"
  echo ""


  exit 1


fi



if


[ ! -s /tmp/warm.backup.${ORACLE_SID}.tmp ]
then

  echo "ERROR: No files available for backup"
  echo ""
  echo "**** Backup ABORTED at `date` !!! ****"





echo ""


  exit 1


fi




# prepare SQL*Plus command script executing backup




echo "Preparing command script ..."



cat <<EOF > /tmp/backup_${ORACLE_SID}.awk
BEGIN {


    prev_tbs = ""                       # no tablespace processed yet


    tar=${TAR}


    home="${ORACLE_HOME}"


    sid="${ORACLE_SID}"


    target="${TARGET}"

    num = 1                             # start number for files


}


{



tablespace = \$1      # extract tablespace and file name


    filename = \$2


     #   from SQL*Plus output


    count=split(filename, a, "/")


    if (prev_tbs != tablespace)         # if at the begining of files
    {

               #   of next tablespace:
        if (length(prev_tbs) > 0)
    # end backup of previous tbsp
        print "ALTER TABLESPACE " prev_tbs " END BACKUP;\n"
        print "ALTER TABLESPACE " tablespace " BEGIN BACKUP;"





   }


    if (tar)                            # "tar" if to special device

       print "!tar cvAf " target " " filename
    else
        # "cp" if to directory
        print "!cp " filename " " target "/" a[count] "." num





    prev_tbs=tablespace


    num = num + 1


}



END   {


   if (length(prev_tbs) > 0)           # end backup of last tabspace



  print "ALTER TABLESPACE " prev_tbs " END BACKUP;"



    print ""



   if (!tar)                           # backup control and init file

{
            print "ALTER DATABASE BACKUP CONTROLFILE TO " "'" target \
                   "/ctrl" sid ".ctl." num "';"
            num = num + 1

           print "!cp " home "/dbs/init" sid ".ora " \
                     target "/init" sid ".ora." num
        }
    else
        {
               #   if tar, use temporary file
            print "ALTER DATABASE BACKUP CONTROLFILE TO " "'" \
                     home "/tmp/ctrl" sid ".ctl.tmp';"

           print "!cd " home "/tmp; tar cvf " target " ctrl" sid ".ctl.tmp"

          print "!cd " home "/dbs; tar cvf " target " init" sid ".ora"





 }



    print ""


}


EOF


awk -f /tmp/backup_${ORACLE_SID}.awk \


    /tmp/warm.backup.${ORACLE_SID}.tmp > /tmp/warm.backup.${ORACLE_SID}.tmp1



# do backup




echo "Copying files ..."



sqlplus -SILENT "/ as sysdba" <<EOF

       WHENEVER SQLERROR EXIT 1
       WHENEVER OSERROR EXIT 1
       SET ECHO ON
       START  /tmp/warm.backup.${ORACLE_SID}.tmp1
       ALTER SYSTEM ARCHIVE LOG CURRENT;


EOF


EXITCODE=$?



export EXITCODE



if [ "$EXITCODE" -ne 0 ]


then


  echo "ERROR: SQL*Plus exit


code: $EXITCODE"


  echo ""


  echo "**** Backup ABORTED at `date` !!! ****"
  mail $NOTIFYADDR -s "${ORACLE_SID} backup serveris `hostname`
ebaõnnestus\!" < \


       warm.backup.`hostname`.${ORACLE_SID}.log
 echo ""


  exit 1


fi


echo ""
echo "**** Backup finished at `date` ****"
echo ""

mail $NOTIFYADDR -s "${ORACLE_SID} backup serveris `hostname` OK" < \
       warm.backup.`hostname`.${ORACLE_SID}.log
--------------------------------------------------------------------------





"Subrahmanyam Arya" <avsrk_at_mailcity.com> wrote in message
news:25c1993e.0308280610.7b1aa993_at_posting.google.com...

> Hi there,
> I am on solaris. As mentioned by you i fixed the line which read
> cat <<EOF > /tmp/backup_${ORACLE_SID}.awk
> > BEGIN
> > {
>
> to
> cat <<EOF > /tmp/backup_${ORACLE_SID}.awk
> > BEGIN {
>
> This made the following errors go away
> awk: syntax error near line 2
> awk: bailing out near line 2
>
> But now i get the following errors in warm.backup.<sid>.log file
> Preparing command script .....
> awk: syntax error near line 42
> awk: illegal statement near line 42
> awk: syntax error near line 50
> awk: illegal statement near line 50
>
> and the line 42 in my /tmp/backup_${ORACLE_SID}.awk file is this
>
> print "ALTER DATABASE BACKUP CONTROLFILE TO " "'" target \
>         "/ctrl" sid ".ctl." num "';"
>
> I would appreciate if somebody already used this script successfully,
> can they cut and paste the awk portion of the script which i can cross
> test against mine.
>
> -Sincerely, (admit that i am starnger to awk/sed..and am beginning to
> learn via this...)
> avsrk
>
>
> "Tanel Poder" <change_to_my_first_name_at_integrid.info> wrote in message


news:<3f4d1b5c$1_1_at_news.estpak.ee>...

> > Hi!
> >
> > Which platform are you on, Linux?
> >
> > Try to search and replace:
> >
> > cat <<EOF > /tmp/backup_${ORACLE_SID}.awk
> > BEGIN
> > {
> >
> > with
> >
> > cat <<EOF > /tmp/backup_${ORACLE_SID}.awk
> > BEGIN {
> >
> > These are the nasty small differences in various Unix'es and Linux'es
> > shells...
> >
> > Tanel.
> >
> > "Subrahmanyam Arya" <avsrk_at_mailcity.com> wrote in message
> > news:25c1993e.0308271156.3360d350_at_posting.google.com...
> > > Dear Oracle gurus,
> > >
> > > I am looking at a unix script that automates warm backup of all
> > > available tablespaces and found in metalink a doc-id (30454.1)which
> > > has this script documented. Unfortunately i have problems running this
> > > script as it is.. It is resulting in some awk errors.
> > >
> > > Can anybody give me this script in working form if they have tried it.
> > >
> > > -thanks,
> > > avsrk


Received on Thu Aug 28 2003 - 11:12:20 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US