使用 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;