A shallow summary of oracle log miner
Sometimes, we should recover one or more rows data which is deleted carelessly by users, but it is not necessary to recover the whole db and the flashback does not recovery the data because it's timeout.
For this moment, we should mine the log file to find the redo or undo data to recover the loss data and find who delete the data.
How to use the log miner
After oracle 9i, oracle provides a tool to mine the log file, there is a package of oracle dbms for the logmnr named dbms_logmnr.
[sql]
Make sure the db is archive log mode
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FAR/arch1
Oldest online log sequence 833
Next log sequence to archive 834
Current log sequence 834
SQL>
Example of log miner analyze procedure:
SQL>
SQL> create table test(id number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> begin
2 for i in 1..100 loop
3 insert into test values(i);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Switch log file by user system
SQL> alter system switch logfile;
System altered.
SQL>
Engine the log miner.
select name, thread#, sequence#, first_change#, next_change# from V$ARCHIVED_LOG;
NAME THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------------------- ---------- ---------- ------------- ------------
1 831 4062556971 4064186822
2 634 4063922165 4064177226
2 635 4064177226 4064275220
2 636 4064275220 4064429426
1 832 4064186822 4066775319
+FAR/arch1/1_833_817 1 833 4066775319 4066914873
620325.dbf
+FAR/arch1/1_834_817 1 834 4066914873 4071821640
620325.dbf
NAME THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------------------- ---------- ---------- ------------- ------------
+FAR/arch2/2_637_817 2 637 4064429426 4071821645
620325.dbf
SQL>
exec dbms_logmnr.add_logfile(logfilename=>'+FAR/arch1/1_834_817620325.dbf', options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL>
options: dbms_logmnr.new stand for the first log file to be analyzed.
SQL> exec dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL>
select scn, timestamp, seg_name, seg_owner, sql_redo, sql_undo from v$logmnr_contents where seg_name='TEST' and set_owner='RAY';
SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO
---------- --------- ---------- ---------- ---------------------------------------- ----------------------------------------
4071818078 11-SEP-13 TEST RAY create table test(id number);
4071818819 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1
1'); ' and ROWID = 'AAAY8eAAFAAABbNAAA';
4071818840 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1
1'); ' and ROWID = 'AAAY8eAAFAAABbNAAB';
4071818851 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1
1'); ' and ROWID = 'AAAY8eAAFAAABbNAAC';
4071819125 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1
SCN T