对logmnr的学习

logminer工具的学习

1 logmnr 安装 
日志挖掘需要DBMS_LOGMNR_D和DBMS_LOGMNR包，如果数据库没有这两包，SYSDBA身份运行下面脚本：
 @$ORACLE_HOME/rdbms/admin/dbmslm.sql;
 @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;

2、 logminer不支持
默认情况，数据库不提供补充日志，所以默认下logminer不支持：
1,索引族，链行和迁移行。
2,直接路径插入
3,摘取logminer字典到redo log
4,跟踪ddl
5,生成健列的sql_redo和sql_undo
6,long和lob数据类型
如果要使用这些特性的话，必须打开这个附加功能
alter database add supplemental log data;
SQL> Select supplemental_log_data_min from v$database;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES

3、Logmnr对源字典提供三种选择：
提取数据字典到数据文件(extracting the dictionary to a flat file)
提取字典到日志文件（extracting the dictionary to the redo logs）
使用联机目录（using online catalog）

为了完整的对日志文件进行转换，我们需要使用数据字典文件。利用字典文件可以转换内部的对象标识符与实际对象名。
比如：我执行如下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是不一样的。

3.1、生成字典文件到文本文件
当字典文件为文本文件，和包含在redo logs的日志文件相比，占用更少的系统资源。一般建议定期备份该字典文件以便对老的日志的分析。
           为了提取数据字典文件为文本文件，那么需要使用dbms_logmnr_d.build的store_in_flat_file属性。
            在生成flat文件过程中，不能有任何的DDL操作。

 生成文本文件的步骤;
1）  设置参数UTL_FILE_DIR = /arch，注意该文件夹的权限以及重新启动数据库以便该参数设置生效。
2）  执行oracle包，生成oracle字典文件
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/arch/',OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
           也可以只指定文件名以及文件路径，不指定STORE_IN_FLAT_FILE，结果是一样。
---第一个参数表示字典文件名
---第二个参数表示字典文件目录，和utl_file_dir目录一致
---第三个参数表示要使用字典文件

  execute  sys.dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location => '/arch',OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
  第一步，建立分析列表:
  execute  sys.dbms_logmnr.add_logfile (LogFileName =>'/u01/oracle/oradata/ora10g/redo04.log',Options =>dbms_logmnr.new);
  execute  sys.dbms_logmnr.add_logfile (LogFileName =>'/u01/oracle/oradata/ora10g/redo03.log',Options =>dbms_logmnr.addfile);
若要移除日志的话就要DBMS_LOGMNR.REMOVEFILE
    execute  dbms_logmnr.add_logfile (LogFileName =>' ',Options => dbms_logmnr.REMOVEFILE);
第二步启动logmnr
  execute  dbms_logmnr.start_logmnr (dictFileName => '/arch/dictionary.ora' ,options=>dbms_logmnr.addfile);
  execute  dbms_logmnr.start_logmnr (dictFileName => '/arch/dictionary.ora' ,OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);


3.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';
最后就是建议能够定期备份该字典文件。
  execute  sys.dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location => '/arch',OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
  第一步，建立分析列表:
  execute  sys.dbms_logmnr.add_logfile (LogFileName =>'/u01/oracle/oradata/ora10g/redo04.log',Options =>dbms_logmnr.new);
  execute  sys.dbms_logmnr.add_logfile (LogFileName =>'/u01/oracle/oradata/ora10g/redo03.log',Options =>dbms_logmnr.addfile);
若要移除日志的话就要DBMS_LOGMNR.REMOVEFILE
    execute  dbms_logmnr.add_logfile (LogFileName =>' ',Options => dbms_logmnr.REMOVEFILE);
第二步启动logmnr
  execute  dbms_logmnr.start_logmnr (dictFileName => '/arch/dictionary.ora' ,OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);


