#!/bin/ksh
#
# Creates and optionally runs an analyze script for (archived) redo log files
#
# AUTHOR: M. Wunderli, Trivadis AG, Kanalstrasse 5, 8152 Glattbrugg
#
# VERSION: 1.1, 1999-09-30, MaW
#          1.2, 1999-10-31, MaW, added support for ranges of archive files
#          1.3, 1999-11-04, MaW, ORACLE_HOME for sequence SID is automatically
#                                determined
#
# BUGS:
# - none, only features ;-)
#

Usage () {
  cat << _EOI

Usage: `basename $0` <OPTIONS> <FILES>

       OPTIONS:
       -p <pwd>: Password for 'internal' user
       -s <SID-from-to> e.g. -s T805A-110-120
          sequence of redo logs of database SID to be analyzed
       -t <Thread>: Thread (default 1)
       -o <AnalyzeFile> (default /tmp/analyze_redo_PROCESSID.sql)
       -d <DictionaryFile>
       -q: Generate simple query to be included in the analyze script
       -l: Add result spooling to the simple query
           (only has an effect in conjunction with -q)
       -r: Run the generated SQL Script immediately
       -c: Cleanup temporary files after running 
           (only has an effect in conjunction with -r)
       <FILES>: archive files to be analyzed in addition to the range above

ORACLE_HOME and ORACLE_SID of the analyzing SID must be set,
NOT the ORACLE_HOME of the analyzed SID! The ORACLE_HOME of the analyzed SID
is determined from the oratab file.

The analyzing database and the database whose archived redo logs are
to be analyzed must be on the same host.

The password for the internal user may be passed as a parameter
but is not necessary if the executing user is of group dba.

_EOI
  exit 1
}

Quit () {
  echo $1
  exit 1
}

# Evaluate parameters
Analyze_ORACLE_HOME=$ORACLE_HOME
DictFile=""
AnalyzeFile=/tmp/analyze_redo_$$.sql
Analyze_ORACLE_SID=""
InternalConnect=internal
runSQL=0
genListCmd=0
spoolResults=0
cleanup=0
thread=1

while getopts p:s:t:d:o:rcql Option
do
  case ${Option} in
     p) InternalConnect="internal/""${OPTARG}"
        ;;
     s) Analyze_ORACLE_SID="`echo "${OPTARG}" | awk -F- '{print $1}'`"
        startRange="`echo "${OPTARG}" | awk -F- '{print $2}'`"
        endRange="`echo "${OPTARG}" | awk -F- '{print $3}'`"
        ;;
     t) thread="${OPTARG}"
        ;;
     d) DictFile="${OPTARG}"
        ;;
     o) AnalyzeFile=`basename "${OPTARG}" .sql`.sql
        ;;
     r) runSQL=1
        ;;
     c) cleanup=1
        ;;
     q) genListCmd=1
        ;;
     l) spoolResults=1
        ;;
     ?) Usage
        ;;
  esac
done
shift $((${OPTIND}-1))

if [ "$ORACLE_SID" = "" ]
then
  Quit "ORACLE_SID not set. Cannot run the script." 
fi

if [ "$ORACLE_HOME" = "" ]
then
  Quit "ORACLE_HOME not set. Cannot run the script." 
fi

if [ "$Analyze_ORACLE_SID" != "" ]
then
  if [ "`uname -s`" = "Solaris" ]
  then
    oratab="/var/opt/oracle/oratab"
  else
    oratab="/etc/oratab"
  fi
  if [ ! -r $oratab ]
  then
    Quit "Cannot read $oratab to get ORACLE_HOME!"
  fi
  Analyze_ORACLE_HOME="`grep "^${Analyze_ORACLE_SID}:" $oratab | \
                        awk -F: '{print $2}'"

  Save_ORACLE_SID=$ORACLE_SID
  export ORACLE_SID=$Analyze_ORACLE_SID
  Save_LD_LIBRARY_PATH=$LD_LIBRARY_PATH
  export LD_LIBRARY_PATH=$Analyze_ORACLE_HOME/lib:$LD_LIBRARY_PATH
  Save_ORACLE_HOME=$ORACLE_HOME
  export ORACLE_HOME=$Analyze_ORACLE_HOME

  log_archive_dest="`$Analyze_ORACLE_HOME/bin/svrmgrl << _EOI | grep -v grep \
                    | grep 'log_archive_dest ' | awk '{print $2}'
