Log Miner 挖挖挖

我不是女神ヾ 2022-05-03 23:40 169阅读 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');
    1 row created.
    sys@RAC11G>insert into test1 values('xx','2');
    1 row created.
    sys@RAC11G>insert into test1 values('xxx','3');
    1 row created.
    sys@RAC11G>commit;
    Commit complete.
    sys@RAC11G>update test1 set name = 'xxxx' where id =3;
    1 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=11gnode1 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=11gnode1 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=11gnode1 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=11gnode1 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=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
    11gnode1 (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=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
    11gnode1 (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=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
    11gnode1 (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=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
    11gnode1 (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数据库。

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

### **|  作者简介** ###

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

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