合 Oracle日志挖掘工具LogMiner的使用

发布日期 2022年4月14日 · 已更新 2023年8月25日

0 901 5

👉 本文共约4138个字，系统预计阅读时间或需16分钟。

简介
安装LogMiner工具
与LogMiner相关的数据字典视图
LogMiner的数据字典
①将字典文件提取为一个Flat File（平面文件或中间接口文件）
②　将字典文件提取为Redo日志
③使用Online Catalog（联机日志）
跟踪DDL语句
过滤LogMiner返回的数据
典型的LogMiner步骤
常用SQL
利用平面文件作为数据字典的问题
模拟情况
总结
参考

简介

众所周知，所有对用户数据和数据字典的改变都记录在Oracle的Redo日志中，因此，Redo日志包含了所有进行恢复操作所需要的信息。但是，原始的Redo日志文件无法看懂，所以，Oracle从8i以后提供了一个非常有用的分析工具，称为LogMiner。使用该工具可以轻松获得Redo日志文件（包含归档日志文件）中的具体内容。特别是该工具可以分析出所有对于数据库的DML操作（INSERT、UPDATE、DELETE等）语句。Oracle 9i后可以分析DDL语句，另外还可分析得到一些必要的回滚SQL语句。LogMiner一个最重要的用途就是不用全部恢复数据库就可以恢复数据库的某个变化。该工具特别适用于调试、审计或者回退某个特定的事务。

LogMiner工具既可以用来分析在线日志，也可以用来分析离线日志文件，既可以分析本身自己数据库的重作日志文件，也可以用来分析其它数据库的重作日志文件。当分析其它数据库的重作日志文件时，需要注意的是，LogMiner必须使用被分析数据库实例产生的字典文件，而不是安装LogMiner的数据库产生的字典文件，另外，必须保证安装LogMiner数据库的字符集和被分析数据库的字符集相同。源数据库（Source Database）平台必须和分析数据库（Mining Database）平台一样。

Oracle通过LogMiner工具对Redo日志进行挖掘，显示出一系列可读的信息，该过程称为日志挖掘。LogMiner通过V$LOGMNR_CONTENTS视图显示Redo日志中的信息。

总的说来，LogMiner工具的主要用途有：

1、跟踪数据库的变化：可以离线地跟踪数据库的变化，而不会影响在线系统的性能

2、回退数据库的变化：回退特定的变化数据，减少Point-In-Time Recovery的执行

3、优化和扩容计划：可通过分析日志文件中的数据以分析数据的增长模式

4、确定数据库的逻辑损坏时间：准确定位操作执行的时间和SCN

5、确定事务级要执行的精细逻辑恢复操作，可以取得相应的Undo操作

6、执行后续审计

安装LogMiner工具

在默认情况下，Oracle已经安装了LogMiner工具。若是没有安装，则可以运行下面两个脚本：

$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql

1 2	$ORACLE_HOME/rdbms/admin/dbmslm.sql $ORACLE_HOME/rdbms/admin/dbmslmd.sql

这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。若要使普通用户具有日志挖掘的权限，则可以执行如下的SQL进行赋权：

GRANT EXECUTE ON  DBMS_LOGMNR TO LHR_TEST;

1	GRANT EXECUTE ON DBMS_LOGMNR TO LHR_TEST;

脚本执行完毕后，LogMiner包含两个PL/SQL包和几个视图：

1、DBMS_LOGMNR_D包，包括一个用于提取数据字典信息的过程，即DBMS_LOGMNR_D.BUILD()过程，还包含一个重建LogMiner表的过程，DBMS_LOGMNR_D.SET_TABLESPACE。在默认情况下，LogMiner的表是建在SYSTEM表空间下的。 可以设置单独的表空间：

create tablespace ts_LOGMNR datafile '/sda4/u01/app/oracle/oradata/orcllinux/logmnr.dbf' size 1G autoextend on next 2M ;
exec dbms_logmnr_d.set_tablespace('ts_logmnr');

1 2	create tablespace ts_LOGMNR datafile '/sda4/u01/app/oracle/oradata/orcllinux/logmnr.dbf' size 1G autoextend on next 2M ; exec dbms_logmnr_d.set_tablespace('ts_logmnr');

2、DBMS_LOGMNR包，它有3个存储过程：

ADD_LOGFILE(NAME VARCHAR2,OPTIONS NUMBER) 用来添加或删除用于分析的日志文件
START_LOGMNR(START_SCN NUMBER,END_SCN NUMBER,START_TIME NUMBER,END_TIME NUMBER,DICTFILENAME VARCHAR2,OPTIONS NUMBER) 用来开启日志分析，同时确定分析的时间或SCN窗口以及确认是否使用提取出来的数据字典信息
END_LOGMNR()存储过程用来终止分析会话，它将回收LogMiner所占用的内存

