#!/bin/ksh

# version 7.4, 2009-04-25, Michael Wang <xw73@columbia.edu>, sponsored by
# DataBase Intelligence Group, Inc. (http://DBIGusa.com).
# * Concatenate columns in select list to ensure they are on the same line.
# * Added orapw<SID> to the backup.
# * Change "set linesize 960" after "connect / as sysdba".
#
# version 7.2, 2009-03-10, Michael Wang <xw73@columbia.edu>, sponsored by
# DataBase Intelligence Group, Inc. (http://DBIGusa.com).
# * Find spfile from v$paramter instead of $ORACLE_HOME/dbs/spfileSID.ora
# * Preserve the path for pfile, spfile.
# * Save pfile created from spfile as <spfile>.txt.
#
# version 7.1, 2008-12-27, Michael Wang <xw73@columbia.edu>, sponsored by
# DataBase Intelligence Group, Inc. (http://DBIGusa.com).
# * Added COPY_DIRECTORY_PATH option to copy files under
#   DIRECTORY_PATH of dba_directories.
#
# version 7.0, 2008-12-25, Michael Wang <xw73@columbia.edu>.
# * Port to ksh88 requested and sponsored by
#   DataBase Intelligence Group, Inc. (http://DBIGusa.com).
# * Added check for compression software.
# * Added NUM_COPIES.
#
# version 6.3, 2004-07-19, Michael Wang <xw73@columbia.edu>.
# * Added the case without compression.
#
# version 6.2, 2004-01-08, Michael Wang <xw73@columbia.edu>.
# * changed init$ORACLE_SID.ora to @(init|spfile)$ORACLE_SID.ora.
#
# version 6.1, 2002-05-07, Michael Wang <xw73@columbia.edu>.
# * Added RESTORE section.
# * Added help=pdf.
#
# version 6.0, 2001-12-06, Michael Wang <xw73@columbia.edu>.
# * Solaris 8 snapshot backup.
# * Documentation via help=y.
# * Add mount= and umount= option to cooperate with unix backup.
# - ksh93 test (see ver 4,3).
# - replace "set -- $(IFS=@:; print -- $i)" with "set -- ${i//[@:]/ }".
# + /usr/dt/bin/dtksh: i=a@b:c; set -- $(IFS=@:; print -- $i); print $# => 1
# - fixed mycp().
#
# version 5.0, 2001-11-13, Michael Wang <xw73@columbia.edu>.
# * Added the functionality to backup on remote filesystem by specifying
#   user@host:/filesystem in the conf file.
# * Use *ora_${THE_PID}@([._])*trc to match: ibk_ora_123.trc (Solaris),
#   ora_123.trc (Linux), and ora_123_ibk.trc (AIX) while filtering out
#   ibk_ora_1234.trc (see ver 4.3)
# * Construct POSIX PATH dynamically with getconf (see ver 4.2).
# * Replaced cryptical sed with awk (see ver 2.21):
#   ORACLE_HOME=$(awk -F: "/^$ORACLE_SID:/ {print \$2; exit}" $ORATAB)
# * Open file with cat, no need for gzip large file support (avail ver 1.3+).
# * Eliminated "run by root" implemented by ver 2.1 in favor of safer,
#   cleaner code. Feature best achieved by sudo or autosys job dependency.
# * Syntax clean up.
#
# version 4.31, 2001-06-07, Michael Wang <xw73@columbia.edu>.
# * when CDPATH is set, cd may generate output, directed to /dev/null.
#   oracle:ADEV:816$cd app/..
#   /u0/oracle
#
# version 4.3, 2001-02-27, Michael Wang <xw73@columbia.edu>.
# * Change *_ora_$THE_PID.trc to *ora_${THE_PID}[!0-9]*trc
#   to match the trace file format on Solaris and on AIX.
# * Change #!/bin/ksh to /usr/dt/bin/dtksh as some ksh does not recognize
#   [[ ... == ... ]] format
#
# version 4.2, 2001-02-22, Michael Wang <xw73@columbia.edu>.
# * For compatibilities:
# * PATH=/usr/bin => PATH=/usr/xpg4/bin:/usr/bin
# * df -k => df -Pk
# * replace tee /dev/stderr with print -u1, print -u2 in while loop. 
#
# version 4.1, 2001-01-04, Michael Wang <xw73@columbia.edu>.
# * Corrected error message: HOT BACKUP FAILED => COLD BACKUP FAILED.
#
# version 4.0, 2000-11-04, Michael Wang <xw73@columbia.edu>.
# * Make SUBMIT, INIT_D configurable options.
# * Fixed the problem when running the script without typing path (as in
#   orahot.ksh SID) by using dirname.
# * Added [[ -d $ORACLE_SID ]] || mkdir $ORACLE_SID for each BKUP_DIRS.
# * Reducing mandatory configuration parameters by setting the default
#   values.
# * Cleaned the obsolete ksh syntax as reported by ksh93 -n option:
#   -eq within [[...]] obsolete, use ((...))
#   '=' obsolete, use '=='
# * unset LD_LIBRARY_PATH which is not needed for properly installed
#   Oracle binaries.
# * Better handling of trap and log messages when program is killed/failed.
#
# version 3.141, 2000-10-07, Michael Wang <xw73@columbia.edu>.
# * Added options for using compress, gzip, or bzip2 as compression tool.
#
# version 2.21, 2000-08-06, Michael Wang <xw73@columbia.edu>.
# * Chang on ORACLE_HOME= line shown below
#   OLD => ORACLE_HOME=$(sed -n "s/^$ORACLE_SID:\(.*\):.*$/\1/p" $ORATAB)
#   NEW => ORACLE_HOME=$(sed -n "s/^$ORACLE_SID:\([^:]*\):.*$/\1/p" $ORATAB)
#   This accommodates the generalized oratab with more than 3 fields.
#
# version 2.2, 1999-08-20, Michael Wang <xw73@columbia.edu>.
# * Replace ? to $ORACLE_HOME in $THE_DUMP_DEST
# * Make gen_controlfile world readable
# * General code cleanup.
#
# version 2.1, 1999-06-28, Michael Wang <xw73@columbia.edu>.
# * Added PRE_PROCESSING and POST_PROCESSING.
# * Script is runable by root as well. Previous version was written to be run 
#   by oracle user. This version can be run by both oracle and root user. This
#   is done because certain pre and post processing scripts (eg applmgr) cannot
#   be processed by oracle user. 
#
# version 2.0, 1999-03-18, Michael Wang <xw73@columbia.edu>.
# also backup init<SID>.ora and get_controlfile.sql per suggestion from
# Ping Huang, and Susan Gruebel, with help again from
# Stephanie Shen. minor code clean up.
#
# version 1.1, 1999-02-04, Michael Wang <xw73@columbia.edu>.
# * Prevent duplicate copy of the script from running. 
# * Back up files in the order of used space to take the full advantage of
#   parallel processing. SQL script was provided by Stephanie Shen.
# * General code cleanup.
# 
# version 1.0, 1999-01-19, Michael Wang <xw73@columbia.edu>.
# Rewrite based on previous versions developped in house last modified and
# maintained by Mark Speziale, and Satyaki Chatterjee. 
 
OPATH=; unset OPATH
OPATH=$PATH

GETCONF=; unset GETCONF

for GETCONF in /bin/getconf /usr/bin/getconf; do
  [[ -x $GETCONF ]] && {
    PATH=$($GETCONF PATH)  # avoid ksh93 (m, l, ...) getconf
    break;                 # which has problems.
  }
done

PATH=$PATH:/usr/sbin

typeset -x PATH

function my_getopts {
  typeset PATH= i
  typeset -u _I
  [[ _I = *=* ]] && eval ${_I%%?(:)=*}= unset ${_I%%?(:)=*}
  for i; do
    _I=$i
    eval ${_I%%?(:)=*}= unset ${_I%%?(:)=*}
    case $_I in
      ?(U)MOUNT=*) eval _${_I%%=*}=${_I#*=} ;;
      *[!:]=*)     eval ${_I%%=*}=${_I#*=}  ;;
      *:=*)        eval ${_I%%:=*}=${i#*:=} ;;
      !(*=*))      SID=$i                   ;;
    esac
  done
}

my_getopts SID:= HELP= DEBUG= MOUNT= _MOUNT= UMOUNT= _UMOUNT= "$@"

[[ $DEBUG = Y ]] && set -x

