Monday, November 29, 2021

OPERATIONSQL_REDOSQL_UNDOINFO
DDLcreate table t (id number(8,0), c clob); USER DDL (PlSql=0 RecDep=0)
INSERTinsert into "ADMIN"."T"("ID","C") values ('1',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '1'; 
UPDATEupdate "ADMIN"."T" set "C" = 'a' where "ID" = '1';update "ADMIN"."T" set "C" = NULL where "ID" = '1'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('2',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '2'; 
UPDATEupdate "ADMIN"."T" set "C" = 'b' where "ID" = '2';update "ADMIN"."T" set "C" = NULL where "ID" = '2'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('3',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'c' where "ID" = '3';update "ADMIN"."T" set "C" = NULL where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'c1' where "ID" = '3';update "ADMIN"."T" set "C" = NULL where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'b1' where "ID" = '2';update "ADMIN"."T" set "C" = NULL where "ID" = '2'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('4',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '4'; 
UPDATEupdate "ADMIN"."T" set "C" = NULL where "ID" = '4';update "ADMIN"."T" set "C" = NULL where "ID" = '4'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('5',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '5'; 
UPDATEupdate "ADMIN"."T" set "C" = NULL where "ID" = '5';update "ADMIN"."T" set "C" = NULL where "ID" = '5'; 
UPDATEupdate "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '4';update "ADMIN"."T" set "C" = NULL where "ID" = '4'; 
SEL_LOB_LOCATORDECLARE 
 loc_c CLOB; 
 buf_c VARCHAR2(6156); 
 loc_b BLOB; 
 buf_b RAW(6156); 
 loc_nc NCLOB; 
 buf_nc NVARCHAR2(6156); 
BEGIN
 select "C" into loc_c from "ADMIN"."T" where "ID" = '4' for update;
 LOB sql_redo not re-executable
INTERNAL   
LOB_WRITE
 buf_c := '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 1, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 1023, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 2045, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 934, 3067, buf_c);
 LOB sql_redo not re-executable
INTERNAL   
INTERNALEND;

  
UPDATEupdate "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '5';update "ADMIN"."T" set "C" = NULL where "ID" = '5'; 
SEL_LOB_LOCATORDECLARE 
 loc_c CLOB; 
 buf_c VARCHAR2(6156); 
 loc_b BLOB; 
 buf_b RAW(6156); 
 loc_nc NCLOB; 
 buf_nc NVARCHAR2(6156); 
BEGIN
 select "C" into loc_c from "ADMIN"."T" where "ID" = '5' for update;
 LOB sql_redo not re-executable
INTERNAL   
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 		 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 		 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 2045, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 3067, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 4031, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 5053, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 6075, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 		 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 8061, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 9083, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 10105, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 		 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 12091, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 13113, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 		 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 15157, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 16121, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 17143, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 18165, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';
  dbms_lob.write(loc_c, 964, 19187, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 20151, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';  
  dbms_lob.write(loc_c, 1022, 21173, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';  
  dbms_lob.write(loc_c, 1022, 22195, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 23217, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 24181, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 25203, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 26225, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c := '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 27247, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 28211, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 29233, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 30255, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 31277, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 527, 32241, buf_c);
 LOB sql_redo not re-executable
INTERNAL   
INTERNALEND;

  


EXEC rdsadmin.rdsadmin_util.force_logging(p_enable => true);

begin

    rdsadmin.rdsadmin_util.alter_supplemental_logging(

        p_action => 'ADD');

end;

/

begin

    rdsadmin.rdsadmin_util.alter_supplemental_logging(

        p_action => 'ADD',

        p_type   => 'PRIMARY KEY');

end;

/


begin

    rdsadmin.rdsadmin_util.set_configuration(

        name  => 'archivelog retention hours',

        value => '24');

end;

/

commit;


drop table t purge;


select current_scn from v$database; --759308


create table t (id number(8,0), c clob);

insert into t values(1,'a');

commit;

insert into t values(2,'b');

commit;

insert into t values(3,'c');

commit;

update t set c='c1' where id=3;

commit;

update t set c='b1' where to_char(c)='b';

commit;


select * from t;


insert into t values(4,'');

insert into t values(5,'');

commit;



declare

  inbuf1  raw(3964); -- 3964 still possible for in-line lob

  inbuf2  raw(20000);

begin

  inbuf1 := utl_raw.cast_to_raw(rpad('FF',3964,'FF'));

  update t set c = inbuf1 where id=4;

  commit;

  inbuf2 := utl_raw.cast_to_raw(rpad('FF',20000,'FF'));

  update t set c = inbuf2 where id=5;

  commit;

end;

/


select length(c) from t;




select current_scn from v$database; --759455



execute dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);


alter session set nls_date_format='yyyy-mon-dd hh24:mi:ss';


select * from v$log;



/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_4_jt9fqdq7_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_3_jt9fqcqr_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_2_jt9fqc7g_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_1_jt9fqbox_.log


select * from v$archived_log where next_change#>759308 and first_change#<759455 ;



execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/arch/redolog-24-1-1089890283.arc', options => dbms_logmnr.new);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_3_jt9fqcqr_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_1_jt9fqbox_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_2_jt9fqc7g_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_4_jt9fqdq7_.log', options => dbms_logmnr.addfile);


begin

  dbms_logmnr.start_logmnr(

   startSCN=> 759308,

   endSCN=> 759455, 

   options => dbms_logmnr.ddl_dict_tracking + dbms_logmnr.dict_from_redo_logs 

   + dbms_logmnr.no_rowid_in_stmt);

end;

/


drop table my_logmnr_contents purge;

create table my_logmnr_contents tablespace users as select * from v$logmnr_contents;


select count(*) from my_logmnr_contents;


select operation,sql_redo,sql_undo,info from my_logmnr_contents where seg_owner='ADMIN' and seg_name='T';



Posted by Luo Donghua at 9:42 PM 2 comments
Labels:
Subscribe to: Posts (Atom)