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