Oracle Log Miner工具说明

 
 一.概要说明 
 

因为oracle数据库中所有的变化都是被记录到了redolog中，所以我们可以通过oracle提供的logmnr工具对redo/archive log进行分析来确认哪些人什么时候对数据库的哪些对象进行了什么操作。我们可以通过logmnr完成以下工作：

1).确认误操作的时间，方便基于SCN对这些数据进行恢复;

2).通过分析对各个表的访问频率进行相关统计；

3).协助进行部分数据的审计工作.

Log Miner是oracle提供的一个命令行工具，对它进行配置和使用前我们最好了解一些相关的概念和对象:
1). 附加日志,附加日志分为最小附加日志和详细附加日志，如果需要使用部分需要分析日志的应用(OGG/Logmnr/logicstandby等)都至少需要打开最小附加日志，打开后数据库会记录更多的日志信息用于标示一行数据而不是rowid(因为我们知道不同数据库的rowid并不一定对应同一条数据);

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

2). 源库/mining database:源库即产生日志的数据库，后者是运行Logminer的数据库,正常情况下我们都会直接在源库进行分析;

3). LogMiner字典:包含了数据库中间的对象信息等，如果没有这个内容的话挖掘出来的对象名/列名都是一些数据库内部的编号(如果在a库分析b库的日志需要将这个字典以指定的方式进行指定，本文会略提到，详细内容官档)，例如:

INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)  VALUES('IT_WT','Technical Writer', 4000, 11000);

如果没有logminer字典会显示为:

insert into "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values
(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),
HEXTORAW('c229'),HEXTORAW('c3020b'));

4). redo日志，需要一次分析的所有日志必须是同一个数据库的，并且它们必须有相同的resetlogs_scn(也就是说不能把resetlogz前后的日志一起分析)

5). V$LOGMNR_CONTENTS视图:此视图其实并未存放的有数据，它是在执行logmnr_start后查询到v$logmnr_contents视图的时候logminer借口才会去对数据库日志里面的内容进行分析并加载。

 
 二.同库日志挖掘的步骤 

 
 2.1.指定需要分析的日志 

 
    指定所需分析的日志可以通过时间段/SCN范围或者直接指定文件的方式进行指定: 
  
    1.手工添加:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/oracle/logs/log1.f', -
OPTIONS => DBMS_LOGMNR.NEW);

注:其中的options包含new/addfile两个选项，使用new表示新创建一个文件列表，后续的文件则使用addfile选项

 
    2.基于时间指定( 
   同SCN都需要指定CONTINUOUS_MINE选项):

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
   STARTTIME => '01-Jan-2003 08:30:00', -
   ENDTIME => '01-Jan-2003 08:45:00', -
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
   DBMS_LOGMNR.CONTINUOUS_MINE);

 
   3.基于SCN指定

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
   STARTSCN => 111, -
   ENDSCN => 222, -
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
   DBMS_LOGMNR.CONTINUOUS_MINE);

 
 2.2.启动logminer 

 
   我们可以通过dbms_logmnr.start_logmnr过程来启动日志挖掘，语法如下:

DBMS_LOGMNR.START_LOGMNR (
   startScn           IN NUMBER default 0,
   endScn             IN NUMBER default 0,
   startTime          IN DATE default '01-jan-1988',
   endTime            IN DATE default '31-dec-2110',
   DictFileName       IN VARCHAR2 default '',
   Options            IN BINARY_INTEGER default 0 );

 
   其中的option可选值(多个选项用"+"链接)为:

1).DICT_FROM_ONLINE_CATALOG--表示dictionary在当前数据库

EXEC DBMS_LOGMNR.START_LOGMNR (
Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + COMMITTED_DATA_ONLY);

2).DICT_FROM_REDO_LOGS--表示dictionary存储在日志列表中(不能与1共用)

3).CONTINUOUS_MINE--连续性挖掘，在基于SCN/时间时必须指定

4).COMMITTED_DATA_ONLY--表示仅加载提交了的数据

5).SKIP_CORRUPTION--表示跳过逻辑错误的数据,对于日志有错的很有效。如果报错，$logmnr_contents视图中的INFO字段会包含出错的块信息,OPERATION字段值为:CORRUPTED_BLOCKS,而STATUS字段值包含1343

如果没有指定跳过错误:

ERROR at line 3:
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 6 change 73528 time 11/06/2002 11:30:23
ORA-00334: archived log: /usr/oracle/data/dbarch1_16_482701534.log

