#!/usr/local/bin/ksh
# 
# Copy archivelog files to standby, apply the logs, and check for errors.
# Type "update_standby help=y" for man page.
#
# version 3.3, 2004-05-02, Michael Wang, xw73@columbia.edu.
# * Updated to use ssh instead of rsh.
# version 3.2, 2002-12-17, Michael Wang, xw73@columbia.edu.
# * Change "compress -c | zcat" to copy directly.
# version 3.1, 2002-08-21, Michael Wang, xw73@columbia.edu.
# * Fixes on archivelog pattern matching.
# version 3.0, 2002-08-13, Michael Wang, xw73@columbia.edu.
# * Complicated and generalized the operation.
# version 2.0, 1999-12-00, Michael Wang, xw73@columbia.edu.
# * Simplified and stablized the operation.
# version 1.0, 0000-00-00, Hasan Jawaid.

set -e
PATH=$(/bin/getconf PATH)
PATH=$PATH:/usr/local/bin

MY_BASE=$(basename $0 .ksh)
MY_DIR=$(dirname $0)
MY_LOGDIR=$MY_DIR/log/$MY_BASE
[[ -d $MY_LOGDIR ]] || mkdir -p $MY_LOGDIR
find $MY_LOGDIR -mtime +30 -type f -exec rm {} \;

MY_LOGFILE=$MY_LOGDIR/$MY_BASE.$(date +%Y-%m-%d)

exec 3>&1
exec 4>&2
exec 1>> $MY_LOGFILE 2>&1

function get_date { date +%Y-%m-%d.%H:%M:%S; }
MY_TIME=$(get_date)
print $MY_TIME

find $MY_LOGDIR -type f -mtime +30 -exec rm {} \;

MY_LOCKDIR=$MY_LOGDIR/$MY_BASE.lock

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

trap "print \"running trap, please wait.\";
      rmdir $MY_LOCKDIR;
      print \"finish running trap.\"" exit

[[ -r $MY_DIR/$MY_BASE.cf ]] || {
  print "ERROR: Expect to find configuation file ($MY_DIR/$MY_BASE.cf)."
  exit 1
}
. $MY_DIR/$MY_BASE.cf

[[ -n "$ORACLE_SID" && -n "$ORACLE_HOME" && -n "$MY_BACKSVR" ]] || {
  print "ERROR: I need $ORACLE_SID and $ORACLE_HOME and $MY_BACKSVR ($ORACLE_SID:$$ORACLE_HOME:$MY_BACKSVR)."
  exit 1
}

[[ $MY_BACKSVR == *@* ]] || {
  MY_BACKSVR=$(id -un)@$MY_BACKSVR
}

PATH=$PATH:$ORACLE_HOME/bin

sqlplus /nolog << EOF
  connect / as sysdba
  alter system archive log current;
EOF
sleep 10

function find_archive_dest {
  sqlplus /nolog << EOF | awk -F= "/^a=/ {print \$2}"
    connect / as sysdba
    set linesize 240
    set trimspool on
    SELECT 'a=' || destination a
    FROM   v\$archive_dest
    WHERE  target='PRIMARY'
    AND    destination LIKE '%/%'
    AND    rownum <=1;
EOF
}

function find_highest_archived_thread_sequence {
  typeset a b
  sqlplus /nolog << EOF | awk -F= "/^a=/ {print \$2}" | read a b
    set linesize 240
    set trimspool on
    connect / as sysdba
    select 'a=' || THREAD#, SEQUENCE#
    from v\$log
    where SEQUENCE# = (
      select min(SEQUENCE#) from v\$log
      where archived='NO');
EOF
  print "$a $(( b-1 ))"
}

function find_log_archive_format {
  sqlplus /nolog << EOF | awk -F= "/^a=/ {print \$2}"
    set linesize 240
    set trimspool on
    connect / as sysdba
    select 'a=' || value a
    from v\$parameter
    where name='log_archive_format';
EOF
}

