Logminer简介及应用

充浩波

2023-12-01

背景

近期在研究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数据字典，否则无法读懂分析结果。

限制

源数据库和分析数据库可以是同一个数据库。
源数据库与分析数据库必须运行在相同的硬件平台上（与字节序有关）。
分析数据库的版本不能低于源数据库的版本。
分析数据库与源数据库必须具有相同的字符集。
LogMiner数据字典必须在源数据库中生成。
同时分析多个重做日志和归档日志时，这些日志必须是同一个源数据库的日志。
同时分析多个重做日志和归档日志时，这些日志必须具有相同的resetlogs scn。
重做日志和归档日志必须在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');