详解oracle日志挖掘--LogMiner（附实例演示）
概述
相信大家在生产环境总会碰到其他同事删除了一些表数据，但是这个时候却只知道大概时间段，不知道具体做的一些操作，假设归档日志一直有保留，这个时候大家不烦可以考虑用logminer来做下日志挖掘，看下是否能恢复数据？今天就介绍下这方面内容和一个简单的实验，带大家掌握下这一块。
LogMiner相关概念
1、 logminer的作用
Oracle LogMiner是oracle公司从产品8i后提供的一个非常有用的分析工具，使用该工具可以轻松获得oracle在线/归档日志文件中的具体内容，特别是该工具可以分析出所有对于数据库操作的DML和DDL语句，该工具特别适用于调试、审计或者回退某个特定的事务。
LogMiner分析工具实际上是由一组PL/SQL包和一些动态视图组成，它作为Oracle数据库的一部分来发布是一个完全免费的工具。但该工具和其他ORACLE内建工具相比使用起来显得有些复杂，主要原因是该工具没有提供任何的图形用户界面（GUI）。
日志文件中存放着所有进行数据库恢复的数据，记录了针对数据库结构的每一个变化，也就是对数据库操作的所有DML语句进行记录。logminer工具既可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重做日志文件，也可以用来分析其他数据库的重做日志文件。
LogMiner Database Configuration
总的来说。logminer工具的主要用途有：
1）跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。 2）回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。 3）优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。
2、 LogMiner的配置要求
（1）源数据库和分析数据库必须运行在相同硬件平台上；
（2）分析数据库可以是独立数据库或源数据库；
（3）分析数据库的版本不能低于源数据库的版本；
（4）分析数据库与源数据库必须具有相同的字符集。
3、 LogMiner使用注意事项
（1）LogMiner字典必须在源数据库中生成
（2）当分析多个重做日志和归档日志时，它们必须是同一个源数据库的重做日志和归档日志
（3）当分析多个重做日志和归档日志时，它们必须具有相同的resetlogs scn
（4）当分析的重做日志和归档日志必须在ORACLE 8.0版本以上
4、 什么是补充日志，补充日志的作用是什么
重做日志用于实现例程恢复和介质恢复，这些操作所需要的数据被自动记录在重做日志中。但是重做应用可能还需要记载其他列信息到重做日志中，记录其他列的日志过程被称为补充日志。
默认情况下，Oracle数据库没有提供任何补充日志，从而导致默认情况下LogMiner无法支持以下特征：
（1）索引簇、链行和迁移行 （2）直接路径插入 （3）摘取LogMiner字典到重做日志 （4）跟踪DDL （5）生成键列的SQL_REDO和SQL_UNDO信息 （6）LONG和LOB数据类型
5、 激活数据库补充日志
为了充分利用LogMiner提供的特征，必须激活补充日志。
注意：激活时不用重启数据库，数据库联机即可。
1）、检查supplemental logging
select supplemental_log_data_min from v$database;
2）、增加补充日志
alter database add supplemental log data;
如果是YES或者IMPLICT则表明已经生效了，否则需要启动。
说明：
supplemental logging(补充日志)：在通常情况下，redo log只记录进行恢复所必须的信息，但是这些信息对于我们使用redo log进行一些其他应用时是不够的。
例如在redo log中使用rowid唯一标志一行而不是通过Primary key,如果我们在另外的数据库分析这些日子并想重新执行某些dml时就可能会有问题，因为不同数据库其
rowid代表的内容是不同的，在这时候就需要一些额外的信息（columns）加入redo log,这就是supplementa logging。supplemental logging分为两个级别database_level和table_level,默认不开启，建议开启数据库级别。
LogMiner使用详解
LogMiner两种使用类型，一种是使用源数据库的数据字典分析DML操作，另一种是提取LogMiner数据字典到字典文件分析DDL操作。
Decision Tree for Choosing a LogMiner Dictionary
1、 安装LogMiner
在使用LogMiner之前需要确认Oracle是否带有进行LogMiner分析包，一般来说Windows操作系统Oracle10g以上都默认包含。如果不能确认，可以DBA身份登录系统，查看系统中是否存在运行LogMiner所需要的dbms_logmnr、dbms_logmnr_d包，如果没有需要安装LogMiner工具，必须首先要运行下面这样三个脚本：
$ORACLE_HOME/rdbms/admin/dbmslm.sql:DBMS_LOGMNR $ORACLE_HOME/rdbms/admin/dbmslmd.sql:DBMS_LOGMNR_D
这两个脚本必须均以DBA用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。
创建完毕后将包括如下过程和视图：
2、 相关视图
select * from v$logmnr_disctionary; select * from v$logmnr_logs; select * from v$logmnr_contents;
3、 使用源数据库的数据字典（Online catalog）分析DML操作
1）、开启补充日志
alter database add supplemental log data; select supplemental_log_data_min from v$database;
2）、建立日志分析列表
--第一次添加
execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.new)
--第二次添加
execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.addfile)
3）、启动分析
execute dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog)
//execute dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog,startscn=>123,endScn=>124);
//exec dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog,
starttime=> to_date('2019-02-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),
endtime=> to_date('2019-02-03 00:00:00','YYYY-MM-DD HH24:MI:SS')); --有条件分析4）、查询日志分析结果
select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;
5）、结束分析
execute dbms_logmnr.end_logmnr;
4、 使用LogMiner数据字典到字典文件分析DDL操作
1）、提取logminer字典
设置一个字典文件路径:
show parameter utl_file_dir; --需要重启DB alter system set utl_file_dir='/oracle' scope=spfile;
创建一个数据字典文件
exec dbms_logmnr_d.build('dict.ora','/oracle');2）、建立日志分析列表
--第一次添加
execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.new)
--第二次添加
execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.addfile)
//execute dbms_logmnr.add_logfile('日志文件',dbms_logmnr.addfile)3）、启动分析
exec dbms_logmnr.start_logmnr(DictFileName=>'/oracle/dict.ora') --无条件分析
//exec dbms_logmnr.start_logmnr(DictFileName=>'/oracle/dict.ora',startscn=>123,endScn=>124); --有条件分析
//exec dbms_logmnr.start_logmnr(DictFileName=>'/oracle/dict.ora',
starttime=> to_date('2019-02-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),
endtime=> to_date('2019-02-03 00:00:00','YYYY-MM-DD HH24:MI:SS')); --有条件分析4）、查询日志分析结果
select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;
5）、结束分析
dbms_logmnr.end_logmnr;
实例演示（分析生产系统表数据丢失的原因）
由于某生产系统test用户模式下test表数据丢失，需分析丢失原因，下面是整个恢复步骤）
1、模拟实验环境
create tablespace test datafile '/home/oracle/app/oracle/oradata/nwppdb/test.dbf' size 30m autoextend off; create user test01 identified by test01 default tablespace test; grant dba to test01; conn test01/test01; create table test01(id number(10),name varchar2(10)); insert into test01 values(1,'itpux01'); insert into test01 values(2,'itpux02'); insert into test01 values(3,'itpux03'); insert into test01 values(4,'itpux04'); insert into test01 values(5,'itpux05'); commit; alter system switch logfile; select * from test01.test01;
2、删除数据
drop table test01; conn / as sysdba; alter system switch logfile; alter system checkpoint;
3、查看归档日志
archive log list
这里我拿几个归档日志出来分析
/home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/o1_mf_1_1775_g997b43w_.arc /home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/ o1_mf_1_1776_g99rj49r_.arc /home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/ o1_mf_1_1777_g99rl39o_.arc /home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/ o1_mf_1_1778_g99rm7q4_.arc
4、安装logminer
@$ORACLE_HOME/rdbms/admin/dbmslm.sql @$ORACLE_HOME/rdbms/admin/dbmslmd.sql @$ORACLE_HOME/rdbms/admin/dbmslms.sql
5、使用LogMiner建立日志分析列表
5.1、设置一个字典文件路径:
show parameter utl_file_dir; --需要重启DB alter system set utl_file_dir='/home/oracle' scope=spfile;
5.2、创建一个数据字典文件
exec dbms_logmnr_d.build('dict.ora','/home/oracle');
5.3、建立日志分析列表
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/o1_mf_1_1775_g997b43w_.arc',options=>dbms_logmnr.new); execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/o1_mf_1_1776_g99rj49r_.arc',options=>dbms_logmnr.addfile); execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/o1_mf_1_1777_g99rl39o_.arc',options=>dbms_logmnr.addfile); execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oracle/flash_recovery_area/NWPPDB/archivelog/2019_03_22/o1_mf_1_1778_g99rm7q4_.arc',options=>dbms_logmnr.addfile);
6、使用LogMiner数据字典到字典文件分析DDL操作
6.1、启动分析
exec dbms_logmnr.start_logmnr(DictFileName=>'/home/oracle/dict.ora'); --无条件分析
6.2、查询日志分析结果
这里注意一个小技巧，先将查询出来的内容创建个临时表，然后就可以关闭分析了，比较不耗资源。
create table test01.logmnr_temp nologging as select * from v$logmnr_contents; select username,scn,timestamp,sql_redo,sql_undo from logmnr_temp where seg_owner='TEST01' and seg_name='TEST01';
因为DDL语句没法UNDO，所以就显示成这样了。
6.3、结束分析
做完一定要记得结束分析！！
exec dbms_logmnr.end_logmnr;
上面的实验主要介绍整体通过归档日志来挖掘之前的一些信息，建议大家抽空去做一下相关的实验，不过我一般是比较习惯用来分析DML相关操作，会比较方便，生产环境也比较少用logminer数据字典到字典文件分析这种方式，毕竟要重启数据库来配置utl_file（如果之前没配置的话），今天因为是测试环境，所以就用了这个方式做一下演示。
后面会分享更多关于DBA方面内容，感兴趣的朋友可以关注下！
