notes/logminer/logmnr_02.txt

# https://redikx.wordpress.com/2015/07/10/logminer-to-analyze-archive-logs-on-different-database/

alias HUTTPRD='rlwrap sqlplus sys/"Secret00!"@bakura:1521/HUTTPRD as sysdba'
alias ZABRAKPRD='rlwrap sqlplus sys/"Secret00!"@togoria:1521/ZABRAKPRD as sysdba'

alias DURGA='rlwrap sqlplus jedi/"Secret00!"@bakura:1521/DURGA as sysdba'
alias MAUL='rlwrap sqlplus jedi/"Secret00!"@togoria:1521/MAUL as sysdba'

alias WOMBAT='sqlplus wombat/animal@bakura/DURGA'


# on PDB DURGA as WOMBAT user
alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

drop table DEMO purge;
create table DEMO(d date);

insert into DEMO values (sysdate); 
insert into DEMO values (sysdate);
insert into DEMO values (sysdate);
insert into DEMO values (sysdate);
insert into DEMO values (sysdate);
commit;
insert into DEMO values (sysdate);
commit;
delete from DEMO;
commit;

# backup generated archivelog
rman target /
run
{
  set nocfau;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  allocate channel ch02 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  backup as compressed backupset archivelog all delete input;
}

# store dictionary in redolog
begin
   dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);
end;
/

# identify archivelog containing the dictionary
select thread#,sequence# from gv$archived_log where DICTIONARY_BEGIN='YES';
select thread#,sequence# from gv$archived_log where DICTIONARY_END='YES';

# backup archivelog containing the dictionary
rman target /
run
{
  set nocfau;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  allocate channel ch02 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  backup as compressed backupset archivelog sequence 12 delete input;
}

# Goal: list all DML against DEMO table between 2024-06-23 15:00:00 and 2024-06-23 16:00:00

# identify required archivelog
select THREAD#,max(SEQUENCE#) from gv$archived_log where FIRST_TIME<=timestamp'2024-06-23 15:00:00' group by THREAD#;
select THREAD#,min(SEQUENCE#) from gv$archived_log where NEXT_TIME>=timestamp'2024-06-23 16:00:00' group by THREAD#;


# all operation will be realized on a different CDB on the CDB$ROOT
# restore required archivelog
rman target /
run
{
  set nocfau;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  allocate channel ch02 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  set archivelog destination to '/mnt/yavin4/tmp/00000/logminer/arch/';
  restore archivelog from sequence 3 until sequence 8;
}


# restore dictionary archivelog
rman target /
run
{
  set nocfau;
  allocate channel ch01 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  allocate channel ch02 device type disk format '/mnt/yavin4/tmp/00000/logminer/backup/%d_%U_%s_%t.bck';
  set archivelog destination to '/mnt/yavin4/tmp/00000/logminer/arch/';
  restore archivelog from sequence 12 until sequence 12;
}


# add log
execute dbms_logmnr.add_logfile(logfilename=>'/mnt/yavin4/tmp/00000/logminer/arch/1_12_1172413318.arc', options => dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/mnt/yavin4/tmp/00000/logminer/arch/1_3_1172413318.arc', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/mnt/yavin4/tmp/00000/logminer/arch/1_4_1172413318.arc', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/mnt/yavin4/tmp/00000/logminer/arch/1_5_1172413318.arc', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/mnt/yavin4/tmp/00000/logminer/arch/1_6_1172413318.arc', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/mnt/yavin4/tmp/00000/logminer/arch/1_7_1172413318.arc', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/mnt/yavin4/tmp/00000/logminer/arch/1_8_1172413318.arc', options => dbms_logmnr.addfile);

# to list added log

set lines 256
col FILENAME for a60
col INFO for a60
select FILENAME,INFO from V$LOGMNR_LOGS;

# start logminer
begin 
  DBMS_LOGMNR.START_LOGMNR (startTime=>timestamp'2024-06-23 15:00:00'
                           ,endTime=>  timestamp'2024-06-23 16:00:00'
                           ,OPTIONS=>DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.COMMITTED_DATA_ONLY
                           );
end;
/

# do mining
alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

col username for a20
col sql_redo for a70
col table_name for a20
col timestamp for a25

select timestamp,username,table_name,sql_redo from v$logmnr_contents where seg_name='DEMO';