与LogMiner相关的数据字典视图

V$LOGHIST：显示历史日志文件的一些信息
V$LOGMNR_DICTIONARY：因为LOGMINER可以有多个字典文件，所以该视图显示字典文件信息
V$LOGMNR_PARAMETERS：显示LOGMINER的参数
V$LOGMNR_LOGS：显示用于分析的日志列表信息
V$LOGMNR_CONTENTS：LOGMINER结果


SELECT * FROM V$LOGMNR_CONTENTS         ;
SELECT * FROM V$LOGMNR_LOGS       ;
SELECT * FROM V$LOGMNR_DICTIONARY   ;
SELECT * FROM V$LOGMNR_PARAMETERS   ;
SELECT * FROM V$LOGMNR_LOGFILE      ;
SELECT * FROM V$LOGMNR_PROCESS      ;
SELECT * FROM V$LOGMNR_TRANSACTION    ;
SELECT * FROM V$LOGMNR_REGION     ;
SELECT * FROM V$LOGMNR_CALLBACK     ;
SELECT * FROM V$LOGMNR_SESSION      ;
SELECT * FROM V$LOGMNR_LATCH      ;
SELECT * FROM V$LOGMNR_DICTIONARY_LOAD  ;
SELECT * FROM V$LOGMNR_SYS_OBJECTS    ;
SELECT * FROM V$LOGMNR_SYS_DBA_SEGS   ;
SELECT * FROM V$LOGMNR_EXTENTS      ;
SELECT * FROM V$LOGMNR_DBA_OBJECTS    ;
SELECT * FROM V$LOGMNR_OBJECT_SEGMENTS  ;
SELECT * FROM V$LOGMNR_STATS;

SELECT * FROM V$LOGMNR_CONTENTS ;

SELECT * FROM V$LOGMNR_LOGS ;

SELECT * FROM V$LOGMNR_DICTIONARY ;

SELECT * FROM V$LOGMNR_PARAMETERS ;

SELECT * FROM V$LOGMNR_LOGFILE ;

SELECT * FROM V$LOGMNR_PROCESS ;

SELECT * FROM V$LOGMNR_TRANSACTION ;

SELECT * FROM V$LOGMNR_REGION ;

SELECT * FROM V$LOGMNR_CALLBACK ;

SELECT * FROM V$LOGMNR_SESSION ;

SELECT * FROM V$LOGMNR_LATCH ;

SELECT * FROM V$LOGMNR_DICTIONARY_LOAD ;

SELECT * FROM V$LOGMNR_SYS_OBJECTS ;

SELECT * FROM V$LOGMNR_SYS_DBA_SEGS ;

SELECT * FROM V$LOGMNR_EXTENTS ;

SELECT * FROM V$LOGMNR_DBA_OBJECTS ;

SELECT * FROM V$LOGMNR_OBJECT_SEGMENTS ;

SELECT * FROM V$LOGMNR_STATS;

LogMiner的数据字典

为了完全地转换Redo日志中的内容，LogMiner需要访问一个数据库字典。LogMiner使用该字典将Oracle内部的对象标识符和数据类型转换为对象名称和外部的数据格式。没有字典，LogMiner将使用16进制字符显示内部对象ID。

例如，对于如下的SQL语句：

INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);

1	INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);

在没有数据字典的情况下，LogMiner将显示为：

INSERT INTO Object#2581(col#1, col#2) VALUES (hextoraw('4a6f686e20446f65'),hextoraw('c306'));

1	INSERT INTO Object#2581(col#1, col#2) VALUES (hextoraw('4a6f686e20446f65'),hextoraw('c306'));

LogMiner提供了3种提取字典文件的方式：

①　将字典文件提取为一个Flat File（平面文件或中间接口文件）

②　将字典文件提取为Redo日志

③　使用Online Catalog（联机日志）

下面分别介绍这3种方式：

①将字典文件提取为一个Flat File（平面文件或中间接口文件）

为了将数据库字典信息提取为Flat File，需要使用带有STORE_IN_FLAT_FILE参数的DBMS_LOGMNR_D.BUILD程序。DBMS_LOGMNR_D.BUILD程序需要访问一个能够放置字典文件的目录。因为PL/SQL 程序通常不能直接访问用户目录，必须手动指定一个由DBMS_LOGMNR_D.BUILD程序使用的目录。为了指定该目录，必须修改初始化文件中的UTL_FILE_DIR参数：

ALTER SYSTEM SET UTL_FILE_DIR ='/home/oracle' SCOPE=SPFILE;

