--创建测试数据
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.