connect $InternalConnect
select name,value from v\\\$parameter where name = 'log_archive_dest';
_EOI
`"

  log_archive_format="`$Analyze_ORACLE_HOME/bin/svrmgrl << _EOI | grep -v grep \
                      | grep 'log_archive_format ' | awk '{print $2}'
connect $InternalConnect
select name,value from v\\\$parameter where name = 'log_archive_format';
_EOI
`"

  log_file=$log_archive_dest$log_archive_format
  export ORACLE_SID=$Save_ORACLE_SID
  export LD_LIBRARY_PATH=$Save_LD_LIBRARY_PATH
  export ORACLE_HOME=$Save_ORACLE_HOME

  sequence="$startRange"
  while [ "$sequence" -le "$endRange" ]
  do
    file=`echo $log_file | sed "s/%t/$thread/" | sed "s/%s/$sequence/"`
    if [ ! -f $file ]
    then
      Quit "Redo log $file does not exist"
    fi
    let sequence=sequence+1
    set $* $file
  done
fi 

if [ $# -eq 0 ]
then
  Usage
fi

for file in $* 
do
  # this may not be necessary for the files generated above, but who cares...
  if [ ! -f $file ]
  then
    Quit "Redo log $file does not exist"
  fi
done

# Start the analysis file generation
cp /dev/null $AnalyzeFile
result=$?
if [ $result -ne 0 ]
then
  Quit "Output File $AnalyzeFile could not be initialized!"
fi

echo "begin" >> $AnalyzeFile
if [ "`echo $file | cut -c1`" != "/" ]
then
  file=`pwd`/$1
else
  file=$1
fi
echo "\
  dbms_logmnr.add_logfile(Options => dbms_logmnr.NEW,
                          LogFileName => '$file');" >> $AnalyzeFile
shift

if [ $# -gt 0 ]
then
  for file in $*
  do
    if [ "`echo $file | cut -c1`" != "/" ]
    then
      file=`pwd`/$file
    fi
    echo "\
  dbms_logmnr.add_logfile(Options => dbms_logmnr.ADDFILE,
                          LogFileName => '$file');" >> $AnalyzeFile
  done
fi

if [ "$DictFile" = "" ]
then
  echo "  dbms_logmnr.start_logmnr();" >> $AnalyzeFile
else
  echo "  dbms_logmnr.start_logmnr(DictFileName => '$DictFile');" >> $AnalyzeFile
fi
echo "end;" >> $AnalyzeFile
echo "/" >> $AnalyzeFile


# Should we generate a command to list the results from v$logmnr_contents?
if [ $genListCmd -eq 1 ]
then
  echo "\
set linesize 80
col username form a10
col operation form a10
col sql_redo form a80 newline
col sql_undo form a80 newline
set pagesize 0
set feedback off" >> $AnalyzeFile
 
  # After selecting the results, should we spool it to a file? 
  if [ $spoolResults -eq 1 ]
  then
    AnalyzeLogFile=`dirname $AnalyzeFile`/`basename $AnalyzeFile .sql`.lis
    if [ "`echo $AnalyzeLogFile | cut -c1`" != "/" ]
    then
      AnalyzeLogFile=`pwd`/$AnalyzeLogFile
    else
      AnalyzeLogFile=$AnalyzeLogFile
    fi
    echo "spool $AnalyzeLogFile" >> $AnalyzeFile
  fi

  echo "\
select to_char(timestamp, 'HH24:MI:SS'),scn,username,operation,sql_redo,sql_undo \
from v\$logmnr_contents;" >> $AnalyzeFile

fi

if [ $runSQL -eq 1 ]
then
  echo "exit" >> $AnalyzeFile
  echo "Just a moment please..."
  sqlplus /nolog << _EOI
connect $InternalConnect
@$AnalyzeFile 
_EOI
  if [ $cleanup -eq 1 ]
  then
    rm $AnalyzeFile
  fi
fi