Oracle logminer

一、logminer的用途日志文件中存放着所有进行数据库恢复的数据，记录了针对数据库结构的每一个变化，也就是对数据库操作的所有DML语句。logminer 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。总的说来，logminer工具的主要用途有：
　1．跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
　2． 回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。

　3．优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。

二、安装logminer
要安装logminer工具，必须首先要运行下面这样两个脚本：
　l. $ORACLE_HOME/rdbms/admin/dbmslm.sql
　2. $ORACLE_HOME/rdbms/admin/dbmslmd.sql

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

[sql] view plain copy
 
  1. SQL> @?/rdbms/admin/dbmslm.sql    
  2.     
  3. 程序包已创建。    
  4.     
  5. 授权成功。    
  6.     
  7. 同义词已创建。    
  8.     
  9. SQL> @?/rdbms/admin/dbmslmd.sql    
  10.     
  11. 程序包已创建。    
  12.     
  13. 同义词已创建。    
  14.     
  15. SQL> show user    
  16. USER 为 "SYS"    

三、使用logminer工具
下面将详细介绍如何使用logminer工具。
　1、创建数据字典文件（data-dictionary）
前面已经谈到，logminer工具实际上是由两个新的PL/SQL内建包（(DBMS_LOGMNR 和 DBMS_ LOGMNR_D)和四个V$动态性能视图（视图是在利用过程DBMS_LOGMNR.START_LOGMNR启动logminer时创建）组成。在使用logminer工具分析redo log文件之前，必须使用DBMS_LOGMNR_D 包将数据字典导出为一个文本文件。该字典文件是可选的，但是如果没有它，logminer解释出来的语句中关于数据字典中的部分（如表名、列名等）和数值都将是16进制的形式，我们是无法直接理解的。

例如，下面的sql语句： INSERT INTO dm_dj_swry (rydm, rymc) VALUES (00005, '张三'); 

logminer解释出来的结果将是下面这个样子， insert into Object#308(col#1, col#2) values (hextoraw('c30rte567e436'), hextoraw('4a6f686e20446f65'));

创建数据字典的目的就是让logminer引用涉及到内部数据字典中的部分时为他们实际的名字，而不是系统内部的16进制。数据字典文件是一个文本文件，使用包DBMS_LOGMNR_D来创建。如果我们要分析的数据库中的表有变化，影响到库的数据字典也发生变化，这时就需要重新创建该字典文件。另外一种情况是在分析另外一个数据库文件的重作日志时，也必须要重新生成一遍被分析数据库的数据字典文件。首先指定数据字典文件的位置，也就是添加一个参数UTL_FILE_DIR，该参数值为服务器中放置数据字典文件的目录。重新启动数据库，使新加的参数生效，然后创建数据字典文件。

[sql] view plain copy
 
  1. SQL> show user    
  2. USER 为 "SYS"    
  3. SQL> show parameter utl_file_dir    
  4.     
  5. NAME                                 TYPE        VALUE    
  6. ------------------------------------ ----------- ------------------------------    
  7. utl_file_dir                         string    
  8.     
  9. SQL> alter system set utl_file_dir='/u01/app/logminer' scope=spfile;    
  10.     
  11. 系统已更改。    
  12.     
  13. SQL> shutdown immediate    
  14. 数据库已经关闭。    
  15. 已经卸载数据库。    
  16. ORACLE 例程已经关闭。    
  17. SQL> startup    
  18. ORACLE 例程已经启动。    
  19.     
  20. Total System Global Area  238530560 bytes    
  21. Fixed Size                  1335724 bytes    
  22. Variable Size             150998612 bytes    
  23. Database Buffers           83886080 bytes    
  24. Redo Buffers                2310144 bytes    
  25. 数据库装载完毕。    
  26. 数据库已经打开。    
  27. SQL> show parameter utl_file_dir    
  28.     
  29. NAME                                 TYPE        VALUE    
  30. ------------------------------------ ----------- ------------------------------    
  31. utl_file_dir                         string      /u01/app/logminer    
  32. SQL> exec dbms_logmnr_d.build( 'dictionary.ora', '/u01/app/logminer');    
  33.     
  34. PL/SQL 过程已成功完成。    

开始之前

[sql] view plain copy
 
  1. SQL> show user    
  2. USER 为 "SYS"    
  3. SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;    
  4.     
  5. SUPPLEME    
  6. --------    
  7. NO    
  8.     
  9. SQL> alter database add supplemental log data;    
  10.     
  11. 数据库已更改。    
  12.     
  13. SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;    
  14.     
  15. SUPPLEME    
  16. --------    
  17. YES    

