使用 Oracle logminer 挖掘日志恢復誤刪數據以及查找操作者

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

一、案例環境準備
要能使用 logminer 工具，數據庫必須啟用最小補充日志
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

新建一張表 TEST ，插入6行數據做測試

接下來，刪除幾行數據
# 正常刪除
SQL> DELETE FROM TEST WHERE ID=1;
SQL> COMMIT;

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

假設發現刪除的時候已經過去了很長時間，閃回恢復已經無法發揮作用，那么接下來只能挖掘日志了，通過挖掘日志可以得到恢復數據的SQL語句，還能追查是誰在什么時候刪除了數據。

二、確定挖掘其實時間
確定挖掘起始時間通常可以使用一次啊三種方法
1、查詢 DBA_HIST_ACTIVE_SESS_HISTORY , V$SQLAREA 視圖，看是否能找到刪除數據的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、用戶知道大概的刪除時間
如果刪除的時間比較長，SQL已經被從 shared pool 中刷出去了，就查不到了，這時候只能根據用戶的反映確定一個大概的時間。

三、開始挖掘日志
這里我們以 2019-11-02 15:24:45 為挖掘的開始時間，通過這個時間來找出要從哪個日志開始
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
;

得到對應的日志序列號是 49，所以我們就以49號日志為挖掘起點。

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

2、生成數據字典文件，文件路徑為 /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、指定用于存放挖掘數據的表空間，建議單獨建一個表空間
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);

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

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

6、查看挖掘結果
v$logmnr_contents 視圖保存了吃歸檔日志中挖掘出來的數據，包括 REDO SQL 和 UNDO SQL。
v$logmnr_contents 視圖只對當前會話有效，如果數據量比較大的話建議重建成一張普通表，建立索引之后再查詢，這里我們創建新的表 LOG_MNR_TAB 來保存挖掘出來的數據。
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;

把查詢出來的 SQL_UNDO 按順序執行，就能恢復到之前的狀態。
沒太搞清楚的是，有的能看到操作者和機器名有的看不到。

如果發現挖掘的日志不夠多，可以繼續添加繼續挖掘

7、結束日志挖掘
SQL> exec dbms_logmnr.end_logmnr;