1	ALTER SYSTEM SET UTL_FILE_DIR ='/home/oracle' SCOPE=SPFILE;

然后重新启动数据库。

确保在创建Flat File文件的过程中，不能有DDL操作被执行。在创建Flat File文件时，数据库必须处于OPEN状态，然后执行DMBS_LOGMNR_D.BUILD程序：

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/home/oracle');

1	EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/home/oracle');

脚本执行完成后会在/home/oracle下生成一个dictionary.ora的文本文件。

不过，从Oracle 18c开始，需要使用DIRECTORY来创建目录了：

-- 创建一个目录对象
CREATE DIRECTORY my_dir AS '/home/oracle';

-- 授权给用户
GRANT all  ON DIRECTORY my_dir TO lhr;

-- 生成字典
EXECUTE DBMS_LOGMNR_D.BUILD('dic.ora', 'LOGMNR');

-- 创建一个目录对象

CREATE DIRECTORY my_dir AS '/home/oracle';

-- 授权给用户

GRANT all ON DIRECTORY my_dir TO lhr;

-- 生成字典

EXECUTE DBMS_LOGMNR_D.BUILD('dic.ora', 'LOGMNR');

该字典文件中包含一系列的建表语句和插入语句，空库产生的字典文件也有40M，例如：


-- *************** LOGMNR DICTIONARY FILE *************** 
-- 
-- The LogMnr Dictionary file contains the dictionary data from the 
-- target database. The dictionary data is collected from the dictionary
-- tables defined in the target database. The LogMnr_Dictionary package
-- build script queries the dictionary tables and reconstructs the table
-- contents as a set of "SQL like" commands n the dictionary file.

-- The dictionary file contains commands to create a table, insert into a
-- table and create an index to a table. To protect against an unintentional
-- execution of the dictionary file, these commands are named 
-- 
--      CREATE_TABLE  - create a table (notice the underbar)
--      CREATE_INDEX  - create an index for a  table (notice the underbar)
--      INSERT_INTO   - insert a row into table (again notice the underbar)

-- The command syntax is identical to the SQL command except for the
-- underbar replacing the space. 
-- 

-- The dictionary file can be converted to an executable SQL by globally
-- replacing all occurences of the following text strings : 
-- 
--      CREATE_TABLE => CREATE TABLE
--      CREATE_INDEX => CREATE INDEX
--      INSERT_INTO  => INSERT INTO
--      ,, => ,NULL,--      (, => (NULL,--      ,) => ,NULL)-- 

-- For example, CREATE_TABLE may be easily globally replaced through the
-- following SED request at a system command prompt
-- 
--  unix_box1>> sed -e/CREATE_TABLE/CREATE TABLE/ dictionary.ora > dictionary.sql
-- 
-- 
-- 

