Oracle Logminer 日志挖掘

生产环境中考虑到数据库的性能问题，很少会打开数据的审计功能，应用层也不会记录SQL的执行信息；但是生产上经常会遇到某张表的某几条被修改掉，但是应用又查不到是哪个接口修改的记录，这时候Logminer 就派上用场了。

Logminer 8i之后的一款免费日志分析工具：通过分析在线日志文件或者归档日志文件，返回数据库DDL/DML操作语句、执行时间、用户等等可以追查的信息，快速定位问题。

使用Logminer 工具，数据库需要开启强制日志和归档模式

数据库配置

# 开启强制日志模式 # 查看当前数据库日志模式 SQL> SELECT supplemental_log_data_min, force_logging FROM v$database; # 如果返回结果为NO，则 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> ALTER DATABASE FORCE LOGGING; # 确认是否已经修改，输出为YES SQL> SELECT supplemental_log_data_min, force_logging FROM v$database; # 切换系统日志 SQL> ALTER SYSTEM SWITCH LOGFILE; # 数据库处于归档模式 SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 107 Next log sequence to archive 109 Current log sequence 109 # 默认归档日志路径 SQL> show parameter db_recover NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4977M # 若数据库处于非归档模式，则需要 # 干净的关闭掉数据库 SQL> shutdown immediate # 打开至mount状态 SQL> startup mount # 设置为归档模式 SQL> alter database archivelog; # 开启数据库 SQL> alter database open;

Logminer 配置

# 首先安装 logminer 使用到的包，将创建用于分析的过程和视图 SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql # 参数配置，用于创建字典文件 SQL> show parameter utl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ utl_file_dir string /u01/app/oracle/logminer # 如果VALUE 为 NULL，那么需要在线修改并重启实例生效 SQL> alter system set utl_file_dir='/u01/app/oracle/logminer' scope=spfile; # 创建字典文件 SQL> CREATE DIRECTORY utlfile AS '/u01/app/oracle/logminer'; SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/u01/app/oracle/logminer'); SQL> !ls /u01/app/oracle/logminer dictionary.ora

对测试数据表进行操作

#  创建临时表 SQL> create table u_logminer.dba_objects as select * from all_objects; # 更新操作 SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER'; STATUS OWNER OBJECT_NAME ------- ------------------------------ -------------------- INVALID U_LOGMINER ALL_OBJECTS INVALID U_LOGMINER ALL_OBJECTS INVALID U_LOGMINER ALL_OBJECTS INVALID U_LOGMINER ALL_OBJECTS # update 更新 SQL> update U_LOGMINER.dba_objects set STATUS = 'VALID' where owner = 'U_LOGMINER'; 4 rows updated. SQL> commit; Commit complete. SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER'; STATUS OWNER OBJECT_NAME ------- ------------------------------ -------------------- VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS # insert 插入 SQL> insert into U_LOGMINER.dba_objects select * from dba_objects where owner = 'U_LOGMINER'; 4 rows created. SQL> commit; Commit complete. SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER'; STATUS OWNER OBJECT_NAME ------- ------------------------------ -------------------- VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS VALID U_LOGMINER ALL_OBJECTS 8 rows selected. # delete 删除 SQL> delete from U_LOGMINER.dba_objects where owner = 'U_LOGMINER'; 8 rows deleted. SQL> commit; Commit complete.

使用Logminer 分析归档日志

# 添加日志文件进行分析，第一个文件 dbms_logmnr.NEW，后面的文件dbms_logmnr.ADDFILE SQL> BEGIN 2 dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_106_dshlmn6r_.arc',options=>dbms_logmnr.NEW); 3 dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_107_dshlmoo2_.arc',options=>dbms_logmnr.ADDFILE); 4 dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_108_dshm5n1x_.arc',options=>dbms_logmnr.ADDFILE); 5 end; 6 / PL/SQL procedure successfully completed. # 开始分析，当前是完整分析日志文件的内容，可以操作时间、SCN等过滤需要分析的内容 SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename =>'/u01/app/oracle/logminer/dictionary.ora'); # 或者 SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName =>'/u01/app/oracle/logminer/dictionary.ora', StartTime =>to_date('2017-8-19 00:00:00','YYYY-MM-DD HH24:MI:SS'),EndTime =>to_date('2017-8-19 17:00:00','YYYY-MM-DD HH24:MI:SS ')); # 或者 SQL> SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName =>'/u01/app/oracle/logminer/dictionary.ora', StartScn>=20, EndScn<= 50); PL/SQL procedure successfully completed. # 结果放在视图中，注意当会话结束，临时表会被删除 SQL> SELECT count(*) FROM v$logmnr_contents; COUNT(*) ---------- 42118 # 将临时表数据备份至物理表中 SQL> create table u_logMIner.logminer_tmp as select * from v$logmnr_contents; Table created.

日志文件分析完成，接下来就看看对表到底做了什么操作

# 根据事务开始时间进行排序，查询数据表的变更记录 SQL > SELECT START_TIMESTAMP,COMMIT_TIMESTAMP,sql_redo,sql_undo,machine_name,os_username,username,table_name FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP; # sql_redo 更改数据的SQL # sql_undo 回滚数据的SQL # machine_name,os_username,username 三位一体定位执行更新的机器、数据库用户名 # 如下： SQL> SELECT sql_redo FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP; SQL_REDO -------------------------------------------------------------------------------- update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'INVALID' where "STATUS" = 'VAL ID' and ROWID = 'AAAD8EAAIAAAAJAAAf'; update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'INVALID' where "STATUS" = 'VAL ID' and ROWID = 'AAAD8EAAIAAAAKHAAv'; ………… # 如下： SQL> SELECT sql_undo FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP; SQL_UNDO -------------------------------------------------------------------------------- update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL ID' and ROWID = 'AAAD8EAAIAAAAJAAAf'; update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL ID' and ROWID = 'AAAD8EAAIAAAAKHAAv'; update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL ID' and ROWID = 'AAAD8EAAIAAAAPoAA0'; ………………

SQL拿出来了，就可以定位问题，但是能不能立刻回滚到生产环境，还需要和业务部门进行沟通。

建议在创建数据库时，就打开强制日志、归档模式，配置 utl_file_dir 参数，避免数据库重启对线上的影像

当然也可以直接下其他环境下的数据库做日志分析工作，有两个点需要注意：数据字典和日志文件，其他和在本机处理没什么两样。

相关资源：七夕情人节表白HTML源码(两款)