--创建测试数据
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 - 64bitProduction
With the Partitioning, OLAP, Data Mining and Real Application Testingoptions
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),cvarchar2(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 wheresegment_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> executedbms_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> executedbms_logmnr.add_logfile(logfilename=>'C:archARC00041_0842045960.001',options=>dbms_logmnr.new);
PL/SQL 过程已成功完成。
SQL> executedbms_logmnr.start_logmnr(dictFilename=>'c:oracletest.ora');
PL/SQL 过程已成功完成。
SQL> create table zwc.t_logminer tablespace zwc as select * fromv_$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' andseg_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 - 64bitProduction
With the Partitioning, OLAP, Data Mining and Real Application Testingoptions
SQL> set lines 150 pages 200
SQL> select SQL_UNDO from t_logminer where seg_name='TAB01' andseg_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' andseg_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.
