oracle 日志挖掘(LogMiner)

1.LogMiner工具即可以用来分析在线,也可以用来分析离线日志文件,即可以分析本身自己数据库的重作日志文件,也可以用来分析其他数据库的重作日志文件。
总的说来,LogMiner 工具的主要用途有:

(1).跟踪数据库的变化:可以离线的跟踪数据库的变化,而不会影响在线系统的性能。

(2).回退数据库的变化:回退特定的变化数据,减少point-in-time recovery的执行。

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

(4).确定数据库的逻辑损坏时间:准确定位操作执行的时间和SCN ==> 基于时间和SCN 的恢复

(5).确定事务级要执行的精细逻辑恢复操作

(6).执行后续审计

2.用户执行日志挖掘,需要权限:execute_catralog_role

3.安装LogMiner 工具,必须首先要运行下面这样两个脚本:

  1. $ORACLE_HOME/rdbms/admin/dbmslm.sql      #  创建DBMS_LOGMNR 包,该包用来分析日志文件。  
  2. $ORACLE_HOME/rdbms/admin/dbmslmd.sql    #  用来创建DBMS_LOGMNR_D 包,该包用来创建数据字典文件。  
  3. # 注:这两个脚本必须均以SYS 用户身份运行。  

4.基本对象
Source Database: 日志所属的数据库
Mining Database: 执行LogMiner 操作要使用的数据库,相同硬件平台,相同字符集,版本不低于Source Database
LogMiner字典: 日志文件中,对于表等用户对象,并不是保存名字,而是保存一个ID号;
建立字典文件的目的就是使logminer在分析时可以将Object ID翻译成我们所熟悉的对象名,在Source Database上生成

5.LogMiner字典三种类型:

(1).使用源数据库数据字典[ 表结构无变化 Source-DB必须OPEN,只能跟踪DML不能为DDL ]

  1. SQL> exec DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );  

(2).摘取LogMiner字典到重做日志[ Source-DB 必须OPEN, Archivelog模式 ]

  1. SQL> exec DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS );  

(3).摘取LogMiner 字典到字典文件,配置字典文件所在目录: 静态参数: UTL_FILE_DIR
建立字典文件:

  1. SQL> exec dbms_logmnr_d.build (dictionary_filename=>’dictionary.ora’,  
  2. dictionary_location=>’/u02/app/oracle/logminer’,  
  3. OPTIONS=>DBMS_LOGMNR_D.STORE_IN_FLAT_FILE ); # 存储到平面文件  

6.与LogMiner相关的数据字典:

  1. v$loghist   # 显示历史日志文件的一些信息  
  2. v$logmnr_dictionary   # 因logmnr 可以有多个字典文件,显示字典文件信息  
  3. v$logmnr_parameters   # 显示logmnr 的参数  
  4. v$logmnr_logs   # 显示用于分析的日志列表信息  
  5. v$logmnr_contents   # 挖掘结果  
  6. dbms_logmnr_d   # 生成日志挖掘数据字典  
  7. dbms_logmnr   # 日志挖掘相关函数  

7.使用LogMiner 进行分析[完整顺序]

(1).设定用于LogMiner分析的日志文件存放的位置
设置 UTL_FILE_DIR,需要重启数据库。
设置 utl_file_dir=* 表示你能操作任何目录
在initsid.ora文件中加入utl_file_dir 参数或者:

  1. SQL> alter system set utl_file_dir=’/u02/app/oracle/logminer’ scope=spfile;  
  2. SQL> shutdown immediate  
  3. SQL> starup  

(2).生成数据字典文件

  1. SQL> exec dbms_logmnr_d.build (dictionary_filename=>’dict.ora’,dictionary_location=>’/u02/app/oracle/logminer’,);  
  2. # 注 dictionary_location 指的是Logminer 数据字典文件存放的位置,它必须匹配utl_file_dir 的设定。
    dictionary_filename 指的是放于存放位置的字典文件的名字,名字可以任意取。

(3).建立日志分析表
建立日志分析表数据库必须在mount或nomount状态;建立日志分析表,使用dbms_logmnr.add_logfile()

  1. SQL>exec dbms_logmnr.add_logfile(logfilename=>’/u01/app/oracle/oradata/orcl/redo02.log’,options=>dbms_logmnr.new);  
  2. # 其中的options有三种取值:  
  3. dbms_logmnr.new:用于建一个日志分析表  
  4. dbms_logmnr.addfile:用于加入用于分析的的日志文件  
  5. dbms_logmnr.removefile:用于移出用于分析的日志文件  

(4).启动LogMiner进行分析
[分析指定的时间段或者指定的scn号]

  1. SQL>dbms_logmnr.start_logmnr(DictFileName =>’/u02/app/oracle/logminer/dict.ora’,starttime => to_date(‘20080710 09:15:00′,’yyyymmdd hh24:mi:ss’),endtime => to_date(‘20080710 10:00:00′,’yyyymmdd hh24:mi:ss’));  
  2. # 即分析2008 年7 月10 日这天9:15 至10:00 这段时间,并把分析结果放到数据字典中以用于查询。还有两个参数startscn (起始SCN号) 及endscn (终止SCN) 号。  
  3. 或者[全部分析]  
  4. SQL>exec dbms_logmnr.start_logmnr(DictFileName =>’/u02/app/oracle/logminer/dict.ora’);  

(5).查看日志分析的结果[存储在此视图内:V$logmnr_contents] ==> 具体查询操作,需根据实际情况来查询

查看DML操作:

  1. SQL>SELECT operation, sql_redo, sql_undo FROM V$logmnr_contents WHERE table_name=’E’;  
  2. # 注:其中operation指的是操作,sql_redo指的是实际的操作,sql_undo指的是用于取消的相反的操作。  

查看DDL操作:

  1. SQL>SELECT timstamp, sql_redo FROM v$logmnr_contents WHERE upper(sql_redo) like ‘%TRUNCATE%’;  

(6).结束LogMiner的分析

  1. SQL>exec dbms_logmnr.end_logmnr;  

8.案例:

  1. SYS>alter system set utl_file_dir=’/u02/app/oracle/logminer’ scope=spfile;  
  2. SYS>shutdown immediate  
  3. SYS>startup  
  4. SYS>exec dbms_logmnr_d.build(‘dict.ora’,’/u01/app/oracle/logminer’);  
  5. SYS>exec dbms_logmnr.add_logfile(logfilename=>’/u01/app/oracle/oradata/orcl/redo01.log’,options=>dbms_logmnr.new);  
  6. SYS>exec dbms_logmnr.start_logmnr(DictFileName =>’/u02/app/oracle/logminer/dict.ora’);  
  7. SYS>select username,scn,to_char(timestamp,’yyyy-mm-dd hh24:mi:ss’),sql_redo from v$logmnr_contents where table_name=’E’;  
  8. SYS>exec dbms_logmnr.end_logmnr;