Tuesday, December 4, 2012

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>

No comments:

Post a Comment

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...