导航：首页 > 数据库 >

Log Miner的知识点有哪些

发表于：2026-11-03 作者：千家信息网编辑

千家信息网最后更新 2026年11月03日，这篇文章主要讲解了"Log Miner的知识点有哪些"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"Log Miner的知识点有哪些"吧！| Log M

千家信息网最后更新 2026年11月03日Log Miner的知识点有哪些

这篇文章主要讲解了"Log Miner的知识点有哪些"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"Log Miner的知识点有哪些"吧！

| 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 hh34: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 hh34: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;