Using Log Mining

Today I will show you how to use log mining.

Create new tablespace and move objects which belongs SYSAUX tablespace to new tablespace.

SQL> create tablespace test_tbs datafile '/home/oracle/oracle/product/10.2.0/oradata/mydb/test_tbs01.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

SQL> EXEC DBMS_LOGMNR_D.SET_TABLESPACE('TEST_TBS');

PL/SQL procedure successfully completed.

SQL>

Now start Log Mining and load archive log file into it

Firstly, I created table under demo user and made some DML operations. Then using alter system switch logfile I enforce archive current log file.

  

SQL> create table demo.test(id number, str varchar2(10));

Table created.

SQL> insert into demo.test values(1,'a');

1 row created.

SQL> insert into demo.test values(2,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from demo.test where id=2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL>

Check archivelog files --select the latest one

SQL> select name, status, archived, blocks from v$archived_log where status='A';

NAME                                                                                                        STATUS   ARCH     BLOCKS-----------------------------------------------------------------------------------------------------------------------------------------------------------

/home/oracle/oracle/product/10.2.0/oradata/mydb/ARCH/arch_1_76_770556397.arc A        YES      10146

/home/oracle/oracle/product/10.2.0/oradata/mydb/ARCH/arch_1_77_770556397.arc A        YES       35742

/home/oracle/oracle/product/10.2.0/oradata/mydb/ARCH/arch_1_78_770556397.arc A        YES       12014

/home/oracle/oracle/product/10.2.0/oradata/mydb/ARCH/arch_1_79_770556397.arc A        YES       11974

/home/oracle/oracle/product/10.2.0/oradata/mydb/ARCH/arch_1_80_770556397.arc A        YES       24974

SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/home/oracle/oracle/product/10.2.0/oradata/mydb/ARCH/arch_1_80_770556397.arc',DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> 

Now perform log mining using DBMS_LOGMNR.START_LOGMNR

SQL> exec DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQL procedure successfully completed.

SQL> 

Now check below query

SQL> SELECT USERNAME, TO_CHAR(TIMESTAMP,'DD-MON-YYYYYHH24:MI:SS') TIMESTAMP, SESSION_INFO, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS

WHERE SEG_OWNER='DEMO'  AND TABLE_NAME='TEST';  

USERNAME                       TIMESTAMP

------------------------------ --------------------

SESSION_INFO

--------------------------------------------------------------------------------

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

UNKNOWN                        04-DEC-2012214:16:58

UNKNOWN

create table demo.test(id number, str varchar2(10));

USERNAME                       TIMESTAMP

------------------------------ --------------------

SESSION_INFO

--------------------------------------------------------------------------------

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

UNKNOWN                        04-DEC-2012214:18:06

UNKNOWN

insert into "DEMO"."TEST"("ID","STR") values ('1','a');

delete from "DEMO"."TEST" where "ID" = '1' and "STR" = 'a' and ROWID = 'AAAN6EAA

EAAAALUAAA';

USERNAME                       TIMESTAMP

------------------------------ --------------------

SESSION_INFO

--------------------------------------------------------------------------------

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

UNKNOWN                        04-DEC-2012214:18:19

UNKNOWN

insert into "DEMO"."TEST"("ID","STR") values ('2','b');

delete from "DEMO"."TEST" where "ID" = '2' and "STR" = 'b' and ROWID = 'AAAN6EAA

USERNAME                       TIMESTAMP

------------------------------ --------------------

SESSION_INFO

--------------------------------------------------------------------------------

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

EAAAALUAAB';

UNKNOWN                        04-DEC-2012214:18:41

UNKNOWN

delete from "DEMO"."TEST" where "ID" = '2' and "STR" = 'b' and ROWID = 'AAAN6EAA

USERNAME                       TIMESTAMP

------------------------------ --------------------

SESSION_INFO

--------------------------------------------------------------------------------

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

EAAAALUAAB';

insert into "DEMO"."TEST"("ID","STR") values ('2','b');

SQL> spool off;

SQL> 

Open spooled file via any text editor.

ex : gedit output_logminer.txt

You can filter your query too

SQL> SELECT USERNAME, TO_CHAR(TIMESTAMP,'DD-MON-YYYYYHH24:MI:SS') TIMESTAMP, SESSION_INFO, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS

WHERE SEG_OWNER='DEMO'  AND TABLE_NAME='TEST' AND OPERATION='DELETE';

USERNAME    TIMESTAMP SESSION_INFO SQL_REDO SQL_UNDO

----------------------------------------------------------------------------------------------------------------------------------------------------------------

UNKNOWN                        04-DEC-2012214:18:41

UNKNOWN

delete from "DEMO"."TEST" where "ID" = '2' and "STR" = 'b' and ROWID = 'AAAN6EAA

EAAAALUAAB';

insert into "DEMO"."TEST"("ID","STR") values ('2','b');

USERNAME                       TIMESTAMP

------------------------------ --------------------

SESSION_INFO

--------------------------------------------------------------------------------

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

SQL>