Tracking table change records without auditing enabled by Oracle

OPS Group's boss called and said they found tens of thousands of business was re-pushed again, and it is the third time, the problem is quite serious, want to track who did the misoperation, they have time period and the table involved, ask there is no way to track.

The database version is 10.2.0.4. The first thought is the audit function, but helpless database does not open audit. The second thought is log mining (Logminer), but I'm not sure if we can find the user and host for the corresponding operation. In the QQ group raised this question, the answer is can be found, but also in the official documents found in the v$logmnr_contents in the Session_info field:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/92/08/wKiom1j6tL3yvSJRAAFboRbAd2k773.png "title=" 1.png "alt=" Wkiom1j6tl3yvsjraafborbad2k773.png "/> From the information given above can be traced to the execution of SQL when the corresponding user and host information.

Here is a simple test for a simple application of Logminer reference: 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);P l/sql procedure successfully  completed. SQL&GT;&NBSP;EXEC&NBSP;DBMS_LOGMNR.START_LOGMNR (dictfilename=> '/home/oracle/logminer/dictionary.ora ');P L/ Sql procedure successfully completed. sql> col table_name for a10 sql> col session_info for  a180sql> set linesize 200sql> 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 [email protected]  (TNS  V1-V3) T1   login_username=zx client_info= os_username=oracle machine_name=rhel5  OS_terminal=pts/0 OS_process_id=2596 [email protected]  (TNS&NBSP;V1-V3) t1    login_username=zx client_info= os_username=oracle machine_name=rhel5 os_ terminal=pts/0 os_process_id=2596 [email protected]  (TNS&NBSP;V1-V3) T1    login_username=zx client_info= os_username=oracle machine_name=rhel5 os_terminal=pts/0  OS_process_id=2596 [email protected]  (TNS&NBSP;V1-V3)

You can see from the above query that the user and host information can be mined from the log.

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

If you encounter username and session_info as null or unknown refer to the following:

Column USERNAME and Session_info is UNKNOWN Or NULL in v$logmnr_contents (document ID 110301.1)

Cause

2. If Supplemental logging is not active at the time when the redo records were created and then Logminer won ' t be able To obtain all the required information. The oracle database Utilities manual mentions:
   

   by default, Oracle Database does not prov IDE any supplemental logging, which means this by default Logminer are not usable. Therefore, must enable at least minimal supplemental logging prior to generating log files which would be analyzed by L Ogminer.

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

   SQ l> connect/as SYSDBA
   sql> ALTER DATABASE ADD supplemental LOG DATA;

   
   Then the information necessary to populate the  USERNAME  and  session_info  columns'll be stored in the redo stream.
   
   

4. The redo stream does not contain the USERNAME and session_info data for every transaction. This information was only stored for the first transaction executed in the user's session. So-in order-to-be-able to-see-Information- v$logmnr_contents, all the redo generated during the entire SES Sion must is added to the mining session. Should this is not being done, then the USERNAME and session_info columns would remain empty.
   
   

6. Logminer was first available in oracle8i. If the COMPATIBLE instance parameter is set to a value of lower than 8.1.0 you'll not have access to their full func Tionality.
   
   

8. In Oracle9i and lower releases of Oracle, the transaction_auditing instance parameter are set to TRUE by default. This causes the generation of a redo record containing the user logon name, username, session ID, and some operating syste M and client information. For each successive transaction in the session, the Oracle would store only the session ID. These session IDs is linked the first record to retrieve user and session information.
   
   When transaction_auditing are set to FALSE, this redo record is not written and the user information are not Availa ble to Logminer.

Solution

This can result from your database parameter settings and also from the method of you is using to mine redo logs using Logmi Ner.

2. Ensure that database is in minimum supplemental logging on the time that the redo information is created:
   

   sql> SELECT name, supplemental_log_data_min from V$database;
   
   NAME Suppleme
   ------------------------------ --------
   M10202wa YES

4. Ensure that all archive redo logs containing the necessary redo information has been added to the Logminer session.
   
   

6. Ensure that the COMPATIBLE initialization parameter are set to 8.1.0 or higher.
   

   Sql> Show parameter compatible
   
   name                                   type        value
   ------------------- --------------------------------------
   compatible                             string      10.2.0.2.0

8. For Oracle8i and Oracle9i Only:ensure, the transaction_auditing instance parameter is set to TRUE (default).
   

   Sql> Show Parameter transaction_auditing
   
   NAME TYPE VALUE
   ------------------------------------ ----------- ----------
   Transaction_auditing Boolean TRUE

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1918407

Oracle does not turn on Audit tracking table change records