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/，如需轉載，請註明出處，否則將追究法律責任。