CREATE_TABLE DICTIONARY_TABLE ( DB_NAME VARCHAR2(9), DB_ID NUMBER(20), DB_CREATED VARCHAR2(20), DB_DICT_CREATED VARCHAR2(20), DB_RESETLOGS_CHANGE# NUMBER(22), DB_RESETLOGS_TIME VARCHAR2(20), DB_VERSION_TIME VARCHAR2(20), DB_REDO_TYPE_ID VARCHAR2(8), DB_REDO_RELEASE VARCHAR2(60), DB_CHARACTER_SET VARCHAR2(30), DB_VERSION VARCHAR2(64), DB_STATUS VARCHAR2(64), DB_DICT_MAXOBJECTS NUMBER(22), DB_DICT_OBJECTCOUNT NUMBER(22), DB_DICT_SCN NUMBER(22), DB_THREAD_MAP RAW(8), DB_TXN_SCNBAS NUMBER(22), DB_TXN_SCNWRP NUMBER(22));

INSERT_INTO DICTIONARY_TABLE VALUES ('LHR11G',2007947551,'07/24/2020 08:10:07','08/24/2023 16:01:16',925702,'07/24/2020 08:10:09','07/24/2020 08:10:07','','','AL32UTF8','11.2.0.4.0','Production',92176,86961,1146205,,1146601,0);

CREATE_TABLE OBJ$_TABLE (OBJ# NUMBER(22), DATAOBJ# NUMBER(22), OWNER# NUMBER(22), NAME VARCHAR2(30), NAMESPACE NUMBER(22), SUBNAME VARCHAR2(30), TYPE# NUMBER(22), CTIME DATE, MTIME DATE, STIME DATE, STATUS NUMBER(22), REMOTEOWNER VARCHAR2(30), LINKNAME VARCHAR2(128), FLAGS NUMBER(22), OID$ RAW(16), SPARE1 NUMBER(22), SPARE2 NUMBER(22), SPARE3 NUMBER(22), SPARE4 VARCHAR2(1000), SPARE5 VARCHAR2(1000), SPARE6 DATE ); 

INSERT_INTO OBJ$_TABLE VALUES (20,2,0,'ICOL$',1,'',2,to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:47:37', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,0,1,0,'','', );
INSERT_INTO OBJ$_TABLE VALUES (46,46,0,'I_USER1',4,'',1,to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,0,65535,0,'','', );
INSERT_INTO OBJ$_TABLE VALUES (28,28,0,'CON$',1,'',2,to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:52:40', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,0,1,0,'','', );

-- *************** LOGMNR DICTIONARY FILE ***************

-- The LogMnr Dictionary file contains the dictionary data from the

-- target database. The dictionary data is collected from the dictionary

-- tables defined in the target database. The LogMnr_Dictionary package

-- build script queries the dictionary tables and reconstructs the table

-- contents as a set of "SQL like" commands n the dictionary file.

-- The dictionary file contains commands to create a table, insert into a

-- table and create an index to a table. To protect against an unintentional

-- execution of the dictionary file, these commands are named

-- CREATE_TABLE - create a table (notice the underbar)

-- CREATE_INDEX - create an index for a table (notice the underbar)

-- INSERT_INTO - insert a row into table (again notice the underbar)

-- The command syntax is identical to the SQL command except for the

-- underbar replacing the space.

-- The dictionary file can be converted to an executable SQL by globally

-- replacing all occurences of the following text strings :

-- CREATE_TABLE => CREATE TABLE

-- CREATE_INDEX => CREATE INDEX

-- INSERT_INTO => INSERT INTO

-- ,, => ,NULL,-- (, => (NULL,-- ,) => ,NULL)--

-- For example, CREATE_TABLE may be easily globally replaced through the

-- following SED request at a system command prompt

-- unix_box1>> sed -e/CREATE_TABLE/CREATE TABLE/ dictionary.ora > dictionary.sql

CREATE_TABLE DICTIONARY_TABLE ( DB_NAME VARCHAR2(9), DB_ID NUMBER(20), DB_CREATED VARCHAR2(20), DB_DICT_CREATED VARCHAR2(20), DB_RESETLOGS_CHANGE# NUMBER(22), DB_RESETLOGS_TIME VARCHAR2(20), DB_VERSION_TIME VARCHAR2(20), DB_REDO_TYPE_ID VARCHAR2(8), DB_REDO_RELEASE VARCHAR2(60), DB_CHARACTER_SET VARCHAR2(30), DB_VERSION VARCHAR2(64), DB_STATUS VARCHAR2(64), DB_DICT_MAXOBJECTS NUMBER(22), DB_DICT_OBJECTCOUNT NUMBER(22), DB_DICT_SCN NUMBER(22), DB_THREAD_MAP RAW(8), DB_TXN_SCNBAS NUMBER(22), DB_TXN_SCNWRP NUMBER(22));

INSERT_INTO DICTIONARY_TABLE VALUES ('LHR11G',2007947551,'07/24/2020 08:10:07','08/24/2023 16:01:16',925702,'07/24/2020 08:10:09','07/24/2020 08:10:07','','','AL32UTF8','11.2.0.4.0','Production',92176,86961,1146205,,1146601,0);

CREATE_TABLE OBJ$_TABLE (OBJ# NUMBER(22), DATAOBJ# NUMBER(22), OWNER# NUMBER(22), NAME VARCHAR2(30), NAMESPACE NUMBER(22), SUBNAME VARCHAR2(30), TYPE# NUMBER(22), CTIME DATE, MTIME DATE, STIME DATE, STATUS NUMBER(22), REMOTEOWNER VARCHAR2(30), LINKNAME VARCHAR2(128), FLAGS NUMBER(22), OID$ RAW(16), SPARE1 NUMBER(22), SPARE2 NUMBER(22), SPARE3 NUMBER(22), SPARE4 VARCHAR2(1000), SPARE5 VARCHAR2(1000), SPARE6 DATE );

INSERT_INTO OBJ$_TABLE VALUES (20,2,0,'ICOL$',1,'',2,to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:47:37', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,0,1,0,'','', );

INSERT_INTO OBJ$_TABLE VALUES (46,46,0,'I_USER1',4,'',1,to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,0,65535,0,'','', );

INSERT_INTO OBJ$_TABLE VALUES (28,28,0,'CON$',1,'',2,to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:52:40', 'MM/DD/YYYY HH24:MI:SS'),to_date('08/24/2013 11:37:35', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,0,1,0,'','', );

还有很多的其它SQL。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

②　将字典文件提取为Redo日志

为了将字典文件提取为Redo日志，数据库必须处于OPEN状态，并且处于归档模式。将字典提取为Redo日志的过程中，数据库系统不能有DDL语句被执行。为了将字典提取为Redo日志，需要使用带有STORE_IN_REDO_FILES参数的DBMS_LOGMNR_D.BUILD程序：

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

1	SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

需要注意的是，将字典文件提取为Redo文件的时候需要开启附加日志，如下所示：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

1	ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

在这些Redo日志被归档之后，可以通过查询V$ARCHIVED_LOG视图来查询：

SELECT *
 FROM V$ARCHIVED_LOG A
 WHERE A.NAME IS NOT NULL
  AND (A.DICTIONARY_BEGIN = 'YES' OR A.DICTIONARY_END = 'YES');

SELECT *

FROM V$ARCHIVED_LOG A

WHERE A.NAME IS NOT NULL

AND (A.DICTIONARY_BEGIN = 'YES' OR A.DICTIONARY_END = 'YES');

如果将字典信息提取为Redo文件，那么在使用DBMD_LOGMNR.ADD_LOGFILE指定所需要分析的日志文件时，需要将这些包含字典信息的Redo文件也添加进去。同时在使用START_LOGMNR开始分析时，需要指定DICT_FROM_REDO_LOGS的参数。

③使用Online Catalog（联机日志）

为了使LogMiner直接使用数据库当前使用的字典，在开始LogMiner时可以指定将联机目录作为字典源：

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

1	SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

使用联机目录，意味着不需要再提取字典文件，是开始分析日志的最快的方式。除了可以分析联机Redo日志外，还可以在和产生归档日志文件相同的系统上分析归档日志文件。然而，记住联机目录只能重建应用于表的最新版本上的SQL语句。一旦表被修改，联机目录就无法反映出表之前的版本。这就意味着LogMiner不能重建执行于表的旧版本上的SQL语句。

跟踪DDL语句

当LogMiner被启动时，它会自动创建自己的内部字典。如果源字典是Flat File字典或Redo日志中的字典，则可以使用DDL_DICT_TRACKING参数来跟踪DDL语句。DDL跟踪默认是关闭的。为了打开这一功能，可以在启动LogMiner时使用DDL_DICT_TRACKING参数：

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING);

1	SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING);

当使用DDL_DICT_TRACKING时，需要注意如下几点：

当使用联机目录（Online catalog）时，也就是当使用DICT_FROM_ONLINE_CATALOG参数时，是不能使用DDL_DICT_TRACKING选项的。
使用DDL_DICT_TRACKING时，要求数据库处于OPEN状态。
尽量为LogMiner的表提供一个单独的表空间。默认情况下LogMiner的表是创建在SYSTEM表空间中的。使用DBMS_LOGMNR_D.SET_TABLESPACE可以在一个单独的表空间中重建这些LogMiner的表：SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');。

过滤LogMiner返回的数据

可以使用COMMITTED_DATA_ONLY参数来只显示那些被提交的事务：

SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

1	SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

当使用这一参数时，LogMiner将所有的DML操作按照事务的关系组合在一起。这些事务按照它们被提交的顺序显示出来。

可以使用SKIP_CORRUPTION参数来忽略Redo日志中的所有错误：

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.SKIP_CORRUPTION);

1	SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.SKIP_CORRUPTION);

可以使用使用STARTTIME和ENDTIME参数按照时间过滤数据，还可以使用STARTSCN和ENDSCN参数按照SCN（System Change Number）来过滤数据。

BEGIN
    DBMS_LOGMNR.START_LOGMNR(STARTSCN => 23573690,
    ENDSCN   => 23632671,
    OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/
BEGIN 
    DBMS_LOGMNR.START_LOGMNR(STARTTIME => '2015-06-03 11:10:12',
    ENDTIME => '2015-06-03 11:13:06',
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE); 
END; 
/

BEGIN

DBMS_LOGMNR.START_LOGMNR(STARTSCN => 23573690,

ENDSCN => 23632671,

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

END;

BEGIN

DBMS_LOGMNR.START_LOGMNR(STARTTIME => '2015-06-03 11:10:12',

ENDTIME => '2015-06-03 11:13:06',

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

END;

典型的LogMiner步骤

一个典型的LogMiner的操作包含如下步骤：

1、进行初始化设置：开启附加日志，设置LogMiner的表空间，设置UTL_FILE_DIR参数的值；

2、提取一个字典：将字典文件提取为Flat File或Redo日志，或者直接使用Online Catalog；

3、指定需要分析的Redo日志文件：利用DBMS_LOGMNR.ADD_LOGFILE来添加日志；

4、开始LogMiner：执行DBMS_LOGMNR.START_LOGMNR来启动LogMiner；

5、查询V$LOGMNR_CONTENTS视图；

6、结束LogMiner：通过执行EXECUTE DBMS_LOGMNR.END_LOGMNR来结束分析。

下面给出一个使用Online Catalog作为数据字典进行日志挖掘的示例：

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
Session altered.
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------
+DATA/orclasm/onlinelog/group_3.263.850260263
+FRA/orclasm/onlinelog/group_3.259.850260267
+DATA/orclasm/onlinelog/group_2.262.850260259
+FRA/orclasm/onlinelog/group_2.258.850260263
+DATA/orclasm/onlinelog/group_1.261.850260255
+FRA/orclasm/onlinelog/group_1.257.850260259
6 rows selected.
SQL> execute dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_1.261.850260255',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_2.262.850260259',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_3.263.850260263',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);
PL/SQL procedure successfully completed.
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='T' and OPERATION='INSERT';
SQL_REDO                                              SQL_UNDO
-------------------------------------------  ------------------------------------------------------------------------ 
insert into "LHR"."T"("ID") values ('1');        delete from "LHR"."T" where "ID" = '1' and ROWID = 'AAAaN7AAEAAAnhjAAA';
insert into "LHR"."T"("ID") values ('2');        delete from "LHR"."T" where "ID" = '2' and ROWID = 'AAAaN7AAEAAAnhjAAB';

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';

Session altered.

SQL> select member from v$logfile;

MEMBER

---------------------------------------------------

+DATA/orclasm/onlinelog/group_3.263.850260263

+FRA/orclasm/onlinelog/group_3.259.850260267

+DATA/orclasm/onlinelog/group_2.262.850260259

+FRA/orclasm/onlinelog/group_2.258.850260263

+DATA/orclasm/onlinelog/group_1.261.850260255

+FRA/orclasm/onlinelog/group_1.257.850260259

6 rows selected.

SQL> execute dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_1.261.850260255',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_2.262.850260259',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_3.263.850260263',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='T' and OPERATION='INSERT';

SQL_REDO SQL_UNDO

------------------------------------------- ------------------------------------------------------------------------

insert into "LHR"."T"("ID") values ('1'); delete from "LHR"."T" where "ID" = '1' and ROWID = 'AAAaN7AAEAAAnhjAAA';

insert into "LHR"."T"("ID") values ('2'); delete from "LHR"."T" where "ID" = '2' and ROWID = 'AAAaN7AAEAAAnhjAAB';

常用SQL


-- 附加日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
alter database add supplemental log data(primary key) columns;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_pk FROM V$DATABASE;

-- 找到需要进行日志挖掘的归档日志
list archivelog all completed between '2017-01-01 16:20:00'  and '2017-01-01 16:25:00';
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';

exec  dbms_logmnr.add_logfile('归档日志文件', Options => dbms_logmnr.new);
exec  dbms_logmnr.add_logfile('归档日志文件', Options => dbms_logmnr.ADDFILE);

-- 若归档日志不在本地，则需要恢复相应的归档日志到本地目录。
   run {allocate channel ci type disk;
      set archivelog destination to '/tmp';
      restore archvielog from logseq xxx until logseq xxx;
      release channel ci;
    };

--------------  利用平面文件作为数据字典
alter system set utl_file_dir='/home/oracle/' scope=spfile; --然后重启库
exec dbms_logmnr_d.build('log.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);
exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_952.463.869481079',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_953.462.869481107',dbms_logmnr.addfile);
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log.ora');
create table LHR.testlog as select * from  v$logmnr_contents a;
EXEC DBMS_LOGMNR.END_LOGMNR;

-------------------------- redo 字典
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
exec  dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
 select a.*
   from v$archived_log a
  WHERE a.name IS NOT NULL
    and (a.DICTIONARY_BEGIN = 'YES' or a.DICTIONARY_END = 'YES');

-------------- 利用在线日志作为数据字典
SELECT '  dbms_logmnr.add_logfile(''' || MEMBER || ''');' FROM v$logfile;

BEGIN
  dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_3.263.850260263',dbms_logmnr.new)  ;
  dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_2.262.850260259',dbms_logmnr.ADDFILE)  ;
  dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_1.261.850260255',dbms_logmnr.ADDFILE))  ;
  dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
END;

SELECT scn, sql_redo, a.SQL_UNDO, a.*
  FROM v$logmnr_contents a
 WHERE a.OPERATION = 'INSERT'
   and a.TABLE_NAME = 'AABB';

create table testlog as select * from  v$logmnr_contents a;

begin
  dbms_logmnr.end_logmnr();
end;

EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); 

-------------------------- online字典
begin
 dbms_logmnr.start_logmnr(startScn => 23573690,
                          endScn   => 23632671,
                          Options  => dbms_logmnr.DICT_FROM_ONLINE_CATALOG + dbms_logmnr.CONTINUOUS_MINE);
end;

begin
 dbms_logmnr.start_logmnr(startScn => 23573690,
                                   endScn   => 23632671,
                                   Options  => dbms_logmnr.DICT_FROM_REDO_LOGS +
                                               dbms_logmnr.CONTINUOUS_MINE);
end;

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss'
list archivelog all  completed between '2015-06-03 08:00:00' and '2015-06-03 09:00:00';
exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2015_06_03/o1_mf_1_505_7w35gdnx_.arc', sys.dbms_logmnr.New); 
BEGIN 
    DBMS_LOGMNR.START_LOGMNR( 
    STARTTIME => '2015-06-03 11:10:12', 
    ENDTIME => '2015-06-03 11:13:06', 
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE); 
END; 
/

-- 查询挖掘到的结果
select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A where table_name='XXXX' and OPERATION='INSERT' order by a.SCN;

-- 附加日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

alter database add supplemental log data(primary key) columns;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_pk FROM V$DATABASE;

-- 找到需要进行日志挖掘的归档日志

list archivelog all completed between '2017-01-01 16:20:00' and '2017-01-01 16:25:00';

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';

exec dbms_logmnr.add_logfile('归档日志文件', Options => dbms_logmnr.new);

exec dbms_logmnr.add_logfile('归档日志文件', Options => dbms_logmnr.ADDFILE);

-- 若归档日志不在本地，则需要恢复相应的归档日志到本地目录。

run {allocate channel ci type disk;

set archivelog destination to '/tmp';

restore archvielog from logseq xxx until logseq xxx;

release channel ci;

};

-------------- 利用平面文件作为数据字典

alter system set utl_file_dir='/home/oracle/' scope=spfile; --然后重启库

exec dbms_logmnr_d.build('log.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);

exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_952.463.869481079',dbms_logmnr.new);

exec dbms_logmnr.add_logfile('+FRA/orclasm/archivelog/2015_01_20/thread_1_seq_953.462.869481107',dbms_logmnr.addfile);

exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log.ora');

create table LHR.testlog as select * from v$logmnr_contents a;

EXEC DBMS_LOGMNR.END_LOGMNR;

-------------------------- redo 字典

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

exec dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);

select a.*

from v$archived_log a

WHERE a.name IS NOT NULL

and (a.DICTIONARY_BEGIN = 'YES' or a.DICTIONARY_END = 'YES');

-------------- 利用在线日志作为数据字典

SELECT ' dbms_logmnr.add_logfile(''' || MEMBER || ''');' FROM v$logfile;

BEGIN

dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_3.263.850260263',dbms_logmnr.new) ;

dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_2.262.850260259',dbms_logmnr.ADDFILE) ;

dbms_logmnr.add_logfile('+DATA/orclasm/onlinelog/group_1.261.850260255',dbms_logmnr.ADDFILE)) ;

dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);

END;

SELECT scn, sql_redo, a.SQL_UNDO, a.*

FROM v$logmnr_contents a

WHERE a.OPERATION = 'INSERT'

and a.TABLE_NAME = 'AABB';

create table testlog as select * from v$logmnr_contents a;

begin

dbms_logmnr.end_logmnr();

end;

EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

-------------------------- online字典

begin

dbms_logmnr.start_logmnr(startScn => 23573690,

endScn => 23632671,

Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG + dbms_logmnr.CONTINUOUS_MINE);

end;

begin

dbms_logmnr.start_logmnr(startScn => 23573690,

endScn => 23632671,

Options => dbms_logmnr.DICT_FROM_REDO_LOGS +

dbms_logmnr.CONTINUOUS_MINE);

end;

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss'

list archivelog all completed between '2015-06-03 08:00:00' and '2015-06-03 09:00:00';

exec SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2015_06_03/o1_mf_1_505_7w35gdnx_.arc', sys.dbms_logmnr.New);

BEGIN

DBMS_LOGMNR.START_LOGMNR(

STARTTIME => '2015-06-03 11:10:12',

ENDTIME => '2015-06-03 11:13:06',

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

END;

-- 查询挖掘到的结果

select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A where table_name='XXXX' and OPERATION='INSERT' order by a.SCN;

利用平面文件作为数据字典的问题

碰到过一个案例，环境为：主库+物理备库+解析库，现在在解析库上直接利用物理备库生成的平面文件作为数据字典，这可能导致dbms_logmnr.start_logmnr过程缓慢（我测试也就4秒做左右，但客户忍受不了），尤其当平面文件很大时，解析可能会有些慢，暂时未找到解决办法。已经尝试过修改sga_max_size、pga_aggregate_target、shared_pool_size、large_pool_size、streams_pool_size等都不起作用。

尝试过将字典文件导入数据库中，但是没啥用。。。

模拟情况

-- Oracle 生成20000张表，共包含约40万个字段
DECLARE
    sql1 VARCHAR2(4000);
    sql2 VARCHAR2(4000);
    v_sql VARCHAR2(32767);
BEGIN
  FOR i IN 1..20000 LOOP

      -- 表
    sql1 :='' ;
    sql1 := 'CREATE TABLE ' || 'tb_' || i || ' (';

        -- 列
    sql2:= '';
    FOR j IN 1..20 LOOP
      sql2 := sql2 || 'c_' || j || ' number,';
    END LOOP;

    sql2 := RTRIM(sql2, ',') || ')';

    v_sql := sql1 || sql2;
    -- dbms_output.put_line(v_sql);
    EXECUTE IMMEDIATE v_sql;

  END LOOP;

END;
/

select count(*)  from  dba_tables ;
select count(*) from dba_tab_cols;
select d.owner,COUNT(1)  from dba_tables d GROUP BY d.owner ORDER BY 2 desc;

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/home/oracle');

select * from v$logfile;

BEGIN sys.dbms_logmnr.end_logmnr(); end; 

BEGIN
  dbms_logmnr.add_logfile('/u01/app/oracle/oradata/LHR11G/redo03.log', dbms_logmnr.addfile);
end;

begin 
 dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dictionary.ora');
end;

select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A
where rownum<=10;

-- Oracle 生成20000张表，共包含约40万个字段

DECLARE

sql1 VARCHAR2(4000);

sql2 VARCHAR2(4000);

v_sql VARCHAR2(32767);

BEGIN

FOR i IN 1..20000 LOOP

-- 表

sql1 :='' ;

sql1 := 'CREATE TABLE ' || 'tb_' || i || ' (';

-- 列

sql2:= '';

FOR j IN 1..20 LOOP

sql2 := sql2 || 'c_' || j || ' number,';

END LOOP;

sql2 := RTRIM(sql2, ',') || ')';

v_sql := sql1 || sql2;

-- dbms_output.put_line(v_sql);

EXECUTE IMMEDIATE v_sql;

END LOOP;

END;

select count(*) from dba_tables ;

select count(*) from dba_tab_cols;

select d.owner,COUNT(1) from dba_tables d GROUP BY d.owner ORDER BY 2 desc;

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/home/oracle');

select * from v$logfile;

BEGIN sys.dbms_logmnr.end_logmnr(); end;

BEGIN

dbms_logmnr.add_logfile('/u01/app/oracle/oradata/LHR11G/redo03.log', dbms_logmnr.addfile);

end;

begin

dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dictionary.ora');

end;

select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A

where rownum<=10;

总结

1、生成平面文件数据字典，可以在Oracle的物理备库中直接生成（在19c中已测试）

参考

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOGMNR.html#GUID-C1E24B58-CA7A-484D-99B8-BF304B22C412

How To Reduce The Performance Impact Of LogMiner Usage On A Production Database (Doc ID 1629300.1)

打赏赞(5)

标签： Oracle 备份恢复日志挖掘 LogMiner 数据挖掘

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

合 Oracle日志挖掘工具LogMiner的使用

简介

安装LogMiner工具

与LogMiner相关的数据字典视图

LogMiner的数据字典

①将字典文件提取为一个Flat File（平面文件或中间接口文件）

②　将字典文件提取为Redo日志

③使用Online Catalog（联机日志）

跟踪DDL语句

过滤LogMiner返回的数据

典型的LogMiner步骤

常用SQL

利用平面文件作为数据字典的问题

模拟情况

总结

参考

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 Oracle日志挖掘工具LogMiner的使用

简介

安装LogMiner工具

与LogMiner相关的数据字典视图

LogMiner的数据字典

①将字典文件提取为一个Flat File（平面文件或中间接口文件）

② 将字典文件提取为Redo日志

③使用Online Catalog（联机日志）

跟踪DDL语句

过滤LogMiner返回的数据

典型的LogMiner步骤

常用SQL

利用平面文件作为数据字典的问题

模拟情况

总结

参考

相关文章

您可能还喜欢...

[FATAL] [DBT-10317] Specified SID Name (lhr122) already exists

数据库构造大表或慢查询SQL语句

视图 vw_sql_rubbish_monitor_lhr 的内容

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

②　将字典文件提取为Redo日志

发表回复取消回复