OracleLogminer使用

 　　--創建測試數據

　　C:>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

　　C:>sqlplus / as sysdba

　　SQL*Plus: Release 10.2.0.4.0 - Production on 星期三 3月 12 22:10:38 2014

　　Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

　　連接到:

　　Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

　　With the Partitioning, OLAP, Data Mining and Real Application Testing options

　　SQL> create tablespace zwc datafile 'C:oraclezwc01.dbf' size 500m;

　　表空間已創建。

　　SQL> create user zwc identified by zwc;

　　用戶已創建。

　　SQL> grant resource,connect to zwc;

　　授權成功。

　　SQL> conn zwc

　　輸入口令:

　　已連接。

　　SQL> create table zwc.tab01(a int primary key,b varchar2(100),c varchar2(100),d date default sysdate) tablespace zwc;

　　表已創建。

　　SQL> create or replace procedure p_inst_tab01 as

　　2 begin

　　3 for i in 1..2000000 loop

　　4 insert into tab01(a,b,c,d) values(i,i,i,sysdate);

　　5 if mod(i,2000)=0 then

　　6 commit;

　　7 end if;

　　8 end loop;

　　9 end p_inst_tab01;

　　10 /

　　過程已創建。

　　SQL> show user

　　USER 為 "ZWC"

　　SQL> exec p_inst_tab01;

　　PL/SQL 過程已成功完成。

　　SQL> select count(*) from tab01;

　　COUNT(*)

　　----------

　　2000000

　　SQL> select sum(bytes)/1024/1024 "size MB" from user_segments where segment_name='TAB01';

　　size MB

　　----------

　　80

　　--刪除、更新數據

　　SQL> show user

　　USER 為 "SYS"

　　SQL> alter database add supplemental log data;

　　數據庫已更改。

　　SQL> delete from zwc.tab01 where rownum<=100;

　　已刪除100行。

　　SQL> update zwc.tab01 set d=sysdate-100 where rownum<=10;

　　已更新10行。

　　SQL> commit;

　　提交完成。

　　--使用logminer找回數據

　　SQL> alter system set utl_file_dir='c:oracle' scope=spfile;

　　系統已更改。

　　SQL> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';

　　會話已更改。

　　SQL> shutdown immediate

　　數據庫已經關閉。

　　已經卸載數據庫。

　　ORACLE 例程已經關閉。

　　SQL> startup

　　ORACLE 例程已經啟動。

　　Total System Global Area 612368384 bytes

　　Fixed Size 2067656 bytes

　　Variable Size 167772984 bytes

　　Database Buffers 436207616 bytes

　　Redo Buffers 6320128 bytes

　　數據庫裝載完畢。

　　數據庫已經打開。

　　SQL> execute dbms_logmnr_d.build(dictionary_filename=>'test.ora',dictionary_location=>'c:oracle');

　　PL/SQL 過程已成功完成。

　　--select group#,status from v$log;

　　--select group#,member from v$logfile;

　　SQL> execute dbms_logmnr.add_logfile(logfilename=>'C:archARC00041_0842045960.001',options=>dbms_logmnr.new);

　　PL/SQL 過程已成功完成。

　　SQL> execute dbms_logmnr.start_logmnr(dictFilename=>'c:oracletest.ora');

　　PL/SQL 過程已成功完成。

　　SQL> create table zwc.t_logminer tablespace zwc as select * from v_$logmnr_contents;

　　表已創建。

　　SQL> select count(*) from zwc.t_logminer;

　　COUNT(*)

　　----------

　　212

　　SQL> execute dbms_logmnr.end_logmnr;

　　PL/SQL 過程已成功完成。

　　SQL> select count(*) from zwc.t_logminer where seg_name='TAB01' and seg_owner='ZWC';

　　COUNT(*)

　　----------

　　110

　　--需要恢復數據查詢SQL_UNDO，執行誤刪除的是SQL_REDO，OPERATION是操作類型

　　[oracle@db10 ~]$ sqlplus zwc/[email protected]:1521/prod

　　SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 12 23:10:15 2014

　　Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

　　Connected to:

　　Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

　　With the Partitioning, OLAP, Data Mining and Real Application Testing options

　　SQL> set lines 150 pages 200

　　SQL> select SQL_UNDO from t_logminer where seg_name='TAB01' and seg_owner='ZWC' and OPERATION='DELETE';

　　SQL_UNDO

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

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('201','201','201',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('202','202','202',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('203','203','203',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('204','204','204',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('205','205','205',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('206','206','206',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('207','207','207',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('208','208','208',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('209','209','209',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('210','210','210',TO_DATE('02-12月-13', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('211','211','211',TO_DATE('12-3月 -14', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('212','212','212',TO_DATE('12-3月 -14', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('213','213','213',TO_DATE('12-3月 -14', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('214','214','214',TO_DATE('12-3月 -14', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('215','215','215',TO_DATE('12-3月 -14', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('216','216','216',TO_DATE('12-3月 -14', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('217','217','217',TO_DATE('12-3月 -14', 'DD-MON-RR'));

　　insert into "ZWC"."TAB01"("A","B","C","D") values ('218','218','218',TO_DATE('12-3月 -1