葵花宝典--Logmnr

日志挖掘

9.1 日志中数据用途

所有对用户数据以及数据字典的改变全部被保存在联机日志中。当然nologging，insert/*+append+/情况比较特殊除外，因此归档日志可以用来做数据库的恢复。为了数据库中的日志含有用的信息，一般需要启用最小化的追加日志的属性(alter database add supplemental log data;)

 

以下列举了日志的常用功能：

1）  恢复数据库的逻辑灾难。比如，在增加员工工资时，应该增加10%，但是却增加了100%，这就需要进行基于时间点逻辑的恢复。提出日志中的undo SQL。

2）  恢复用户的错误操作：比如错误删除了数据，错误的更新了数据，总之错误的执行了DML操作。

3）  决定了如何在事务级别进行粒度很细地恢复。如果我们完全知道事务的依赖性，那么我们可以做基于表的任何一系列操作的回滚。而通常的方法是，我们先把表回滚，然后再开始应用归档日志。

4）  趋势分析。分析哪张表的DML操作比较频繁。为调优提供支持。

5）  进行事后审计，对操作人以及动作进行审计

 

安装logmnr工具

要安装LogMiner工具，必须首先要运行下面这样两个脚本：

1）$ORACLE_HOME/rdbms/admin/dbmslm.sql

2）$ORACLE_HOME/rdbms/admin/dbmslmd.sql.

    这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

 

 

日志信息的访问

   Oracle通过logminer工具访问日志。该工具是oracle server软件的一部分。Logminer通过固定视图v$logmnr_contents视图去展现日志中信息。

   V$logmnr_contents视图包含的日志信息如下：

1）  数据库改变的操作类型OPERATION：insert,delete,update,DDL

2）  操作时的SCN（scn列）

3）  该操作提交时的SCN （commit scn）

4）  操作所属的事务：XIDUSN, XIDSLT, and XIDSQN

5）  操作所改变对象的表名和模式名：（seg_name,seg_owner）

6）  发出该操作的用户名：（username）

7）  重新构造生成该条日志的SQL,如果原SQL中有密码，那么密码是加密的。（sql_redo）

8）  构造（7）中回退的SQL。如果操作为DDL那么该列为空。(sql_undo)

在logminer中生成的各列的信息都是用唯一的数字信息标识的。如果要显示为用户定义的名字，那么就需要数据字典。DBMS_LOGMNR_D.BUILD可以生成数据字典。

9.2 联机日志和数据字典文件

在使用logmnr之前，我们需要搞清楚logmnr如何使用日志文件和字典文件。这样可以使我们得到更加精确的结果，更有效的使用系统资源。

9.2.1 日志文件

     当我们使用logmnr时，我们必须提供日志文件让logmnr分析，logmnr分析后，返回v$logmnr_contents视图。我们可以通过查看v$logmnr_contents视图去得到挖掘日志后的有用信息。

1）  redo logs必须是8.0以后版本。Logmnr功能在9.01以后版本中才引入的。

2）  9.2版本开始支持long和Lob版本

3）  Logmnr运行的数据库的字符集要与产生日志的数据库字符集一致

4）  logmnr日志分析过程中需要的字典文件需要从生成日志文件的数据库中生成。

5）  如果数据字典是文本文件(flat file)或者数据字典是保存在联机日志中的，那么我们将要分析的日志文件可以为：运行logmnr数据库的日志文件，也可以是来自于其它数据库的日志文件。

6）  如果使用联机目录(online catalog)作为logmnr的数据字典，那么我们只能分析来之运行logmnr数据库的日志文件。

Logmnr运行的硬件平台需要与分析日志产生的运行平台需要一致，但是他们并不需要相同的系统。

在运行logmnr的时候，指定正确的日志非常重要，如果你省略了你想要的包含重要数据的日志，那么在分析v$logmnr_contents日志的时候，可能会得出不正确的结果。

7）  查看当前正在被分析的日志通过视图：v$logmnr_logs视图查看。

9.2.2 字典选项

    为了完整的对日志文件进行转换，我们需要使用数据字典文件。利用字典文件可以转换内部的对象标识符与实际对象名。

比如：我执行如下SQL: INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);

使用logmnr分析后将显示如下：

insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),

hextoraw('c306'));"

logmnr字典文件，标识了数据库的相关信息。它是用来匹配字典文件与所需要创建日志的匹配。如果不匹配的话，日志分析将失败。

字典文件与要分析的日志文件必须有相同的数据库字符集，也必须来自同样的数据库。该字典一旦提取生成出来，我们就可以用它在其他数据库而不一定是原数据库进行数据库日志挖掘。

提前生成一个字典文件的好处为：有可能数据库目前的字典只包含最近的数据库对象的定义了，而之前的数据库对象的定义已经不存在，或者是被重新创建了。数据库对象删除后重新创建的object_id是不一样的。

 

Logmnr对源字典提供三种选择：

提取数据字典到数据文件(extracting the dictionary to a flat file)

提取字典到日志文件（extracting the dictionary to the redo logs）

使用联机目录（using online catalog）

 

  1、生成字典文件到文本文件

当字典文件为文本文件，和包含在redo logs的日志文件相比，占用更少的系统资源。一般建议定期备份该字典文件以便对老的日志的分析。

           为了提取数据字典文件为文本文件，那么需要使用dbms_logmnr_d.build的store_in_flat_file属性。

            在生成flat文件过程中，不能有任何的DDL操作。

       生成文本文件的步骤;

1）  设置参数UTL_FILE_DIR = /oracle/database，注意该文件夹的权限以及重新启动数据库以便该参数设置生效。

2）  执行oracle包，生成oracle字典文件

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/',

OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

           也可以只指定文件名以及文件路径，不指定STORE_IN_FLAT_FILE，结果是一样。

 

    2、生成字典文件到日志文件

          为了让生成字典文件到日志文件，数据库必须打开并且运行在归档日志模式。当数据字典文件生成到日志文件过程中，任何的DDL操作是不允许的。这就保证了字典文件生成与数据库的一致性。而一致性在字典生成到文本文件里是无法保证的。

          执行如下语句实现把字典文件提取到日志文件中，而无需指定文件名与路径：

          EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

          提取字典文件到日志文件中占用数据库资源，但是你把这个提取操作限制在非高峰时期，那么这就不是个问题了。而且该操作要比把字典生成到文本文件中要快。根据字典文件的大小的不同，可能该字典包含多个日志文件。加入日志已经归档，我们通过查询视图而得知字典文件的开始和结束日志序号。

       SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN= 'YES';

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

最后就是建议能够定期备份该字典文件。

 

    3、使用联机目录

          为了让logmnr使用数据库正在使用的字典文件，当启动logmnr的时候，需要指定联机目录作为源字典(source dictory)

           执行如下语句：

           EXECUTE

DBMS_LOGMNR.START_LOGMNR

(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

         使用联机目录，意思就是我们不需要提取数据字典到文本文件或者是日志文件中。此外可以使用联机目录去分析联机日志。还有使用它分析在同一个系统上产生的归档日志。

          联机目录包含了大量的数据库信息，也是一种最快的分析数据库日志的方法。

          因为DDL语句对数据库对象的改变在日志中只有很少的记录，因此日志挖掘中只提供少量的分析信息。

          使用联机目录分析，只能挖掘最近的数据库对象的信息，而对以前的老的对象，或者是归档日志中的对象目前已经不存在或者改变的，只有使用以前的方法去分析。使用前两种方法分析的前提是：含有以前提取的数据字典文件的备份。

9.3 跟踪DDL

当启动logmnr时，Logmnr自动从源字典建立自己的数据字典。

当使用文本文件或者日志文件作为字典文件时，

使用

EXECUTE DBMS_LOGMNR.START_LOGMNR

(OPTIONS=>DBMS_LOGMNR.DDL_DICT_TRACKING);对执行的DDL进行跟踪，默认情况下，这种跟踪是关闭的。

 

注意：应该明白logmnr内部的数据字典和包含在文本文件以及日志文件中的logmnr数据字典是不一样的。Logmnr会更新其内部的数据字典，但是logmnr不会更新已经保存在文本文件或者日志文件中的数据字典。

9.4 logminer建议和限制

9.4.1 建议

1）数据库应该单独提供logminer使用的表空间。默认情况下logmnr使用的是system表空间。而使用EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$')该语句会重新在logmnrts$表空间中重新创建logmnr相关的表。

注意dbms_logmnr包中含有两个过程：

2）SQL> desc DBMS_LOGMNR_D

PROCEDURE BUILD

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 DICTIONARY_FILENAME          VARCHAR2               IN     DEFAULT

 DICTIONARY_LOCATION          VARCHAR2               IN     DEFAULT

 OPTIONS                        NUMBER                  IN     DEFAULT

PROCEDURE SET_TABLESPACE

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 NEW_TABLESPACE                 VARCHAR2               IN     DEFAULT

 DICTIONARY_TABLESPACE         VARCHAR2               IN     DEFAULT

 SPILL_TABLESPACE               VARCHAR2               IN     DEFAULT

 

对BUILD包的解释：

1、该包用于对字典文件的创建，

DICTIONARY_FILENAME字典文件名，

DICTIONARY_LOCATION为字典文件的路径，

而options为字典文件的类型，数据字典存储在文本文件中还是在日志文件中（store_in_flat_file,store_in_redo_logs）。

2、在下列情况下，执行该包会出现异常：

Ora-1308:UTL_FILE_DIR系统参数没有设置。

Ora-1336:目录位置不存在

           UTL_FILE_DIR没有对字典路径设置为具有访问权限。

           字典文件为只读。

3、其他注意事项：

   如果数据库在进行任何的DDL操作，那么build过程无法执行。

 

   执行logmnr存储过程的数据库必须处于mount或者是open状态。（个人测试好像只有open才能执行build的操作，而start_logmnr的操作在mount阶段是可执行的）。

 

   在执行build操作时，为了监控建立数据字典的进度，执行SET SERVEROUTPUT ON

 

   当建立文本文件的字典文件时，存储过程会扫描整个数据库，并且将表的内容存入文本文件。在建立文本的字典文件时，需要满足如下条件：

   创建字典文件的数据库必须就是日志将要被分析的数据库。

   在参数文件中必须设置utl_file_dir参数。

   在创建字典文件期间，确保没有任何DDL操作，否则创建的字典文件就不是数据字典的一个快照。在创建字典文件期间是允许执行DDL操作的。

 

   当建立日志文件的字典文件时，需要满足如下条件：

   Supplement logging功能必须启用，以能够使数据库日志中包含足够有用的信息。

   该过程必须在oracle9i或者是以后的版本中运行。

   必须启用归档模式。

   使用oracle 9i的兼容参数compatibility

   生成的字典文件以及需要分析的日志必须为同一数据库。

    

对set_tablespace包的解释

    在默认情况下，logmnr相关的表都是存放在系统sys表空间中的。我们使用该存储过程可以将相关表转移到其他表空间中。

NEW_TABLESPACE 一个已经存在的表空间名字，logmnr表将创建在该表空间中。如果想全部在该指定表空间中重新创建所有的logmnr表，则只需指定这一个参数即可。       

DICTIONARY_TABLESPACE  

SPILL_TABLESPACE       

   

    使用注意事项：

    在运行该过程时，一是不能有logmnr进程正在跑，也不能在跑该进程之前有非法中断的logmnr进程。

    该过程可以执行多次，但是oracle也不推荐和保证这种操作的安全性。

    将logmnr视图不放入系统表空间，可能会提高系统的性能。

9.4.2 限制

1）以下是不支持的

   简单或者是嵌套的抽象类型

   集合（嵌套表和数组）

   引用对象类型

   索引组织表

   利用簇键创建一个表

 2）logmnr可以运行在8.1和以后版本中，但是我们可以8.0以后版本中的日志。可以从日志中获取的信息决定于日志的版本，而不是正在使用的数据库的版本。

9.5 挖掘数据返回过滤

Logmnr可以在v$logmnr_contents中产生大量的信息。我们可以利用一些方法可以过滤。掉一些信息。

1）  只返回已经提交的数据

    EXECUTE

DBMS_LOGMNR.START_LOGMNR

(OPTIONS=>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

过滤掉正在执行或者是已经回滚的事务的操作。

默认情况下，返回的v$logmnr_contents数据是按照进入日志文件的顺序进行排序的。而进行上述设置后，返回是按照scn号进行排序。SCN号顺序是正常事务恢复的顺序。

2）  跳过损坏的日志

EXECUTE

DBMS_LOGMNR.START_LOGMNR

(OPTIONS =>DBMS_LOGMNR.SKIP_CORRUPTION);

3）  根据Time/SCN过滤

EXECUTE DBMS_LOGMNR.START_LOGMNR(

DICTFILENAME => '/oracle/dictionary.ora',

STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),

ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

 

EXECUTE DBMS_LOGMNR.START_LOGMNR(

DICTFILENAME => '/oracle/dictionary.ora',

STARTSCN => 100,

ENDSCN => 150);

该时间和SCN是指信息在日志中产生的时间，信息产生时的SCN。

9.6 访问logmnr

相关视图

1）  V$LOGMNR_CONTENTS

显示对用户和表的相关的改变信息

2）  V$LOGMNR_DICTIONARY

如果使用flat file的数据字典，那么该视图会显示和该字典相关的数据库信息。

3）  V$LOGMNR_LOGS

相关涉及的日志信息，一个日志文件一条记录。

4）  V$LOGMNR_PARAMETERS

5）  DBMS_LOGMNR.MINE_VALUE（redo_value/undo_value,schema.tablename.column）返回相应的string格式的值。

该函数如下两种情况下返回null

schema.tablename.column在redo/undo列中不存在

schema.tablename.column在redo/undo中值为null

返回日期的形式为：(DD-MON-YYYY HH24:MI:SS.SS)，无论是否设置了日期的format。

   

Dbms_logmnr. COLUMN_PRESENT的参数同mine_value。

如果该列存在于redo/undo,那么Dbms_logmnr. COLUMN_PRESENT返回1，否则返回0

9.7 追加补足日志

日志为了实例恢复和介质恢复而存在的。

以下情况，需要追加日志：

1）  当重新构建生成的SQL，需要在另外一个数据库执行。通过主键去更新数据库，而不是通过rowid。而logmnr一般是通过rowid去更新记录的。主键在日志中并不记录，除非主键被更新才会记录。

2）  更加有效的跟踪行的改变，并不只记录修改的列，而是记录所有的列。

 

数据库默认并不提供追加日志的功能，而如果想充分使用logmnr的功能，需要使用追加日志的功能。如果在数据库级别启用该功能，那么性能会受影响的。

9.7.1 数据库级别补足日志

有两种追加日志的方式：minimal and identification key logging。

 

Minimal日志的形式，只记录最小数量的信息，以便于logminer去识别，分组，合并DML的redo操作。它可以保证logmnr可以识别链接行以及聚簇对象。在大多数情况下，你最少可以启动该补足日志形式。

使用如下语句打开mininal logging模式：ALTER DATABASE ADD SUPPLEMENTAL LOG DATA；

在oralce9.01 mininal日志是默认模式，而在9.2下补足日志功能是关闭的。

 

 

identification key logging在更新时，记录所有的主键或者是唯一约束。通过这种方式，应用程序可以通过逻辑上识别更新的行，而不需要通过rowid。

该种模式可以用于逻辑备用数据库上，比如，该种形式记录的日志，需要在其他的数据库上执行。

执行如下语句启用supply logging功能：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

上述语句，直接导致全数据库范围内，无论主键是否改变，都在日志中记录主键。

如果一个表没有主键，但是有非空的唯一约束，那么将使用该非空的唯一约束的索引代替主键。

     如果一个表没有主键也没有非空的唯一约束，那么将记录所有除long,lob类型的所有列的值。因此如果要启动identification key logging数据库属性，那么所有的表最好建立主键或者是唯一约束。

 

注意：无论identification key logging是否启用，Logmnr挖掘的日志中都包含rowid条件。我们可以过滤掉该条件。

 

停止追加日志功能：

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

9.7.2 标识键日志注意点

1）对于delete日志的记录并不需要identification key logging模式，delete日志是自动记录全部的列才能标识一列。

2）如果在数据库打开的情况下，启用identification key logging，所有在游标缓冲区中的DML操作的游标，全部变为无效。这可能导致性能上的影响，直到该缓冲重新生成。

9.7.3表级别的追加日志

表级别的追加的日志，使用日志文件组记录追加的日志。有两种类型的表级别的追加日志形式：

1）  无条件的日志组 无论特定的列是否被更新都记录特定的列。

ALTER TABLE scott.emp

ADD SUPPLEMENTAL LOG GROUP emp_parttime (empno, ename, deptno) ALWAYS;

2）  有条件的日志组 只有指定的一个列被更新，才记录指定的日志组

ALTER TABLE scott.emp

ADD SUPPLEMENTAL LOG GROUP emp_fulltime (empno, ename, deptno);

 

使用表级别的追加日志注意：

1）  一个表的列可以属于多个日志组，但是其只能被记录一次。

2）  联机日志中并不包含一个列属于哪个日志组中的信息，

3）  如果一个列同时在有条件和无条件的日志，那么oracle只记录无条件的日志组中。

9.8 Logminer会话信息

注意：logmnr为sys用户所有，那么

9.8.1 执行初始化活动

1）设置强制日志模式

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

2）在特定的表空间中重新创建相应的logmnr系统表

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(’logmnrts$’);

9.8.2 提取数据字典

提取数据字典，请参照9.2.2

9.8.3 指定要分析的日志

1）mount或者umount一个instance

2）添加日志

EXECUTE

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log1.f',

OPTIONS => DBMS_LOGMNR.NEW);

 

EXECUTE

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f',

OPTIONS => DBMS_LOGMNR.ADDFILE);

该包的options参数是可选选项

 

删除日志

EXECUTE

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f',

OPTIONS => DBMS_LOGMNR.REMOVEFILE);

不断挖掘属性

如果我们在产生日志的数据库上进行日志挖掘，那么我们可以先指定其中一个归档日志，然后使用lgmnr的DBMS_LOGMNR.CONTINUOUS_MINE属性，可以不断的添加新产生的归档日志。

9.8.4启动logmnr进程

我们强烈建议使用数据字典，否则我们无法识别内部的对象的标识。也无法使用MINE_VALUE 和COLUMN_PRESENT函数。

1）  如果使用字典文件的方式，需要指定字典文件的路径

EXECUTE

DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/oracle/database/dictionary.ora');

2）  如果没有选择文件字典，那么需要指定

DICT_FROM_REDO_LOGS或者DICT_FROM_ONLINE_CATALOG选项

如EXEC

SYS.DBMS_LOGMNR.START_LOGMNR(

OPTIONS=>SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

如果指定了DICT_FROM_REDO_LOGS，那么需要使用add_logfile加入这些日志。具体哪些日志包含了字典文件，可以查询v$archived_log视图。

3）  可以通过时间或者是SCN过滤

EXECUTE

DBMS_LOGMNR.START_LOGMNR(

OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +DBMS_LOGMNR.COMMITTED_DATA_ONLY);

 

EXECUTE

DBMS_LOGMNR.START_LOGMNR(

STARTTIME => TO_DATE('07-Aug-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),

ENDTIME => TO_DATE('21-Aug-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'),

DICTFILENAME => '/usr/local/dict.ora');

4）  我们可以同时多次执行 DBMS_LOGMNR.START_LOGMNR包，如果执行一次没有得到想要的v$logmnr_contents，那么可以使用不同参数属性再次执行，而不需要每次添加已经添加的日志文件。

9.8.5 查询v$logmnr_contents视图

9.8.6 结束logmnr会话

EXECUTE DBMS_LOGMNR.END_LOGMNR;

使用该包，释放资源，尤其使用DICT_FROM_REDO_LOGS，DDL_DICT_TRACKING

属性，一般要建议去使用包释放资源。