supplemental logging（扩充日志）：在通常情况下，redo log 只记录的进行恢复所必需的信息，但是这些信息对于我们使用redo log进行一些其他应用时是不够的，例如在 redo log中使用rowid唯一标识一行而不是通过Primary key，如果我们在另外的数据库分析这些日志并想重新执行某些dml时就可能会有问题，因为不同的数据库其rowid代表的内容是不同的。在这时候就需要一些额外的信息（columns）加入redo log，这就是supplemental logging。supplemental logging分为两个级别database_level and table_level，其中database_level级别分为两种，minimal supplemental logging and identification key logging，其中minimal supplemental logging不会显著增加系统的负担，但是identification key logging对系统负担比较大，在默认情况下oracle不会设置任何supplemental logging，但是如果要使用logminer，oracle建议至少设置minimal supplemental logging。我在使用logminer是，不设置minimal supplemental logging，从v$logmnr_contents中几乎得不到任何信息。

    2、创建要分析的日志文件列表
Oracle的重作日志分为两种，在线（online）和离线（offline）归档日志文件，下面就分别来讨论这两种不同日志文件的列表创建。

 （1）分析在线重作日志文件

[sql] view plain copy
 
  1. SQL> show user    
  2. USER 为 "SYS"    
  3. SQL> select * from v$log;    
  4.     
  5.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME    
  6. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ------------ --------------    
  7.          1          1         67   52428800        512          1 YES INACTIVE                643449 14-12月-11           653614 15-12月-11    
  8.          2          1         68   52428800        512          1 NO  CURRENT                 653614 15-12月-11       2.8147E+14    
  9.          3          1         66   52428800        512          1 YES INACTIVE                614556 13-12月-11           643449 14-12月-11    
  10.     
  11. SQL> select * from v$logfile;    
  12.     
  13.     GROUP# STATUS  TYPE    MEMBER                                                       IS_    
  14. ---------- ------- ------- ------------------------------------------------------------ ---    
  15.          1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log                      NO    
  16.          2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log                      NO    
  17.          3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log                      NO    
  18.     
  19. SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo01.log', dbms_logmnr.new);    
  20.     
  21. PL/SQL 过程已成功完成。    
  22.     
  23. SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo02.log', dbms_logmnr.addfile);    
  24.     
  25. PL/SQL 过程已成功完成。    
  26.     
  27. SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo03.log', dbms_logmnr.addfile);    
  28.     
  29. PL/SQL 过程已成功完成。    

删除列表中的日志文件：exec dbms_logmnr.add_logfile('/u01/app/Oracle/oradata/orcl/redo03.log', dbms_logmnr.removefile);
关于这个日志文件列表中需要分析日志文件的个数完全由你自己决定，但这里建议最好是每次只添加一个需要分析的日志文件，在对该文件分析完毕后，再添加另外的文件。

  3、使用logminer进行日志分析
（1）无限制条件

[sql] view plain copy
 
  1. SQL> exec dbms_logmnr.start_logmnr( dictfilename=>'/u01/app/logminer/dictionary.ora');    
  2.     
  3. PL/SQL 过程已成功完成。    

（2）有限制条件
通过对过程DBMS_ LOGMNR.START_LOGMNR中几个不同参数的设置（参数含义见表1），可以缩小要分析日志文件的范围。通过设置起始时间和终止时间参数我们可以限制只分析某一时间范围的日志。

如下面的例子，我们仅仅分析2004年9月18日的日志，：
SQL> EXECUTE dbms_logmnr.start_logmnrdictfilename => '/u01/app/logminer/dictionary.ora',starttime => to_date('2006-02-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),endtime => to_date(''2004-9-18 23:59:59','YYYY-MM-DD HH24:MI:SS '));
也可以通过设置起始SCN和截至SCN来限制要分析日志的范围：
SQL> EXECUTE dbms_logmnr.start_logmnr( dictfilename => '/u01/app/logminer/dictionary.ora', startscn => 20, endscn => 50);

参数 参数类型 默认值 含义
StartScn 数字型（Number） 0 分析重作日志中SCN≥StartScn日志文件部分
EndScn 数字型（Number） 0 分析重作日志中SCN≤EndScn日志文件部分
StartTime 日期型(Date) 1998-01-01 分析重作日志中时间戳≥StartTime的日志文件部分
EndTime 日期型(Date) 2988-01-01 分析重作日志中时间戳≤EndTime的日志文件部分
DictFileName 字符型(VARCHAR2) 0 字典文件，该文件包含一个数据库目录的快照。使用该文件可以使得到的分析结果是可以理解的文本形式，

  4、观察分析结果（v$logmnr_contents）
一共有四个表，详细说明如下
V$LOGMNR_DICTIONARY-------查询使用的数据字典文件

