ITPub博客

首页 > 数据库 > Oracle > Log Miner 挖挖挖

Log Miner 挖挖挖

原创 Oracle 作者:沃趣科技 时间:2018-10-23 09:53:14 0 删除 编辑

| Log Miner简介

Log Miner是Oracle自Oracle 8i以后推出的一个可以分析数据库redo log和archivelog内容的工具,可以通过日志分析所有对数据库的DDL和DML操作,也可以分析出操作的时间与操作时的SCN和进行操作的机器,对于DML操作还可以查询出还原操作的sql。


| Log Miner组成

  • 源数据库 产生LogMiner分析的所有重做日志文件的数据库

  • 挖掘数据库 是执行LogMiner分析时使用的数据库。

  • LogMiner数据字典 是LogMiner使用字典将内部对象标识符和数据类型转换为可读数据。如果没有字典,Log Miner分析的结果会显示为二进制数据。


| Log Miner数据字典选项

当LogMiner分析重做数据时,需要一个数据字典将日志的对象ID转换为可读数据。LogMiner提供了三个使用数据字典的方式。

1、使用在线目录( Online Catalog) 

使用catalog的数据字典,必须在源数据库执行。启动命令为:


SQL> execute dbms_logmnr.start_logmnr (


options

=>

dbms_logmnr.dict_from_online_catalog);


2、将LogMiner字典提取到archive log。启动命令为:


SQL> execute dbms_logmnr_d.build(


options

=>

dbms_logmnr_d.store_in_redo_logs);

使用这种操作的


3、将LogMiner字典提取到操作系统文件。启动命令为:



SQL>


 execute dbms_logmnr_d.build (

'directory_name'

'/xxx/xxx/'

,dbms_logmnr_d.store_in_flat_file);


使用这种方式的话,需要设置utl_file_dir参数,该参数需要重启才能生效。


这个工具使用起来并不复杂。由于将Log Miner数据字典提取到操作系统文件在未设置参数的情况下需要重启数据库,使用场景比较狭隘,所以以下测试场景为使用Online catalog数据字典模式和将字典提取到redo log。


| 测试场景

1、确认数据库开启了补充日志


sys@RAC11G>

select

 SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES
--如果返回结果为 no ,通过以下命令开启
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


注意: 在使用Log Miner分析的日志文件之前,必须启用补充日志。 
启用补充日志时,会在重做日志流中记录其他信息。如果不开启,LogMiner的挖掘的一些信息无法正常显示。

2、创建测试表,并做一些DML与DDL操作



sys

@

RAC11G

> create table test1 (NAME varchar2(

20

), ID number);

Table   created .
sys @ RAC11G > insert  into test1 values( 'x' , '1' );
row   created .
sys @ RAC11G > insert  into test1 values( 'xx' , '2' );
row   created .
sys @ RAC11G > insert  into test1 values( 'xxx' , '3' );
row   created .
sys @ RAC11G > commit ;
Commit   complete .
sys @ RAC11G > update  test1 set name =  'xxxx'  where id = 3 ;
row   updated .
sys @ RAC11G > commit ;
Commit   complete .
sys @ RAC11G > truncate  table test1;
Table   truncated .


3、切换归档日志


sys@RAC11G>alter system 

switch

 logfile;

