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:
b开发者_运维问答egin
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?
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)
This behavior is due to In Memory Undo, the new feature introduced from Oracle 10g. You can check the article for more info. http://www.freelists.org/post/oracle-l/First-17-updated-records-disappeared-from-REDOLOG-files,2
精彩评论