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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/750077/viewspace-2112702/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/750077/viewspace-2112702/