3、使用联机目录
          为了让logmnr使用数据库正在使用的字典文件，当启动logmnr的时候，需要指定联机目录作为源字典(source dictory)
           执行如下语句：
           EXECUTE  DBMS_LOGMNR.START_LOGMNR (OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
         使用联机目录，意思就是我们不需要提取数据字典到文本文件或者是日志文件中。此外可以使用联机目录去分析联机日志。还有使用它分析在同一个系统上产生的归档日志。
          联机目录包含了大量的数据库信息，也是一种最快的分析数据库日志的方法。
          因为DDL语句对数据库对象的改变在日志中只有很少的记录，因此日志挖掘中只提供少量的分析信息。
          使用联机目录分析，只能挖掘最近的数据库对象的信息，而对以前的老的对象，或者是归档日志中的对象目前已经不存在或者改变的，只有使用以前的方法去分析。使用前两种方法分析的前提是：含有以前提取的数据字典文件的备份。


建立分析列表，即所要分析的日志：
---通过日志挖掘
SQL> select ' exec  sys.dbms_logmnr.add_logfile(logfilename=>'''||MEMBER||''');' a from v$logfile;
A
------------------------------------------------------------------------------------------
sys.dbms_logmnr.add_logfile(logfilename=>'/u01/oracle/oradata/ora10g/redo03.log');
sys.dbms_logmnr.add_logfile(logfilename=>'/u01/oracle/oradata/ora10g/redo02.log');
sys.dbms_logmnr.add_logfile(logfilename=>'/u01/oracle/oradata/ora10g/redo01.log');
sys.dbms_logmnr.add_logfile(logfilename=>'/u01/oracle/oradata/ora10g/redo04.log');

execute dbms_logmnr.add_logfile(LOGFILENAME=>'/u01/oracle/oradata/ora10g/redo03.log',OPTIONS=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/u01/oracle/oradata/ora10g/redo02.log',OPTIONS=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/u01/oracle/oradata/ora10g/redo01.log',OPTIONS=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/u01/oracle/oradata/ora10g/redo04.log',OPTIONS=>dbms_logmnr.addfile);

进行日志分析，该例中没有建立数据字典,使用dbms_logmnr.dict_from_online_catalog选项参数，则logminer期望在所指定的dbms_logmnr.add_logfile或重做日志中找到数据字典。
exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);

查看分析结果，其中sql_redo是重做sql文本，即所做过的操作，如果要回退，可以查sql_undo值，seg_name是段名，一般是所操作过的对象如表：
select scn,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents  where upper(seg_name)='T1';

建立归档分析列表，即所要分析的归档：
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_41242_707251512.arc',OPTIONS=>dbms_logmnr.new);

SQL> select 'execute dbms_logmnr.add_logfile(LOGFILENAME=>''/tmp/1_'||SEQUENCE# ||'_790098559.dbf'',OPTIONS=>dbms_logmnr.addfile);'from v$archived_log where to_char(first_time,'yyyy-mm-dd hh24:mi:ss')>'2014-11-17 10:00:00' and to_char(first_time,'yyyy-mm-dd hh24:mi:ss')<'2014-11-17 17:30:00' order by thread#,SEQUENCE#;

'EXECUTEDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>''/TMP/1_'||SEQUENCE#||'_790098559.DBF'',OPTIONS=>DBMS_LOGMNR.ADDFILE);'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_378_790098559.dbf',OPTIONS=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_379_790098559.dbf',OPTIONS=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_380_790098559.dbf',OPTIONS=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_381_790098559.dbf',OPTIONS=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_382_790098559.dbf',OPTIONS=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_383_790098559.dbf',OPTIONS=>dbms_logmnr.addfile);

  若要移除日志的话就要DBMS_LOGMNR.REMOVEFILE
    execute  dbms_logmnr.add_logfile (LogFileName =>' ',Options => dbms_logmnr.REMOVEFILE);
select thread#,SEQUENCE#,FIRST_TIME, NEXT_TIME from v$archived_log where to_char(first_time,'yyyy-mm-dd hh24:mi:ss')>'2011-04-01 10:00:00' and to_char(first_time,'yyyy-mm-dd hh24:mi:ss')<'2011-04-01 10:30:00' order by thread#,SEQUENCE#

进行日志分析，该例中没有建立数据字典,使用dbms_logmnr.dict_from_online_catalog选项参数，则logminer期望在所指定的dbms_logmnr.add_logfile或重做日志中找到数据字典。
exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
查看分析结果，其中sql_redo是重做sql文本，即所做过的操作，如果要回退，可以查sql_undo值，seg_name是段名，一般是所操作过的对象如表：
select scn,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents  where upper(seg_name)='T1';

归档恢复，分别在两个实例上进行
run{
  allocate channel t1 type sbt;
  restore archivelog from sequence 28692  until sequence 28693 thread=1;
  release channel t1;
}


run{
  allocate channel t1 type sbt;
  restore archivelog from sequence 79531  until sequence 79537 thread=2;
  release channel t1;
} 


.复制恢复出来的归档
  考虑到归档定期执行，建议将恢复出来的归档复制到其它文件系统下

.logmnr操作
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_41242_707251512.arc',OPTIONS=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_41243_707251512.arc',OPTIONS=>dbms_logmnr.addfile);
.
.
.
DBMS_LOGMNR.START_LOGMNR(OPTIONS=>dbms_logmnr.dict_from_online_catalog);
为方便后面的查询，建议按查询结果生成表：
create table logmnr 
     as select a.seg_owner,a.seg_name,a.operation,a.TIMESTAMP,a.sql_redo,a.sql_undo 
       from v$logmnr_contents a where a.SEG_OWNER='XXX' and a.SEG_NAME='XXX' and a.OPERATION='DELETE';
create table logmnr 
     as select  *
       from v$logmnr_contents a where a.SEG_OWNER='XXX' and a.SEG_NAME='XXX' and a.OPERATION='DELETE';


建表方法二：
create table xxz_logmnr nologging as select * from v$logmnr_contents where 1=2;
create table xxz_logmnr nologging  TABLESPACE XXX as select * from v$logmnr_contents where 1=2;
insert /*+ append */ into xxz_logmnr select * from v$logmnr_contents;
做这一步之前注意将nls_date_format改成'yyyy-mm-dd hh24:mi:ss'，不然中文乱码，时间会变成问号。


查询语句：


set lines 200
col seg_owner format a20
col seg_name format a30
set pages 400
select operation,count(operation),seg_owner,seg_name from  wudd_logmnr group by seg_owner,seg_name,operation order by count(operation);

Select  SQL_REDO, SQL_UNDO from wudd_logmnr where seg_owner=’XXX’;


生成临时表
利用logmnr表中sql_undo中的记录，生成临时表，并交应用确认是否为删除记录。
create  table XXX as select * from  USERNAME.TABLE_NAME  where 1=2;    =>只要表结构。
执行SQL_UNDO到相应的表


结束logmnr
SQL> execute dbms_logmnr.end_logmnr;


4、跟踪DDL
当启动logmnr时，Logmnr自动从源字典建立自己的数据字典。
当使用文本文件或者日志文件作为字典文件时，
使用
EXECUTE DBMS_LOGMNR.START_LOGMNR
(OPTIONS=>DBMS_LOGMNR.DDL_DICT_TRACKING);对执行的DDL进行跟踪，默认情况下，这种跟踪是关闭的。
 
注意：应该明白logmnr内部的数据字典和包含在文本文件以及日志文件中的logmnr数据字典是不一样的。Logmnr会更新其内部的数据字典，但是logmnr不会更新已经保存在文本文件或者日志文件中的数据字典。


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。

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

7、表级别的追加日志
表级别的追加的日志，使用日志文件组记录追加的日志。有两种类型的表级别的追加日志形式：
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只记录无条件的日志组中

8 启动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 限制
1）以下是不支持的
   简单或者是嵌套的抽象类型
   集合（嵌套表和数组）
   引用对象类型
   索引组织表
   利用簇键创建一个表
 2）logmnr可以运行在8.1和以后版本中，但是我们可以8.0以后版本中的日志。可以从日志中获取的信息决定于日志的版本，而不是正在使用的数据库的版本。


10 注意
默认情况下logmnr使用的是system表空间 ，而使用EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$')该语句会重新在logmnrts$表空间中重新创建logmnr相关的表。
  execute  sys.dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location => '/arch',OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
DICTIONARY_FILENAME字典文件名，
DICTIONARY_LOCATION为字典文件的路径，
而options为字典文件的类型，数据字典存储在文本文件中还是在日志文件中（store_in_flat_file,store_in_redo_logs）。


在下列情况下，执行该包会出现异常：
Ora-1308:UTL_FILE_DIR系统参数没有设置。
Ora-1336:目录位置不存在  ，UTL_FILE_DIR没有对字典路径设置为具有访问权限。 字典文件为只读。
如果数据库在进行任何的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视图不放入系统表空间，可能会提高系统的性能。

    

参考：http://blog.chinaunix.net/uid-14877370-id-2782027.html