合 Oracle日志挖掘工具LogMiner的使用
Tags： Oracle备份恢复日志挖掘数据挖掘LogMiner
简介
众所周知，所有对用户数据和数据字典的改变都记录在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工具。若是没有安装，则可以运行下面两个脚本：
1 2 | $ORACLE_HOME/rdbms/admin/dbmslm.sql $ORACLE_HOME/rdbms/admin/dbmslmd.sql |
这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。若要使普通用户具有日志挖掘的权限，则可以执行如下的SQL进行赋权：
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表空间下的。 可以设置单独的表空间：
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结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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语句：
1 | INSERT INTO emp(name, salary) VALUES ('John Doe', 50000); |
在没有数据字典的情况下，LogMiner将显示为：
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参数：
1 | ALTER SYSTEM SET UTL_FILE_DIR ='/home/oracle' SCOPE=SPFILE; |
然后重新启动数据库。
确保在创建Flat File文件的过程中，不能有DDL操作被执行。在创建Flat File文件时，数据库必须处于OPEN状态，然后执行DMBS_LOGMNR_D.BUILD程序：
1 | EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/home/oracle'); |
脚本执行完成后会在/home/oracle下生成一个dictionary.ora的文本文件。
不过，从Oracle 18c开始，需要使用DIRECTORY来创建目录了：
1 2 3 4 5 6 7 8 | -- 创建一个目录对象 CREATE DIRECTORY my_dir AS '/home/oracle'; -- 授权给用户 GRANT all ON DIRECTORY my_dir TO lhr; -- 生成字典 EXECUTE DBMS_LOGMNR_D.BUILD('dic.ora', 'LOGMNR'); |
该字典文件中包含一系列的建表语句和插入语句，空库产生的字典文件也有40M，例如：
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | -- *************** 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。
