oracle覆盖更新,logminer恢复误覆盖更新的数据

昨日程序部署人员在运营数据库误更新覆盖了系统重要数据。具体误操作方式在一表上用A字段和

B字段数据基本相同,但B字段中有null值，然后B字段的数据更新了A字段数据，之后删除了B字段，

导致系统重要入口数据无法显示，考虑表结构修改过，且只对单表操作，为尽快恢复数据，因此，

使用logminer读取日志的方式恢复数据。具体模拟相应场景

alter database archivelog;

alter database open;

一 建立环境

1 建新表并插入数据

create table TEST.t1(t_id number,t_name varchar2(50),t_salary number(8) );

insert into TEST.t1 values(1,'jy',10000);

insert into TEST.t1 values(2,'wj',8000);

select * from TEST.t1;

2更改数据，删除字段

update test.t1 set t_name = t_salary;

alter table test.t1 drop column t_salary ;

alter system switch logfile;

二恢复日志

1查询删除数据的归档日志

select name,sequence#,first_change#,first_time from v$archived_log ;

2增加归档日志

begin

dbms_logmnr.add_logfile(logfilename=>'D:\ANZHUANG\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_06_02\O1_MF_1_212_CNZ3DBKX_.ARC',options=>dbms_logmnr.NEW);

end;

3分析归档日志

begin

dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

end;

4插入分析归档日志

insert into logmnr_contents  select * from v$logmnr_contents ;

5结束分析归档

begin

dbms_logmnr.end_logmnr;

end;

6查看误操作表的操作步骤

select seg_name,username,scn,timestamp,sql_redo,sql_undo from logmnr_contents where seg_name='T1';

2016/6/2 9:33:30 "create table TEST.t1(t_id number,t_name varchar2(50),t_salary number(8) )

;"

2016/6/2 9:33:32 Dictionary Version Mismatch Dictionary Version Mismatch

2016/6/2 9:33:32 Dictionary Version Mismatch Dictionary Version Mismatch

2016/6/2 9:33:59 update "TEST"."T1" set "T_NAME" = '10000' where "T_NAME" = 'jy' and ROWID = 'AAANI4AAEAAEKBHAAA'; update "TEST"."T1" set "T_NAME" = 'jy' where "T_NAME" = '10000' and ROWID = 'AAANI4AAEAAEKBHAAA';

2016/6/2 9:33:59 update "TEST"."T1" set "T_NAME" = '8000' where "T_NAME" = 'wj' and ROWID = 'AAANI4AAEAAEKBHAAB'; update "TEST"."T1" set "T_NAME" = 'wj' where "T_NAME" = '8000' and ROWID = 'AAANI4AAEAAEKBHAAB';

2016/6/2 9:34:40 "alter table test.t1 drop column t_salary

;"

7找出被更新的数据，逆向被更新的恢复数据

update "TEST"."T1" set "T_NAME" = 'jy' where "T_NAME" = '10000' and ROWID = 'AAANI4AAEAAEKBHAAA';

update "TEST"."T1" set "T_NAME" = 'wj' where "T_NAME" = '8000' and ROWID = 'AAANI4AAEAAEKBHAAB';

select * from TEST.t1;