#!/usr/bin/ksh
#  set -xv
# author  : B. Polarski
# program : smenu_logminer.ksh
# date    : 22 Decembre 2005

HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
LEN=70
ROWNUM=40
# -------------------------------------------------------------------------------------
function help 
{

  cat <<EOF

        lgm -tbs <TABLESPACE>
        lgm -s <SCN> -i <n>
        lgm -l -a <ARCH NUM> -desc/asc -r <ROW_NUM> -nc

          -r   : Show at least <ROW_NUM> rows
       -desc   : Order output descending
        -asc   : Order output ascending
          -l   : List the contents of the selected archives, display with counts
        -tbs   : Rellocate logmine to <TABLESPACE>
          -s   : Given an SCN and retrieve the SQL statement
      -start   : Start logminer and add all the configured redo log 
         -nc   : Don't list commits in list
        -ist   : Include rows whose operation is start
        -sys   : Include rows generated by sys rows
         -i    : force instance number


   List all rows for a archive log               : lgm -l -a <nnn>
   List all rows for an object in an archive log : lgm -o <OBJECT_NAME> -a <nnn> -u <OWNER>

EOF
exit
}
# -------------------------------------------------------------------------------------
function do_execute
{
LLEN=`expr 55 + $LEN`
$SETXV
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66
set linesize 100
set termout on pause off
set embedded on
set verify off
set heading off

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline, '(help: lgm -h) $TTITLE ' nline
from sys.dual
/
set head on
$BREAK
set linesize $LLEN pagesize 30 long 500
col table_name format A18 head "Table name"
col sql_redo format A124 head "SQL"
col son format A45 head 'Object Owner and Name'
col scn format 999999999999 head 'Scn' justify c
col fdate format A22 head 'Oeration time' justify c
col operation format A18 head 'Operation' justify c
col cpt format 99999999 head "Nbr rows|in SCN"
prompt 
$SQL
EOF
}
# -------------------------------------------------------------------------------------
#                    Main
# -------------------------------------------------------------------------------------
if [ -z "$1" ];then 
   help; exit
fi

# ............ some default values and settings: .................
typeset -u ftbs
if [ -f $SBIN/data/stream_$ORACLE_SID.txt ];then
   STRMADMIN=`cat $SBIN/data/stream_$ORACLE_SID.txt | grep STRMADMIN=| cut -f2 -d=`
   STR_PASS=`cat $SBIN/data/stream_$ORACLE_SID.txt | grep STR_PASS=| cut -f2 -d=`
   DEF_SID=`cat $SBIN/data/stream_$ORACLE_SID.txt | grep DEF_SID=| cut -f2 -d=`
   DEST_QUEUE_NAME=$STRMADMIN.STREAMS_QUEUE@$DEST_DB
fi
STRMADMIN=${STRMADMIN:-STRMADMIN}
STRMADMIN_PASS=${STR_PASS:-STRMADMIN}
EXECUTE=NO
INCLUDE_DML=TRUE
INCLUDE_DDL=FALSE
SRC_DB=$ORACLE_SID
DEST_DB=$DEF_SID
QUEUE_NAME=$STRMADMIN.STREAMS_QUEUE
SRC_QUEUE_NAME=$STRMADMIN.STREAMS_QUEUE
DEST_QUEUE_NAME=${DEST_QUEUE_NAME:-STRMADMIN.STREAMS_QUEUE}
SYS=" username != 'SYS' and "
START=" OPERATION != 'START' and "

