阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle未开启审计情况下追踪表变更记录

195次阅读
没有评论

共计 4713 个字符,预计需要花费 12 分钟才能阅读完成。

运维组的老大打电话说,他们发现有几万笔业务被重新推送了一遍,而且是第三次了,问题还是挺严重的,想要追踪是谁做的误操作,他们有时间段和涉及的表,问有没有办法追踪到。

Oracle 数据库版本为 10.2.0.4。首先想到的是审计功能,但是无奈数据库没有开审计。再次想到的是日志挖掘 (LogMiner),但是不确定能不能找到对应操作的用户和主机。在 QQ 群里提出了这个问题,得到的答案是可以找到,同时也在官方文档中找到了 v$logmnr_contents 中对就的 SESSION_INFO 字段:

Oracle 未开启审计情况下追踪表变更记录 从上面给出的信息可以看出,可以跟踪到执行 sql 时对应的用户和主机信息。

下面做一个简单的测试,关于 LogMiner 的简单应用参考:http://www.linuxidc.com/Linux/2017-02/140525.htm

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

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-04/143045.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4713字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中