#!/usr/bin/ksh
#  set -xv
# author  : B. Polarski
# program : smenu_stream_capture.ksh
# date    : 15 Decembre 2005
#           09 October  2007 Added -lck -lrp options
#           17 October  2007 Added -lr
#           22 November 2007 added -reset, -ckf, -chk, -cpt

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

  cat <<EOF

    cap -cn <CAPTURE_NAME> -u <OWNER> -l -qn <QUEUE_NAME> -rs <RULE SET>  -strmadmin <STRMADMIN>
        -start/stop <CAPTURE> 
        -ti -si -min_si -abort -so <source TABLE_OWNE> -t <table> 
        -la [ -id <nn>] -cpt -lstb
        -lr -lrp -prm
        -par <nn> -trace <127|0> -cn <CAPTURE_NAME> -ret <nn>  -ckf -chk <nn>
        -reset <scn> -cn <CAPTURE_NAME>
        -get_curr_scn -v


        -ab : Abort instantiation                                      -prm : List capture parameters
        -cn : Capture name                                              -qn : Queue name
    -create : Create a capture process using DBMS_CAPTURE_ADM           -rs : Rule set name
       -chk : Set Check point frequency for capture process            -ret : change checkpoint retention time
      -drop : Drop a capture process                                     -s : Show capture streams execution server stats
         -i : Tables prepared for instantiation                        -sga : Set SGA_SIZE for logminser session
         -l : List capture process                                      -si : Prepare schema instantiation
        -la : List min mandatory present on disk archive                -so : source Owner of the table
              for logminer and the restart capture scn          -start/stop : Start or stop capture process
        -lr : List rule associate with the capture                       -t : table name
       -lrp : List archived registered for purge                        -ti : Prepare table instantiation
       -lck : List required checkpoint scn                           -trace : Set trace on a capture, 127 to trace, 0 to trace off
    -min_si : show lowest Prepared scn                                   -u : Owner of the table or capture process
       -par : Set parallelism of capture process                       -cpt : Count rows in system.logmnr_restart_ckpt\$
        -fk : force a stream checkpoint (_CHECKPOINT_FORCE)          -reset : Reset the capture scn to <SCN>. last applied SCN used if SCN not given
      -rcfg : Remove streams configuration                              -lg : List logminers sessions
       -gm  : Count rows from system.logmnr data dictionary           -pckp : Purge Restart_logmnr_ckpt\$
      -lstb : List archives with build in dict above first_scn       -build : Export the data dictionary to redo (dbms_capturea_adm.build)
      -ses  : List logminer sessions and the capture it is attached     -id : Logminer id
      -shrk : Shrink and analyse table system.logmnr_restart_ckpt\$      -v : Verbose

          -get_curr_scn : show the current scn. to be used to initialize remote apply process
          -strmadmin    : Use the user in argument to perform the operation

    Stream administrator admin and his password can be deduced by smenu if you defined one for this instance
    in SM/3.8 ortherwise it will try to default to STRMADMIN/STRMADMIN



   To Create a capture process : cap -create -cn <CAPTURE_NAME> -qn <QUEUE_NAME> -u <QUEUE OWNER> -rs <RULE_SET_NAME>
   To start/stop               : cap -start <Capture name>         cap -stop <Capture name>
   To abort a capture process  : cap -abort -so <OWNER>
   Advance First SCN to default: cap -rest

   To set parallelism capture  : cap -par <nn> -cn <CAPTURE_NAME>
   To set trace on  capture    : cap -trace 127 -cn <CAPTURE_NAME>   off :  cap -trace 0 -cn <CAPTURE_NAME>
   To set checkpoint frequency : cap -chk <nn> -cn <CAPTURE_NAME>
   To SGA of logminer          : cap -sga <nn> -cn <CAPTURE_NAME>     # n is expressed in bytes
   List min SCN requiered  for capture : cap -la    or      cap -la -id <n> if mutliple capture process are present

 Do one of the 2 below then retrieve an SCN with -get_curr_scn and perform on remote  'app -si <SCN from get_curr_scn>'
   prepare schema instantiation:  cap -si -so <OWNER>  
   Prepare table instantiation :  cap -ti -so <table_owner> -t <TABLE> -u <strmadmin> 
   

EOF

