Oracle使用_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/zwc@192.168.1.10: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月 -14", "DD-MON-RR"));

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

100 rows selected.

SQL> select SQL_UNDO from t_logminer where seg_name="TAB01" and seg_owner="ZWC" and OPERATION="UPDATE";

SQL_UNDO

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

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAAMAEs";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAAMAEt";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAAMAEu";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAANAAA";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAANAAB";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAANAAC";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAANAAD";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAANAAE";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAANAAF";

update "ZWC"."TAB01" set "D" = TO_DATE("12-3月 -14", "DD-MON-RR") where "D" = TO_DATE("02-12月-13", "DD-MON-RR") and ROWID = "AAAM7rAAGAAAAANAAG";

10 rows selected.