Log Miner Example

Minimum Requriment

DBA role privs
Archive log mode enable if want to analyze ARCHIVELOG FILES

Create one table and delete one row and commit

SQL> create table taj ( no number, name varchar2(20));

Table created.

SQL> insert into taj values ( 1, 'TEST');

1 row created.

SQL> insert into taj values (2,'TEST');

1 row created.

SQL> commit;

Commit complete.

SQL> delete taj where no = 2;

1 row deleted.

SQL> commit;

Commit complete.

Through Logminer we can see deleted rows and recover

Before using logminer ENABLE SUPPLEMENTAL LOG

SQL> alter database add supplemental log data;

Database altered.

Query and select which REDOLOG file want to analyze

SQL> column memeber format a50
SQL> select a.member, b.status
2 from v$logfile a, v$log b
3 where a.group# = b.group#;

MEMBER STATUS
-------------------------------------------------- ----------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG INACTIVE
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG CURRENT
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG INACTIVE


Note: here we analyze CURRENT REDO LOG FILE

Specify Redo Log Files for Analysis

SQL> execute sys.dbms_logmnr.add_logfile(-
> logfilename => 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG',-
> options => sys.dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

Start LogMiner

SQL> execute sys.dbms_logmnr.start_logmnr(-
> options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

Query V$LOGMNR_CONTENTS

SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name = 'TAJ';

SQL_REDO SQL_UNDO
------------------------------ ------------------------------
drop table taj purge;
create table taj ( no number,
name varchar2(20));

insert into "MTAJ"."TAJ"("NO", delete from "MTAJ"."TAJ" where
"NAME") values ('1','TEST'); "NO" = '1' and "NAME" = 'TEST
' and ROWID = 'AAALjxAAIAAAAAk
AAA';

insert into "MTAJ"."TAJ"("NO", delete from "MTAJ"."TAJ" where
"NAME") values ('2','TEST'); "NO" = '2' and "NAME" = 'TEST

SQL_REDO SQL_UNDO
------------------------------ ------------------------------
' and ROWID = 'AAALjxAAIAAAAAk
AAB';

delete from "MTAJ"."TAJ" where insert into "MTAJ"."TAJ"("NO",
"NO" = '2' and "NAME" = 'TEST "NAME") values ('2','TEST');
' and ROWID = 'AAALjxAAIAAAAAk
AAB';

End the LogMiner Session

SQL> EXECUTE sys.DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

Recover Delete rows

SQL> insert into "MTAJ"."TAJ"("NO",
2 "NAME") values ('2','TEST');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from taj;

NO NAME
---------- --------------------
1 TEST
2 TEST

Hope this helps
Taj