exit
}
# -------------------------------------------------------------------------------------
function do_execute
{
$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, '$TTITLE (cap -h for help)' nline
from sys.dual
/
set head on
col rsn format A28 head "Rule Set name"
col rn format A30 head "Rule name"
col rt format A64 head "Rule text"
COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10
COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10
col CHECKPOINT_RETENTION_TIME head "Checkpoint|Retention|time" justify c
col LAST_ENQUEUED_SCN for 999999999999 head "Last scn|enqueued" justify c
col las format 999999999999 head "Last remote|confirmed|scn Applied" justify c
col REQUIRED_CHECKPOINT_SCN for 999999999999 head "Checkpoint|Require scn" justify c
col nam format A31 head 'Queue Owner and Name'
col capture_type format A10 head 'Capture |Type'
col RULE_SET_NAME format a15 head "Rule set Name"
col NEGATIVE_RULE_SET_NAME format a15 head "Neg rule set"
col table_name format A30 head 'table Name'
col queue_owner format A20 head 'Queue owner'
col capture_user format A20 head 'Capture user'
col table_owner format A20 head 'table Owner'
col rsname format A34 head 'Rule set name'
col cap format A22 head 'Capture name'
col ti format A22 head 'Date'
col lct format A18 head 'Last|Capture time' justify c
col cmct format A18 head 'Capture|Create time' justify c
col emct format A18 head 'Last enqueued|Message creation|Time' justify c
col ltme format A18 head 'Last message|Enqueue time' justify c
col ect format 999999999 head 'Elapsed|capture|Time' justify c
col eet format 9999999 head 'Elapsed|Enqueue|Time' justify c
col elt format 9999999 head 'Elapsed|LCR|Time' justify c
col tme format 999999999999 head 'Total|Message|Enqueued' justify c
col tmc format 999999999999 head 'Total|Message|Captured' justify c
col status format A8 head 'Status'
col scn format 999999999999 head 'Scn' justify c
col emn format 999999999999 head 'Enqueued|Message|Number' justify c
col cmn format 999999999999 head 'Captured|Message|Number' justify c
col lcs format 999999999999 head 'Last scn|Scanned' justify c
col ncs format 999999999999 head 'Captured|Start scn' justify c
col AVAILABLE_MESSAGE_NUMBER format 999999999999 head 'Last system| scn'  justify c
$BREAK
set linesize 132
$SQL
EOF
}
# -------------------------------------------------------------------------------------
#                    Main
# -------------------------------------------------------------------------------------
if [ -z "$1" ];then 
   help; exit
fi

# ............ some default values and settings: .................
typeset -u ftable
typeset -u fdblk
typeset -u fcapture
typeset -u fsource_owner
typeset -u fowner
typeset -u frsname
typeset -u fqueue
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}

