運維組的老大打電話說，他們發(fā)現(xiàn)有幾萬筆業(yè)務被重新推送了一遍，而且是第三次了，問題還是挺嚴重的，想要追蹤是誰做的誤操作，他們有時間段和涉及的表，問有沒有辦法追蹤到。

創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網綜合服務，包含不限于做網站、成都網站設計、瑯琊網絡推廣、微信小程序、瑯琊網絡營銷、瑯琊企業(yè)策劃、瑯琊品牌公關、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)公司為所有大學生創(chuàng)業(yè)者提供瑯琊建站搭建服務，24小時服務熱線：18982081108，官方網址：www.cdcxhl.com

數據庫版本為10.2.0.4。首先想到的是審計功能，但是無奈數據庫沒有開審計。再次想到的是日志挖掘(LogMiner)，但是不確定能不能找到對應操作的用戶和主機。在QQ群里提出了這個問題，得到的答案是可以找到，同時也在官方文檔中找到了v$logmnr_contents中對就的SESSION_INFO字段：

從上面給出的信息可以看出，可以跟蹤到執(zhí)行sql時對應的用戶和主機信息。

下面做一個簡單的測試，關于LogMiner的簡單應用參考：http://hbxztc.blog.51cto.com/1587495/1871934

SQL> exec dbms_logmnr.add_logfile(LOGFILENAME=>'/u01/app/oracle/flashback_area/MYDB/archivelog/2017_04_21/o1_mf_1_4_dhn2m29n_.arc',OPTIONS=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(DICTFILENAME=>'/home/oracle/logminer/dictionary.ora');

PL/SQL procedure successfully completed.

SQL> col table_name for a10 
SQL> col session_info for a180
SQL> set linesize 200
SQL> select table_name,session_info from v$logmnr_contents where table_name='T1' and rownum < 5;

TABLE_NAME SESSION_INFO
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
T1	   login_username=ZX client_info= OS_username=oracle Machine_name=rhel5 OS_terminal=pts/0 OS_process_id=2596 OS_program_name=sqlplus@rhel5 (TNS V1-V3)
T1	   login_username=ZX client_info= OS_username=oracle Machine_name=rhel5 OS_terminal=pts/0 OS_process_id=2596 OS_program_name=sqlplus@rhel5 (TNS V1-V3)
T1	   login_username=ZX client_info= OS_username=oracle Machine_name=rhel5 OS_terminal=pts/0 OS_process_id=2596 OS_program_name=sqlplus@rhel5 (TNS V1-V3)
T1	   login_username=ZX client_info= OS_username=oracle Machine_name=rhel5 OS_terminal=pts/0 OS_process_id=2596 OS_program_name=sqlplus@rhel5 (TNS V1-V3)

從上面的查詢可以看出可以從日志中挖掘出用戶和主機信息。

v$logmnr_contents:http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1154.htm#REFRN30132

LogMiner:http://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#sthref1875

如果遇到USERNAME和SESSION_INFO為NULL或UNKNOWN參考如下：

Column USERNAME And SESSION_INFO Are UNKNOWN Or NULL In V$LOGMNR_CONTENTS (文檔 ID 110301.1)

CAUSE

1. If supplemental logging was not active at the time when the redo records were created, then LogMiner won't be able to obtain all the required information. The Oracle Database Utilities manual mentions:

   By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging prior to generating log files which will be analyzed by LogMiner.

   So, we have to enable supplemental logging by using a SQL statement similar to the following:

   SQL> CONNECT / AS SYSDBA
   SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

   
   Then the information necessary to populate the USERNAME and SESSION_INFO columns will be stored in the redo stream.
   

2. The redo stream does not contain the USERNAME and SESSION_INFO data for every transaction. This information is only stored for the first transaction executed in the user's session. So in order to be able to see this information in V$LOGMNR_CONTENTS, all the redo generated during the entire session must be added to the mining session. Should this not be done, then the USERNAME and SESSION_INFO columns will remain empty.
   

3. LogMiner was first available in Oracle8i. If the COMPATIBLE instance parameter is set to a value lower than 8.1.0 you will not have access to its full functionality.
   

4. In Oracle9i and lower releases of Oracle, the TRANSACTION_AUDITING instance parameter is set to TRUE by default. This causes the generation of a redo record containing the user logon name, username, session ID, and some operating system and client information. For each successive transaction in the session, Oracle will store only the session ID. These session IDs are linked back to the first record to retrieve user and session information.
   
   When TRANSACTION_AUDITING is set to FALSE, this redo record is not written and the user information is not available to LogMiner.

SOLUTION

This can result from your database parameter settings and also from the method you are using to mine redo logs using LogMiner.

1. Ensure that database was in minimum supplemental logging at the time that the redo information was created:

   SQL> SELECT name, supplemental_log_data_min FROM v$database;
   
   NAME                           SUPPLEME
   ------------------------------ --------
   M10202WA                       YES

2. Ensure that all archive redo logs containing the necessary redo information have been added to the LogMiner session.
   

3. Ensure that the COMPATIBLE initialization parameter is set to 8.1.0 or higher.

   SQL> show parameter compatible
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ----------
   compatible                           string      10.2.0.2.0

4. For Oracle8i and Oracle9i only: ensure that the TRANSACTION_AUDITING instance parameter is set to TRUE (default).

   SQL> show parameter transaction_auditing
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ----------
   transaction_auditing                 boolean     TRUE