[sql] view plain copy
 
  1. SQL> select filename from v$logmnr_dictionary;    
  2.     
  3. FILENAME    
  4. --------------------------------------------------    
  5. /u01/app/logminer/dictionary.ora   

V$LOGMNR_PARAMETERS-------查询当前LogMiner设定的参数

[sql] view plain copy
 
  1. SQL> select * from v$logmnr_parameters;    
  2.     
  3. START_DATE     REQUIRED_START END_DATE        START_SCN REQUIRED_START_SCN    END_SCN    OPTIONS INFO                                 STATUS    
  4. -------------- -------------- -------------- ---------- ------------------ ---------- ---------- -------------------------------- ----------    
  5.                               01-1月 -11         614556                  0          0          0                                           0    

V$LOGMNR_LOGS-------查询分析的日志文件

[sql] view plain copy
 
  1. SQL> select filename from v$logmnr_logs;    
  2.     
  3. FILENAME    
  4. --------------------------------------------------    
  5. /u01/app/oracle/oradata/orcl/redo03.log    
  6. /u01/app/oracle/oradata/orcl/redo01.log    
  7. /u01/app/oracle/oradata/orcl/redo02.log    

V$LOGMNR_CONTENTS-------日志文件的内容
到现在为止，我们已经分析得到了重作日志文件中的内容。动态性能视图v$logmnr_contents包含logminer分析得到的所有的信息。SQL> select scn,timestamp,sql_redo from v$logmnr_contents;
如果我们仅仅想知道某个用户对于某张表的操作，可以通过下面的SQL查询得到，该查询可以得到用户DB_ZGXT对表SB_DJJL所作的一切工作。

[sql] view plain copy
 
  1. SQL> select operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='U1' and seg_name='TEST';    
  2.     
  3. 未选定行   

operation 指的是操作 ，sql_redo 指的是实际操作，sql_undo 指的是用于取消的相反操作。
需要强调一点的是，视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个会话的生命期中存在。这是因为所有的logminer存储都在PGA内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。

[sql] view plain copy
 
  1. SQL> exec dbms_logmnr.end_logmnr;    
  2.     
  3. PL/SQL 过程已成功完成。    
  4. SQL> select * from v$logmnr_contents;    
  5. select * from v$logmnr_contents    
  6.               *    
  7. 第 1 行出现错误:    
  8. ORA-01306: 在从 v$logmnr_contents 中选择之前必须调用 dbms_logmnr.start_logmnr()    


四、其他注意事项
我们可以利用logminer日志分析工具来分析其他数据库实例产生的重作日志文件，而不仅仅用来分析本身安装logminer的数据库实例的redo logs文件。使用logminer分析其他数据库实例时，有几点需要注意：
　1. logminer必须使用被分析数据库实例产生的字典文件，而不是安装logminer的数据库产生的字典文件，另外必须保证安装logminer数据库的字符集和被分析数据库的字符集相同。
　2. 被分析数据库平台必须和当前logminer所在数据库平台一样，也就是说如果我们要分析的文件是由运行在UNIX平台上的Oracle 9i产生的，那么也必须在一个运行在UNIX平台上的Oracle实例上运行logminer，而不能在其他如Microsoft NT上运行logminer。当然两者的硬件条件不一定要求完全一样。
　3. logminer日志分析工具仅能够分析Oracle 8以后的产品，对于8以前的产品，该工具也无能为力。

五、结语
logminer对于数据库管理员（DBA）来讲是个功能非常强大的工具，也是在日常工作中经常要用到的一个工具，借助于该工具，可以得到大量的关于数据库活动的信息。其中一个最重要的用途就是不用全部恢复数据库就可以恢复数据库的某个变化。另外，该工具还可用来监视或者审计用户的活动，如你可以利用logminer工具察看谁曾经修改了那些数据以及这些数据在修改前的状态。我们也可以借助于该工具分析任何Oracle 8及其以后版本产生的重作日志文件。另外该工具还有一个非常重要的特点就是可以分析其他数据库的日志文件。总之，该工具对于数据库管理员来讲，是一个非常有效的工具，深刻理解及熟练掌握该工具，对于每一个数据库管理员的实际工作是非常有帮助的。


六、例子

首先用一个用户u1做一些dml操作：

[sql] view plain copy
 
  1. SQL> show user    
  2. USER 为 "U1"    
  3. SQL> delete from t1 where id=3;    
  4.     
  5. 已删除 1 行。    
  6.     
  7. SQL> insert into t1 values(3,'test');    
  8.     
  9. 已创建 1 行。    
  10.     
  11. SQL> commit;    
  12.     
  13. 提交完成。    
  14.     
  15. SQL> update t1 set value = 'v3' where id = 3;    
  16.     
  17. 已更新 1 行。    
  18.     
  19. SQL> commit;    
  20.     
  21. 提交完成。    
  22.     
  23. SQL> select * from t1;    
  24.     
  25.         ID VALUE    
  26. ---------- ----------    
  27.          1 v1    
  28.          2 v2    
  29.          3 v3    


