How to Recover from delete on a table using 10g logminer (by example)?

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.3
Information in this document applies to any platform.

Goal

How to Recover from delete on a table using 10g logminer?

Solution

In 10g when you are using the LogMiner against the same database that generated the
redo log files, LogMiner scans the control file and determines the redo log files needed, based
on the requested time or SCN range.  You no longer need to map the time frame to an explicit set of redo log files.
For this scan to occur, we need to use the continuous_mine option and specify approximate startscn
or starttime.

1- create a table as scott user.

SQL> connect scott/tiger;
Connected.

SQL> create table tab1(id number ,name varchar2(20));

Table created.

2- insert 100 records in the new table

SQL> begin
for i in 1..100 loop
insert into TAB1 values(i,'test'||i);
commit;
end loop;
end;
/



3- find out what is the current time

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';



SQL> select sysdate from dual;

SYSDATE
--------------------
06-SEP-2006 23:19:22

4- delete the 100 records from the scott.tab1 table

SQL> delete from tab1;

100 rows deleted.

SQL> commit;

Commit complete.

5- Find out what is the current time.

SQL> select sysdate from dual;

SYSDATE
--------------------
06-SEP-2006 23:19:55


6- Connect as sysdba and start a logminer using STARTTIME => '06-SEP-2006 23:19:22', ENDTIME => '06-SEP-2006 23:20:22'

SQL> connect / as sysdba
Connected.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '06-SEP-2006 23:19:22', ENDTIME => '06-SEP-2006 23:20:22',options=> DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +DBMS_LOGMNR.CONTINUOUS_MINE);

PL/SQL procedure successfully completed.


7- create a table to store the info from v$logmnr_contents. you can select directly from v$logmnr_contents but the
contents of the v$logmnr_contents will be lost once you end up logminer session.

SQL> create table mycontents as select * from v$logmnr_contents;

table created.

8- at this time you can end up your logminer session and just work later on the mycontents table you created in step 7 above.


sql>execute dbms_logmnr.end_logmnr();



9- now you can select from the table and get the sql_undo



SQL> set heading off
SQL> Set feedback off
SQL> spool myinsert.sql

SQL>SELECT sql_undo
FROM mycontents
WHERE seg_owner='SCOTT'
AND table_name='TAB1' and operation='DELETE';

SQL> spool off




10- myinsert.sql should look something like


insert into "SCOTT"."TAB1"("ID","NAME") values ('1','test1');
insert into "SCOTT"."TAB1"("ID","NAME") values ('2','test2');
insert into "SCOTT"."TAB1"("ID","NAME") values ('3','test3');
insert into "SCOTT"."TAB1"("ID","NAME") values ('4','test4');
insert into "SCOTT"."TAB1"("ID","NAME") values ('5','test5');
insert into "SCOTT"."TAB1"("ID","NAME") values ('6','test6');
insert into "SCOTT"."TAB1"("ID","NAME") values ('7','test7');
---
---
---
---
insert into "SCOTT"."TAB1"("ID","NAME") values ('92','test92');
insert into "SCOTT"."TAB1"("ID","NAME") values ('93','test93');
insert into "SCOTT"."TAB1"("ID","NAME") values ('94','test94');
insert into "SCOTT"."TAB1"("ID","NAME") values ('95','test95');
insert into "SCOTT"."TAB1"("ID","NAME") values ('96','test96');
insert into "SCOTT"."TAB1"("ID","NAME") values ('97','test97');
insert into "SCOTT"."TAB1"("ID","NAME") values ('98','test98');
insert into "SCOTT"."TAB1"("ID","NAME") values ('99','test99');
insert into "SCOTT"."TAB1"("ID","NAME") values ('100','test100');



NOTE: You need to double check   the record  in v$logmnr_contents are actually the right records. Do describe on the v$logmnr_contents  and you will see a lot of columns that will help you to verify  you are working on the correct set of data.

For complete  information on logminer usage please refer to

Oracle® Database Utilities
10g Release 1 (10.1)
Part Number B10825-01chapter 19 Using LogMiner to Analyze Redo Log Filesl!

[출처] How to Recover from delete on a table using 10g logminer (by example)?|작성자 오토맨