function format_to_file {
  typeset a b c d e
  a=$1  # format
  b=$2  # thread
  c=$3  # sequence number
  d=$4  # ksh pattern (pat) or regular expression (regex)
  if [[ $d == "pattern" ]]; then
    e=${a//%s/@($c)}
    e=${e//%S/*(0)$c}
    e=${e//%t/@($b)}
    e=${e//%T/*(0)$b}
  else
    e=${a//%s/$c}
    e=${e//%S/0*$c}
    e=${e//%t/$b}
    e=${e//%T/0*$b}
  fi
  print $e
}

MY_ARCHDIR=$(find_archive_dest)
[[ -d "$MY_ARCHDIR" ]] || {
  print "ERROR: did find archivelog destination ($MY_ARCHDIR)."
  exit 1
}
: ${MY_BACKDIR:=$MY_ARCHDIR}
: ${MY_BACKSID:=$ORACLE_SID}

find_highest_archived_thread_sequence | read MY_THREAD MY_HIGH_SEQ
[[ $MY_THREAD == +([0-9]) ]] && [[ $MY_HIGH_SEQ == +([0-9]) ]] || {
  print "ERROR: did not get thread/sequence number ($MY_THREAD/$MY_HIGH_SEQ)."
  exit 1
}

MY_ARCHFMT=$(find_log_archive_format)
[[ -n "$MY_ARCHFMT" ]] || {
  print "ERROR: did find archivelog format ($MY_ARCHFMT)."
  exit 1
}

i= MY_DONE_SEQ= unset i MY_DONE_SEQ
i=$MY_ARCHDIR/standby.done
[[ -r $i ]] || {
  print "ERROR: I need $MY_ARCHDIR/standby.done."
  exit 1
}

MY_DONE_SEQ=$(< $i)

[[ $MY_DONE_SEQ == +([0-9]) ]] || {
  print "ERROR: did not get MY_DONE_SEQ correctly ($MY_DONE_SEQ)."
  exit 1
}

(( MY_HIGH_SEQ > MY_DONE_SEQ )) || {
  print "ERROR: there is nothing to do (MY_HIGH_SEQ=$MY_HIGH_SEQ, MY_DONE_SEQ=$MY_DONE_SEQ)."
  exit 1
}

for (( MY_SEQ = MY_DONE_SEQ + 1; MY_SEQ <= MY_HIGH_SEQ; MY_SEQ++ )); do
  MY_ARCHLOG=$(format_to_file $MY_ARCHFMT $MY_THREAD $MY_SEQ "pattern")
  MY_ARCHLOG=$(cd $MY_ARCHDIR >/dev/null && eval ls $MY_ARCHLOG)
  [[ -r $MY_ARCHDIR/$MY_ARCHLOG ]] || {
    print "ERROR: can not find archive log ($MY_ARCHDIR/$MY_ARCHLOG)."
    exit 1
  }

  get_date
  print "=> processing $MY_ARCHDIR/$MY_ARCHLOG <="
  MY_CKSUM=$(cksum < $MY_ARCHDIR/$MY_ARCHLOG)

  i= unset i
  (( i = 1 ))
  while (( i <= 3 )); do
    rsync -av --rsh=ssh $MY_ARCHDIR/$MY_ARCHLOG ${MY_BACKSVR}:$MY_BACKDIR/$MY_ARCHLOG
    MY_CKSUMBAK=$(ssh -l ${MY_BACKSVR/@/ } "cksum < $MY_BACKDIR/$MY_ARCHLOG")
    print "$MY_CKSUM: cksum of $MY_ARCHDIR/$MY_ARCHLOG"
    print "$MY_CKSUMBAK: cksum of $MY_BACKSVR:$MY_BACKDIR/$MY_ARCHLOG"
    if [[ "$MY_CKSUM" == "$MY_CKSUMBAK" ]]; then
      (( i = 5 ))
    else
      (( i++ ))
      sleep 180
    fi
  done

  if (( i == 5 )); then
    print "OK: $MY_ARCHDIR/$MY_ARCHLOG => $MY_BACKSVR:$MY_BACKDIR/$MY_ARCHLOG"
  else
    print "ERROR: $MY_ARCHDIR/$MY_ARCHLOG => $MY_BACKSVR:$MY_BACKDIR/$MY_ARCHLOG"
    exit 1
  fi
done

MY_TIME=$(get_date)
print $MY_TIME

ssh -l ${MY_BACKSVR/@/ } "
  export ORACLE_SID=$ORACLE_SID
  awk -F: \"/^$ORACLE_SID:/ {print \\\$2; exit}\" $ORATAB | read a
  eval export ORACLE_HOME=\$a
  eval export LIBPATH=\$a/lib
  eval \$ORACLE_HOME/bin/sqlplus /nolog << EOF
    set linesize 400
    set trimspool on
    connect / as sysdba
    recover standby database until cancel;
    auto
EOF
"

exec 1>&-
exec 2>&-
exec 1>&3
exec 2>&4
exec 1>> $MY_LOGFILE 2>&1

tot= ok= bad= unset tot ok bad
(( tot = 0 )); (( ok = 0 )); (( bad = 0 ))
for (( MY_SEQ = MY_DONE_SEQ + 1; MY_SEQ <= MY_HIGH_SEQ; MY_SEQ++ )); do
  MY_ARCHLOG=$(format_to_file $MY_ARCHFMT $MY_THREAD $MY_SEQ "pattern")
  MY_ARCHLOG=$(cd $MY_ARCHDIR >/dev/null && eval ls $MY_ARCHLOG)
  (( tot++ ))
  if sed -n "/^$MY_TIME/,\$p" $MY_LOGFILE |
     grep -i "log file '.*/$MY_ARCHLOG' no longer needed"
  then
    (( ok++ ))
    print $MY_SEQ > $MY_ARCHDIR/standby.done.tmp &&
      mv $MY_ARCHDIR/standby.done.tmp $MY_ARCHDIR/standby.done
    ssh -l ${MY_BACKSVR/@/ } "rm $MY_BACKDIR/$MY_ARCHLOG"
  else
    (( bad++ ))
    print "ERROR: log file '$MY_BACKDIR/$MY_ARCHLOG' failed to apply or may have already applied."
  fi
done

print "INFO: $ok/$tot logs applied."
(( MY_STATUS = 1 ))
(( bad == 0 && tot == ok + bad )) && (( MY_STATUS = 0 ))

get_date
trap - exit
rmdir $MY_LOCKDIR
print "status=$MY_STATUS"
exec 3>&-
exec 4>&-
exec 1>&-
exec 2>&-

exit $MY_STATUS