`

深入分析Oracle数据库日志文件

阅读更多

来源:ccidnet.com 作者:程永新 时间:2006-10-06

作为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 utl
            NAME                         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 set
            ORA-06512: at "SYS.DBMS_LOGMNR_D", line 923
            ORA-06512: at "SYS.DBMS_LOGMNR_D", line 1938
            ORA-06512: at line 1
需要注意的是,在oracle817 for Windows版中会出现以下错误:
14:26:05 SQL> execute dbms_logmnr_d.build('oradict.ora','c:\oracle\admin\ora\log');
            BEGIN dbms_logmnr_d.build('oradict.ora','c:\oracle\admin\ora\log'); END;
            *
            ERROR at line 1:
            ORA-06532: Subscript outside of limit
            ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
            ORA-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:\oracle\ora81\rdbms\admin\dbmslmd.sql
            Package 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:\oracle\admin\ora\log');
            PL/SQL procedure successfully completed.
3、添加需要分析的日志文件
SQL>exec 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:02
            2003-09-21 09:42:39
这里需要注意的是,因为我之前已经设置NLS_DATE_FORMAT环境变量,所以上面的日期可以直接按这个格式写就行了,如果你没有设,则需要使用to_date函数来转换一下。
SQL> !env|grep NLS
            NLS_LANG=american_america.zhs16cgb231280
            NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
            ORA_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数据库的硬件平台要求一致,操作系统版本可以不一致。笔者做试验时(如果读者有兴趣可以到我网站http://www.ncn.cn上下载试验全过程,因为太长就不放在这里了),所用的两个数据库操作系统都是Tru64 UNIX,但一个是 V5.1A,另一个则是V4.0F。如果操作系统不一致则会出现下面的错误:
ORA-01284: file /data6/cyx/logmnr/arch_1_163570.arc cannot be opened
            ORA-00308: cannot open archived log '/data6/cyx/logmnr/arch_1_163570.arc'
            ORA-27048: skgfifi: file header information is invalid
            ORA-06512: at "SYS.DBMS_LOGMNR", line 63
            ORA-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:55
            create table t (c1 number);
2、追踪用户误操作或恶意操作:
例如我们现实中有这样需求,有一次我们发现一位员工通过程序修改了业务数据库信息,把部分电话的收费类型改成免费了,现在就要求我们从数据库中查出到底是谁干的这件事?怎么查?LogMiner提供了我们分析日志文件的手段,其中v$logmnr_contents的SESSION_INFO列包含了下面的信息:
login_username=NEW_97
            client_info= OS_username=oracle8 Machine_name=phoenix1
            OS_terminal=ttyp3 OS_process_id=8004 OS_program name=sqlplus@phoenix1
            (TNS V1-V3)
虽然其中信息已经很多了,但在我们的业务数据库中,程序是通过相同的login_username登录数据库的,这样单从上面的信息是很难判断的。
不过我们注意到,因为公司应用服务器不是每个人都有权限在上面写程序的,一般恶意程序都是直接通过他自己的PC连到数据库的,这就需要一个准确的定位。IP追踪是我们首先想到的,并且也满足我们的实际要求,因为公司内部IP地址分配是统一管理的,能追踪到IP地址我们就可以准确定位了。但从面的SESSION_INFO中我们并不能直接看到IP,不过我们还是有办法的,因为这个SESSION_INFO里面的内容其实是日志从V$SESSION视图里提取的,我们可以在生产数据库中创建一个追踪客户端IP地址的触发器:
create or replace trigger on_logon_trigger
            after logon on database
            begin
            dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
            end;
            /
现在,我们就可以在V$SESSION视图的CLIENT_INFO列中看到新登录的客户端IP地址了。那么上面的提出的问题就可以迎刃而解了。假如被更新的表名为HMLX,我们就可以通过下面的SQL来找到所需信息:
SQL > select session_info ,sql_redo from v$logmnr_contents
            2 where upper(operation) = 'UPDATE'  and upper(sql_redo) like '%HMLX%'
            3 /
            SESSION_INFO
            -----------------------------------------
            SQL_REDO
            -----------------------------------------
            login_username=C client_info=10.16.98.26 OS_username=sz-xjs-chengyx Machine_name
            =GDTEL\SZ-XJS-CHENGYX
            update "C"."HMLX" set "NAME" = 'free' where "NAME" = 'ncn.cn' and ROWID = 'AAABhTAA
            FAAABRaAAE';
好了,到此为止,这篇文章就要结束了,如果读者朋友还有什么疑问,可以登录我的个人网站(www.ncn.cn)来获得最新消息,也可以通过MSN(gototop_ncn@hotmail.com)直接和我联系。
六、参考资料:
1、Technical White Paper Oracle9i LogMiner
2、Metalink文档:How to Setup LogMiner(文档ID:111886.1)
分享到:
评论

相关推荐

    深入分析Oracle数据库日志文件.rar

    日志文件在Oracle数据库系统中起着至关重要的作用,它们记录了数据库的所有更改,确保数据的安全性和可恢复性。本文将深入探讨Oracle数据库的日志文件,包括redo logs(重做日志)和archive logs(归档日志),以及...

    oracle数据库日志查看方法

    ### Oracle数据库日志查看方法详解 #### 一、概述 Oracle数据库是企业级应用中广泛使用的数据库管理系统之一,为了确保数据库系统的稳定运行和快速定位问题,掌握Oracle数据库日志查看方法至关重要。本文将详细...

    深入分析oracle日志文件

    深入分析 Oracle 日志文件 Oracle 日志文件是 Oracle 数据库管理员 (DBA) 实现数据库恢复和追踪用户操作的重要工具。通过分析 Oracle 日志文件,可以追踪用户的恶意操作、恢复误删除的数据、执行事后审计等。Oracle...

    分析Oracle数据库日志文件(1) .txt

    ### Oracle数据库日志文件分析详解 ...以上便是对Oracle数据库日志文件分析的深入探讨,通过LogMiner这一强大的工具,数据库管理员可以更好地理解和控制数据库的状态变迁,为数据库管理和安全提供有力的支持。

    论文研究-Oracle8i数据库日志文件的分析与恢复.pdf

    首先,Oracle数据库日志文件是数据库管理的重要组成部分,它记录了数据库中所有事务的详细信息。Oracle 8i数据库日志文件主要包含以下几个关键概念和组件: 1. 转储文件(Dump File):转储文件是Oracle在处理日志...

    Oracle数据库日志文件的分析与应用.pdf

    总的来说,Oracle数据库日志文件的分析与应用是数据库管理中的重要环节,LogMiner作为Oracle提供的工具,能够帮助管理员深入理解数据库的操作历史,有效地进行数据恢复、审计监控和程序调试。通过正确安装、设置数据...

    oracle日志分析的一组文档

    2. **深入分析Oracle数据库日志文件**: 这些文档可能涵盖了如何解读redo log文件的内容,包括日志条目的结构、格式,以及如何通过日志条目追踪数据变化。可能还会讨论日志切换、日志组的概念,以及如何管理这些...

    Oracle数据库性能分析

    - **数据库文件**: 包括数据文件(Data Files)、重做日志文件(Redo Log Files)和控制文件(Control Files)。 #### 二、Oracle处理SQL语句过程 当一个SQL语句被提交给Oracle时,Oracle会经历一系列复杂的步骤来处理这...

    ORACLE数据库 ORACLE数据库

    本篇文章将深入探讨Oracle数据库的核心概念、特点、主要组件以及一些关键操作。 1. **核心概念** - **SQL**:结构化查询语言,是用于管理关系数据库的标准语言,Oracle数据库支持SQL的多种扩展。 - **表空间**:...

    oracle基本知识+内存分析+数据库IO深入分析

    本资料包包含了三份PDF文件,分别是“oracle内存分析”、“Oracle数据库IO深入分析”以及“oracle基本知识”,旨在帮助读者深入理解Oracle的核心概念和技术。 首先,"oracle基本知识.pdf"将涵盖Oracle数据库的基础...

    oracle通过日志查看数据库变更情况.txt

    根据给定的文件标题、描述以及部分内容,我们可以深入探讨如何在Oracle数据库中通过日志查看数据库变更情况。这一过程对于数据库管理员(DBA)来说至关重要,因为它有助于追踪数据更改历史,进行故障诊断,以及恢复...

    Oracle数据库资料大全

    其主要组件包括数据库实例、表空间、数据文件、控制文件、重做日志文件等。数据库实例是内存结构与后台进程的集合,负责处理用户请求;表空间是存储数据的逻辑单元,由一个或多个数据文件组成。 二、SQL语言与PL/...

    oracle11g监听日志文件过大导致监听无法启动2

    在Oracle数据库系统中,监听器(Listener)是一个关键组件,负责管理数据库实例与客户端应用程序之间的通信。当"Oracle11g监听日志文件过大导致监听无法启动"的问题出现时,通常意味着监听器的日志文件(listener....

    Oracle数据库使用指南

    Oracle数据库由多个组件组成,如数据库实例(Instance)、数据库文件(Data Files)、控制文件(Control Files)、重做日志文件(Redo Log Files)等。实例是运行在操作系统上的内存结构,用于处理对数据库的请求,...

    手工创建oracle数据库

    手工创建Oracle数据库是一项复杂而细致的工作,需要深入理解Oracle数据库的架构原理和配置选项。通过以上分析,我们可以看到,无论是环境变量的设置,还是PFILE文件的编写,每一个细节都可能直接影响到数据库的稳定...

    ORACLE 数据库构架图

    10. **数据库(Database)**:Oracle数据库是由一个或多个数据文件、控制文件、重做日志文件和其他相关文件组成的一个完整的物理存储结构。 11. **实例启动与挂载(Instance Startup and Mount)**:数据库实例启动...

    ORACLe数据库讲义.ppt

    在学习资源方面,可以参考俞盘祥的《Oracle数据库系统基础》、沈佩娟和汤荷美的《数据库管理及应用开发》以及孙宏昌和金毳的《Oracle数据库管理员教程》等书籍,这些资料将帮助初学者深入理解Oracle数据库的体系结构...

    Oracle数据库深入浅出(高清带目录)进阶与诊断案例

    《Oracle数据库深入浅出:进阶与诊断案例》是一本专为那些希望深入了解Oracle数据库管理和优化的专业人士编写的书籍。本书旨在提供一个全面且实践性强的学习平台,帮助读者提升在Oracle数据库领域的技能。 Oracle...

    Oracle数据库IO深入分析

    ### Oracle数据库IO深入分析 #### 1. Oracle中IO的产生 ##### 1.1 写操作 在深入了解Oracle数据库的I/O操作之前,我们首先需要了解Oracle数据库的基本物理结构,这有助于更好地理解I/O产生的原因及其过程。 **...

    Oracle数据库管理员技术指南,Oracle数据库管理员技术指南

    本文将深入探讨Oracle数据库创建与配置的关键步骤,确保读者能够构建稳定、高效且易于维护的数据库系统。 #### 数据库创建规划 创建数据库前的规划工作如同建筑一座大厦的地基,决定了数据库未来的稳定性和性能。...

Global site tag (gtag.js) - Google Analytics