System altered.
sys@RAC11G>alter system  switch  logfile;
System altered.
--然后查看最后生成的归档日志
sys@RAC11G>select *  from  (select name   from  v$archived_log where name like  '%archive%'   order  by  SEQUENCE # desc ) where rownum <3;
NAME
---------------------------------------------------------------------------------------------------------------------------------------
+DATADG/rac11g/archivelog/ 2018 _10_14/thread_1_seq_293 .847.989533723
+DATADG/rac11g/archivelog/ 2018 _10_14/thread_1_seq_292 .846.989533631


4、Log Miner添加需要分析的归档日志


--添加日志

sys@RAC11G> execute dbms_logmnr.add_logfile(logfilename=> '+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723' ,options=>dbms_logmnr.new);
PL/SQL  procedure   successfully   completed .
--添加多个日志,使用 DBMS_LOGMNR . ADDFILE 选项
sys @ RAC11G > execute   dbms_logmnr . add_logfile (logfilename=> '+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631' ,options=>DBMS_LOGMNR.ADDFILE) ;

PL/SQL  procedure   successfully   completed .


5、启动Log Miner


sys@RAC11G>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL  procedure   successfully   completed .


6、通过视图v$logmnr_contents进行分析结果查询

v$logmnr_contents只有在开启了Log Miner后才可以进行查询



select

 to_char(

timestamp

,

'yyyy-mm-dd hh24:mm:ss'

),

operation,
username,
SESSION_INFO,
sql_redo 
from  v$logmnr_contents
where  table_name =  'TEST1' ;
TO_CHAR(TIMESTAMP,' OPERATION                USERNAME        SESSION_INFO                                SQL_REDO
------------------- -------------------------------- --------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2018-10-14 22:10:50 DDL                  SYS         login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS   create   table  test1 ( NAME  varchar2( 20 ),  ID   number );
                                 _terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
                                 1-V3)
2018-10-14 22:10:29  INSERT                 SYS          login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS  insert   into   "SYS" . "TEST1" ( "NAME" , "ID" values  ( 'x' , '1' );
                                 _terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
                                 1-V3)
2018-10-14 22:10:33  INSERT                 SYS          login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS  insert   into   "SYS" . "TEST1" ( "NAME" , "ID" values  ( 'xx' , '2' );
                                 _terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
                                 1-V3)
2018-10-14 22:10:37  INSERT                 SYS          login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS  insert   into   "SYS" . "TEST1" ( "NAME" , "ID" values  ( 'xxx' , '3' );
                                 _terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
                                 1-V3)
2018-10-14 22:10:51  UPDATE                 SYS          login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS  update   "SYS" . "TEST1"   set   "NAME"  =  'xxxx'   where   "NAME"  =  'xxx'   and   ROWID  =  '
                                 _terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V AAAE6eAABAAAKHBAAC'
;
                                 1-V3)
2018-10-14 22:10:12 DDL                  SYS         login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS  truncate   table  test1;
                                 _terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
                                 1-V3)
.


7、关闭Log Miner



EXECUTE

 DBMS_LOGMNR.END_LOGMNR();


注意: Log Miner的数据只存在PGA中,如果查询的会话断开连接,Log Miner也会随之关闭。

8、将数据字典提取到redo log


sys@RAC11G>EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

PL/SQL  procedure   successfully   completed .
根据字典的大小,它可能包含在多个归档文件中。如果已归档相关的重做日志文件,则可以找出包含提取的字典的开头和结尾的归档日志。可以查询 V $ ARCHIVED_LOG 视图
sys @ RAC11G > SELECT   NAME   FROM   V $ ARCHIVED_LOG   WHERE   DICTIONARY_BEGIN =' YES ';

NAME
---------------------------------------------------------------------------------------------------------------------------------------
+DATADG/rac11g/archivelog/ 2018 _10_15/thread_1_seq_308. 869.989570647
sys@RAC11G>SELECT  NAME  FROM V$ARCHIVED_LOG WHERE DICTIONARY_END= 'YES' ;
NAME
---------------------------------------------------------------------------------------------------------------------------------------
+DATADG/rac11g/archivelog/ 2018 _10_15/thread_1_seq_309. 871.989570649


9、添加包含数据字典的归档日志以及需要分析的归档日志


sys@RAC11G>execute dbms_logmnr.add_logfile(logfilename=>

'+DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_308.869.989570647'

,options=>dbms_logmnr.new);

PL/SQL  procedure   successfully   completed .
sys @ RAC11G > execute   dbms_logmnr . add_logfile (logfilename=> '+DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_309.871.989570649' ,options=>dbms_logmnr.addfile) ;

PL/SQL  procedure   successfully   completed .
sys @ RAC11G > execute   dbms_logmnr . add_logfile (logfilename=> '+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723' ,options=>dbms_logmnr.addfile) ;

PL/SQL  procedure   successfully   completed .
sys @ RAC11G > execute   dbms_logmnr . add_logfile (logfilename=> '+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631' ,options=>dbms_logmnr.addfile) ;

