經常遇到開發人員錯誤刪除，修改,插入等誤操作,事後又不知道具體操作哪些資料,希望把恢復到某某時間點,在資料庫開歸檔並有備份的情況的下有一下方法

1 閃回資料庫(需要開啟flashback)

2 異機資料庫不完全恢復(可基於部分表空間)

3 LOGMINER 日誌挖掘(需要設定supplemental logging)

對DML誤操作,如果flashback沒有開啟,只有選擇後兩種方法,如果資料庫較大對於第二種方法將比較笨重,需要準備新的機器,安裝軟體,複製備份,執行恢復。在開啟supplemental logging(建議資料庫開啟supplemental logging最小模式,對日誌量影響較小)，情況下則可以選擇LOGMINER。

關於使用logminer 需要開啟supplemental logging 官方文件有明確說明

Database-Level Supplemental Logging

There are two types of database-level supplemental logging: minimal supplemental logging and identification key logging, as described in the following sections. Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. However, enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging for LogMiner

---挖掘過程

一 建立使用者

create user logmnr_user identified by logmnr_user;

grant connect,resource,dba to logmnr_user;

alter user logmnr_user default tablespace TBS_XINHUA40_MZMX_1

二 複製源表

create table LOGMNR_USER.D405 nologging as select * from FANGCHENG40.D405 where 1=2;

alter table LOGMNR_USER.D405 add r_rowid varchar2(1000);

create index LOGMNR_USER.D405_ROWID on LOGMNR_USER.D405(r_rowid);

把源表的rowid 作為新表的一個欄位,方便快速更新

insert /*+ append */ into LOGMNR_USER.D405(D405_01,

D405_02,

D405_03,

D405_04,

D405_05,

D405_06,

D405_07,

D405_08,

D405_09,

D405_10,

r_rowid) Select D405_01,

D405_02,

D405_03,

D405_04,

D405_05,

D405_06,

D405_07,

D405_08,

D405_09,

D405_10,

rowid

From FANGCHENG40.D405;

commit;

exec dbms_stats.gather_table_stats(ownname => 'LOGMNR_USER',tabname => 'D405',granularity => 'ALL',cascade => true);

三 修改引數

alter system set utl_file_dir='/home/oracle' scope=spfile;

shutdown immediate

startup

四 生成字典

exec dbms_logmnr_d.build('dictionary.ora','/home/oracle');

五 新增需要的日誌

查詢需要新增的日誌

select name from v$archived_log a where a.FIRST_TIME>=to_date('&DT','yyyy-mm-dd HH24:mi:ss')

新增日誌

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56140_690352144.dbf',Options=>dbms_logmnr.new);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56141_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56142_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56143_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56144_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56145_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56146_690352144.dbf',Options=>dbms_logmnr.addfile);

六 挖日誌

exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dictionary.ora');

七 logmnr的資料暫時儲存在一個表裡面，免得再次查詢時候不用再次新增歸檔日誌

create table logmnr_user.tab_logmnr_contents nologging as select * from v$logmnr_contents where 1=2;

insert /*+ append */ into logmnr_user.tab_logmnr_contents select * from v$logmnr_contents;

commit;

八結束日誌挖掘

exec dbms_logmnr.end_logmnr;

---處理資料階段

一 檢測是否發生過DDL

select operation

from LOGMNR_USER.TAB_LOGMNR_CONTENTS

WHERE

SEG_NAME = 'D405' AND

SEG_OWNER = 'FANGCHENG40'

group by operation

二 處理資料

根據新增加的R_ROWID列UPDATE DELETE

declare i int;

begin

i:=0;

for aa in (select replace(replace(SQL_UNDO,'"FANGCHENG40"','"LOGMNR_USER"'),'ROWID','R_ROWID') sql_undo

from LOGMNR_USER.TAB_LOGMNR_CONTENTS

WHERE

SEG_NAME = 'D405' AND

SEG_OWNER = 'FANGCHENG40'

and operation in ('INSERT','UPDATE','DELETE')

and TIMESTAMP>=to_TIMESTAMP('2013-12-02 09:17:00','yyyy-mm-dd hh24:mi:ss')

order by to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') desc) loop

execute immediate replace(aa.sql_undo,';','');

i:=i+1;

end loop;

dbms_output.put_line(to_char(i));

end;

基於LOGMINER 的表DML誤操作恢復

---挖掘過程

---處理資料階段

相關文章