使用 Oracle logminer 挖掘日志恢復(fù)誤刪數(shù)據(jù)以及查找操作者

案例背景：用戶反映有張表的數(shù)據(jù)總是莫名其妙被刪除了，希望能恢復(fù)數(shù)據(jù)，并找出刪除數(shù)據(jù)的人。

一、案例環(huán)境準(zhǔn)備
要能使用 logminer 工具，數(shù)據(jù)庫(kù)必須啟用最小補(bǔ)充日志
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

新建一張表 TEST ，插入6行數(shù)據(jù)做測(cè)試

接下來(lái)，刪除幾行數(shù)據(jù)
# 正常刪除
SQL> DELETE FROM TEST WHERE ID=1;
SQL> COMMIT;

# 綁定變量方式刪除
SQL> DELETE FROM TEST WHERE ID=:1;
SQL> COMMIT;

假設(shè)發(fā)現(xiàn)刪除的時(shí)候已經(jīng)過(guò)去了很長(zhǎng)時(shí)間，閃回恢復(fù)已經(jīng)無(wú)法發(fā)揮作用，那么接下來(lái)只能挖掘日志了，通過(guò)挖掘日志可以得到恢復(fù)數(shù)據(jù)的SQL語(yǔ)句，還能追查是誰(shuí)在什么時(shí)候刪除了數(shù)據(jù)。

二、確定挖掘其實(shí)時(shí)間
確定挖掘起始時(shí)間通?？梢允褂靡淮伟∪N方法
1、查詢 DBA_HIST_ACTIVE_SESS_HISTORY , V$SQLAREA 視圖，看是否能找到刪除數(shù)據(jù)的SQL
SQL> SELECT SQL_ID,LAST_ACTIVE_TIME,SQL_FULLTEXT FROM V$SQLAREA WHERE UPPER(SQL_TEXT) LIKE 'DELETE%TEST%' ORDER BY LAST_ACTIVE_TIME DESC;

SQL> SELECT HIS.SESSION_ID,HIS.SESSION_SERIAL#,HIS.SQL_EXEC_START,HIS.MACHINE,HIS.PROGRAM,S.SQL_ID,S.SQL_FULLTEXT
FROM DBA_HIST_ACTIVE_SESS_HISTORY HIS, V$SQLAREA S
WHERE
  HIS.SQL_ID=S.SQL_ID
  AND HIS.SQL_OPNAME='DELETE'
ORDER BY SQL_EXEC_START DESC NULLS LAST
;
2、用戶知道大概的刪除時(shí)間
如果刪除的時(shí)間比較長(zhǎng)，SQL已經(jīng)被從 shared pool 中刷出去了，就查不到了，這時(shí)候只能根據(jù)用戶的反映確定一個(gè)大概的時(shí)間。

三、開(kāi)始挖掘日志
這里我們以 2019-11-02 15:24:45 為挖掘的開(kāi)始時(shí)間，通過(guò)這個(gè)時(shí)間來(lái)找出要從哪個(gè)日志開(kāi)始
SQL> SELECT THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,NAME
FROM V$ARCHIVED_LOG
WHERE TO_DATE('2019-11-02 15:24:45', 'YYYY-MM-DD HH24:MI:SS') BETWEEN FIRST_TIME AND NEXT_TIME
;

得到對(duì)應(yīng)的日志序列號(hào)是 49，所以我們就以49號(hào)日志為挖掘起點(diǎn)。

1、設(shè)置 utl_file_dir 參數(shù)，指定一個(gè)目錄用于存放數(shù)據(jù)字典，次參數(shù)是靜態(tài)參數(shù)，設(shè)置之后需重啟數(shù)據(jù)庫(kù)生效。
SQL> alter system set utl_file_dir='/home/oracle/utl_file_dir' scope=spfile;

2、生成數(shù)據(jù)字典文件，文件路徑為 /home/oracle/utl_file_dir/log_mnr_dictionary.log
SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'log_mnr_dictionary.log',dictionary_location => '/home/oracle/utl_file_dir');

3、指定用于存放挖掘數(shù)據(jù)的表空間，建議單獨(dú)建一個(gè)表空間
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('log_mnr_tbs');

4、添加歸檔日志
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'+DATA/racdb/archivelog/2019_11_02/thread_1_seq_49.343.1023295243', Options=>dbms_logmnr.new);

如果有需要的話，可以繼續(xù)添加更多的歸檔日志
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'+DATA/racdb/archivelog/2019_11_02/thread_1_seq_50.344.1023296047', Options=>dbms_logmnr.addfile);

5、開(kāi)始挖掘
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/utl_file_dir/log_mnr_dictionary.log');

6、查看挖掘結(jié)果
v$logmnr_contents 視圖保存了吃歸檔日志中挖掘出來(lái)的數(shù)據(jù)，包括 REDO SQL 和 UNDO SQL。
v$logmnr_contents 視圖只對(duì)當(dāng)前會(huì)話有效，如果數(shù)據(jù)量比較大的話建議重建成一張普通表，建立索引之后再查詢，這里我們創(chuàng)建新的表 LOG_MNR_TAB 來(lái)保存挖掘出來(lái)的數(shù)據(jù)。
SQL> SELECT SCN,TIMESTAMP,OPERATION,SEG_TYPE_NAME,USERNAME,SEG_NAME,OS_USERNAME,MACHINE_NAME,SQL_REDO,SQL_UNDO
FROM LOG_MNR_TAB WHERE TABLE_NAME='TEST' ORDER BY SCN;

把查詢出來(lái)的 SQL_UNDO 按順序執(zhí)行，就能恢復(fù)到之前的狀態(tài)。
沒(méi)太搞清楚的是，有的能看到操作者和機(jī)器名有的看不到。

如果發(fā)現(xiàn)挖掘的日志不夠多，可以繼續(xù)添加繼續(xù)挖掘

7、結(jié)束日志挖掘
SQL> exec dbms_logmnr.end_logmnr;