Logminer简介及应用

心已赠人 2022-06-09 07:27 355阅读 0赞

## 背景 ##

近期在研究OGG复制时，对于官方稳定中提到了11.2.0.4及以后的数据库出现了一种新的捕获日志的方法：integrated capture mode。该方法使用Log Mining Server捕获日志。故对于LogMiner进行了一些回顾，整理资料如下。

LogMiner用于分析重做日志和归档日志所记载的事务操作。  
Logminer是oracle提供的用于分析重做日志信息的工具，它包括DBMS\_LOGMNR和DBMS\_LOGMNR\_D两个package，后边的D是字典的意思。它既能分析redo log file，也能分析archive log file。分析日志需要使用数据字典，一般先生成数据字典文件再分析日志，10g以后的版本还可以使用在线数据字典。

## 使用场景： ##

1. 确定数据库误操作时间点或SCN。  
假定某个用户执行drop table操作误删除了一张重要的表，通过LogMiner可以准确定位该误操作执行的时间或者SCN，然后通过基于时间或者SCN的恢复可以完全恢复该表数据。  
2. 确定单个事务恢复操作。  
假定某个用户在某表上执行了DML操作并提交了事务，并且该DML操作存在错误，则可以通过LogMiner可以取得任何用户的DML操作及其相应的UNDO操作，执行该UNDO操作可以取消该事务操作。  
3. 执行后续审计。  
通过LogMiner可以跟踪Oracle数据库的所有DML、DDL、DCL操作，从而取得执行这些操作的时间顺序、执行这些操作的用户等信息。

## 组件 ##

LogMiner可以使用产生redo log或archive log的数据库做分析（源数据库），也可以使用其他数据库做分析（分析数据库），为了将数据库内部对象ID号和数据类型转换为对象名和外部数据格式，需要使用LogMiner数据字典，否则无法读懂分析结果。

## 限制 ##

1.  源数据库和分析数据库可以是同一个数据库。
2.  源数据库与分析数据库必须运行在相同的硬件平台上（与字节序有关）。
3.  分析数据库的版本不能低于源数据库的版本。
4.  分析数据库与源数据库必须具有相同的字符集。
5.  LogMiner数据字典必须在源数据库中生成。
6.  同时分析多个重做日志和归档日志时，这些日志必须是同一个源数据库的日志。
7.  同时分析多个重做日志和归档日志时，这些日志必须具有相同的resetlogs scn。
8.  重做日志和归档日志必须在oracle 8.0以上。

## 补充日志 ##

Redo log用户instance recovery和media recovery，这些操作所需要的数据都被自动记录在redo log中（一般记录rowid和修改的列值就足够了），但是如果想让解析出来的日志应用在其他方面，比如逻辑复制，则redo中还需要记录行中其他列的信息，以定位具体操作的行（因为源端和目标端rowid很可能是不一样的，不能用rowid来定位具体操作的行），记录其他列的日志被称为补充日志。

默认情况下Oracle数据库没有开启补充日志，从而导致LogMiner无法支持以下特性：  
IOT表，行链接，行迁移。  
直接路径插入。  
获取数据字段到重做日志。  
跟踪DDL。  
生成键列的SQL\_REDO和SQL\_UNDO信息。  
LONG和LOB数据类型。

为了充分利用LogMiner，必须激活补充日志，此操作数据库不需要重启。

SQL> alter database add supplemental log data;

## LogMiner数据字典 ##

三种使用方式：  
1. 使用源数据库的数据字典（online catalog）  
如果被分析的表的结构没有发生变化，建议使用该选项分析重做日志和归档日志。  
该选项只能用于跟踪DML操作，不能用于跟踪DDL操作。（一般用来查询DML操作）  
使用当前数据库的数据字典，需要在启动LogMiner时执行如下操作：

SQL> execute dbms_logmnr.start_logmnr (options=>dbms_logmnr.dict_from_online_catalog);

2.摘取LogMiner字典到重做日志  
如果被分析的表结构发生了变化 ，建议使用该选项分析重做日志和归档日志。也可用于使用分析数据库分析重做日志和归档日志。  
操作如下：

SQL> execute dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);

3.摘取LogMiner数据字典到操作系统文件  
数据字典文件用于存放对象ID号和对象名的映射信息，该选项是为了与早期版本兼容而保留的（意思就是现在基本不用了）。  
如果要分析新建的对象，必须重新建立字典文件。  
操作如下：

SQL> execute dbms_logmnr_d.build ('logminer_dict.ora'，'/u01/app/oracle/arch'，dbms_logmnr_d.store_in_flat_file);

（可以用这种方式来查DDL的操作记录，如数据库没有配置utl\_file\_dir参数，需要配置后需要重启数据库）

## 使用示例 ##

## 使用源数据库数据字典分析日志 ##

### 创建测试环境 ###

