Shulou(Shulou.com)06/01 Report--

The boss of the operation and maintenance team called and said that they found that tens of thousands of transactions had been re-pushed once, and it was the third time. The problem was still quite serious. If they wanted to track who did the wrong operation, they had the time period and the table involved, and asked if there was any way to track it.

The database version is 10.2.0.4. The first thing that comes to mind is the audit function, but the helpless database does not open the audit. LogMiner comes to mind again, but I'm not sure if I can find the user and host for the corresponding operation. This question was raised in QQ group, and the answer was that it could be found. At the same time, the SESSION_INFO field in v$logmnr_contents was found in the official document:

As you can see from the information given above, you can track the corresponding user and host information when executing sql.

Here's a simple test, a simple application reference for LogMiner: 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 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 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 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.

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.

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.

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.

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

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

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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.