while [ -n "$1" ]
do
  case "$1" in
   -abort ) TTITLE="ABORT instantiation" ; ACTION=ABORT ; CHOICE=ABORT_INSTANTIATE ;;
   -build ) CHOICE=BUILD ;;
     -chk ) TTITLE="Set checkpoint frequency " ; check_freq=$2 ; shift ; CHOICE=CHECK_FREQ ;;
      -cn ) fcapture=$2 ; shift ;;
  -create ) CHOICE=CREATE ;;
     -cpt ) EXECUTE=YES; CHOICE=COUNT; TTITLE="Count rows in system.logmnr_restart_ckpt\$ ";;
    -shrk ) EXECUTE=YES; CHOICE=SHRINK; TTITLE="Shrink and analyse table system.logmnr_restart_ckpt\$";;
      -cs ) cs=$2; shift ;;
    -dblk ) fdblk=$2; shift ;;
    -drop ) CHOICE=START_STOP_DROP ; fcapture=$2 ; shift ; ACTION=DROP;;
  -get_curr_scn  ) CHOICE=GET_CURR_SCN ; EXECUTE=YES;;
       -i ) TTITLE="Table Prepared for instantiation" ; EXECUTE=YES ; CHOICE=TBL_INS ;;
       -l ) EXECUTE=YES ; TTITLE="List capture process" ; CHOICE=LIST ;;
      -la ) CHOICE=LOGMINER; TTITLE="List minimum requiered archived log"  ; EXECUTE=YES;;
     -lck ) CHOICE=LIST_LCK; TITTLE="List Checkpoints" ; EXECUTE=YES ;;
      -lr ) CHOICE=CAP_RUL; TTITLE="List capture rule "  ; EXECUTE=YES;;
      -lg ) CHOICE=LIST_LG; TTITLE="List logminers sessions "  ; EXECUTE=YES;;
     -lrp ) CHOICE=REGISTERED; TTITLE="List archived log purge status"  ; EXECUTE=YES;;
    -lstb ) CHOICE=LSTB ; EXECUTE=YES ;;
  -min_si ) TTITLE="Show lowest prepared SCN" ; CHOICE=MIN_SI ; EXECUTE=YES ;;
     -par ) TTITLE="Set parallel capture " ; PAR=parallelism ; value=$2 ; shift ; CHOICE=SET_PAR ;;
     -fk ) TTITLE="Set _CHECKPOINT_FORCE capture " ; PAR=_CHECKPOINT_FORCE ; value="'Y'" ; fcapture=$2;shift ; CHOICE=SET_PAR ;;
     -ret ) TTITLE="Set  checkpoint retention time " ; value=$2 ; shift ; CHOICE=SET_RET ;;
     -prm ) CHOICE=PARAMETERS; TITTLE="List Capture parameters"; EXECUTE=YES;;
      -qn ) fqueue=$2 ; shift ;;
   -reset ) CHOICE=RESET_SCN ; 
            if [ -n "$2" ];then
                 SCN=$2; shift
            fi ;;
      -rs ) frsname=$2 ; shift ;;
    -rcfg ) CHOICE=RCFG  ;;
      -gm ) CHOICE=GM ; EXECUTE=YES ;;
    -pckp ) CHOICE=PURGE_CKP ;;
       -s ) TTITLE="List execute capture server process stats" ; EXECUTE=YES ; CHOICE=CAPTURE_SERVER ;;
     -sga ) TTITLE="Set SGA_SIZE for logminer " ; sga_size=$2 ; shift ; CHOICE=SET_SGA ;;
     -ses ) TTITLE="List logminer sessions and the capture it is attached" ; CHOICE=LIST_SES ; EXECUTE=YES;;
      -si ) TTITLE="Prepare schema instantiation"; CHOICE=PREPARE ;;
      -so ) fsource_owner=$2 ; shift ;;
      -id ) LOGMNR_ID=$2; shift ;;
 -src_sid ) src_sid=$2 ; shift ;;
   -start ) TITTLE="Starting capture $fcapture"; CHOICE=START_STOP_DROP ; fcapture=$2 ; shift ; ACTION=START ;;
    -stop ) TITTLE="Stopping capture $fcapture";CHOICE=START_STOP_DROP ; fcapture=$2 ; shift ; ACTION=STOP ;;
       -t ) ftable=$2; shift ;;
      -ti ) TTITLE="Prepare table instantiation" ; CHOICE=PREPARE ;;
   -trace ) TTITLE="Set trace level " ; trace_level=$2 ; shift ; CHOICE=TRACE ;;
       -u ) fowner=$2 ; shift ;;
       -v ) SETXV="set -xv";;
-strmadmin ) STRMADMIN=$2; S_USER=$STRMADMIN; shift ;;
       -x ) EXECUTE=YES;;
        * ) echo "Invalid argument $1"
            help ;;
 esac
 shift
done
if [  "$CHOICE" = "CREATE" -o "$CHOICE" = "PREPARE" ];then
   export S_USER=$fowner
fi
if [ -z "$cs" ];then
. $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
else
  CONNECT_STRING=$cs
fi
# ......................................
# List Logminser sessions
# ......................................
if [ "$CHOICE" = "LIST_SES" ];then
SQL="col SESSION_NAME format a20
select SESSION_NAME, SESSION# , CLIENT#, START_SCN, END_SCN , SPILL_SCN,SPILL_TIME,OLDEST_SCN,RESUME_SCN
    from system.LOGMNR_SESSION\$
/"
# ......................................
# Export the dictionary to redo
# ......................................
elif [ "$CHOICE" = "BUILD" ];then
SQL="set serveroutput on 
variable f_scn number; 
begin 
:f_scn := 0; 
dbms_capture_adm.build(:f_scn); 
dbms_output.put_line('the first_scn value is ' || :f_scn); 
end; 
/
"
# ......................................
# List archives with built in dictionary above the first SCN
# ......................................

elif [ "$CHOICE" = "LSTB" ];then
SQL="prompt The following archives is suitable new first_scn for capture. If name is empty then archive is not more on disk
prompt
col first_change# head SCN
col name format a80
set lines 190 
select sequence#, first_change#,FIRST_TIME, name from v\$archived_log 
    where DICTIONARY_BEGIN =  'YES' and first_change# > (select min(first_scn) from dba_capture) 
    order by FIRST_TIME desc ;
"
# ......................................
# Purge Logminer CKPT
# ......................................

elif [ "$CHOICE" = "PURGE_CKP" ];then

   if [ -z "$fcapture" ];then
        var=`sqlplus -s "$CONNECT_STRING" <<EOF
set head off pagesize 0 
select count(*) cpt from dba_capture;
EOF`
        cpt=`echo $var|awk '{print $1}'`
        if [ "$cpt" = "1" ];then
           fcapture=`sqlplus -s "$CONNECT_STRING" <<EOF
set head off pagesize 0 
select capture_name from dba_capture;
EOF`
         else
           echo "You need to add -cn <capture_name>"
           exit
        fi
   fi
   