--切换日志
    SQL> alter system switch logfile;
    System altered.
    
    --创建测试表
    SQL> create table logminer_table
      2  (
      3  id number(10) primary key,
      4  seq_test int,
      5  in_lob clob ,
      6  out_lob clob
      7  )
      8  lob (in_lob) store as (enable storage in row)
      9  lob (out_lob) store as (disable storage in row);
    
    --插入测试数据
    --插入的测试数据包括sequence，LOB类型
    SQL> insert into logminer_table values(1,seqogg.nextval,lpad('a',5000,'b'),rpad('a',5000,'b'));
    SQL> insert into logminer_table values(2,seqogg.nextval,lpad('c',5000,'d'),rpad('c',5000,'d'));
    SQL> insert into logminer_table values(3,seqogg.nextval,lpad('e',5000,'f'),rpad('e',5000,'f'));
    SQL> insert into logminer_table values(4,seqogg.nextval,lpad('g',5000,'h'),rpad('g',5000,'h'));
    SQL> insert into logminer_table values(5,seqogg.nextval,lpad('i',5000,'j'),rpad('i',5000,'j'));
    SQL> insert into logminer_table values(6,seqogg.nextval,lpad('k',5000,'l'),rpad('k',5000,'l'));
    SQL> commit;
    
    --修改测试数据
    SQL> update logminer_table set in_lob=replace(in_lob,'a','ssssssss') where id=1;
    SQL> commit;
    
    SQL> update logminer_table set out_lob=replace(in_lob,'a','wwwwwwww') where id=1;
    SQL> commit;
    
    --再次切换日志
    SQL> alter system switch logfile;
    System altered.

### 建立日志分析列表 ###

--查看最后生成的归档日志
    [root@ora11g arch]# ll
    -rw-r----- 1 oracle oinstall 32312832 Aug 31 01:23 1_77_952761439.dbf
    -rw-r----- 1 oracle oinstall  6698496 Aug 31 11:42 1_78_952761439.dbf
    -rw-r----- 1 oracle oinstall  5421056 Aug 31 14:09 1_79_952761439.dbf
    [root@ora11g arch]# 
    
    --添加要分析的归档日志文件
    --可以添加多个日志文件，执行多条dbms_logmnr.add_logfile命令即可
    --也可以删除某个日志文件，执行dbms_logmnr.removefile可以删除
    SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/arch/1_79_952761439.dbf',options=>dbms_logmnr.new);
    PL/SQL procedure successfully completed.

### 启动Log Miner ###

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
    PL/SQL procedure successfully completed.

### 查看日志分析结果 ###

SQL> set lines 300
    SQL> set pages 200
    SQL> set long 99999
    SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 
    
    SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='LOGMINER_TABLE';

### 简单结果分析 ###

从以下输出结果可以看出，oracle在插入sequence时是直接插入序列的值，这样在使用OGG进行逻辑复制的时候，即使主备两端sequence的nextval不相同也不影响数据正确插入，所以OGG中只要target端的sequence的nextval不小于source端的nextval即可。  
对于LOB对象，是分段插入的。

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='LOGMINER_TABLE' and rownum <=12;
    
    USERNAME                              SCN TIMESTAMP
    ------------------------------ ---------- ---------
    SQL_REDO
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    LILI                               926539 31-AUG-17
    create table logminer_table
    (
    id number(10) primary key,
    seq_test int,
    in_lob clob ,
    out_lob clob
    )
    lob (in_lob) store as (enable storage in row)
    lob (out_lob) store as (disable storage in row);
    
    LILI                               926711 31-AUG-17
    insert into "LILI"."LOGMINER_TABLE"("ID","SEQ_TEST","IN_LOB","OUT_LOB") values ('1','67',EMPTY_CLOB(),EMPTY_CLOB());
    
    LILI                               926711 31-AUG-17
    DECLARE
     loc_c CLOB;
     buf_c VARCHAR2(6156);
     loc_b BLOB;
     buf_b RAW(6156);
     loc_nc NCLOB;
     buf_nc NVARCHAR2(6156);
    BEGIN
     select "IN_LOB" into loc_c from "LILI"."LOGMINER_TABLE" where "ID" = '1' and "SEQ_TEST" = '67' for update;
    
    LILI                               926711 31-AUG-17
    
    
    LILI                               926712 31-AUG-17
    
     buf_c := 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
      dbms_lob.write(loc_c, 1024, 1, buf_c);
    END;
    
    
    LILI                               926712 31-AUG-17
    
     buf_c := 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
      dbms_lob.write(loc_c, 1024, 1025, buf_c);
    END;
    
    
    LILI                               926712 31-AUG-17
    
     buf_c := 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
      dbms_lob.write(loc_c, 1024, 2049, buf_c);
    END;
    
    
    LILI                               926712 31-AUG-17
    
     buf_c := 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
    bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbba';
      dbms_lob.write(loc_c, 928, 3073, buf_c);
    END;
    
    
    LILI                               926712 31-AUG-17
    
    
    LILI                               926712 31-AUG-17
    
    
    LILI                               926712 31-AUG-17
    DECLARE
     loc_c CLOB;
     buf_c VARCHAR2(6156);
     loc_b BLOB;
     buf_b RAW(6156);
     loc_nc NCLOB;
     buf_nc NVARCHAR2(6156);
    BEGIN
     select "OUT_LOB" into loc_c from "LILI"."LOGMINER_TABLE" where "ID" = '1' and "SEQ_TEST" = '67' for update;
    
    LILI                               926712 31-AUG-17
    
    
    
    12 rows selected.

## 使用字典文件分析DDL操作 ##

简单命令操作如下：

--可以设置utl_file_dir参数，将生成的数据字典放在该目录下，设置该参数需要重启数据库
    SQL> alter system set utl_file_dir='/home/oracle' scope=spfile;
    SQL> shutdown immediate
    SQL> startup
    SQL> execute dbms_logmnr_d.build ('lgmr_dict.ora','/home/oracle',dbms_logmnr_d.store_in_flat_file);
    SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/arch/1_79_952761439.dbf',options=>dbms_logmnr.new); 
    SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/lgmr_dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
    SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents
    SQL> execute dbms_logmnr.end_logmnr;

--也可以不用改参数，直接在命令中指定要存放的路径
    SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'lgmr_dict.ora',dictionary_location => '/home/oracle');
    SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/lgmr_dict.ora');