日志分析利器|LogMiner工具使用

原创董宏伟云和恩墨 2023-07-11

467

Oracle LogMiner介绍

Oracle LogMiner允许通过SQL接口查询在线重做日志和归档日志文件。重做日志文件包含有关数据库活动历史的信息。在定位数据库相关操作历史记录的时候非常有用。

当启用补充日志时，重做流中会记录额外的信息，以便使重做日志文件中的信息无缺失记录。

补充日志

重做日志文件通常用于实例恢复和介质恢复。这些操作所需的数据会自动记录在重做日志文件中。然而，基于重做日志的应用程序可能需要在重做日志文件中记录额外的列。记录这些额外列的过程称为补充日志。

两种类型的补充日志组

有两种类型的补充日志组，用于确定何时记录日志组中的列：

无条件补充日志组：指定列的前映像在任何时候更新行时都会被记录，无论更新是否影响了指定的列。有时也称为ALWAYS日志组。
有条件补充日志组：只有当日志组中的至少一个列被更新时，才会记录所有指定列的前映像。

补充日志组可以是系统生成的或用户定义的。

两种级别的补充日志

数据库级别的补充日志

有两种数据库级别的补充日志：最小补充日志和标识键补充日志。最小补充日志对生成重做日志文件的数据库没有很大的开销。然而，启用全局标识键补充日志可能会对生成重做日志文件的数据库造成开销。Oracle建议至少为LogMiner启用最小补充日志。

最小补充日志
最小补充日志记录了LogMiner识别、分组和合并与DML更改相关的重做操作所需的最少信息。它确保LogMiner（以及任何构建在LogMiner技术之上的产品）具有足够的信息来支持行链接和各种存储布局，例如集群表和索引组织表。要启用最小补充日志，请执行以下SQL语句：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
select SUPPLEMENTAL_LOG_DATA_MIN from v$database ;
--ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
复制

标识键补充日志
当重做日志文件不在源数据库实例中挖掘时，标识键补充是必要的，例如，当重做日志文件将在逻辑备用数据库中挖掘时。

有如下几种不同的补充日志组

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

--ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

复制

表级别的补充日志

表级别的标识键日志
表级别的标识键日志提供了与数据库级别相同的选项:all、主键、外键和唯一键。但是，当在表级别指定标识键日志时，只会影响指定的表。

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;


--select table_name, log_group_type,LOG_GROUP_NAME from dba_log_groups;
--alter table dhw.test1 drop SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
or
--alter table [TABLE_NAME] drop supplemental log group [GROUP_NAME]
复制

表级别用户自定义补充日志

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID) ALWAYS;
复制

LogMiner数据字典选项

LogMiner需要一个字典来将对象ID转换为对象名称，以便在返回重做数据时使用。LogMiner提供了三种选项：

Oracle建议当你能够访问重做日志文件所创建的源数据库，并且不预期对所关心表的列定义进行更改时，使用此选项。这是最高效且易于使用的选项。

从重做日志文件中提取LogMiner字典

Oracle建议当你不希望能够访问重做日志文件所创建的源数据库，或者预期会对所关心表的列定义进行更改时，使用此选项。当字典被提取到重做日志流时，不能执行DDL语句。

将LogMiner字典提取到平面文件中

此选项是为了与之前的版本保持向后兼容而保留的。该选项不能保证事务一致性。Oracle建议你使用在线目录或从重做日志文件中提取字典。

选择LogMiner字典的决策树

无补充日志时

测试数据操作

create table test1(id number,name varchar2(30));
insert into test1 values(1,'a');
commit;
insert into test1 values(2,'b');
commit ;
insert into test1 select * from test1;
commit ;
delete from test1 where id=1;
commit;
truncate table test1;
复制

进行日志挖掘


select name from v$archived_log where first_time >= to_date('2023071110','yyyymmddhh24mi') 
and first_time <= to_date('2023071115','yyyymmddhh24mi') and dest_id=1 and name is not null order by thread#,sequence#;