function shdoc {
  typeset version="3.1, 2008-05-28, Michael Wang <xw73@columbia.edu>."

  typeset help doc file

  typeset    tmpdir=/tmp/shdoc_$$_$RANDOM
  typeset    base tmpf i flag=N

  eval help=y doc=pod "$@"

  [[ -n ${OPATH:-} ]] && typeset -x PATH=$OPATH

  set -- $(echo $help $doc | tr '[a-z]' '[A-Z]')
  help=$1 doc=$2

  base=${file##*/}
  base=${base%.*sh}

  case ${doc} in
    (POD) mkdir -p ${tmpdir}          && tmpf=${tmpdir}/${base}.pod        ;;  
    (MAN) mkdir -p ${tmpdir}/man/man1 && tmpf=${tmpdir}/man/man1/${base}.1 ;;
  esac

  while IFS= read -r i; do
    [[ $i = "## ${doc}_START"* ]] && { flag=Y; continue; }
    [[ $i = "## ${doc}_STOP"*  ]] && { flag=N; continue; }

    if [[ $flag = "Y" ]]; then
      i=${i#"##"}
      i=${i#" "}
      printf "%s\n" "${i}"
    fi
  done < ${file} > ${tmpf}

  case $help in
    (Y)      case ${doc} in
               (POD) perldoc ${tmpf}                   ;;
               (MAN) MANPATH=${tmpdir}/man man ${base} ;;
             esac
             ;;
    (PS|PDF) case ${doc} in
               (POD) pod2man ${tmpf} ;;
               (MAN) cat     ${tmpf} ;;
             esac |
               groff -man |
                 case ${help} in
                   (PS)  cat     >${base}.ps  ;;
                   (PDF) ps2pdf - ${base}.pdf ;;
                 esac
             ;;
    (POD)    cp ${tmpf} ${base}.pod
             ;;
    (HTML)   pod2html ${tmpf} > ${base}.html
             rm pod2htmd.tmp pod2htmi.tmp
             ;;
  esac

  rm -rf ${tmpdir}
  return 0
}

[[ $HELP = @(Y|PS|PDF|POD|HTML) ]] && {
  : ${DOC:=man}
  shdoc file=$0 doc=$DOC help=$HELP
  exit 0
}

[[ -z "$SID" ]] && {
  print "ERROR: you must specify ORACLE_SID."
  exit 1
}

typeset -x ORACLE_SID=$SID

# $0 could be "./foo", convert to absolute path
PDIR=$(cd $(dirname $0)/.. >/dev/null && pwd -P) 

LOGDIR=$PDIR/operlog/$ORACLE_SID
[[ -d ${LOGDIR%/*} && ! -d ${LOGDIR} ]] && mkdir ${LOGDIR}

TMPDIR=$PDIR/temp/$ORACLE_SID
[[ -d ${TMPDIR%/*} && ! -d ${TMPDIR} ]] && mkdir ${TMPDIR}

CONFILE=$PDIR/etc/oracold_$ORACLE_SID.cf

RUN_UID= unset RUN_UID
(( RUN_UID = $(id -u) ))

cd $TMPDIR

(( RUN_UID == 0 )) || {
  mkdir $LOGDIR/bar.$$ || {
    print "ERROR: Log directory ($LOGDIR) is not writable."
    exit 1
  }

  rmdir $LOGDIR/bar.$$

  i= unset i

  while [[ -z $i || -f $i ]]
  do
    [[ -z $i ]] || sleep $(( RANDOM%10 ))
    i=$LOGDIR/oracold_$ORACLE_SID.log.$(date +%m%d.%H%M)
  done

  print "Please check the logfile: $i."

  exec >> $i 2>&1

  function get_date { date +"%Y-%m-%d %H:%M:%S"; }

  job_start= job_finish= job_elapse=; unset job_start job_finish job_elapse
  job_start=$(get_date)
  print "$job_start"
  (( job_elapse = 0 ))
  (( SECONDS = 0 ))
}

i= unset i

for i in SUBMIT INIT_D NUM_PROCS MAX_SECS PRE_PROCESSING POST_PROCESSING \
         SNAPSHOT SNAPMTPT SNAPBKST WAIT_MOUNT WAIT_UMOUNT NUM_COPIES
do
  eval $i= unset $i
done

[[ -r $CONFILE ]] || {
  print "ERROR: Can not find configuration file: $CONFILE."
  exit 1
}

. $CONFILE

: ${SNAPSHOT:=N}

[[ $SNAPSHOT = @([YN]) ]] || {
  print "ERROR: SNAPSHOT in $CONFILE must be Y or N."
  exit 1
}

[[ $SNAPSHOT = Y ]] && {
  MOUNT=${_MOUNT:-$MOUNT}
  UMOUNT=${_UMOUNT:-$UMOUNT}
  : ${MOUNT:=Y}
  : ${UMOUNT:=Y}

  [[ $MOUNT = Y ]] && {
    : ${WAIT_MOUNT:=300}
    : ${SNAPMTPT:=/snapshot}
    : ${SNAPBKST:=/var/tmp}
  }

  [[ $UMOUNT = Y ]] && : ${WAIT_UMOUNT:=120}

  [[ $MOUNT = Y && $UMOUNT = @(Y|N) ||
     $MOUNT = N && $UMOUNT = Y      ]] || {
    print "ERROR: MOUNT:UMOUNT must be Y:Y, Y:N, or N:Y."
    exit 1
  }

  [[ $MOUNT  = Y && $WAIT_MOUNT  != +([0-9]) ||
     $UMOUNT = Y && $WAIT_UMOUNT != +([0-9]) ]] && {
    print "ERROR: WAIT_MOUNT/WAIT_UMOUNT in $CONFILE must be numbers."
    exit 1
  }

  [[ $SNAPMTPT != /* || $SNAPBKST != /* && $MOUNT = Y ]] && {
    print "ERROR: SNAPMTPT/SNAPBKST in $CONFILE must be valid directory names."
    exit 1
  }
}

(( RUN_UID == 0 )) && {
  [[ $SNAPSHOT = Y && $MOUNT = Y ]] && {
    until [[ -f root_mount.sh.tmp ]]; do sleep 10; done
    grep -Ev "mkdir|mount" root_mount.sh || sh root_mount.sh
    rm -f root_mount.sh.tmp
  }

  [[ $SNAPSHOT = Y && $UMOUNT = Y ]] && {
    until [[ -f root_umount.sh.tmp ]]; do sleep 10; done
    grep -Ev "fssnap|umount" root_umount.sh || sh root_umount.sh
    rm -f root_umount.sh.tmp
  }
  exit 0
}

function snap_umount {
  if [[ -r root_umount.sh && -r root_mount.sh ]]; then
    ln root_umount.sh root_umount.sh.tmp
  else
    print "ERROR: Can not find root_umount.sh/root_mount.sh in $TMPDIR."
    exit 1
  fi

  typeset i UMOUNT SECONDS
  (( SECONDS = 0 ))

  while (( SECONDS <= WAIT_UMOUNT )); do
    (( UMOUNT = 1 ))
    for i in $(awk "/^mkdir -p / {print \$3}" root_mount.sh); do
      fssnap -i -o blockdevname ${i#$SNAPMTPT} | read ||
      df -Pk $i | grep "^/dev/fssnap/[0-9]\{1,\}" && {
       (( UMOUNT = 0 ))
       break
      }
    done
    (( UMOUNT == 1 )) && break
    sleep 1
  done

  (( UMOUNT == 1 )) || {
    print "ERROR: root failed to umount snapshot within $WAIT_UMOUNT seconds."
    exit 1
  }

  rm -f root_umount.sh.tmp
  print "INFO: root umounted snapshot."
  return 0
}

[[ $SNAPSHOT = Y && $MOUNT = N && $UMOUNT = Y ]] && snap_umount && {
  print "UMOUNT PART OF COLD BACKUP FOR $ORACLE_SID FINISHED SUCCESSFULLY."
  exit 0
}

function chkdir {
  [[ -n "$1" ]] || {
    print "ERROR: No directory specified for chkdir function."
    return 1
  }

  typeset i=$1
  typeset j
  
  if [[ -d $i ]]
  then 
    set -- $(j=$i/foo.$$; print $j; mkdir $j; ls -d $j; rmdir $j)
  elif [[ $i = *@*:* ]]; then
    # set -- ${i//[@:]/ }
    set -- $(echo $i | sed "s/[@:]/ /g")
    set -- $(rsh -l $1 $2 "j=$3/foo.\$\$; print \$j; mkdir \$j; ls -d \$j; rmdir \$j")
  else
    print "ERROR: Can not find $i directory."
    return 1
  fi

  if (( $# == 2 )) && [[ $1 = $2 ]]; then
    return 0
  else print "ERROR: Can not write in $i directory."
    return 1
  fi
}

chkdir $TMPDIR || {
  print "ERROR: in checking $TMPDIR."
  exit 1
}

: ${SUBMIT:=/usr/local/bin/submit}

[[ -x $SUBMIT ]] || {
  print "ERROR: Can not find SUBMIT=$SUBMIT."
  exit 1
}

: ${INIT_D:=/etc/init.d}
i= RC_ORACLE= unset i RC_ORACLE
RC_ORACLE=$TMPDIR/rc.oracle
rm -rf $RC_ORACLE

( cd $INIT_D && for i in oracle-$ORACLE_SID rc.oracle online; do
    [[ -f $i ]] && {
      ln -s $INIT_D/$i $RC_ORACLE
      break
    }
  done
)

[[ -L $RC_ORACLE ]] || {
  print "ERROR: cannot find rc.oracle|oracle-$ORACLE_SID|online in $INIT_D."
  exit 1
}

COMPRESS=; unset COMPRESS
COMPRESS=${COMPRESS_C%% *}

if [[ $COMPRESS = */compress ]]
then THEZ=".Z"
elif [[ $COMPRESS = */gzip ]]
then THEZ=".gz"
elif [[ $COMPRESS = */bzip2 ]]
then THEZ=".bz2"
elif [[ $COMPRESS = */cat ]]
then THEZ=""
else COMPRESS="/usr/bin/compress"
     COMPRESS_C="$COMPRESS -c"
     THEZ=".Z"
fi

[[ -x $COMPRESS ]] || {
  print "ERROR: $COMPRESS is not an executable."
  exit 1
}

: ${COPY_DIRECTORY_PATH:=N}

[[ $COPY_DIRECTORY_PATH = @([YN]) ]] || {
  print "ERROR: COPY_DIRECTORY_PATH in $CONFILE must be Y or N."
  exit 1
}

[[ -n "$BKUP_DIRS" ]] || {
  print "ERROR: BKUP_DIRS not defined in $CONFILE."
  exit 1
}

: ${NUM_COPIES:=1}

[[ "$NUM_COPIES" = +([0-9]) ]] || {
  print "ERROR: NUM_COPIES in $CONFILE is not a number."
  exit 1
}

for i in $BKUP_DIRS
do
  j= unset j
  j=$i/$ORACLE_SID

  if [[ $j = *@*:* ]]; then
    # set -- ${j//[@:]/ }
    set -- $(echo $j | sed "s/[@:]/ /g")

    {
      print "set -- ${3}_+([-0-9])_+([:0-9])"
      print
      print "[[ -d \$1 ]] && {"
      print "  (( k = 1 ))"
      print
      print "  while (( k <= $# - $NUM_COPIES + 1 )); do"
      print "    PATH=$PATH eval rm -rf \\\${\$k}"
      print "    (( k = k + 1 ))"
      print "  done"
      print "}"
      print
      print "PATH=$PATH mkdir -p $3"
    } | rsh -l $1 $2 /bin/ksh
  else
    set -- ${j}_+([-0-9])_+([:0-9])

    [[ -d $1 ]] && {
      (( k = 1 ))

      while (( k <= $# - NUM_COPIES + 1 )); do
        eval rm -rf \${$k}
        (( k = k + 1 ))
      done
    }

    mkdir -p $j
  fi

  chkdir $j || {
    print "ERROR: in checking $j."
    exit 1
  }
done

: ${NUM_PROCS:=3}

[[ "$NUM_PROCS" = +([0-9]) ]] || {
  print "ERROR: NUM_PROCS in $CONFILE is not a number."
  exit 1
}

: ${MAX_SECS:=999999}

[[ "$MAX_SECS" = +([0-9]) ]] || {
  print "ERROR: MAX_SECS in $CONFILE is not a number."
  exit 1
}

(( MAX_SECS == 0 )) && {
  print "INFO: COLD BACKUP FOR $ORACLE_SID SKIPPED."
  exit 0
}

ORACLE_HOME=; unset ORACLE_HOME
ORATAB=; unset ORATAB

for i in /var/opt/oracle/oratab /etc/oratab; do
  [[ -r $i ]] && {
    ORATAB=$i
    break
  }
done

[[ -n $ORATAB ]] || {
  print "ERROR: Can not find oratab."
  exit 1
}

typeset -x ORACLE_HOME=$(awk -F: "/^$ORACLE_SID:/ {print \$2; exit}" $ORATAB)

typeset -x LD_LIBRARY_PATH=
ISQL=$ORACLE_HOME/bin/svrmgrl
[[ -x $ISQL ]] || ISQL=$ORACLE_HOME/bin/sqlplus

[[ -x $ISQL ]] || {
  print "ERROR: Can not find $ISQL. Possibly on the other node of the cluster."
  exit 1
}

ksh $RC_ORACLE sid=$ORACLE_SID action=status verbose=n || {
  print "ERROR: Database $ORACLE_SID is down before cold backup starts."
  exit 1
}

mkdir $LOGDIR/lock || {
  print "ERROR: A copy of the script is already running."
  exit 1
}

trap "rmdir $LOGDIR/lock" EXIT INT

find $LOGDIR -mtime +60 -name \*log\* -type f -print -exec rm -f {} \;
find $TMPDIR -type f -print -exec rm -f {} \;

for i in $BKUP_DIRS
do
  if [[ $i = *@*:* ]]; then
    # set -- ${i//[@:]/ }
    set -- $(echo $i | sed "s/[@:]/ /g")
    rsh -l $1 $2 "PATH=$PATH find $3/$ORACLE_SID -type f -print -exec rm -f {} \;"
  else
    find $i/$ORACLE_SID -type f -print -exec rm -f {} \;
  fi
done

isql= fsql= unset isql fsql
typeset -Z3 isql

(( isql = 1 ))
fsql=${isql}.all.select

# Generate a list of files to be backed up ordered by _used_ size.

{ print "spool ./all.select"
  print "set echo on"

  [[ $ISQL = *sqlplus ]] && {
    print "connect / as sysdba"
    print "set linesize 960"
    print "set trimspool on"
  }

  [[ $ISQL = *svrmgrl ]] && {
    print "connect internal"
    print "set charwidth 960"
  }

  print "ALTER DATABASE BACKUP CONTROLFILE TO TRACE"
  print "/"

  print "SELECT 'THE_DUMP_DEST=' || value DUMP_DEST"
  print "FROM v\$parameter WHERE name='user_dump_dest'"
  print "/"

  print "SELECT 'THE_PID=' || spid PID"
  print "FROM v\$process p, v\$session s, v\$mystat stat"
  print "WHERE s.sid=stat.sid and p.addr=s.paddr and rownum=1"
  print "/"

  print "SELECT c.file_name || ' ' || c.used_bytes"
  print "FROM"
  print "("
  print "  SELECT a.file_name as file_name, "
  print "    SUM(a.bytes)/COUNT(a.bytes) - SUM(NVL(b.bytes,0)) as used_bytes"
  print "  FROM dba_data_files a, dba_free_space b"
  print "  WHERE a.file_id = b.file_id(+)"
  print "  GROUP BY a.file_name"
  print "  UNION"
  print "  SELECT b.member as file_name, a.bytes used_bytes"
  print "  FROM v\$log a, v\$logfile b"
  print "  WHERE a.group#=b.group#"
  print "  UNION"
  print "  SELECT name as file_name, 0 as used_bytes"
  print "  FROM v\$controlfile"
  print ") c"
  print "ORDER BY c.used_bytes DESC"
  print "/"

  [[ $COPY_DIRECTORY_PATH = Y ]] && {
    print "SELECT UNIQUE 'DIRECTORY_PATH=' || directory_path"
    print "FROM dba_directories"
    [[ -n $FILTER_DIRECTORY_PATH ]] && print $FILTER_DIRECTORY_PATH
    print "ORDER BY 1"
    print "/"
  }

  print "SET SERVEROUTPUT ON SIZE 1000000"
  print
  print "DECLARE"
  print "  sp VARCHAR2(200) := NULL;"
  print "BEGIN"
  print "SELECT value INTO sp FROM v\$parameter WHERE name = 'spfile';"
  print
  print "  IF sp IS NOT NULL THEN"
  print "    EXECUTE IMMEDIATE 'CREATE PFILE = ''$TMPDIR/spfile.txt'' FROM SPFILE';"
  print "    DBMS_OUTPUT.PUT_LINE('THE_SPFILE='|| sp);"
  print "  END IF;"
  print "END;"
  print "/"

  print "spool off"
  print "exit"
} > ${fsql}.sql

[[ $ISQL = *svrmgrl ]] && $ISQL command="@${fsql}.sql"
[[ $ISQL = *sqlplus ]] && $ISQL /nolog   @${fsql}.sql

i= unset i
i=$(egrep "^THE_.*=" all.select)

egrep "ORA-|MGR-" all.select || (( $(print "$i" | grep -c "^") <= 1 ))  && {
  print "ERROR: In running ${fsql}.sql."
  exit 1
}

eval "$i"

# THE_DUMP_DEST=${THE_DUMP_DEST/#\?/$ORACLE_HOME}

[[ $THE_DUMP_DEST = [?]* ]] && {
  THE_DUMP_DEST=$(echo ${THE_DUMP_DEST} | sed "s:^\?:$ORACLE_HOME:")
}

[[ -d "$THE_DUMP_DEST" ]] || {
  print "ERROR: Can not find THE_DUMP_DEST=$THE_DUMP_DEST."
  exit 1
}

print "INFO: THE_DUMP_DEST = $THE_DUMP_DEST."

[[ "$THE_PID" = +([0-9]) ]] || {
  print "ERROR: THE_PID=$THE_PID is not a number."
  exit 1
} # THE_DUMP_DEST and THE_PID used at end for gen_controlfile.sql

awk "NF == 2 && /^\//" all.select > all.file
num_file=$(grep -c "^" all.file)    # faster than wc, no space problem

(( num_file < 1 )) && {
  print "ERROR: I have nothing to backup."
  exit 1
}

[[ $SNAPSHOT = Y ]] && {
  function resolve_path {  # stripped down version of resolve_path().
    typeset PATH=
    typeset IPATH=$1
    typeset j PERL=/bin/perl
    typeset d f s="set"
  
    while [[ -n "$s" ]]; do
      d=$(cd ${IPATH%/*} >/dev/null && pwd -P)
      f=${IPATH##*/}
      s=$($PERL -e "print(STDOUT readlink(\"$IPATH\"));")
      [[ -n "$s" ]] && IPATH=$d/$s || { IPATH=$d/$f && print "$IPATH"; }
    done
  }

  i= j= k= l= unset i j k l
  while read i j; do
    k=$(resolve_path $i)
    l=$(df -Pk ${k%/*} | awk "!/^Filesystem/ {print \$6}")
    print "$i $k $l $j"
  done < all.file > all.snapshot

  rm -f root_mount.sh.tmp root_umount.sh.tmp
  > root_mount.sh
  > root_umount.sh

  awk "{print \$3}" all.snapshot | sort -u | while read i; do
    { print "mkdir -p ${SNAPMTPT}${i}"
      print "mount -F ufs -o ro \$(fssnap -F ufs -o bs=${SNAPBKST},unlink $i) ${SNAPMTPT}${i}"
    } >> root_mount.sh

    { print "umount ${SNAPMTPT}${i}"
      print "fssnap -F ufs -d ${i}"
    } >> root_umount.sh
  done
}

[[ -n "$PRE_PROCESSING" ]] && {
    $PRE_PROCESSING || {
    print "ERROR: PRE_PROCESSING $PRE_PROCESSING failed."
    exit 1
  }
  print "INFO: PRE_PROCESSING $PRE_PROCESSING finished successfully."
}

SHUTDOWN= unset SHUTDOWN
(( SHUTDOWN = 1 ))
ksh $RC_ORACLE sid=$ORACLE_SID action=stop verbose=n
ksh $RC_ORACLE sid=$ORACLE_SID action=stat verbose=n 

(( $? == 1 )) || {
  print "ERROR: Database $ORACLE_SID can not be shutdown for cold backup."
  exit 1
}

# To make *.[pid|log|stat] expandable by shell, used by cleanup function

> $TMPDIR/000.pid; > $TMPDIR/000.log; > $TMPDIR/000.stat

function cleanup {
  trap 'print Please wait for cleanup to finish.' EXIT INT

  (( $1 == 0 )) || print "Please wait for cleanup to finish."

  rmdir $LOGDIR/lock

  [[ -f $TMPDIR/root_mount.sh.tmp ]] && {
    ln $TMPDIR/root_umount.sh $TMPDIR/root_umount.sh.tmp
    rm $TMPDIR/root_mount.sh.tmp
  }

  (( SHUTDOWN == 1 )) && {
    ksh $RC_ORACLE sid=$ORACLE_SID action=stat || {
      ksh $RC_ORACLE sid=$ORACLE_SID action=start
    }
  }

  typeset i
  typeset j

  for i in +([0-9]).pid; do
    j=${i%.pid}
    [[ -f $j.stat ]] || kill -- -$(< $i)

    [[ -s $j.log ]] && {
      print
      cat $j.log
    }
  done

  (( $1 == 0 )) || print "ERROR: COLD BACKUP FOR $ORACLE_SID FAILED."
  trap '' 0
  return 0
}

trap "print \"ERROR: program terminated.\";
      cleanup 1;
      exit 1" EXIT INT

[[ $SNAPSHOT = Y ]] && {
  ln root_mount.sh root_mount.sh.tmp
  (( SECONDS = 0 ))
  i= MOUNT= unset i MOUNT

  while (( SECONDS <= WAIT_MOUNT )); do
    (( MOUNT = 1 ))

    for i in $(awk "/^mkdir -p / {print \$3}" root_mount.sh); do
      fssnap -i -o blockdevname ${i#$SNAPMTPT} | read j &&

      df -Pk $i | grep "^$j" || {
        (( MOUNT = 0 ))
        break
      }
    done

    (( MOUNT == 1 )) && break
    sleep 1
  done

  (( MOUNT == 1 )) || {
    print "ERROR: root failed to mount snapshot within $WAIT_MOUNT seconds."
    exit 1
  }

  rm -f root_mount.sh.tmp
  print "INFO: root mounted snapshot."

  (( SHUTDOWN = 0 ))
  ksh $RC_ORACLE sid=$ORACLE_SID action=start verbose=n
  ksh $RC_ORACLE sid=$ORACLE_SID action=stat  verbose=n

  (( $? == 0 )) || {
    print "ERROR: Database $ORACLE_SID can not be started after snapshot mount."
    exit 1
  }

  [[ $UMOUNT = N ]] && {
    print "MOUNT PART OF COLD BACKUP FOR $ORACLE_SID FINISHED SUCCESSFULLY."
    trap - EXIT
    exit 0
  }
}

typeset -Z3 the_num_file
(( the_num_file = 0 ))        # number of files submitted
(( the_num_stat = 0 ))        # number of files finished successfully
(( SECONDS = 0 ))

# We will continue until all the files finished successfully. 

while (( the_num_stat < num_file ))
do
  set -- +([0-9]).stat; (( the_num_stat = $# - 1 ))
  set -- +([0-9]).pid; (( the_num_file = $# - 1 ))
  (( the_num_proc = the_num_file - the_num_stat ))

  print "num_file_submitted:num_file_finished:num_file_allowed:num_file_total=\c"
  print $the_num_file:$the_num_stat:$NUM_PROCS:$num_file

  if (( SECONDS >= MAX_SECS ))
  then
    print "ERROR: exceeding maximum time ($MAX_SECS seconds)."
    (( the_num_stat =  num_file + 1 ))
  elif grep -v "^0$" +([0-9]).stat
  then
    print "ERROR: one of copy sessions has a non-zero exit code."
    (( the_num_stat =  num_file + 1 ))
  elif (( the_num_proc >= NUM_PROCS ))
  then
    print "INFO: $(date +%H:%M:%S) maximum number of copy session reached, wait."
    sleep 10
  elif (( the_num_file >= num_file ))
  then 
    print "INFO: $(date +%H:%M:%S) wait for file copying to finish."
    sleep 10
  else
    (( the_num_file+=1 ))

    (( the_avail = -1 ))
    for dir in $BKUP_DIRS
    do
      if [[ $dir = *@*:* ]]; then
        # set -- ${dir//[@:]/ }
        set -- $(echo $dir | sed "s/[@:]/ /g")
        avail=$(rsh -l $1 $2 "PATH=$PATH df -Pk $3" | awk '/^\// {print $4}')
      else
        avail=$(df -Pk $dir | awk '/^\// {print $4}')
      fi
      if (( avail > the_avail ))
      then the_avail=$avail
           THE_DIR=$dir
      fi
    done
    
    if [[ $SNAPSHOT = Y ]]; then
      set -- $(sed -n ${the_num_file}p all.snapshot)
      path_file=$1
      copy_file=${SNAPMTPT}$2
    else
      set -- $(sed -n ${the_num_file}p all.file)
      path_file=$1
      copy_file=$path_file
    fi
    file=${path_file##*/}
    path=${path_file%/*}
    relative_path=${path#/}
    THE_PATH=$THE_DIR/$ORACLE_SID/$relative_path
    if [[ $THE_PATH = *@*:* ]]; then
      # set -- ${THE_PATH//[@:]/ }
      set -- $(echo $THE_PATH | sed "s/[@:]/ /g")
      u= r= d= unset u r d
      u=$1 r=$2 d=$3
      rsh -l $u $r "PATH=$PATH mkdir -p $d"
      $SUBMIT -i $the_num_file.pid -l $the_num_file.log -e $the_num_file.stat \
        "cat $copy_file | $COMPRESS_C | rsh -l $u $r \"PATH=$PATH cat > $d/$file$THEZ\"" 
    else
      mkdir -p $THE_PATH
      $SUBMIT -i $the_num_file.pid -l $the_num_file.log -e $the_num_file.stat \
        "cat $copy_file | $COMPRESS_C | cat > $THE_PATH/$file$THEZ"
    fi    # not UUOC: "cat 2+GB_file | gzip" works w/o lf support (ver < 1.3).
  fi
done

(( the_num_stat == num_file )) || {
  print "ERROR: COLD BACKUP FOR $ORACLE_SID FAILED."
  exit 1
}

function mycp {
  typeset file_i=$1
  typeset file_j=$2
  typeset cksum_i=$(cksum < $file_i)
  typeset cksum_j

  [[ -f $file_i ]] || {
    print "ERROR: Can not find the file: $file_i"
    exit 1
  }

  if [[ $file_j = *@*:* ]]; then
    # set -- ${file_j//[@:]/ }
    set -- $(echo $file_j | sed "s/[@:]/ /g")
    rsh -l $1 $2 "PATH=$PATH mkdir -p ${3%/*}"
    rcp $file_i $file_j
    cksum_j=$(rsh -l $1 $2 "PATH=$PATH cksum < $3")
  else
    mkdir -p ${file_j%/*}
    cp $file_i $file_j
    cksum_j=$(cksum < $file_j)
  fi

  if [[ "$cksum_i" = "$cksum_j" ]]
  then print "INFO: cp $file_i $file_j ok."
  else print "ERROR: cp $file_i $file_j failed."
       exit 1
  fi
}

i= j=; unset i j
for i in $ORACLE_HOME/dbs/@(init$ORACLE_SID.ora|orapw$ORACLE_SID); do
  j=${i#/}
  mycp $i $THE_DIR/$ORACLE_SID/$j
done

[[ -n $THE_SPFILE && -r $THE_SPFILE ]] && {
  mycp $THE_SPFILE $THE_DIR/$ORACLE_SID/${THE_SPFILE#/}
  mycp $TMPDIR/spfile.txt $THE_DIR/$ORACLE_SID/${THE_SPFILE#/}.txt
}

set -- $(ls -t $THE_DUMP_DEST/*ora_${THE_PID}@([._])*trc)

if (( $# >= 1 )); then
  mycp $1 $THE_DIR/$ORACLE_SID/gen_controlfile.sql
else
  set -- $(ls -t $ORACLE_HOME/rdbms/log/*ora_${THE_PID}@([._])*trc)

  if (( $# >= 1 )); then
    mycp $1 $THE_DIR/$ORACLE_SID/gen_controlfile.sql
  else
    print "ERROR: Can not find backup controlfile (*ora_${THE_PID}@([._])*trc)."
    exit 1
  fi
fi

[[ $COPY_DIRECTORY_PATH = Y ]] && {
  dir= file= rel_file=; unset dir file rel_file

  for dir in $(awk -F= "/^DIRECTORY_PATH=/ {print \$2}" all.select); do
    set -- $dir/.* $dir/*

    for file in "$@"; do
      [[ -d $file ]] && continue

      rel_file=$file
      rel_file=${rel_file##+(/)}

      [[ ( -f $file || -L $file ) && -r $file ]] && {
        mycp $file $THE_DIR/$ORACLE_SID/$rel_file
      }
    done
  done
}

(( SHUTDOWN == 1 )) && {
  ksh $RC_ORACLE sid=$ORACLE_SID action=stat verbose=n
  (( $? == 1 )) || {
    print "ERROR: Database $ORACLE_SID is not down during cold backup."
    exit 1
  }

  ksh $RC_ORACLE sid=$ORACLE_SID action=start verbose=n
  SHUTDOWN=0
 
  ksh $RC_ORACLE sid=$ORACLE_SID action=stat verbose=n || {
    print "ERROR: Database $ORACLE_SID can not be started after cold backup."
    exit 1
  }
}

[[ $SNAPSHOT = Y && $UMOUNT = Y ]] && snap_umount

[[ -n "$POST_PROCESSING" ]] && {
  $POST_PROCESSING || {
    print "ERROR: POST_PROCESSING $POST_PROCESSING failed."
    exit 1
  }

  print "INFO: POST_PROCESSING $POST_PROCESSING finished successfully."
}

cleanup 0   # Since SHUTDOWN=0, DB start part is skipped.

FINISH_DATE= i= j= k=; unset FINISH_DATE i j k
FINISH_DATE=$(date +%Y-%m-%d_%H:%M:%S)

for i in $BKUP_DIRS; do
  j=$i/$ORACLE_SID
  if [[ $j = *@*:* ]]; then
    set -- $(echo $j | sed "s/[@:]/ /g")
    rsh -l $1 $2 "PATH=$PATH mv $3 ${3}_${FINISH_DATE}; echo \$?" | read k
    (( k == 0 )) || {
      print "ERROR: Can not rename $j to ${3}_${FINISH_DATE}."
      exit 1
    }
  else
    mv ${j} ${j}_${FINISH_DATE} || {
      print "ERROR: Can not rename $j to ${j}_${FINISH_DATE}."
      exit 1
    }
  fi
done

job_finish=$(get_date)
(( job_elapse += SECONDS ))
JOB="${0##*/} for $ORACLE_SID"

print
print "$JOB started:  $job_start."
print "$JOB finished: $job_finish."
print "$JOB duration: $(( job_elapse/60 )) minutes."

print "COLD BACKUP FOR $ORACLE_SID FINISHED SUCCESSFULLY."
trap - EXIT
exit 0

## POD_START
## =head1 NAME
## 
## oracold.ksh - make Oracle database cold backup, with Solaris 8 snaphot option.
## 
## =head1 SYNOPSIS
## 
## oracold.ksh help=y
## 
## oracold.ksh MySID|sid:=MySid [mount=y umount=n|mount=n umount=y] [debug=y]
## 
## =head1 DESCRIPTION
## 
## When Solaris 8 snaphot option is not available or not used, 
## oracold.ksh generates a list of datafiles, logfiles, and
## controlfiles ("Oracle files") to be backed up, shutdown the database,
## backup the Oracle files, and then start the database. The database has
## to remain down for the duration of the cold backup without Solaris 8
## snaphot option.
## 
## With Solaris 8 snaphot option, oracold.ksh generates a list of Oracle files,
## shutdown the database, create snapshots of the file systems for the
## database, start the database immediately. The database is only required
## to be down for the duration of snapshots creation, which is a matter of
## a few minutes. Then the backup is made against the snapshots, and when it
## is finished, the snapshort is deleted.
## 
## oracold.ksh can run the entire show if there are available file systems for
## backup, or it can cooperate with central Unix file system backup. In the
## latter case, it runs in two stages. First it runs with "mount=y umount=n"
## option just to create and mount the snapshots, and then leave the snapshot
## file systems to the central Unix backup. When the central backup finishes,
## we can schedule to run oracold.ksh again with "mount=n umount=y" to
## umount and delete the snapshots. The options can be specified either as
## command line options, or in the configuration file. The command line options
## have higher precedence over the configuration file.
## 
## As only root can create and delete snapshots, oracold.ksh include a small
## portion of the code for root to run. To make backup, the script needs to
## run by both Oracle and root user at same time. oracle will do oracle's job,
## root will do root's job. Oracle sends root instruction via dummy files
## in the file system. The one way communication is illustrated below: 
## 
##   $ ./oracold.ksh MySID # (Oracle user)
##   # ./oracold.ksh MySID # (root user)
## 
##   root:   (waiting for order from oracle.)
##   oracle: shutdown database.
##   oracle: root, please create snapshots.
##   root:   (verify order) ok.
##   oracle: startup database.
##   oracle: backup snapshots.
##   oracle: root, please delete snapshots.
##   root:   (verify order) ok.
##   root:   bye.
##   oracle: bye.
## 
## The backup job with Solaris 8 snaphot option can be completed interactively
## by oracle user and root user, or by using both oracle and root's crontabs,
## or by using root's crontab alone with "su" command, or best of all, by
## creating a autosys box job:
## 
##   insert_job: MYSID_COLD_BACKUP_BX job_type: b
##   owner: oracle
##   ...
##   description: "BOX JOB that backs up MYSID with Solaris 8 snapshot."
##   
##     insert_job: MYSID_COLD_BACKUP_ORACLE job_type: f
##     box_name: MYSID_COLD_BACKUP_BX
##     command: <job_dir>/ora_cold/script/oracold.ksh sid:=MySID
##     owner: oracle
##     ...
##     description: "oracle's job in MYSID_COLD_BACKUP_BX"
##   
##     insert_job: MYSID_COLD_BACKUP_ROOT job_type: f
##     box_name: MYSID_COLD_BACKUP_BX
##     command: <job_dir>/ora_cold/script/oracold.ksh sid:=MySID
##     owner: root
##     ...
##     description: "root's job in MYSID_COLD_BACKUP_BX"
## 
## oracold.ksh also backs up init<SID>.ora, and "text copy of the controlfile"
## generated with "alter database controlfile to trace" as a courtesy (renamed
## "gen_controlfile.sql" in the backup set) if it backup the Oracle files by
## itself.
##  
## To coordinate with central backup, then we need to setup two box jobs:
## 
##   MYSID_COLD_BACKUP_A_BX
##   MYSID_COLD_BACKUP_B_BX
##
## Both boxes are identical as MYSID_COLD_BACKUP_BX except that the "A_BX"
## has "MOUNT=Y UMOUNT=N" option, and "B_BX" has "MOUNT=N UMOUNT=Y" option.
## The options can be dynamically changed in the configuration file per
## day of the week condition as shown in the sample configuration file below,
## or specified as command line options, which override the configuration
## file. 
## 
## The job dependencies are the central backup job depends on the successful
## completion of A_BX job, and the B_BX depends on the successful completion
## of central backup job. The dependencies can be coded in autosys JIL code
## if central backup can be scheduled using autosys, or just by using time
## windows.
##
## Other features include:
## 
## =over 4
## 
## =item *
## 
## keeps multiple (NUM_PROCS) "compress"
## processes running at the same time (the compress utility can be compress,
## gzip, bzip2, or whatever which can takes stdin and compress to stdout).
## The large file (>2GB) support on the compress utility is I<not> required.
## 
## =item *
##
## keeps multiple copies online (NUM_COPIES).
## 
## =item *
## 
## it uses multiple file systems including remote file systems, and dynamically
## balance the storage usage among the file systems.
## 
## =item *
## 
## startup the database
## via signal handler when an exception is caught, and clean up the backup
## processes.
## 
## =item *
## 
## maximum allowed run time, or backup skip (MAX_SECS).
## 
## =item *
## 
## Prevent duplicate backup processes from running.
## 
## =back
## 
## =head1 INSTALLATION
## 
## To install, follow these easy 1-2-3 steps: 
## 
## =over 4
## 
## =item 1.
## 
## Create an install directory for cold backup <job dir>/ora_cold. And
## under ora_cold, create following sub-directories:
## 
##   script
##   etc
##   operlog/<SID>
##   temp/<SID>
## 
## Where SID is the actual ORACLE_SID name. If you have multiple SIDs,
## then just create them under operlog and temp.
## 
## =item 2. 
## 
##   cp oracold.ksh script/oracold.ksh
##   chmod 755 script/oracold.ksh
## 
## =item 3.
## 
## Edit etc/oracold_SID.cf from the sample configuration shown below. If
## you have multiple SIDs, then just create multiple files.
## 
## =back
## 
##   # *
##   # * Where do you want the cold backup files to go?
##   # * It can be a directory on a remote server.
##   # *
##    
##   BKUP_DIRS="/bkup01/cold oracle@castor:/bkup02/cold"
##   
##   # *
##   # * How many simutaneous compress processes you want to run?
##   # * Rule of thumb is to set it to the number of processors. 
##   # * Default is 3.
##   # *
##   
##   NUM_PROCS=3
##
##   # *
##   # * How many copies you want to keep online?
##   # * ( If you keep only 1 copy, make sure the file system backup
##   # * occurs after the Oracle backup finishes. )
##   # *
##
##   NUM_COPIES=2
##   
##   # *
##   # * What is the maximum time you allow the backup to run?
##   # * Backup will kill itself exceeding the maximum allowed time. 
##   # * Default is 999999.
##   # *
##   # * If it is set to 0, then backup is skipped.
##   # *
##   
##   MAX_SECS=999999
##   
##   # *
##   # * What command do you use to compress a file to standard output?
##   # * Default is "compress -c".
##   # *
##   
##   COMPRESS_C="/usr/bin/compress -c"
##   # COMPRESS_C="/usr/local/bin/gzip -c"
##   # COMPRESS_C="/usr/local/bin/bzip2 -c"
##   
##   # *
##   # * Whether to copy files under each DIRECTORY_PATH
##   # * from dba_directories.
##   # *
##
##   COPY_DIRECTORY_PATH=Y
##
##   # *
##   # * FILTER_DIRECTORY_PATH: to include or exclude certain
##   # * OWNER, DIRECTORY_NAME, or DIRECTORY_PATH from DBA_DIRECTORIES.
##   # *
##
##   FILTER_DIRECTORY_PATH="where DIRECTORY_NAME not in ('AUDIT_DIR')"
##
##   # *
##   # * Where can you find submit?
##   # * /usr/local/bin/submit is the default.
##   # *
##   
##   SUBMIT=<job_dir>/common/submit
##   
##   # *
##   # * What is the directory where you can find oracle-SID?
##   # * /etc/init.d is the default.
##   # *
##   
##   INIT_D=<job_dir>/common
##   
##   # *
##   # * Do you need pre/post processing?
##   # *
##   
##   # PRE_PROCESSING="/bin/true"
##   # POST_PROCESSING="/bin/true"
##   
##   # *
##   # * Solaris 8 snapshot cold backup?
##   # * (1) shutdown the database;
##   # * (2) make a snapshot;
##   # * (3) startup the database immediately;
##   # * (4) backup the snapshot.
##   # *
##   
##   SNAPSHOT=Y                  # Y or N
##   [[ $SNAPSHOT = Y ]] && {   # if backup snapshot, then
##     if [[ $(date +%A) = "_Saturday_" ]]; then
##       MOUNT=Y UMOUNT=N        # "Saturday" mount snapshot central backup
##     elif [[ $(date +%A) = "_Sunday_" ]]; then
##       MOUNT=N UMOUNT=Y        # "Sunday" umount snapshot when done
##     else                      # delete "_" chars in actual use
##       MOUNT=Y UMOUNT=Y
##     fi
## 
##     [[ $MOUNT = Y ]] && {
##       SNAPMTPT=/snapshot      # what is the top level snapshot mount point?
##       SNAPBKST=/var/tmp       # what is the backing store for the snapshot?
##       (( WAIT_MOUNT  = 300 )) # seconds to wait for root to  mount snapshot?
##     }
##
##     [[ $UMOUNT = Y ]] && {
##       (( WAIT_UMOUNT = 120 )) # seconds to wait for root to umount snapshot?
##     }
##   }
## 
## =head1 RESTTORE
##
## The ora_cold.ksh copies Oracle data files, control files,
## and redo log files. The text copy of the control file
## gen_controlfile.sql, and parameter file initI<SID>.ora are
## also backed up gratuitously.
## 
## A sample hot backup file set is shown below: 
##
##  /bkup01/cold/ora901/ora10/oradata/ora901/control01.ctl.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/control02.ctl.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/control03.ctl.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/cwmlite01.dbf.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/drsys01.dbf.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/example01.dbf.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/indx01.dbf.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/redo01.log.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/redo02.log.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/redo03.log.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/system01.dbf.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/tools01.dbf.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/undotbs01.dbf.gz
##  /bkup01/cold/ora901/ora10/oradata/ora901/users01.dbf.gz
##  /bkup01/cold/ora901/gen_controlfile.sql
##  /bkup01/cold/ora901/initora901.ora
##
## To restore, first copy Oracle data files, control files,
## and redo log files to where they were. As the original
## file path are recorded in the backup set, it is very easy to
## automate the task. The following I<ad hoc> script
## does this.
##
##   #!/bin/ksh
##   
##   find /bkup01/cold/ora901/ora* -type f | while read i; do
##     j=${i#*ora901}
##     dir=${j%/*}
##     file=${j%.gz}
##   
##     CAT=cat
##     [[ $i = *.gz ]] && CAT="gzip -dc"
##   
##     mkdir -p $dir
##     $CAT $i > $file
##   done
## 
## What it does is something like,
## 
##   mkdir -p /ora10/oradata/ora901
##   gzip -dc /bkup01/cold/ora901/ora10/oradata/ora901/system01.dbf.gz \
##     > /ora10/oradata/ora901/system01.dbf
## 
## Once all the files are in place, we can go ahead to start the database.
## For example,
##
##   rc.oracle sid=ora901 action=start
##
## That is it, happy restore!
##
## =head1 SEE ALSO
##
## submit help=y, rc.oracle help=y.
##
## =head1 AUTHOR
## 
## Michael Wang <F<xw73@columbia.edu>> with help from many others
## acknowledged in version notes.
## 
## This is free software. You may copy or redistribute it under the same
## terms as Perl. However, if you modify it, you need to send the
## modification to the author via email.
## POD_STOP
## MAN_START
## .\" Automatically generated by Pod::Man v1.37, Pod::Parser v1.32
## .\"
## .\" Standard preamble:
## .\" ========================================================================
## .de Sh \" Subsection heading
## .br
## .if t .Sp
## .ne 5
## .PP
## \fB\\$1\fR
## .PP
## ..
## .de Sp \" Vertical space (when we can't use .PP)
## .if t .sp .5v
## .if n .sp
## ..
## .de Vb \" Begin verbatim text
## .ft CW
## .nf
## .ne \\$1
## ..
## .de Ve \" End verbatim text
## .ft R
## .fi
## ..
## .\" Set up some character translations and predefined strings.  \*(-- will
## .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
## .\" double quote, and \*(R" will give a right double quote.  | will give a
## .\" real vertical bar.  \*(C+ will give a nicer C++.  Capital omega is used to
## .\" do unbreakable dashes and therefore won't be available.  \*(C` and \*(C'
## .\" expand to `' in nroff, nothing in troff, for use with C<>.
## .tr \(*W-|\(bv\*(Tr
## .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
## .ie n \{\
## .    ds -- \(*W-
## .    ds PI pi
## .    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
## .    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
## .    ds L" ""
## .    ds R" ""
## .    ds C` ""
## .    ds C' ""
## 'br\}
## .el\{\
## .    ds -- \|\(em\|
## .    ds PI \(*p
## .    ds L" ``
## .    ds R" ''
## 'br\}
## .\"
## .\" If the F register is turned on, we'll generate index entries on stderr for
## .\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index
## .\" entries marked with X<> in POD.  Of course, you'll have to process the
## .\" output yourself in some meaningful fashion.
## .if \nF \{\
## .    de IX
## .    tm Index:\\$1\t\\n%\t"\\$2"
## ..
## .    nr % 0
## .    rr F
## .\}
## .\"
## .\" For nroff, turn off justification.  Always turn off hyphenation; it makes
## .\" way too many mistakes in technical documents.
## .hy 0
## .if n .na
## .\"
## .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
## .\" Fear.  Run.  Save yourself.  No user-serviceable parts.
## .    \" fudge factors for nroff and troff
## .if n \{\
## .    ds #H 0
## .    ds #V .8m
## .    ds #F .3m
## .    ds #[ \f1
## .    ds #] \fP
## .\}
## .if t \{\
## .    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
## .    ds #V .6m
## .    ds #F 0
## .    ds #[ \&
## .    ds #] \&
## .\}
## .    \" simple accents for nroff and troff
## .if n \{\
## .    ds ' \&
## .    ds ` \&
## .    ds ^ \&
## .    ds , \&
## .    ds ~ ~
## .    ds /
## .\}
## .if t \{\
## .    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
## .    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
## .    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
## .    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
## .    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
## .    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
## .\}
## .    \" troff and (daisy-wheel) nroff accents
## .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
## .ds 8 \h'\*(#H'\(*b\h'-\*(#H'
## .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
## .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
## .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
## .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
## .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
## .ds ae a\h'-(\w'a'u*4/10)'e
## .ds Ae A\h'-(\w'A'u*4/10)'E
## .    \" corrections for vroff
## .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
## .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
## .    \" for low resolution devices (crt and lpr)
## .if \n(.H>23 .if \n(.V>19 \
## \{\
## .    ds : e
## .    ds 8 ss
## .    ds o a
## .    ds d- d\h'-1'\(ga
## .    ds D- D\h'-1'\(hy
## .    ds th \o'bp'
## .    ds Th \o'LP'
## .    ds ae ae
## .    ds Ae AE
## .\}
## .rm #[ #] #H #V #F C
## .\" ========================================================================
## .\"
## .IX Title "ORACOLD.KSH.TXT 1"
## .TH ORACOLD.KSH.TXT 1 "2008-12-28" "perl v5.8.8" "User Contributed Perl Documentation"
## .SH "NAME"
## oracold.ksh \- make Oracle database cold backup, with Solaris 8 snaphot option.
## .SH "SYNOPSIS"
## .IX Header "SYNOPSIS"
## oracold.ksh help=y
## .PP
## oracold.ksh MySID|sid:=MySid [mount=y umount=n|mount=n umount=y] [debug=y]
## .SH "DESCRIPTION"
## .IX Header "DESCRIPTION"
## When Solaris 8 snaphot option is not available or not used, 
## oracold.ksh generates a list of datafiles, logfiles, and
## controlfiles (\*(L"Oracle files\*(R") to be backed up, shutdown the database,
## backup the Oracle files, and then start the database. The database has
## to remain down for the duration of the cold backup without Solaris 8
## snaphot option.
## .PP
## With Solaris 8 snaphot option, oracold.ksh generates a list of Oracle files,
## shutdown the database, create snapshots of the file systems for the
## database, start the database immediately. The database is only required
## to be down for the duration of snapshots creation, which is a matter of
## a few minutes. Then the backup is made against the snapshots, and when it
## is finished, the snapshort is deleted.
## .PP
## oracold.ksh can run the entire show if there are available file systems for
## backup, or it can cooperate with central Unix file system backup. In the
## latter case, it runs in two stages. First it runs with \*(L"mount=y umount=n\*(R"
## option just to create and mount the snapshots, and then leave the snapshot
## file systems to the central Unix backup. When the central backup finishes,
## we can schedule to run oracold.ksh again with \*(L"mount=n umount=y\*(R" to
## umount and delete the snapshots. The options can be specified either as
## command line options, or in the configuration file. The command line options
## have higher precedence over the configuration file.
## .PP
## As only root can create and delete snapshots, oracold.ksh include a small
## portion of the code for root to run. To make backup, the script needs to
## run by both Oracle and root user at same time. oracle will do oracle's job,
## root will do root's job. Oracle sends root instruction via dummy files
## in the file system. The one way communication is illustrated below: 
## .PP
## .Vb 2
## \&  $ ./oracold.ksh MySID # (Oracle user)
## \&  # ./oracold.ksh MySID # (root user)
## .Ve
## .PP
## .Vb 10
## \&  root:   (waiting for order from oracle.)
## \&  oracle: shutdown database.
## \&  oracle: root, please create snapshots.
## \&  root:   (verify order) ok.
## \&  oracle: startup database.
## \&  oracle: backup snapshots.
## \&  oracle: root, please delete snapshots.
## \&  root:   (verify order) ok.
## \&  root:   bye.
## \&  oracle: bye.
## .Ve
## .PP
## The backup job with Solaris 8 snaphot option can be completed interactively
## by oracle user and root user, or by using both oracle and root's crontabs,
## or by using root's crontab alone with \*(L"su\*(R" command, or best of all, by
## creating a autosys box job:
## .PP
## .Vb 4
## \&  insert_job: MYSID_COLD_BACKUP_BX job_type: b
## \&  owner: oracle
## \&  ...
## \&  description: "BOX JOB that backs up MYSID with Solaris 8 snapshot."
## .Ve
## .PP
## .Vb 6
## \&    insert_job: MYSID_COLD_BACKUP_ORACLE job_type: f
## \&    box_name: MYSID_COLD_BACKUP_BX
## \&    command: <job_dir>/ora_cold/script/oracold.ksh sid:=MySID
## \&    owner: oracle
## \&    ...
## \&    description: "oracle's job in MYSID_COLD_BACKUP_BX"
## .Ve
## .PP
## .Vb 6
## \&    insert_job: MYSID_COLD_BACKUP_ROOT job_type: f
## \&    box_name: MYSID_COLD_BACKUP_BX
## \&    command: <job_dir>/ora_cold/script/oracold.ksh sid:=MySID
## \&    owner: root
## \&    ...
## \&    description: "root's job in MYSID_COLD_BACKUP_BX"
## .Ve
## .PP
## oracold.ksh also backs up init<\s-1SID\s0>.ora, and \*(L"text copy of the controlfile\*(R"
## generated with \*(L"alter database controlfile to trace\*(R" as a courtesy (renamed
## \&\*(L"gen_controlfile.sql\*(R" in the backup set) if it backup the Oracle files by
## itself.
## .PP
## To coordinate with central backup, then we need to setup two box jobs:
## .PP
## .Vb 2
## \&  MYSID_COLD_BACKUP_A_BX
## \&  MYSID_COLD_BACKUP_B_BX
## .Ve
## .PP
## Both boxes are identical as \s-1MYSID_COLD_BACKUP_BX\s0 except that the \*(L"A_BX\*(R"
## has \*(L"MOUNT=Y UMOUNT=N\*(R" option, and \*(L"B_BX\*(R" has \*(L"MOUNT=N UMOUNT=Y\*(R" option.
## The options can be dynamically changed in the configuration file per
## day of the week condition as shown in the sample configuration file below,
## or specified as command line options, which override the configuration
## file. 
## .PP
## The job dependencies are the central backup job depends on the successful
## completion of A_BX job, and the B_BX depends on the successful completion
## of central backup job. The dependencies can be coded in autosys \s-1JIL\s0 code
## if central backup can be scheduled using autosys, or just by using time
## windows.
## .PP
## Other features include:
## .IP "\(bu" 4
## keeps multiple (\s-1NUM_PROCS\s0) \*(L"compress\*(R"
## processes running at the same time (the compress utility can be compress,
## gzip, bzip2, or whatever which can takes stdin and compress to stdout).
## The large file (>2GB) support on the compress utility is \fInot\fR required.
## .IP "\(bu" 4
## keeps multiple copies online (\s-1NUM_COPIES\s0).
## .IP "\(bu" 4
## it uses multiple file systems including remote file systems, and dynamically
## balance the storage usage among the file systems.
## .IP "\(bu" 4
## startup the database
## via signal handler when an exception is caught, and clean up the backup
## processes.
## .IP "\(bu" 4
## maximum allowed run time, or backup skip (\s-1MAX_SECS\s0).
## .IP "\(bu" 4
## Prevent duplicate backup processes from running.
## .SH "INSTALLATION"
## .IX Header "INSTALLATION"
## To install, follow these easy 1\-2\-3 steps: 
## .IP "1." 4
## Create an install directory for cold backup <job dir>/ora_cold. And
## under ora_cold, create following sub\-directories:
## .Sp
## .Vb 4
## \&  script
## \&  etc
## \&  operlog/<SID>
## \&  temp/<SID>
## .Ve
## .Sp
## Where \s-1SID\s0 is the actual \s-1ORACLE_SID\s0 name. If you have multiple SIDs,
## then just create them under operlog and temp.
## .IP "2." 4
## .Vb 2
## \&  cp oracold.ksh script/oracold.ksh
## \&  chmod 755 script/oracold.ksh
## .Ve
## .IP "3." 4
## Edit etc/oracold_SID.cf from the sample configuration shown below. If
## you have multiple SIDs, then just create multiple files.
## .PP
## .Vb 4
## \&  # *
## \&  # * Where do you want the cold backup files to go?
## \&  # * It can be a directory on a remote server.
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  BKUP_DIRS="/bkup01/cold oracle@castor:/bkup02/cold"
## .Ve
## .PP
## .Vb 5
## \&  # *
## \&  # * How many simutaneous compress processes you want to run?
## \&  # * Rule of thumb is to set it to the number of processors. 
## \&  # * Default is 3.
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  NUM_PROCS=3
## .Ve
## .PP
## .Vb 5
## \&  # *
## \&  # * How many copies you want to keep online?
## \&  # * ( If you keep only 1 copy, make sure the file system backup
## \&  # * occurs after the Oracle backup finishes. )
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  NUM_COPIES=2
## .Ve
## .PP
## .Vb 7
## \&  # *
## \&  # * What is the maximum time you allow the backup to run?
## \&  # * Backup will kill itself exceeding the maximum allowed time. 
## \&  # * Default is 999999.
## \&  # *
## \&  # * If it is set to 0, then backup is skipped.
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  MAX_SECS=999999
## .Ve
## .PP
## .Vb 4
## \&  # *
## \&  # * What command do you use to compress a file to standard output?
## \&  # * Default is "compress -c".
## \&  # *
## .Ve
## .PP
## .Vb 3
## \&  COMPRESS_C="/usr/bin/compress -c"
## \&  # COMPRESS_C="/usr/local/bin/gzip -c"
## \&  # COMPRESS_C="/usr/local/bin/bzip2 -c"
## .Ve
## .PP
## .Vb 4
## \&  # *
## \&  # * Whether to copy files under each DIRECTORY_PATH
## \&  # * from dba_directories.
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  COPY_DIRECTORY_PATH=Y
## .Ve
## .PP
## .Vb 4
## \&  # *
## \&  # * FILTER_DIRECTORY_PATH: to include or exclude certain
## \&  # * OWNER, DIRECTORY_NAME, or DIRECTORY_PATH from DBA_DIRECTORIES.
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  FILTER_DIRECTORY_PATH="where DIRECTORY_NAME not in ('AUDIT_DIR')"
## .Ve
## .PP
## .Vb 4
## \&  # *
## \&  # * Where can you find submit?
## \&  # * /usr/local/bin/submit is the default.
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  SUBMIT=<job_dir>/common/submit
## .Ve
## .PP
## .Vb 4
## \&  # *
## \&  # * What is the directory where you can find oracle-SID?
## \&  # * /etc/init.d is the default.
## \&  # *
## .Ve
## .PP
## .Vb 1
## \&  INIT_D=<job_dir>/common
## .Ve
## .PP
## .Vb 3
## \&  # *
## \&  # * Do you need pre/post processing?
## \&  # *
## .Ve
## .PP
## .Vb 2
## \&  # PRE_PROCESSING="/bin/true"
## \&  # POST_PROCESSING="/bin/true"
## .Ve
## .PP
## .Vb 7
## \&  # *
## \&  # * Solaris 8 snapshot cold backup?
## \&  # * (1) shutdown the database;
## \&  # * (2) make a snapshot;
## \&  # * (3) startup the database immediately;
## \&  # * (4) backup the snapshot.
## \&  # *
## .Ve
## .PP
## .Vb 9
## \&  SNAPSHOT=Y                  # Y or N
## \&  [[ $SNAPSHOT = Y ]] && {   # if backup snapshot, then
## \&    if [[ $(date +%A) = "_Saturday_" ]]; then
## \&      MOUNT=Y UMOUNT=N        # "Saturday" mount snapshot central backup
## \&    elif [[ $(date +%A) = "_Sunday_" ]]; then
## \&      MOUNT=N UMOUNT=Y        # "Sunday" umount snapshot when done
## \&    else                      # delete "_" chars in actual use
## \&      MOUNT=Y UMOUNT=Y
## \&    fi
## .Ve
## .PP
## .Vb 5
## \&    [[ $MOUNT = Y ]] && {
## \&      SNAPMTPT=/snapshot      # what is the top level snapshot mount point?
## \&      SNAPBKST=/var/tmp       # what is the backing store for the snapshot?
## \&      (( WAIT_MOUNT  = 300 )) # seconds to wait for root to  mount snapshot?
## \&    }
## .Ve
## .PP
## .Vb 4
## \&    [[ $UMOUNT = Y ]] && {
## \&      (( WAIT_UMOUNT = 120 )) # seconds to wait for root to umount snapshot?
## \&    }
## \&  }
## .Ve
## .SH "RESTTORE"
## .IX Header "RESTTORE"
## The ora_cold.ksh copies Oracle data files, control files,
## and redo log files. The text copy of the control file
## gen_controlfile.sql, and parameter file init\fI\s-1SID\s0\fR.ora are
## also backed up gratuitously.
## .PP
## A sample hot backup file set is shown below: 
## .PP
## .Vb 16
## \& /bkup01/cold/ora901/ora10/oradata/ora901/control01.ctl.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/control02.ctl.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/control03.ctl.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/cwmlite01.dbf.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/drsys01.dbf.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/example01.dbf.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/indx01.dbf.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/redo01.log.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/redo02.log.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/redo03.log.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/system01.dbf.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/tools01.dbf.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/undotbs01.dbf.gz
## \& /bkup01/cold/ora901/ora10/oradata/ora901/users01.dbf.gz
## \& /bkup01/cold/ora901/gen_controlfile.sql
## \& /bkup01/cold/ora901/initora901.ora
## .Ve
## .PP
## To restore, first copy Oracle data files, control files,
## and redo log files to where they were. As the original
## file path are recorded in the backup set, it is very easy to
## automate the task. The following \fIad hoc\fR script
## does this.
## .PP
## .Vb 1
## \&  #!/bin/ksh
## .Ve
## .PP
## .Vb 4
## \&  find /bkup01/cold/ora901/ora* -type f | while read i; do
## \&    j=${i#*ora901}
## \&    dir=${j%/*}
## \&    file=${j%.gz}
## .Ve
## .PP
## .Vb 2
## \&    CAT=cat
## \&    [[ $i = *.gz ]] && CAT="gzip -dc"
## .Ve
## .PP
## .Vb 3
## \&    mkdir -p $dir
## \&    $CAT $i > $file
## \&  done
## .Ve
## .PP
## What it does is something like,
## .PP
## .Vb 3
## \&  mkdir -p /ora10/oradata/ora901
## \&  gzip -dc /bkup01/cold/ora901/ora10/oradata/ora901/system01.dbf.gz \e
## \&    > /ora10/oradata/ora901/system01.dbf
## .Ve
## .PP
## Once all the files are in place, we can go ahead to start the database.
## For example,
## .PP
## .Vb 1
## \&  rc.oracle sid=ora901 action=start
## .Ve
## .PP
## That is it, happy restore!
## .SH "SEE ALSO"
## .IX Header "SEE ALSO"
## submit help=y, rc.oracle help=y.
## .SH "AUTHOR"
## .IX Header "AUTHOR"
## Michael Wang <\fIxw73@columbia.edu\fR> with help from many others
## acknowledged in version notes.
## .PP
## This is free software. You may copy or redistribute it under the same
## terms as Perl. However, if you modify it, you need to send the
## modification to the author via email.
## MAN_STOP