玖叶教程网

前端编程开发入门

2024年04月23日

详解oracle日志挖掘--LogMiner(附实例演示)

概述

相信大家在生产环境总会碰到其他同事删除了一些表数据，但是这个时候却只知道大概时间段，不知道具体做的一些操作，假设归档日志一直有保留，这个时候大家不烦可以考虑用logminer来做下日志挖掘，看下是否能恢复数据？今天就介绍下这方面内容和一个简单的实验，带大家掌握下这一块。

LogMiner相关概念

1、 logminer的作用

Oracle LogMiner是oracle公司从产品8i后提供的一个非常有用的分析工具，使用该工具可以轻松获得oracle在线/归档日志文件中的具体内容，特别是该工具可以分析出所有对于数据库操作的DML和DDL语句，该工具特别适用于调试、审计或者回退某个特定的事务。

LogMiner分析工具实际上是由一组PL/SQL包和一些动态视图组成，它作为Oracle数据库的一部分来发布是一个完全免费的工具。但该工具和其他ORACLE内建工具相比使用起来显得有些复杂，主要原因是该工具没有提供任何的图形用户界面（GUI）。

日志文件中存放着所有进行数据库恢复的数据，记录了针对数据库结构的每一个变化，也就是对数据库操作的所有DML语句进行记录。logminer工具既可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重做日志文件，也可以用来分析其他数据库的重做日志文件。

总的来说。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操作。

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方面内容，感兴趣的朋友可以关注下！

标签：oraclenumber类型 

作者:gojiuye , 分类:技术精选 , 浏览:15 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言