begin
dbms_logmnr.add_logfile(logfilename=>'/u01/arch/1_32_1141307293.dbf',options=>dbms_logmnr.new);
dbms_logmnr.add_logfile(logfilename=>'/u01/arch/1_33_1141307293.dbf',options=>dbms_logmnr.addfile);
DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
end;
/
create table logmnrtab  as select * from v$logmnr_contents ;

exec dbms_logmnr.end_logmnr();
复制

分析数据

在不开启补充日志的情况下，发现记录不全，并且部分字段是unknown

set lines 200
col sql_redo for a100
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select t.scn,t.timestamp,t.username,t.sql_redo from logmnrtab t where t.table_name='TEST1';

       SCN TIMESTAMP           USERNAME                       SQL_REDO
---------- ------------------- ------------------------------ -------------------------------------------------------------------
   3162238 2023-07-10 21:47:46 UNKNOWN                        create table test1(id number,name varchar2(30));
   3162269 2023-07-10 21:48:10 UNKNOWN                        insert into "DHW"."TEST1"("ID","NAME") values ('1','a');
   3162437 2023-07-10 21:49:44 UNKNOWN                        truncate table test1;
复制

数据库级别补充日志下挖掘

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
复制

发现日志记录非常全面


SQL> select t.scn,t.timestamp,t.sql_redo from v$logmnr_contents t where t.table_name='TEST1';

       SCN TIMESTAMP           SQL_REDO
---------- ------------------- ----------------------------------------------------------------------------------------------------
   3196170 2023-07-11 10:48:28 drop table test1 purge ;
   3196191 2023-07-11 10:48:30 create table test1(id number,name varchar2(30));
   3196200 2023-07-11 10:48:30 insert into "DHW"."TEST1"("ID","NAME") values ('1','a');
   3196203 2023-07-11 10:48:30 insert into "DHW"."TEST1"("ID","NAME") values ('2','b');
   3196205 2023-07-11 10:48:30 insert into "DHW"."TEST1"("ID","NAME") values ('1','a');
   3196205 2023-07-11 10:48:30 insert into "DHW"."TEST1"("ID","NAME") values ('2','b');
   3196207 2023-07-11 10:48:30 delete from "DHW"."TEST1" where "ID" = '1' and "NAME" = 'a' and ROWID = 'AAAVxIAAEAAAAozAAA';
   3196207 2023-07-11 10:48:30 delete from "DHW"."TEST1" where "ID" = '1' and "NAME" = 'a' and ROWID = 'AAAVxIAAEAAAAozAAC';
   3196223 2023-07-11 10:48:30 truncate table test1;

9 rows selected.

复制

表级别补充日志下挖掘

这种只开启表级别的附件日志情况下，挖掘出来的记录并不完全。

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
alter table dhw.test1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
复制


SQL> select t.scn,t.timestamp,t.sql_redo from v$logmnr_contents t where t.table_name='TEST1';

       SCN TIMESTAMP           SQL_REDO
---------- ------------------- ----------------------------------------------------------------------------------------------------
   3196769 2023-07-11 10:52:43 drop table test1 purge ;
   3196833 2023-07-11 10:53:06  create table test1(id number,name varchar2(30));
   3196862 2023-07-11 10:53:30 alter table dhw.test1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   3196879 2023-07-11 10:53:39 insert into "DHW"."TEST1"("ID","NAME") values ('1','a');
   3196899 2023-07-11 10:53:40 truncate table test1;

复制

总结

1、在不开启数据库级别最小补充日志的情况下，日志挖掘也可以执行，但是记录可能不全。单独开启表级别的补充日志的情况下，即使针对单表的挖掘记录也可能不全。
2、如果只是本地挖掘，表的列定义没有更改时，建议使用在线目录的数据字典（DICT_FROM_ONLINE_CATALOG）进行挖掘。
3、在Oracle Database 19c（19.1）中，dbms_logmnr.start_logmnr包的CONTINUOUS_MINE选项已不再支持，并且不再可用。
4、当在使用DBMS_LOGMNR.START_LOGMNR时使用COMMITTED_DATA_ONLY选项时，只会在V$LOGMNR_CONTENTS视图中显示挖掘范围内已提交事务的行。

订阅号：DongDB手记
墨天轮：https://www.modb.pro/u/231198