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