SQL="set serveroutput on size unlimited
  prompt Before : 
  prompt
  set head on
  select /*+ index_ffs(a LOGMNR_RESTART_CKPT\$_PK) */ count(*) from system.LOGMNR_RESTART_CKPT\$ a ;
DECLARE
 hScn number := 0;
 lScn number := 0;
 sScn number;
 ascn number;
 alog varchar2(1000);
 v_session number;
begin
  select min(start_scn), min(applied_scn) into sScn, ascn from dba_capture where capture_name = '$fcapture' ;
  select logminer_id into v_session from  dba_capture where capture_name = '$fcapture' ;
  -- DBMS_OUTPUT.ENABLE(2000);
  for cr in (select distinct(a.ckpt_scn) from system.logmnr_restart_ckpt\$ a
                    where a.ckpt_scn <= ascn and a.valid = 1 and session# = v_session 
                      and exists (select * from system.logmnr_log\$ l where a.ckpt_scn between l.first_change# and l.next_change#) order by a.ckpt_scn desc)
  loop
    if (hScn = 0) then
       hScn := cr.ckpt_scn;
    else
       lScn := cr.ckpt_scn;
       exit;
    end if;
  end loop;

  if lScn = 0 then
    lScn := sScn;
  end if;
  dbms_output.put_line('dbms_capture_adm.alter_capture( capture_name => ''$fcapture'',first_scn=> ' ||to_char(lScn)||');') ;
  dbms_capture_adm.alter_capture( capture_name => '$fcapture',first_scn=> lScn );
end;
/
  prompt After : 
  prompt
  set head on
  select /*+ index_ffs(a LOGMNR_RESTART_CKPT\$_PK) */ count(*) from system.LOGMNR_RESTART_CKPT\$ a ;
  prompt
"
# ......................................
# List logminer tables contents
# ......................................

elif [ "$CHOICE" = "GM" ];then
SQL="
set serveroutput on size unlimited
declare

var number:=0 ;
begin

for t in (select table_name from dba_tables where owner = 'SYSTEM' and table_name like 'LOGM%'order by table_name)
loop
   execute immediate('select count(*) from system.' || t.table_name) into var ;
   dbms_output.put_line(t.table_name ||':' || to_char(var));
end loop ;
end ;
/
"
# ......................................
# List logminer sessions
# ......................................

elif [ "$CHOICE" = "LIST_LG" ];then
SQL="col ROLE format a16 
col SESSION_ID format 99999 head 'Logmr|id' justify c
col WORK_MICROSEC format 9999990.99 head 'Work(sec)'
col OVERHEAD_MICROSEC format 9999990.99 head 'Overead (sec)'
set lines 190
select SESSION_ID,ROLE,SID,spid,LATCHWAIT,LATCHSPIN,WORK_MICROSEC/1000000,OVERHEAD_MICROSEC/1000000 from  V\$LOGMNR_PROCESS;
"
# ......................................
# Remove streams configuration
# ......................................

elif [ "$CHOICE" = "RCFG" ];then
   if $SBIN/scripts/yesno.sh "to remove streams configuration"
   then
     EXECUTE=YES
     SQL="prompt Doing exec dbms_streams_adm.remove_streams_configuration;;
exec dbms_streams_adm.remove_streams_configuration;"
   else
    SQL="prompt cancelled"
fi

# ......................................
#  Shrink and analyse table system.logmnr_restart_ckpt\$
# ......................................

elif [ "$CHOICE" = "SHRINK" ];then
$SBIN/module2/s4/smenu_desc_table.ksh -u system -t logmnr_restart_ckpt\$
sqlplus -s "$CONNECT_STRING" <<EOF
set time on
prompt After purge of table, do :
prompt
prompt alter table system.LOGMNR_RESTART_CKPT\$ enable row movement ;; 
alter table system.LOGMNR_RESTART_CKPT\$ enable row movement ;

prompt alter table system.LOGMNR_RESTART_CKPT\$ shrink space ;; 
alter table system.LOGMNR_RESTART_CKPT\$ shrink space ;

prompt alter table system.LOGMNR_RESTART_CKPT\$ disable row movement ;; 
alter table system.LOGMNR_RESTART_CKPT\$ disable row movement ;
EOF
echo "Start of analysis"
$SBIN/module3/s6/smenu_gather_stat_tbl.ksh -u system -t logmnr_restart_ckpt\$ -c -x
$SBIN/module2/s4/smenu_desc_table.ksh -u system -t logmnr_restart_ckpt\$

# ......................................
# Count rows in system.logmnr_restart_ckpt$ 
# ......................................

elif [ "$CHOICE" = "COUNT" ];then
$SBIN/module2/s4/smenu_desc_table.ksh -u system -t logmnr_restart_ckpt\$
sqlplus -s "$CONNECT_STRING" <<EOF
prompt After purge of table, do :
prompt alter table system.LOGMNR_RESTART_CKPT\$ enable row movement ;; 
prompt alter table system.LOGMNR_RESTART_CKPT\$ shrink space ;; 
prompt alter table system.LOGMNR_RESTART_CKPT\$ disable row movement ;; 

set linesize 190 pagesize 66 pause off feed off head on
col archive format a14 head 'Thread and |archive num'
 select distinct  thread#,min_scn, scn_date, total_rows, to_char(THREAD#) ||'_'||to_char(SEQUENCE#) archive
         from
           gv\$archived_log a,
   (
select min_scn, scn_date, total_rows from (
     select min_scn,
       (select to_char(scn_to_timestamp(min_scn),'HH24:MI:SS DD-MM-YY') from dual) scn_date ,
       (select count(*)  from system.logmnr_restart_ckpt\$) total_rows
     from (
        select min(CKPT_SCN) min_scn from system.logmnr_restart_ckpt\$ where valid=1)
    )
    ) b
where min_scn between FIRST_CHANGE# and NEXT_CHANGE#
/
EOF
exit
# ......................................
# Reset SCN
# ......................................
elif [ "$CHOICE" = "RESET_SCN" ];then
  $SCN=${SCN:-0}
SQL="
set serveroutput on size unlimited
declare
  fscn number;
  ascn number;
  rscn number;
  sscn number := $SCN ;
begin
    SELECT FIRST_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN into fscn,ascn,rscn 
           FROM DBA_CAPTURE where CAPTURE_NAME = upper('$fcapture');
    if fscn > ascn then
       fscn := ascn ;
    end if;
    if fscn > rscn then
       fscn := rscn ;
    end if;
    if sscn < fscn then
       sscn := fscn-1;
    end if ;
    dbms_output.put_line('dbms_capture_adm.alter_capture(capture_name => ''$fcapture'',first_scn=>'||to_char(fscn)||',start_scn=>'||to_char(sscn)||'); ');
    dbms_capture_adm.alter_capture( capture_name => '$fcapture',first_scn=> fscn,start_scn=>sscn );
    
end;
/
"
# ......................................
# List capture rule
# ......................................
elif [ "$CHOICE" = "GET_CURR_SCN" ];then
SQL="
           set serveroutput on 
           DECLARE
            iscn NUMBER; -- Variable to hold instantiation SCN value
           BEGIN
            iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
            DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
           END;
/
"
# ......................................
# List capture rule
# ......................................
elif [ "$CHOICE" = "CAP_RUL" ];then
SQL="set long 4000
select rsr.rule_set_owner||'.'||rsr.rule_set_name rsn ,rsr.rule_owner||'.'||rsr.rule_name rn,
r.rule_condition rt from dba_rule_set_rules rsr, dba_rules r where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rule_set_name in (select
rule_set_name from dba_capture) order by rsr.rule_set_owner,rsr.rule_set_name;"
# set trace
# ......................................
elif [ "$CHOICE" = "TRACE" ];then
# ......................................
 SQL="execute dbms_capture_adm.set_parameter(capture_name=> '$fcapture' , parameter=> 'trace_level', value => $trace_level); "

# ......................................
# List capture Parameters
# ......................................
elif [ "$CHOICE" = "PARAMETERS" ];then
# ......................................
SQL="break on capture_name on report
col CAPTURE_NAME format a25
col PARAMETER format a30
col value format a30
col SET_BY_USER format a12
select CAPTURE_NAME,PARAMETER, VALUE, SET_BY_USER from SYS.DBA_CAPTURE_PARAMETERS ;
prompt List suplementatl logging setting: IMPLICIT means it is acitivated for this type
col log_data_pk format a12
col log_data_fk format a12
col log_data_ui format a12
col log_data_all format a12

prompt
select SCHEMA_NAME, TIMESTAMP, SUPPLEMENTAL_LOG_DATA_PK log_data_pk, SUPPLEMENTAL_LOG_DATA_UI log_data_ui,
       SUPPLEMENTAL_LOG_DATA_FK log_data_fk, SUPPLEMENTAL_LOG_DATA_ALL log_data_all from DBA_CAPTURE_PREPARED_SCHEMAS ;
"
# ......................................
# List Checkpoints
# ......................................
elif [ "$CHOICE" = "LIST_LCK" ];then
cat <<EOF


====================
Oracle definitions:
====================
First SCN : The first SCN is the lowest SCN in the redo log from which a capture process can capture changes. 
            If you specify a first SCN during capture process creation, then the database must be able to access 
            redo data from the SCN specified and higher.

Start SCN : The start SCN is the SCN from which a capture process begins to capture changes. You can specify a start SCN 
            that is different than the first SCN during capture process creation, or you can alter a capture process to set 
            its start SCN. The start SCN does not need to be modified for normal operation of a capture process. Typically, 
            you reset the start SCN for a capture process if point-in-time recovery must be performed on one of the destination 
            databases that receive changes from the capture process. In these cases, the capture process can be used to capture 
            the changes made at the source database after the point-in-time of the recovery.

Required Checkpoint SCN  : The SCN that corresponds to the lowest checkpoint for which a capture process requires redo data 
            is the required checkpoint SCN. If a capture process is stopped and restarted, then it starts scanning the redo log 
            from the SCN that corresponds to its required checkpoint SCN. The required checkpoint SCN is important for recovery 
            if a database stops unexpectedly. Also, if the first SCN is reset for a capture process, then it must be set to a value 
            that is less than or equal to the required checkpoint SCN for the captured process. You can determine the required checkpoint 
            SCN for a capture process by querying the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view.

Instantiation SCN  : The system change number (SCN) for a table which specifies that only changes that were committed after the SCN 
            at the source database are applied by an apply process.

Checkpoint_retention_time : Controls the amount of days of metadata retained by moving  FIRST_SCN  forward

          First scn must be <= Start Scn
          First scn must be <= applied scn (only when applied scn > 0)
          First scn must be <= required chkpoint SCN
EOF
TITTLE="Report Streams SCN positionning"
SQL="break on capture_name on report
col start_scn for 999999999999 head 'Start scn'
col first_scn for 999999999999 head 'First scn'
col CAPTURED_SCN for 999999999999 head 'Captured scn'
col las for 999999999999 head 'Applied scn'
col LAST_ENQUEUED_SCN for 999999999999 head 'Last scn|Enqueued' justify c
col REQUIRED_CHECKPOINT_SCN for 999999999999 head 'Requiered |Checkpoint scn' justify c
col MAX_CHECKPOINT_SCN for 999999999999 head 'Max |Checkpoint scn' justify c
col capture_name for a22 head 'Capture name'
set linesize 150
select CAPTURE_NAME, FIRST_SCN, start_scn, APPLIED_SCN las, CAPTURED_SCN, CHECKPOINT_RETENTION_TIME, LAST_ENQUEUED_SCN,
         REQUIRED_CHECKPOINT_SCN, MAX_CHECKPOINT_SCN from SYS.DBA_CAPTURE;
"

# ......................................
# List archive purge status 
# ......................................
elif [ "$CHOICE" = "REGISTERED" ];then
SQL="set linesize 150
break on consumer_name on report
col consumer_name format a20
col name format a50
select consumer_name,SEQUENCE# , FIRST_SCN , to_char(FIRST_TIME,'DD-MM-YYYY HH24:MI:SS') FIRST_TIME, name,
        PURGEABLE,DICTIONARY_BEGIN, DICTIONARY_END from SYS.DBA_REGISTERED_ARCHIVED_LOG order by SEQUENCE# desc;
"
# ......................................
# List capture server status
# ......................................
elif [ "$CHOICE" = "CAPTURE_SERVER" ];then
   SQL="col state format a19
select CAPTURE_NAME cap, state,ELAPSED_CAPTURE_TIME ect,ELAPSED_ENQUEUE_TIME eet,ELAPSED_LCR_TIME elt, CAPTURE_MESSAGE_NUMBER cmn ,ENQUEUE_MESSAGE_NUMBER emn, total_messages_captured tmc, TOTAL_MESSAGES_ENQUEUED tme from v\$streams_capture 
/
select CAPTURE_NAME cap,to_char(STARTUP_TIME,'MM/DD HH24:MI:SS') startup, to_char(CAPTURE_TIME,'MM/DD HH24:MI:SS') lct, 
       to_char(CAPTURE_MESSAGE_CREATE_TIME,'MM/DD HH24:MI:SS') cmct, to_char(ENQUEUE_TIME,'MM/DD HH24:MI:SS')ltme,
       to_char(ENQUEUE_MESSAGE_CREATE_TIME,'MM/DD HH24:MI:SS') emct from v\$streams_capture
/
"
# ......................................
# Set checkpoint frequency
# ......................................
elif [ "$CHOICE" = "LOGMINER" ];then
    # bpa : in 10g this is also good 
    # SELECT NVL(MAX(A.SPARE1), 0) FROM SYSTEM.LOGMNR_RESTART_CKPT$ A WHERE A.SESSION# = :b AND A.VALID = 1 AND EXISTS 
    #  (SELECT B.CKPT_SCN FROM SYSTEM.LOGMNR_RESTART_CKPT$ B WHERE B.CKPT_SCN = A.SPARE1 AND B.SESSION# = A.SESSION# AND B.VALID = 1)
   if [ -n "$LOGMNR_ID" ];then
       WHERE_LOGMNR_ID=" where LOGMINER_ID = $LOGMNR_ID "
       AND_LOGMNR_ID=" and session# = $LOGMNR_ID "
   fi
   SQL=" set serveroutput on
DECLARE
 hScn number := 0;
 lScn number := 0;
 sScn number;
 ascn number;
 alog varchar2(1000);
begin
  select min(start_scn), min(applied_scn) into sScn, ascn
    from dba_capture $WHERE_LOGMNR_ID;

  DBMS_OUTPUT.ENABLE(2000); 

  for cr in (select distinct(a.ckpt_scn)
             from system.logmnr_restart_ckpt\$ a
             where a.ckpt_scn <= ascn and a.valid = 1 $AND_LOGMNR_ID
               and exists (select * from system.logmnr_log\$ l
                   where a.ckpt_scn between l.first_change# and
                     l.next_change#)
              order by a.ckpt_scn desc)
  loop
    if (hScn = 0) then
       hScn := cr.ckpt_scn;
    else
       lScn := cr.ckpt_scn;
       exit;
    end if;
  end loop;

  if lScn = 0 then
    lScn := sScn;
  end if;
   select min(name) into alog from v\$archived_log where lScn between first_change# and next_change#;
  dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in log '||alog);
end;
/
"

# ......................................
# Set SGA_SIZE
# ......................................
elif [ "$CHOICE" = "SET_SGA" ];then
   SQL="execute dbms_capture_adm.set_parameter(capture_name=> '$fcapture' , parameter=> '_SGA_SIZE', value => ${sga_size}); "

# ......................................
# Set checkpoint frequency
# ......................................
elif [ "$CHOICE" = "CHECK_FREQ" ];then
   SQL="execute dbms_capture_adm.set_parameter(capture_name=> '$fcapture' , parameter=> '_CHECKPOINT_FREQUENCY', value => $check_freq); "

# ......................................
# Set retention checkpoint time
# ......................................
elif [ "$CHOICE" = "SET_RET" ];then
   SQL="execute dbms_capture_adm.alter_capture(capture_name=> '$fcapture' , checkpoint_retention_time=> $value ); "
elif [ "$CHOICE" = "SET_PAR" ];then
   SQL="execute dbms_capture_adm.set_parameter(capture_name=> '$fcapture' , parameter=> '$PAR', value => $value); "

# ......................................
# Table prepared for instantion
# ......................................
elif [ "$CHOICE" = "TBL_INS" ];then
   BREAK="break on table_owner"
   SQL="
prompt Type cap -lck to see catpure start scn
prompt
select TABLE_OWNER,TABLE_NAME,SCN,to_char(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') ti from dba_capture_prepared_tables order by table_owner;"
# ......................................
# Show lowest prepared SCN
# ......................................
elif [ "$CHOICE" = "MIN_SI" ];then
    if [ -n '$fowner' ];then
         WHERE_OWNER=" where table_owner=upper('$fowner') "
         AND_OWNER=" and table_owner=upper('$fowner') "
    fi
    BREAK="break on table_owner"
    SQL="prompt Type cap -lck to see catpure start scn
 prompt
 select TABLE_OWNER,TABLE_NAME,SCN min_scn,to_char(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') ti
        from dba_capture_prepared_tables where  scn = (select min(scn) from dba_capture_prepared_tables $WHERE_OWNER) $AND_OWNER;"
# ......................................
# Create capture process
# ......................................
elif [ "$CHOICE" = "CREATE" ];then
   if [ -n "$frsname" ];then
      SQL="execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name => '$fowner.$fqueue', capture_name => '$fcapture', rule_set_name => '$fowner.$frsname');"
   fi
# ......................................
# List capture process
# ......................................
elif [ "$CHOICE" = "LIST" ];then
    if [ -n "$fcapture" ];then
          AND_CAPTURE_NAME=" and capture_name = upper('$fcapture') "
          AND_CAPTURE_NAME_A=" and a.capture_name = upper('$fcapture') "
          WHERE=" where 1=1"
    elif [ -n "$LOGMNR_ID" ];then
       AND_LOGMNR_ID="  and LOGMINER_ID = $LOGMNR_ID "
       AND_LOGMNR_ID_A=" and a.LOGMINER_ID = $LOGMNR_ID "
       WHERE=" where 1=1"
   fi
    SQL="
col LOGMINER_ID head 'Logminer|ID' 
select  LOGMINER_ID, CAPTURE_USER,  start_scn ncs,  to_char(STATUS_CHANGE_TIME,'DD-MM HH24:MI:SS') change_time 
    ,CAPTURE_TYPE,RULE_SET_NAME, negative_rule_set_name , status from dba_capture $WHERE $AND_CAPTURE_NAME $AND_LOGMNR_ID
/
col rsname format a22 head 'Rule set name'
col delay_scn head 'Delay|Scanned' justify c
col delay2 head 'Delay|Enq-Applied' justify c
col state format a24
col process_name format a8 head 'Process|Name' justify c
col LATENCY_SECONDS head 'Lat(s)'
col total_messages_captured head 'total msg|Captured'
col total_messages_enqueued head 'total msg|Enqueue'
col ENQUEUE_MESG_TIME format a17 head 'Row creation|initial time'
col CAPTURE_TIME head 'Capture at'
select a.CAPTURE_NAME cap, queue_name , AVAILABLE_MESSAGE_NUMBER, CAPTURE_MESSAGE_NUMBER lcs, 
      AVAILABLE_MESSAGE_NUMBER-CAPTURE_MESSAGE_NUMBER delay_scn,
      last_enqueued_scn , applied_scn las , last_enqueued_scn-applied_scn delay2
      from dba_capture a, v\$streams_capture b where a.capture_name = b.capture_name (+) $AND_CAPTURE_NAME_A $AND_LOGMNR_ID_A;
SELECT
         SUBSTR(s.program,INSTR(s.program,'(')+1,4) PROCESS_NAME,
         c.sid,
         c.serial#,
         c.state,
         to_char(c.capture_time, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,
         to_char(c.enqueue_message_create_time,'HH24:MI:SS MM/DD/YY') ENQUEUE_MESG_TIME ,
        (SYSDATE-c.capture_message_create_time)*86400 LATENCY_SECONDS,
        c.total_messages_captured,
        c.total_messages_enqueued
   FROM V\$STREAMS_CAPTURE c, V\$SESSION s
   WHERE c.SID = s.SID $AND_CAPTURE_NAME  $AND_LOGMNR_ID
  AND c.SERIAL# = s.SERIAL#;

set head off 
select ERROR_MESSAGE from dba_capture;"

# ......................................
# abort instantiation
# ......................................
elif [ "$CHOICE" = "ABORT_INSTANTIATE" ];then
  if [ -n "$ftable" ];then
     if [ -z "$fsource_owner" ];then
        echo "I need the table owner, user 'cap -abort -so OWNER -t TABLE'"
        exit
     fi
     SQL="execute  DBMS_CAPTURE_ADM.${ACTION}_TABLE_INSTANTIATION( table_name  => '$fsource_owner.$ftable'); "
  elif [ -n "$fsource_owner" ];then
     SQL="execute  DBMS_CAPTURE_ADM.${ACTION}_SCHEMA_INSTANTIATION( schema_name  => '$fsource_owner'); "
  else
    echo "Need to be more precise for instantiation : give a owner or owner+table name"
    exit
  fi

# ......................................
# Prepare instantiation
# ......................................
elif [ "$CHOICE" = "PREPARE" ];then
  if [ -z "$fsource_owner" ];then
     echo "I need a schema, use 'cap -so OWNER'"
     exit
  fi
  if [ -z "$ftable" ];then
     SQL="execute  DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION( schema_name  => '$fsource_owner'); "
  else
     src_sid=${src_sid:-$ORACLE_SID}
     SQL="show user
execute dbms_capture_adm.prepare_table_instantiation('$fsource_owner.$ftable') ;
execute DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => '$fsource_owner.$ftable');
"
  fi

# ......................................
# Start / stop / drop capture
# ......................................
elif [ "$CHOICE" = "START_STOP_DROP" ];then
  SQL=" execute  DBMS_CAPTURE_ADM.${ACTION}_CAPTURE( capture_name => '$fcapture');"
fi
if [ "$EXECUTE" = "YES" ];then
   do_execute
else
  echo "$SQL"
fi