while [ -n "$1" ]
do
  case "$1" in
       -s ) CHOICE=SCN; TTITLE="Show SQL command for a given SCN "
            EXECUTE=YES; SCN=$2 ; shift  ;;
     -desc) DESC="DESC" ;;
      -asc) DESC="ASC" ;;
     -start )  CHOICE=START; TTITLE="start Logminer" ;;
     -tbs )  CHOICE=TBS; TTITLE="Set tablespace for Logminer" 
            ftbs=$2; shift;;
       -v ) SETXV="set -xv";;
       -a ) ARCH_NUM=$2 ; shift ;;
       -l ) TTITLE="List contents and counts of an archvielog" 
            EXECUTE=YES ; CHOICE=LIST  ;;
       -x ) EXECUTE=YES;;
     -sys ) INCLUDE_SYS="TRUE" ;;                      # include sys
     -ist ) INCLUDE_START="TRUE" ;;                    # include sys
      -nc ) EXCLUDE_COMMIT=" OPERATION != 'COMMIT' and " ;; 
       -r ) ROWNUM=$2 ; shift ;;
     -len ) LEN=$2; shift ;;
       -i ) INST=$2 ;shift ;;
       -h ) help ; exit ;;
        * ) echo "Invalid argument $1"
            help ;;
 esac
 shift
done

if [ "$INCLUDE_SYS" = "TRUE" ];then
   unset SYS
fi
if [ "$INCLUDE_START" = "TRUE" ];then
   unset START
fi
if [ -n "$DESC" ];then
    ORDER=" order by scn "
fi
S_USER=SYS
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi

if [ "$CHOICE" = "START" ];then
     SQL="EXECUTE DBMS_LOGMNR.start_logmnr();"
# .................................................
# Set logminer tablespace
# .................................................
elif [ "$CHOICE" = "TBS" ];then
     SQL="EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('$ftbs');"

# .................................................
# List contents of an archive log
# .................................................
elif [ "$CHOICE" = "LIST" ];then
      ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select name FROM V\\$ARCHIVED_LOG,V\\$INSTANCE b where instance_number=b.THREAD# and sequence#=$ARCH_NUM  ;
EOF`
    # for cygwin we need to get rid of M$ garbage, hence the tr.
    name=`echo $ret | tr -d '\n'| awk '{print $1}'`
    SQL="execute dbms_logmnr.Add_logfile(options => dbms_logmnr.new, logfilename=>'$name');
execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);
select scn, cpt, son, operation, fdate from (
               select scn, count(scn) cpt, seg_owner||'.'||seg_name son, operation, 
                      to_char(timestamp,'DD-MM-YYYY HH24:MI:SS') fdate 
       from V\$LOGMNR_CONTENTS  where $SYS $START  $EXCLUDE_COMMIT  1=1
            group by scn, seg_owner||'.'||seg_name , operation , 
                                      to_char(timestamp,'DD-MM-YYYY HH24:MI:SS') $ORDER $DESC )
       where rownum<=$ROWNUM ; 
"
    echo "$SQL"

# .................................................
# Show SQL of SCN taken from logminer
# .................................................
elif [ "$CHOICE" = "SCN" ];then
      if [ -n "$INST" ];then
         AND_INST=" a.thread#=$INST and "
      fi
      ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off lines 190
select name FROM V\\$ARCHIVED_LOG a,V\\$INSTANCE b where  $AND_INST
      instance_number=b.THREAD# and $SCN >= first_change#  and $SCN < next_change# ;
EOF`
echo "ret=$ret"
    # for cygwin we need to get rid of M$ garbage, hence the tr.
    name=`echo $ret | tr -d '\n'| awk '{print $1}'`
echo "name=$name"
    SQL="execute dbms_logmnr.Add_logfile(options => dbms_logmnr.new, logfilename=>'$name');
execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog  );
set pagesize 100
SELECT sql_redo FROM V\$LOGMNR_CONTENTS where scn = $SCN ;
"
echo "$SQL"
#+ DBMS_LOGMNR.CONTINUOUS_MINE);

# .................................................
# stop logminer
# .................................................
elif [ "$CHOICE" = "STOP" ];then
     SQL="execute DBMS_LOGMNR.END_LOGMNR( ) ;"
fi
if [ "$EXECUTE" = "YES" ];then
   do_execute
else
  echo "$SQL"
fi
# -- create directory LGM_TMP_DIR as '$SBIN/tmp' ;
    #-- stm:='dbms_logmnr.start_logmnr(dictfilename=>''$SBIN/tmp/dico.ora'',starttime => '||ft||' , endtime =>'|| nt||')' ;