PL/SQL  procedure   successfully   completed .


10、启动Log Miner


sys@RAC11G> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.DICT_FROM_REDO_LOGS);

PL/SQL  procedure   successfully   completed .


11、通过视图v$logmnr_contents进行分析结果查询



select

 to_char(

timestamp

,

'yyyy-mm-dd hh24:mm:ss'

),

operation,
username,
SESSION_INFO,
sql_redo 
from  v$logmnr_contents
7      where  table_name =  'TEST1' ;
TO_CHAR(TIMESTAMP,' OPERATION                USERNAME
------------------- -------------------------------- ------------------------------
SESSION_INFO
---------------------------------------------------------------------------------------------------------------------------------------
SQL_REDO
---------------------------------------------------------------------------------------------------------------------------------------
2018-10-14 22:10:50 DDL                  SYS
login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
11gnode1 (TNS V1-V3)
create   table  test1 ( NAME  varchar2( 20 ),  ID   number );
2018-10-14 22:10:29  INSERT                 SYS
login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS_terminal=pts/ 5  OS_process_id= 17273  OS_program_name=sqlplus@
11 gnode1 (TNS V1-V3)
insert   into   "SYS" . "TEST1" ( "COL 1" , "COL 2" values  (HEXTORAW( '78' ),HEXTORAW( 'c102' ));
2018-10-14 22:10:33  INSERT                 SYS
login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS_terminal=pts/ 5  OS_process_id= 17273  OS_program_name=sqlplus@
11 gnode1 (TNS V1-V3)
insert   into   "SYS" . "TEST1" ( "COL 1" , "COL 2" values  (HEXTORAW( '7878' ),HEXTORAW( 'c103' ));
2018-10-14 22:10:37  INSERT                 SYS
login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS_terminal=pts/ 5  OS_process_id= 17273  OS_program_name=sqlplus@
11 gnode1 (TNS V1-V3)
insert   into   "SYS" . "TEST1" ( "COL 1" , "COL 2" values  (HEXTORAW( '787878' ),HEXTORAW( 'c104' ));
2018-10-14 22:10:51  UPDATE                 SYS
login_username= SYS  client_info= OS_username= oracle  Machine_name= 11 gnode1 OS_terminal=pts/ 5  OS_process_id= 17273  OS_program_name=sqlplus@
11 gnode1 (TNS V1-V3)
update   "SYS" . "TEST1"   set   "COL 1"  = HEXTORAW( '78787878' where   "COL 1"  = HEXTORAW( '787878' and   ROWID  =  'AAAE6eAABAAAKHBAAC' ;
2018-10-14 22:10:12 DDL                  SYS
login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
11gnode1 (TNS V1-V3)
truncate   table  test1;


| Log Miner的一些限制

当然,这个工具也有一定的限制

  • 源数据库与挖掘数据库

    • 源数据库和挖掘数据库都必须在同一操作系统平台上运行。

    • 挖掘数据库可以与源数据库相同或完全独立。

    • 挖掘数据库必须运行与源数据库相同的版本或更高版本的Oracle数据库软件。

    • 挖掘数据库必须使用源数据库使用的相同字符集(或字符集的超集)。

  • Log Miner 数据字典

    • 数据字典必须由源数据库生成。

  • 归档日志

    • 每次分析的所有归档日志必须由相同的源数据生成。

    • 必须与同一数据库关联RESETLOGS SCN。

    • 必须来自8i或更高版本的Oracle数据库。

这些限制都不算苛刻,如果灵活使用这个工具的话,可以使归档日志利用最大化,在关键时间有非常大的作用。


|  作者简介

陈康,沃趣科技数据库技术专家

主要参与公司产品实施、测试、维护以及优化。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28218939/viewspace-2217182/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
杭州沃趣科技股份有限公司创建于2012年(股票代码:839849),是一家专注为企业用户提供基于高性能、高可用、可扩展的开放数据库云平台解决方案的国产厂商。公司创始团队为原阿里巴巴数据库技术团队核心骨干,凭借丰富的研发及运维经验,为行业客户提供数据库云产品及软硬件一体化解决方案。

注册时间:2016-07-18

  • 博文量
    273
  • 访问量
    826594