#!/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
typeset -u ftable
typeset -u fuser
# -------------------------------------------------------------------------------------
function get_current_redo
{
  ret=`sqlplus -s "$CONNECT_STRING" <<EOF
   set feed off pagesize 0 head off lines 500
  select sequence# from v\\$log where STATUS='CURRENT';
EOF`
  echo "$ret"
}
# -------------------------------------------------------------------------------------
function get_archive_name
{
  ARCH=$1
  #cat <<EOF
  ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off lines 500
select a.name FROM V\\$ARCHIVED_LOG a,V\\$INSTANCE b where b.instance_number=a.THREAD# and sequence#=$ARCH
        and sequence# not in (select sequence# from v\\$log)
     union
     select
          (select min(member) from v\\$logfile where GROUP#= a.GROUP#) member
     from v\\$log a, v\\$INSTANCE b
            where SEQUENCE#=$ARCH and b.instance_number=a.THREAD#;
EOF`
  echo "$ret"
}
# -------------------------------------------------------------------------------------
function help 
{

  cat <<EOF

   On line Logminer Operations:  


        lgm -s <SCN> -i <n>
        lgm -l -a <ARCH NUM> -desc/asc -rn <ROW_NUM> -nc 
        lgm -t <table> [-u <user>] -a <ARCH NUM> -desc/asc -r <ROW_NUM> 

        lgm -a <ARCH_NUM> -w "<where clause>"
        lgm -a <ARCH_NUM> -xid <xid>                    # list all sql for XID
        lgm -a <ARCH_NUM> -sid <sid> [-ser <serial>]    # list all sql for session <sid> 

        lgm -tbs <TABLESPACE>

          -l   : List the contents of the selected archives, display with counts
          -s   : Give an SCN and retrieve the SQL statement
         -nc   : Don't list commits in list
        -ist   : Include rows whose operation is start         |    -sys   : Include rows generated by sys rows
          -t   : Table name                                    |    -u     : Segment owner
          -i   : force instance number                         |    -v     : Verbose
         -rn   : Show at least <ROW_NUM> rows                  |    -tbs   : Relocate logminer to <TABLESPACE>
       -desc   : Order output descending                       |    -asc   : Order output ascending
         -la   : replace -a <ARCH_NUM> by -la to mine CURRENT redo logs 

Example:
--------
   List SQL from an SCN                                    : lgm -s <SCN> [-a <arch_num>]
   List all rows for a archive log                         : lgm -l -a <nnn>  -ist
   List all rows for an object in an archive log           : lgm -o <OBJECT_NAME> -a <nnn> -u <OWNER>
   List SCN,time and SQL for table CUST in archive 1244    : lgm -t CUST -a 1244
   List all rows given a where clause                      : lgm -a <nnn> -w "where operation not in ('UPDATE','DELETE','INSERT')"

  Column you can include in the where clause are any columns of V\$LOGMNR_CONTENTS  

        lgm -a 11833 -rn 4000  -w "where operation in ('DELETE','INSERT') and username like 'BPA%'"
        lgm -la -sid <137> to view all entries of session 137 into current redo log

Logminer in table:    
------------------
     Create and add logfile to logminer :  

                   lgm -cr  [-a <arch_num> -a ..... ].  Creates smenu_tt in tbs sysaux

         index on: scn, XID, SID SERIAL#, USERNAME, seg_name


          lgm -tt -u <owner>                   # List all statement for <owner> from table smenu_tt
EOF
exit

}
# -------------------------------------------------------------------------------------
function do_execute
{
LLEN=`expr 55 + $LEN`
echo "MACHINE $HOST - ORACLE_SID : $ORACLE_SID "
sqlplus -s "$CONNECT_STRING" <<EOF
column nline newline
set pagesize 66  linesize 100  termout on pause off  embedded on  verify off  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 A116 head "SQL"
col son format A45 head 'Object Owner and Name'
col scn format 99999999999999 head 'Scn' justify c
col fdate format A22 head 'Operation time' justify c
col operation format A16 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
EXECUTE=YES
SYS=" username != 'SYS' and "
START=" OPERATION != 'START' and "
while [ -n "$1" ]
do
  case "$1" in
       -a ) ARCH_NUM=$2 ; ARCH_LIST="$ARCH_LIST $ARCH_NUM";shift ;;
     -asc ) DESC="ASC" ;;
      -cr ) CHOICE=TBL;; 
      -cpt ) CHOICE=TBL; COUNT=TRUE;; 
      -dr ) CHOICE=DROP_TBL;; 
      -tt ) SMENU_TT=TRUE;;           # in  the if else clause, must be before the others
    -desc ) DESC="DESC" ;;
       -h ) help; exit ;;
     -len ) LEN=$2; shift ;;
       -i ) INST=$2 ;shift ;;
       -l ) TTITLE="List contents and counts of an archvielog"; EXECUTE=YES; CHOICE=LIST  ;;
      -la ) GET_LAST=TRUE ;;
     -ist ) INCLUDE_START="TRUE" ;;                    # include sys
      -nc ) EXCLUDE_COMMIT=" OPERATION != 'COMMIT' and " ;; 
      -rn ) ROWNUM=$2 ; shift ;;
       -s ) CHOICE=SCN; TTITLE="Show SQL command for a given SCN "
            EXECUTE=YES; SCN=$2 ; shift  ;;
       -t ) CHOICE=FTABLE; ftable=$2; shift ;
             TITLE="List contents and counts of an archvielog for table : $ftable";;
     -tbs ) CHOICE=TBS; TTITLE="Set tablespace for Logminer"  ; ftbs=$2; shift;;
     -sid ) CHOICE=W_CLAUSE ;  SID="$2" ; shift;;
     -ser ) SERIAL=$2 ; shift ;;
     -sys ) INCLUDE_SYS="TRUE" ;;                      # include sys
       -u ) fuser=$2; shift ;;
       -v ) VERBOSE=TRUE ;;
       -w ) CHOICE=W_CLAUSE ; shift ; W_CLAUSE="$@" ; break ;;
       -x ) EXECUTE=YES;;
     -xid ) CHOICE=W_CLAUSE ; XID="$2"; shift ; EXECUTE=YES;;
      -nx ) EXECUTE=NO;;
        * ) 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 [ -n "$GET_LAST" ];then
     ret=`get_current_redo` 
     ARCH_NUM=`echo $ret|tr -d '\n' |awk '{print $1}'`
fi
# .................................................
# Query smenu_tt
# .................................................
if [ "$SMENU_TT" = "TRUE" ];then
  
      LEN_TEXT=40 
      FIELDS="session#,serial#,username"
      FIELD1="substr(sql_redo,1,$LEN_TEXT) sql"
#seg_owner||'.'||seg_name son, operation,
      if [ -n "$SID" ];then 
           AND_SID=" and session#=$SID "
      fi
      if [ "$COUNT" = "TRUE" ] ;then
         SQL="select * from (
 select $FIELDS , count(*) count from smenu_tt group by $FIELDS order by 3 desc) 
 where ROWNUM <$ROWNUM;"
      else #default
SQL="
col xid_raw form a18
col xid form a14
col sid for 99999 head 'Sid'
col serial# for 99999 head 'Ser'
col sql for a$LEN_TEXT
set lines 190 pages 66
break on fdete on sid on serial# on scn on xid_raw on report
select * from (
      select to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') fdate,
              session# sid, serial#,xid xid_raw, to_char(XIDUSN)||'.'||to_char(XIDSLT)||'.'||to_char(XIDSQN) xid
             ,scn, $FIELD1 
       from smenu_tt  where    $EXCLUDE_COMMIT 1=1  $AND_SID 
            order by timestamp desc
)
       where rownum<=$ROWNUM ;
"
      fi
# .................................................
# Drop smenu_tt
# .................................................
elif [ "$CHOICE" = "DROP_TBL" ];then
SQL="drop table smenu_tt;"
# .................................................
# Create and populate table smenu_tt
# .................................................
elif [ "$CHOICE" = "TBL" ];then
     if [ -z "$ARCH_LIST" ];then
        echo "I need a list of archive, use -a <arch_num> -a ...... to add one or many archives"
        exit
     fi
     for i in $ARCH_LIST
     do
        unset ret
        ret=`get_archive_name $ARCH_NUM`
        if [ -n "$ret" ];then
            LST_ARCH="$LST_ARCH
execute dbms_logmnr.Add_logfile(options => dbms_logmnr.new, logfilename=>'$ret'); "
        fi
     done
   SQL=" $LST_ARCH
execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);
drop table smenu_tt;
set timing on;
create table smenu_tt tablespace sysaux as
  select THREAD# ,SCN, timestamp, SEG_OWNER, SEG_NAME, SESSION# , SERIAL# ,USERNAME,
         OPERATION, SQL_REDO, xid , XIDUSN,XIDSLT,XIDSQN
    from v\$logmnr_contents ;
create index idx_smenu_tt_xid on smenu_tt(XID) tablespace sysaux;
create index idx_smenu_tt_sid on smenu_tt(SESSION#,SERIAL#) tablespace sysaux;
create index idx_smenu_tt_usr on smenu_tt(USERNAME) tablespace sysaux;
create index idx_smenu_tt_seg on smenu_tt(SEG_NAME) tablespace sysaux;
create index idx_smenu_tt_scn on smenu_tt(scn) tablespace sysaux;"
echo "$SQL"
# .................................................
# 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
ORDER=${ORDER:-order by scn desc}             # default order by last scn
    ret=`get_archive_name $ARCH_NUM`
    # for cygwin we need to get rid of M$ garbage, hence the tr.
    name=`echo $ret | tr -d '\n'| awk '{print $1}'`
    SQL="set lines 190 pages 66
col xid for a16
execute dbms_logmnr.Add_logfile(options => dbms_logmnr.new, logfilename=>'$name');
execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);
select seg_owner,scn,xid
     ,XIDUSN||'.'||XIDSLT||'.'||XIDSQN xid 
    , cpt, -- son,
    operation, fdate from (
               select seg_owner,xid, XIDUSN,XIDSLT,XIDSQN,scn, count(scn) cpt, seg_owner||'.'||seg_name son, operation, 
                      to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') fdate 
       from V\$LOGMNR_CONTENTS  where $SYS $START  $EXCLUDE_COMMIT  1=1
            group by seg_owner, xid,
            XIDUSN,XIDSLT,XIDSQN, scn, seg_owner||'.'||seg_name , operation , 
                                      to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') $ORDER $DESC )
       where rownum<=$ROWNUM ; 
"

# .................................................
# List content of v$logmnr_contents given a where clause
# .................................................
elif [ "$CHOICE" = "W_CLAUSE" ];then
  LEN_TEXT=100
  if [ -n "$SID" ] ;then
       W_CLAUSE=" where session# = '$SID' order by timestamp "
       LEN_TEXT=90
       VAR_FIELD="SESSION#, SERIAL#,"
       VAR_FIELD_DISP="to_char(session#)||chr(10)||to_char(serial#) fsid,"
       if [ -n "$SERIAL" ];then
          W_CLAUSE=" where session# = '$SID' and serial# = '$SERIAL' order by timestamp "
       fi
       WHERE=$W_CLAUSE
  elif [ -n "$XID" ] ;then
       W_CLAUSE=" where xid = '$XID' order by timestamp "
       WHERE=$W_CLAUSE
  elif [ -z "$W_CLAUSE" ];then
       # just order it by time desc if nothing was suggested
       WHERE=" order by scn desc"
  else
       WHERE=$W_CLAUSE
  fi
  ret=`get_archive_name $ARCH_NUM`
  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);
set lines 190 pages 66
col sql_redo for a$LEN_TEXT
col opeartion for a9
col fscn for a22 head 'Scn|Schema'
col fsid for a5 head 'Sess#|ser#' justify c
break on scn on fdate on fsid report
select 
       scn||chr(10)||username fscn,  
       fdate||chr(10)|| 'type='||operation fdate, $VAR_FIELD_DISP
       sql_redo from (
               select scn,username, operation, sql_redo,  $VAR_FIELD
                      to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') fdate 
       from V\$LOGMNR_CONTENTS  $WHERE
       )where rownum<=$ROWNUM ; 
"

# .................................................
# List content of v$logmnr_contents for a given table
# .................................................
elif [ "$CHOICE" = "FTABLE" ];then
      if [ -n "$ftable" ];then
           if [ -n "$fuser" ];then
              if [ "$ftable" = '%' ];then
                 WHERE=" seg_owner='$fuser' "
              else
                 WHERE=" seg_owner='$fuser' and table_name = '$ftable'"
              fi
           else
              if [ "$ftable" = '%' ];then
                  unset WHERE
              else
                  WHERE=" table_name = '$ftable'"
              fi
           fi
      fi

      ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off lines 500
select name FROM V\\$ARCHIVED_LOG,V\\$INSTANCE b where instance_number=b.THREAD# and sequence#=$ARCH_NUM  ;
EOF`
    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);
break on scn on fdate on report
set lines 160 pages 66
col sql for a80
col opeartion for a9
select scn,  fdate||chr(10)|| 'type='||operation fdate, sql_redo from (
               select scn, operation, sql_redo,
                      to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') fdate 
       from V\$LOGMNR_CONTENTS  where $WHERE order by scn desc
       )where rownum<=$ROWNUM ; 
"

# .................................................
# 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 ;
"

#+ DBMS_LOGMNR.CONTINUOUS_MINE);

# .................................................
# stop logminer
# .................................................
elif [ "$CHOICE" = "STOP" ];then
     SQL="execute DBMS_LOGMNR.END_LOGMNR( ) ;"
fi

if [ -n "$VERBOSE" ];then
    echo "$SQL"
fi
if [ "$EXECUTE" = "YES" ];then
   do_execute
else
  echo "$SQL"
  echo "--> (Add '-x' to your command to execute it)"
fi