Oracle 10g Logminer 研究及测试

Oracle 10g Logminer 研究及测试 收藏 LogMiner提供了一个处理重做日志文件并将其内容翻译成代表对数据库的逻辑操作的SQL语句的过程。LogMiner运行在Oracle版本8.1或者更高版本中。一，如何使用Logminer: 先要安装logminer的两个包；以SYS用户运行下面两个脚本,其中第一个脚本dbmslm.sql用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本dbmslmd.sql用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。 D:oracleproduct10.2.0db_1RDBMSADMINsqlplus /nologSQL*Plus: Release .0 - Production on星期五4月10 17:49:02 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.SQL conn sys/oracle as sysdba已连接。SQLSQL dbmslm.sql程序包已创建。授权成功。SQLSQL dbmslmd.sql程序包已创建。二，创建数据字典文件 数据字典文件是一个文本文件，使用包DBMS_LOGMNR_D来创建，如果我们要分析的数据库中的表有变化(比如表结构有变化等)，影响到库的数据字典也发生变化。另外一种情况是在分析另外一个数据库文件的重做日志时，也必须要重新生成一遍被分析数据库的数据字典文件。 首先需要修改参数UTL_FILE_DIR ,该参数值为服务器中放置数据字典文件的目录，10g中我们通过动态修改参数的方式来修改，然后重新启动数据库生效。其中logs_utl_file目录先期建立好。SQL alter system set UTL_FILE_DIR=d:oracleproduct10.2.0oradatatestlogs_utl_file scope=spfile; 系统已更改。 SQL shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。SQLSQL startupORACLE例程已经启动。Total System Global Area 167772160 bytesFixed Size 1295608 bytesVariable Size 125831944 bytesDatabase Buffers 33554432 bytesRedo Buffers 7090176 bytes数据库装载完毕。数据库已经打开。SQLSQL show parameter UTL_FILENAME TYPE- -VALUE-utl_file_dir stringd:oracleproduct10.2.0oradatatestlogs_utl_fileSQL然后创建数据字典文件：BEGINdbms_logmnr_d.build(dictionary_filename = logminer_dict.ora, dictionary_location = D:oracleproduct10.2.0oradatatestlogs_utl_file);END;/OK ,可以看到在logs_ult_file中已经生成了logminer_dict.ora数据字典文件。数据字典文件是可以打开的文本文件。有兴趣可以打开看看。execute dbms_logmnr_d.build(shwdict.ora,d:lick); /本人使用的创建语句创建数据字典是让logminer引用实际到内部数据字典中的部分是使用对象的名称，而不是系统内部的16进制的ID. 三，创建要分析的日志文件(在线日志或归档日志)的列表（1）分析在线的重做日志A。建立列表SQL execute dbms_logmnr.add_logfile(LogFileName=d:oracleproduct10.2.0oradatatestredo01.log, ptions=dbms_logmnr.new);PL/SQL过程已成功完成。B。添加其他日至文件到列表 SQL execute dbms_logmnr.add_logfile(LogFileName=d:oracleproduct10.2.0oradatatestredo02.log, ptions=dbms_logmnr.addfile);PL/SQL过程已成功完成。SQL execute dbms_logmnr.add_logfile(LogFileName=d:oracleproduct10.2.0oradatatestredo03.log, ptions=dbms_logmnr.addfile);PL/SQL过程已成功完成。（2）分析归档的重做日志 A。建立列表SQL execute dbms_logmnr.add_logfile(LogFileName=d:oracleproduct10.2.0oradataarchiveARC00052_0684166863.001, ptions=dbms_logmnr.new);PL/SQL过程已成功完成。B。添加其他日志文件到列表 SQL execute dbms_logmnr.add_logfile(LogFileName=d:oracleproduct10.2.0oradataarchiveARC00053_0684166863.001, ptions=dbms_logmnr.addfile);PL/SQL过程已成功完成。 如果你觉得不需要分析已经在列表中的在线或归档日志，可以通过removefile命令删除：SQL execute dbms_logmnr.add_logfile(LogFileName=d:oracleproduct10.2.0oradataarchiveARC00053_0684166863.001, ptions=dbms_logmnr.removefile);PL/SQL过程已成功完成。四，使用Logminer进行日志分析无限制条件：SQL execute dbms_logmnr.start_logmnr(DictFileName= D:oracleproduct10.2.0oradatatestlogs_utl_filelogminer_dict.ora) ;PL/SQL过程已成功完成。有限制条件：通过对过程DBMS_LOGMNR.START_LOGMNR中的时间或者SCN参数的设置，可以缩小分析日志文件的范围：如我们仅仅分析2009年2月23日到2005年7月26日的日志：SQL execute dbms_logmnr.start_logmnr(DictFileName = D:oracleproduct10.2.0oradatatestlogs_utl_filelogminer_dict.ora ,StartTime = to_date(2009-2-23 00:00:00,YYYY-MM-DD HH24:MI:SS)EndTime = to_date(2009-2-26 23:59:59,YYYY-MM-DD HH24:MI:SS ); 相关的参数如下：参数参数类型默认值含义StartScn数字型0分析重作日志中SCNStartScn日志文件部分EndScn数字型0分析重作日志中SCNEndScn日志文件部分StartTime日期型1998-01-01分析重作日志中时间戳StartTime的日志文件部分EndTime日期型2988-01-01分析重作日志中时间戳EndTime的日志文件部分DictFileName字符型字典文件该文件包含一个数据库目录的快照。使用该文件可以使得到的分析结果是可以理解的文本形式，而非系统内部的16进制Options BINARY_INTEGER 0系统调试参数，实际很少使用五，得到分析结果： 到现在为止，我们已经分析得到了重作日志文件中的内容。动态性能视图v$logmnr_contents包含LogMiner分析得到的所有的信息。SELECT sql_redo FROM v$logmnr_contents;如果我们仅仅想知道某个用户对于某张表的操作，可以通过下面的SQL查询得到，该查询可以得到用户TONY对表R_WIP_TST所作的一切工作。SQL; SELECT sql_redo FROM v$logmnr_contents WHERE username=TONY AND tablename=R_WIP_TST; 需要强调一点的是，视图v$logmnr_contents中的分析结果仅在我们运行过程dbms_logmrn.start_logmnr 这个会话的生命期中存在(如果执行了DBMS_LOGMNR.END_LOGMNR分析结果将消失)。这是因为所有的LogMiner存储都在PGA内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。如果需要，可以通过removefile命令删除日志分析文件。SQL execute dbms_logmnr.add_logfile(LogFileName=d:oracleproduct10.2.0oradataarchiveARC00052_0684166863.001, ptions=dbms_logmnr.removefile);六，使用DBMS_LOGMNR.END_LOGMNR结束日志分析过程，释放内存。最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。SQL execute dbms_logmnr.end_logmnr; 作为Oracle DBA，我们有时候需要追踪数据误删除或用户的恶意操作情况，此时我们不仅需要查出执行这些操作的数据库账号，还需要知道操作是由哪台客户端（IP地址等）发出的。针对这些问题，一个最有效实用而又低成本的方法就是分析Oracle数据库的日志文件。本文将就Oracle日志分析技术做深入探讨。 一、如何分析即LogMiner解释 从目前来看，分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMiner来进行， Oracle数据库的所有更改都记录在日志中，但是原始的日志信息我们根本无法看懂，而LogMiner就是让我们看懂日志信息的工具。从这一点上看，它和tkprof差不多，一个是用来分析日志信息，一个则是格式化跟踪文件。通过对日志的分析我们可以实现下面的目的： 1、查明数据库的逻辑更改； 2、侦察并更正用户的误操作； 3、执行事后审计； 4、执行变化分析。 不仅如此，日志中记录的信息还包括：数据库的更改历史、更改类型（INSERT、UPDATE、DELETE、DDL等）、更改对应的SCN号、以及执行这些操作的用户信息等，LogMiner在分析日志时，将重构等价的SQL语句和UNDO语句（分别记录在V$LOGMNR_CONTENTS视图的 SQL_REDO和SQL_UNDO中）。这里需要注意的是等价语句，而并非原始SQL语句，例如：我们最初执行的是“delete a where c1 cyx;”，而LogMiner重构的是等价的6条DELETE语句。所以我们应该意识到V$LOGMNR_CONTENTS视图中显示的并非是原版的现实，从数据库角度来讲这是很容易理解的，它记录的是元操作，因为同样是“delete a where c1 cyx;”语句，在不同的环境中，实际删除的记录数可能各不相同，因此记录这样的语句实际上并没有什么实际意义，LogMiner重构的是在实际情况下转化成元操作的多个单条语句。 另外由于Oracle重做日志中记录的并非原始的对象（如表以及其中的列）名称，而只是它们在Oracle数据库中的内部编号（对于表来说是它们在数据库中的对象ID，而对于表中的列来说，对应的则是该列在表中的排列序号：COL 1, COL 2 等），因此为了使LogMiner重构出的SQL语句易于识别，我们需要将这些编号转化成相应的名称，这就需要用到数据字典（也就说LogMiner本身是可以不用数据字典的，详见下面的分析过程），LogMiner利用DBMS_LOGMNR_D.BUILD()过程来提取数据字典信息。 LogMiner包含两个PL/SQL包和几个视图： 1、dbms_logmnr_d包，这个包只包括一个用于提取数据字典信息的过程，即dbms_logmnr_d.build()过程。 2、dbms_logmnr包，它有三个过程： 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相关的数据字典。 1、v$logmnr_dictionary，LogMiner可能使用的数据字典信息，因logmnr可以有多个字典文件，该视图用于显示这方面信息。 2、v$logmnr_parameters，当前LogMiner所设定的参数信息。 3、v$logmnr_logs，当前用于分析的日志列表。 4、v$logmnr_contents，日志分析结果。 二、Oracle9i LogMiner的增强： 1、支持更多数据/存储类型：链接/迁移行、CLUSTER表操作、DIRECT PATH插入以及DDL操作。在V$LOGMNR_CONTENTS的SQL_REDO中可以看到DDL操作的原句（CREATE USER除外，其中的密码将以加密的形式出现，而不是原始密码）。如果TX_AUDITING初始化参数设为TRUE，则所有操作的数据库账号将被记录。 2、提取和使用数据字典的选项：现在数据字典不仅可以提取到一个外部文件中，还可以直接提取到重做日志流中，它在日志流中提供了操作当时的数据字典快照，这样就可以实现离线分析。 3、允许对DML操作按事务进行分组：可以在START_LOGMNR()中设置COMMITTED_DATA_ONLY选项，实现对DML操作的分组，这样将按SCN的顺序返回已经提交的事务。 4、支持SCHEMA的变化：在数据库打开的状态下，如果使用了LogMiner的DDL_DICT_TRACKING选项，Oracle9i的 LogMiner将自动对比最初的日志流和当前系统的数据字典，并返回正确的DDL语句，并且会自动侦察并标记当前数据字典和最初日志流之间的差别，这样即使最初日志流中所涉及的表已经被更改或者根本已经不存在，LogMiner同样会返回正确的DDL语句。 5、在日志中记录更多列信息的能力：例如对于UPDATE操作不仅会记录被更新行的情况，还可以捕捉更多前影信息。 6、支持基于数值的查询：Oracle9i LogMiner在支持原有基于元数据（操作、对象等）查询的基础上，开始支持基于实际涉及到的数据的查询。例如涉及一个工资表，现在我们可以很容易地查出员工工资由1000变成2000的原始更新语句，而在之前我们只能选出所有的更新语句。 三、Oracle8i/9i的日志分析过程 LogMiner只要在实例起来的情况下都可以运行，LogMiner使用一个字典文件来实现Oracle内部对象名称的转换，如果没有这个字典文件，则直接显示内部对象编号，例如我们执行下面的语句： delete from C.A where C1 = gototop and ROWID = AAABg1AAFAAABQaAAH;如果没有字典文件，LogMiner分析出来的结果将是：delete from UNKNOWN.OBJ# 6197 where COL 1 = HEXTORAW(d6a7d4ae) and ROWID = AAABg1AAFAAABQaAAH;如果想要使用字典文件，数据库至少应该出于MOUNT状态。然后执行dbms_logmnr_d.build过程将数据字典信息提取到一个外部文件中。下面是具体分析步骤： 1、确认设置了初始化参数：UTL_FILE_DIR，并确认Oracle对改目录拥有读写权限，然后启动实例。示例中UTL_FILE_DIR参数如下： SQL show parameter utlNAME TYPE VALUE- - -utl_file_dir string /data6/cyx/logmnr这个目录主要用于存放dbms_logmnr_d.build过程所产生的字典信息文件，如果不用这个，则可以不设，也就跳过下面一步。 2、生成字典信息文件： exec dbms_logmnr_d.build(dictionary_filename =dic.ora,dictionary_location = /data6/cyx/logmnr);其中dictionary_location指的是字典信息文件的存放位置,它必须完全匹配UTL_FILE_DIR的值，例如：假设 UTL_FILE_DIR=/data6/cyx/logmnr/，则上面这条语句会出错，只因为UTL_FILE_DIR后面多了一个“/”，而在很多其它地方对这一“/”是不敏感的。 dictionary_filename指的是放于字典信息文件的名字，可以任意取。当然我们也可以不明确写出这两个选项，即写成： exec dbms_logmnr_d.build(dic.ora,/data6/cyx/logmnr);如果你第一步的参数没有设，而直接开始这一步，Oracle会报下面的错误： ERROR at line 1:ORA-01308: initialization parameter utl_file_dir is not setORA-06512: at SYS.DBMS_LOGMNR_D, line 923ORA-06512: at SYS.DBMS_LOGMNR_D, line 1938ORA-06512: at line 1需要注意的是，在oracle817 for Windows版中会出现以下错误： 14:26:05 SQL execute dbms_logmnr_d.build(oradict.ora,c:oracleadminoralog);BEGIN dbms_logmnr_d.build(oradict.ora,c:oracleadminoralog); END;*ERROR at line 1:ORA-06532: Subscript outside of limitORA-06512: at SYS.DBMS_LOGMNR_D, line 793ORA-06512: at line 1解决办法： 编辑$ORACLE_HOME/rdbms/admindbmslmd.sql文件，把其中的TYPE col_desc_array IS VARRAY(513) OF col_description;改成：TYPE col_desc_array IS VARRAY(700) OF col_description;保存文件，然后执行一遍这个脚本： 15:09:06 SQL c:oracleora81dbmsadmindbmslmd.sqlPackage created.Package body created.No errors.Grant succeeded.然后重新编译DBMS_LOGMNR_D包： 15:09:51 SQL alter package DBMS_LOGMNR_D compile body;Package body altered.之后重新执行dbms_logmnr_d.build即可：15:10:06 SQL execute dbms_logmnr_d.build(oradict.ora,c:oracleadminoralog);PL/SQL procedure successfully completed.3、添加需要分析的日志文件 SQLexec dbms_logmnr.add_logfile( logfilename=/data6/cyx/rac1arch/arch_1_197.arc, options=dbms_logmnr.new);PL/SQL procedure successfully completed.这里的options选项有三个参数可以用： NEW - 表示创建一个新的日志文件列表 ADDFILE - 表示向这个列表中添加日志文件，如下面的例子 REMOVEFILE - 和addfile相反。 SQL exec dbms_logmnr.add_logfile( logfilename=/data6/cyx/rac1arch/arch_2_86.arc, options=dbms_logmnr.addfile);PL/SQL procedure successfully completed.4、当你添加了需要分析的日志文件后，我们就可以让LogMiner开始分析了： SQL exec dbms_logmnr.start_logmnr(dictfilename=/data6/cyx/logmnr/dic.ora);PL/SQL procedure successfully completed.如果你没有使用字典信息文件（此时我们只需要启动实例就可以了），那么就不需要跟dictfilename参数： SQL exec dbms_logmnr.start_logmnr();PL/SQL procedure successfully completed.当然dbms_logmnr.start_logmnr()过程还有其它几个用于定义分析日志时间/SCN窗口的参数，它们分别是： STARTSCN / ENDSCN - 定义分析的起始/结束SCN号， STARTTIME / ENDTIME - 定义分析的起始/结束时间。 例如下面的过程将只分析从 2003-09-21 09:39:00到2003-09-21 09:45:00这段时间的日志： SQL exec dbms_logmnr.start_logmnr(dictfilename=/data6/cyx/logmnr/dic.ora , -starttime = 2003-09-21 09:39:00,endtime = 2003-09-21 09:45:00);PL/SQL procedure successfully completed.上面过程第一行结尾的“-”表示转行，如果你在同一行，则不需要。我们可以看到有效日志的时间戳： SQL select distinct timestamp from v$logmnr_contents;TIMESTAMP-2003-09-21 09:40:022003-09-21 09:42:39这里需要注意的是，因为我之前已经设置NL2005-1-31_FORMAT环境变量，所以上面的日期可以直接按这个格式写就行了，如果你没有设，则需要使用to_date函数来转换一下。 SQL !env|grep NLSNLS_LANG=american_america.zhs16cgb231280NL2005-1-31_FORMAT=YYYY-MM-DD HH24:MI:SSORA_NLS33=/oracle/oracle9/app/oracle/product/9.2.0/ocommon/nls/admin/data使用to_date的格式如下：exec dbms_logmnr.start_logmnr(dictfilename=/data6/cyx/logmnr/dic.ora,-starttime = to_date(2003-09-21 09:39:00,YYYY-MM-DD HH24:MI:SS),-endtime = to_date(2003-09-21 09:45:00,YYYY-MM-DD HH24:MI:SS);STARTSCN 和ENDSCN参数使用方法类似。 5、好了，在上面的过程执行结束之后，我们就可以通过访问与LogMiner相关的几个视图来提取我们需要的信息了。其中在v$logmnr_logs中可以看到我们当前分析的日志列表，如果数据库有两个实例（即OPS/RAC），在v$logmnr_logs中会有两个不同的THREAD_ID。 而真正的分析结果是放在v$logmnr_contents中，这里面有很多信息，我们可以根据需要追踪我们感兴趣的信息。后面我将单独列出来讲常见的追踪情形。 6、全部结束之后，我们可以执行dbms_logmnr.end_logmnr过程退出LogMiner分析过程，你也可以直接退出SQL*PLUS，它会自动终止。四、如何利用LogMiner分析Oracle8的日志文件 虽然说LogMiner是Oracle8i才推出来，但我们同样可以用它来分析Oracle8的日志文件，只不过稍微麻烦了一点，并且有一定的限制，下面是具体做法： 我们首先复制Oracle8i的$ORACLE_HOME/rdbms/admin/dbmslmd.sql脚本到Oracle8数据库所在主机的同样目录；这个脚本用于创建dbms_logmnr_d包（注意，Oracle9i中还将创建dbms_logmnr包），如果是8.1.5脚本名字为dbmslogmnrd.sql。然后在Oracle8的数据库上运行这个脚本，之后使用dbms_logmnr_d.build过程创建字典信息文件。现在我们就可以把Oracle8的归档日志连同这个字典信息文件复制到Oracle8i数据库所在的主机上，之后在Oracle8i数据库中从上面分析过程的第三步开始分析Oracle8的日志，不过 dbms_logmnr.start_logmnr()中使用的是Oracle8的字典信息文件。 按照我前面所说的那样，如果不是字典文件，我们则可以直接将Oracle8的归档日志复制到Oracle8i数据库所在主机，然后对它进行分析。 其实这里涉及到了一个跨平台使用LogMiner的问题，笔者做过试验，也可以在Oracle9i中来分析Oracle8i的日志。但这些都是有所限制的，主要表现在： 1、LogMiner所使用的字典文件必须和所分析的日志文件是同一个数据库所产生的，并且该数据库的字符集应和执行LogMiner数据库的相同。这很好理解，如果不是同一个数据库所产生就不存在对应关系了。 2、生成日志的数据库硬件平台和执行LogMiner数据库的硬件平台要求一致，操作系统版本可以不一致。笔者做试验时（如果读者有兴趣可以到我网站url/url上下载试验全过程，因为太长就不放在这里了），所用的两个数据库操作系统都是Tru64 UNIX，但一个是V5.1A，另一个则是V4.0F。如果操作系统不一致则会出现下面的错误： ORA-01284: file /data6/cyx/logmnr/arch_1_163570.arc cannot be openedORA-00308: cannot open archived log /data6/cyx/logmnr/arch_1_163570.arcORA-27048: skgfifi: file header information is invalidORA-06512: at SYS.DBMS_LOGMNR, line 63ORA-06512: at line 1五、分析v$logmnr_contents 前面我们已经知道了LogMiner的分析结果是放在v$logmnr_contents中，这里面有很多信息，我们可以根据需要追踪我们感兴趣的信息。那么我们通常感兴趣的有哪些呢？ 1、追踪数据库结构变化情况，即DDL操作，如前所述，这个只有Oracle9i才支持： SQL select timestamp,sql_redo from v$logmnr_contents2 where upper(sql_redo) like %CREATE%;TIMESTAMP-SQL_REDO-2003-09-21 10:01:55create table t (c1 number);2、追踪用户误操作或恶意操作： 例如我们现实中有这样需求，有一次我们发现一位员工通过程序修改了业务数据库信息，把部分电话的收费类型改成免费了，现在就要求我们从数据库中查出到底是谁干的这件事？怎么查？LogMiner提供了我们分析日志文件的手段，其中v$logmnr_contents的SESSION_INFO列包含了下面的信息： login_username=NEW_97 client_info= OS_username=oracle8 Machine_name=phoen