Search code examples
oracleredo-logs

Oracle Logminer can't find redo entries

I updated several rows in "HR"."AAA" table. Now I want to return HR.AAA table to its previous state using LogMiner.

I do the following:

begin 
dbms_logmnr_d.build('dictionary.ora','C:\oracle\product\10.2.0\oradata\ORCL\UTL'); 
end;

I checked and before and after updating the table, CURRENT logfile was :"C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG"

begin 
dbms_logmnr.add_logfile('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG',options=> dbms_logmnr.new); 
end;


begin
dbms_logmnr.start_logmnr (DictFileName =>'C:\oracle\product\10.2.0\oradata\ORCL\UTL\dictionary.ora', options => dbms_logmnr.print_pretty_sql);
end;


select * from v$logmnr_contents
where username='HR'

And the output is nothing. I can't find the changes made by HR schema... Can you tell me why?

Solution

  • I found the solution :):):)

    I looked through the content of the view v$logmnr_contents and found in the INFO column "In Memory Undo is unsupported" value. This line corresponded to the row where my update should be placed(I considered the timestamp column, I mean timestamp corresponded to the time when I updated the table). After googling, I found the following statement:

    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;

    Now I can see my REDO (happy)