开始分析：

[sql] view plain copy
 
  1. SQL> show user    
  2. USER 为 "SYS"    
  3. SQL> alter system switch logfile;    
  4.     
  5. 系统已更改。    
  6.     
  7. SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_12_15/o1_mf_1_80_7gmqb6t5_.arc', dbms_logmnr.new);    
  8.     
  9. PL/SQL 过程已成功完成。    
  10.     
  11. SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/u01/app/logminer/dictionary.ora');    
  12.     
  13. PL/SQL 过程已成功完成。    

查看结果：

[sql] view plain copy
 
  1. SQL> select filename from v$logmnr_dictionary;    
  2.     
  3. FILENAME    
  4. --------------------------------------------------    
  5. /u01/app/logminer/dictionary.ora    
  6.     
  7. SQL> select * from v$logmnr_parameters;    
  8.     
  9. START_DATE     REQUIRED_START END_DATE        START_SCN REQUIRED_START_SCN    END_SCN    OPTIONS INFO                                 STATUS    
  10. -------------- -------------- -------------- ---------- ------------------ ---------- ---------- -------------------------------- ----------    
  11.                               01-1月 -11         653614                  0          0          0                                           0    
  12.     
  13. SQL> select filename from v$logmnr_logs;    
  14.     
  15. FILENAME    
  16. --------------------------------------------------    
  17. /u01/app/oracle/oradata/orcl/redo02.log    
  18.     
  19. SQL> select operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='U1' and seg_name='T1';    
  20.     
  21. OPERATION       SQL_REDO                                           SQL_UNDO    
  22. --------------- -------------------------------------------------- --------------------------------------------------    
  23. DELETE          delete from "U1"."T1" where "ID" = '3' and "VALUE" insert into "U1"."T1"("ID","VALUE") values ('3','v    
  24.                  = 'v3' and ROWID = 'AAAR3vAAFAAAACFAAC';          3');    
  25.     
  26. INSERT          insert into "U1"."T1"("ID","VALUE") values ('3','t delete from "U1"."T1" where "ID" = '3' and "VALUE"    
  27.                 est');                                              = 'test' and ROWID = 'AAAR3vAAFAAAACFAAB';    
  28.     
  29. UPDATE          update "U1"."T1" set "VALUE" = 'v3' where "VALUE"  update "U1"."T1" set "VALUE" = 'test' where "VALUE    
  30.                 = 'test' and ROWID = 'AAAR3vAAFAAAACFAAB';         " = 'v3' and ROWID = 'AAAR3vAAFAAAACFAAB';    
  31.     
  32. SQL> select operation,sql_redo,sql_undo,seg_owner,seg_name from v$logmnr_contents;    
  33.     
  34. OPERATION       SQL_REDO                                           SQL_UNDO                                           SEG_OWNER       SEG_NAME    
  35. --------------- -------------------------------------------------- -------------------------------------------------- --------------- ---------------    
  36. START           set transaction read write;    
  37. DELETE          delete from "U1"."T1" where "ID" = '3' and "VALUE" insert into "U1"."T1"("ID","VALUE") values ('3','v U1              T1    
  38.                  = 'v3' and ROWID = 'AAAR3vAAFAAAACFAAC';          3');    
  39.     
  40. INTERNAL    
  41. INSERT          insert into "U1"."T1"("ID","VALUE") values ('3','t delete from "U1"."T1" where "ID" = '3' and "VALUE" U1              T1    
  42.                 est');                                              = 'test' and ROWID = 'AAAR3vAAFAAAACFAAB';    
  43.     
  44. COMMIT          commit;    
  45. START           set transaction read write;    
  46. UPDATE          update "U1"."T1" set "VALUE" = 'v3' where "VALUE"  update "U1"."T1" set "VALUE" = 'test' where "VALUE U1              T1    
  47.     
  48. OPERATION       SQL_REDO                                           SQL_UNDO                                           SEG_OWNER       SEG_NAME    
  49. --------------- -------------------------------------------------- -------------------------------------------------- --------------- ---------------    
  50.                 = 'test' and ROWID = 'AAAR3vAAFAAAACFAAB';         " = 'v3' and ROWID = 'AAAR3vAAFAAAACFAAB';    
  51.     
  52. COMMIT          commit;    
  53.     
  54. 已选择8行。    


结束分析：

 
    1. SQL> exec dbms_logmnr.end_logmnr;    
    2.     
    3. PL/SQL 过程已成功完成。    
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/youngerger/p/8858583.html