指定跳过错误

RBASQN RBABLK RBABYTE OPERATION        STATUS INFO
13      3       380     DELETE             0
13      0         0     CORRUPTED_BLOCKS   1343 corrupt blocks 4 to 19 skipped

6).NO_SQL_DELIMITER--不会添加SQL结束符，此类SQL可以粘贴出来执行

注:如果业务需要执行部分sql_redo/sql_undo, 请切记仅执行INFO字段中包含USER_DDL值的

7).PRINT_PRETTY_SQL--美化sql_redo/undo字段中的sql语句，美化后可能没法执行;

这也是为什么可能多次执行logmnr_start的原因，因为多次执行可以指定同的启动项。例如，我分析时希望SQL美化，而找到指定的内容可能需要显示可以执行的SQL，那么可以在分析完成后再执行一次logmnr_start指定其他选项( 参数)即可

8).NO_ROWID_IN_STMT--生成的sql中不包含ROWID

9).DDL_DICT_TRACKING--dictionary在文件中的情况会更新内部的dict,通常不会用到

 
 2.3.通过V$LOGMNR_CONTENTS视图访问数据 

 
   我们可以通过访问v$logmnr_contents视图查看需要查看的数据，如果为了方便后续分析我们可以通过create table .. as select * from v$logmnr_contents把内容保留下来，对于此视图有以下值得注意的地方:

1).此视图不是建立在基表上面，而是log文件；

2).视图的OPERATION列是ddl的时候TABLE_SPACE列不会有值，因为ddl可能对应多个表空间，比如创建分区表;

3).临时表的操作不会记录SQL_REDO等;

4).所有返回值是基于SCN的推进顺序返回的;

5). 基于被更新表的列值进行查询,例如查看更新薪资超过2倍的操作,可以通过dbms_logmnr.mine_value(redo_value/undo_value,'schema.object_name.col_name'):

SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
   WHERE
   SEG_NAME = 'EMPLOYEES' AND
   SEG_OWNER = 'HR' AND
   OPERATION = 'UPDATE' AND
    DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.EMPLOYEES.SALARY') >
   2*DBMS_LOGMNR.MINE_VALUE( UNDO_VALUE, 'HR.EMPLOYEES.SALARY');--undo_value中的列值实际上是更新前的值

6).如果被需要判定想要查找的列是否在sql中出现，就考虑使用COLUMN_PRESENT函数。如果没有出现返回0，否则返回1

SELECT
(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
(DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.EMPLOYEES.SALARY') -
   DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'HR.EMPLOYEES.SALARY')) AS INCR_SAL
   FROM V$LOGMNR_CONTENTS
   WHERE
   OPERATION = 'UPDATE' AND
   DBMS_LOGMNR. COLUMN_PRESENT(REDO_VALUE, 'HR.EMPLOYEES.SALARY') = 1 AND
   DBMS_LOGMNR.COLUMN_PRESENT(UNDO_VALUE, 'HR.EMPLOYEES.SALARY') = 1;

 
 2.4.结束logmnr会话 

 
   通常来说，结束链接的时候会自动执行这个动作，此动作会清理日志列表等信息。手工执行的语法如下:

EXECUTE SYS.DBMS_LOGMNR.END_LOGMNR;

 
 三.跨库进行日志挖掘的步骤
 3.1.指定logminer dictionary: 

 
   此方案必须使用相同版本同平台的数据库，可以将dictionary写入到redo中然后将需要分析的日志和包含dictionary的日志传送到木报端:

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

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

注:把以上两个查询获取的sequence和他们之间的seq都需要加载到目标端

 
   在目标库进行分析的时候通过logmnr_start指定DICT_FROM_ONLINE_CATALOG选项即可:

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

 
 3.2.启动logminer 

 
   后续步骤同二中一样

 
 四.相关视图 

 
    V$LOGMNR_CONTENTS 
  
    包含日志分析结果信息 
  
    V$LOGMNR_LOGS 
   
 查看日志列表中指定了哪些日志; 
   
 V$LOGMNR_PARAMETERS 
   
 查看logminer的可选参数 
   
 V$DATABASE, DBA_LOG_GROUPS, ALL_LOG_GROUPS, USER_LOG_GROUPS, DBA_LOG_GROUP_COLUMNS, ALL_LOG_GROUP_COLUMNS, USER_LOG_GROUP_COLUMNS 
   
 包含附加日志信息

秒客网

Oracle Log Miner工具说明

相关文章