Oracle 10g学习笔记（十二） oracle 10g 深入分析日志文件

作为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的原始更新语句，而在之前我们只能选出所有的更新语句。

 

三、logminer使用方法

 

1）打开;init.ora文件，加入utl_file_dir=d:\oracle(注：改为自己的路径，是logminer使用文件的默认路径),然后应用新的设置打开oracle数据库。

　　2）执行SQL>;。 execute dbms_logmnr_d.build('shwdict.ora','D:\oracle'); ，（注:将生成d:\oracle\shwdict.ora文件，此文件可以以后重复使用，但不能同时用于几个logmnr)用于生成数据字典；如果没有dbms_logmnr_d包，需手工执行@d:\oracle\ora8\rdbms\admin\dbmslmd.;如果执行过程中报下标越界错误，则需要打开@d:\oracle\ora8\rdbms\admin\dbmslmd.文件，将其中的TYPE col_desc_array IS VARRAY(513) OF col_description的513加大，我是改成了1513,保存文件重新执行@d:\oracle\ora8\rdbms\admin\dbmslmd.;(注：都要以sysdba身份登陆执行）。

　　3）将要分析的日志文件加入要分析的log list中，察看有哪些日志文件可以用select * from v$logfile;查询已经加入那些可以用select * from v$logmnr_logs(注：查询必须在同一个会话中查询，单独开查询将查不到）语句如下：

 

execute dbms_logmnr.add_logfile
('d:\oracle\oradata\shw\redo01_1.log',dbms_logmnr.new);execute dbms_logmnr.add_logfile
('d:\ORACLE\ORADATA\ORA\REDO02_1.LOG',dbms_logmnr.addfile);execute dbms_logmnr.add_logfile
('d:\ORACLE\ORADATA\ORA\REDO03_1.LOG',dbms_logmnr.addfile);

　　如果需要从分析列表里去掉一个文件用

　　('d:\ORACLE\ORADATA\ORA\REDO03_1.LOG',dbms_logmnr.removefile);

　　4）更改会话时间表达方式（注一定要在本会话下修改，我开始发生错误就是因为在别的会话里修改的，如果允许，可以将数据库默认的时间表达方式修改掉）会话内修改为alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 系统设置在注册表。

　　5）查询分析的日志文件包含的scn范围和日期范围。

 

select low_time,high_time,low_scn,
next_scn from v$logmnr_logs;

    会列出你加入的日志文件的以上信息。

    6）执行分析:

dbms_logmnr.start_logmnr(dictfilename=>;
 'd:\oracle\shwdict.ora',startscn=>;
xxxxxx,endscn=>;xxxxx,starttime =>; 
to_date('20030501 12:15:00','yyyymmdd hh24:mi:ss'),
 endtime =>; 
to_date('20030501 15:40:30','yyyymmdd hh24:mi:ss'));

    要注意scn范围和日期格式，格式要与你修改的一样。

 

    7）查询

Select SCN,timestamp, session# session_num, 
_redoFrom V$LOGMNR_CONTENTSOrder by 1

四、日志分析过程

 

前面是安装LOGMINER工具在这里只是简单介绍一下
以SYS用户执行下面两个脚本
A：$ORACLE_HOME/rdbms/admin/dbmslm.sql ，用来创建DBMS_LOGMNR包
SQL> @dbmslm.sql
程序包已创建。
授权成功。
B：$ORACLE_HOME/rdbms/admin/dbmslmd.sql. 用来创建数据字典文件。
SQL> @dbmslmd.sql
过程已创建。
没有错误。
授权成功。
PL/SQL 过程已成功完成。

程序包已创建。
-------------------------------------------------------------------
第一种方法使用联机目录分析归档日志
1、打开数据库的追加日志(这个一定要注意了，否则分析出来的都是ddl）

---10g特性，不然无法查出dml操作的数据
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
如果结果为YES就不需要追加日志反之，
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA；

2、切换日志，然后执行事务删除.
CONN / AS SYSDBA
ALTER SYSTEM SWITCH LOGFILE；
CONNECT TEST/TEST
DELETE FROM TEST1 WHERE ID=1；
COMMIT；
CONN / AS SYSDBA
ALTER SYSTEM SWITCH LOGFILE；
SELECT NAME FROM V$ARCHIVED_LOG;

3、将新生成的日志文件添加到LOGMINER列表中。
EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-
'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_72_3LHVWNOZ_.ARC',-
DBMS_LOGMNR.NEW);

4、为LOGMINER指定将要使用的联机目录。如果源数据库处于打开或者是可用状态，那么它也可用。
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
PTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

5、在V$LOGMNR_CONTENTS中查询有关删除事务的信息。
SELECT USERNAME,SQL_REDO,SQL_UNDO FROM
V$LOGMNR_CONTENTS WHERE USERNAME='TEST'
AND PERATION='DELETE';

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

TEST
delete from "SYS"."CON$" where "OWNER#" = '74' and "NAME" = 'SYS_C005801' and "C

ON#" = '5801' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and

"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcA

ABAAAN4tAAN';
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPAR

E4","SPARE5","SPARE6") values ('74','SYS_C005801','5801',NULL,NULL,NULL,NULL,NUL

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

L,NULL);

TEST
delete from "SYS"."OBJ$" where "OBJ#" = '54375' and "DATAOBJ#" = '54375' and "OW

NER#" = '74' and "NAME" = 'SYS_C005801' and "NAMESPACE" = '4' and "SUBNAME" IS N

ULL and "TYPE#" = '1' and "CTIME" = TO_DATE('21-9月 -07', 'DD-MON-RR') and "MTIM

E" = TO_DATE('21-9月 -07', 'DD-MON-RR') and "STIME" = TO_DATE('21-9月 -07', 'DD-

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and

"FLAGS" = '4' and "OID$" IS NULL and "SPARE1" = '0' and "SPARE2" = '65535' and
"SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL
and ROWID = 'AAAAASAABAAAMTvABJ';
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"

,"TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$

","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('54375','54375'

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

,'74','SYS_C005801','4',NULL,'1',TO_DATE('21-9月 -07', 'DD-MON-RR'),TO_DATE('21-

9月 -07', 'DD-MON-RR'),TO_DATE('21-9月 -07', 'DD-MON-RR'),'1',NULL,NULL,'4',NULL

,'0','65535',NULL,NULL,NULL,NULL);

TEST
delete from "SYS"."OBJ$" where "OBJ#" = '54355' and "DATAOBJ#" = '54355' and "OW

NER#" = '74' and "NAME" = 'DEMO' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

"TYPE#" = '2' and "CTIME" = TO_DATE('21-9月 -07', 'DD-MON-RR') and "MTIME" = TO

_DATE('21-9月 -07', 'DD-MON-RR') and "STIME" = TO_DATE('21-9月 -07', 'DD-MON-RR'

) and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS

" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS

NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID =

'AAAAASAABAAAMTvAA0';
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

,"TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$

","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('54355','54355'

,'74','DEMO','1',NULL,'2',TO_DATE('21-9月 -07', 'DD-MON-RR'),TO_DATE('21-9月 -07

', 'DD-MON-RR'),TO_DATE('21-9月 -07', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1

',NULL,NULL,NULL,NULL);

TEST

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

delete from "TEST"."DEPT_DEMO" where "DEPT_ID" = '1' and "DEPT_NAME" = '技术部'
and ROWID = 'AAAN1uAAEAAABjkAAA';
insert into "TEST"."DEPT_DEMO"("DEPT_ID","DEPT_NAME") values ('1','技术部');

TEST
delete from "TEST"."DEPT_DEMO" where "DEPT_ID" = '2' and "DEPT_NAME" = '财务部'
and ROWID = 'AAAN1uAAEAAABjkAAB';

USERNAME
------------------------------
SQL_REDO
--------------------------------------------------------------------------------

SQL_UNDO
--------------------------------------------------------------------------------

insert into "TEST"."DEPT_DEMO"("DEPT_ID","DEPT_NAME") values ('2','财务部');

TEST
delete from "TEST"."DEPT_DEMO" where "DEPT_ID" = '3' and "DEPT_NAME" = '部市部'
and ROWID = 'AAAN1uAAEAAABjkAAC';
insert into "TEST"."DEPT_DEMO"("DEPT_ID","DEPT_NAME") values ('3','部市部');

已选择6行。

6、结束LOGMNR
execute dbms_logmnr.end_logmnr;

----------------------------------------------------------------------
第二种方法使用LOGMINER字典
1、首先修改参数文件
添加UTL_FILE_DIR=c:log_miner
2、以sys用户运行脚本，创建数据字典文件

SQL> execute dbms_logmnr_d.build('testtrace.ora', 'c:log_miner',dbms_logmnr_d.store_in_flat_file);

PL/SQL 过程已成功完成。
3、建立日志分析表,使用dbms_logmnr.add_logfile()

SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename=>'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_72_3LHVWNOZ_.ARC');

PL/SQL 过程已成功完成。
4、添加用于分析的日志文件

SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_73_3LHZO54W_.ARC');

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'C:oracleproduct10.2.0flash_recovery_areaTESTARCHIVELOG2007_10_31O1_MF_1_71_3LHVOK7B_.ARC');

PL/SQL 过程已成功完成。

5、启动LogMiner进行分析。

SQL> execute dbms_logmnr.start_logmnr(dictfilename =>'c:log_minertesttrace.ora',starttime =>to_date-('20071031 09:00:00','yyyymmdd hh24:mi:ss'),endtime =>to_date('20071031 12:00:00','yyyymmdd-hh24:mi:ss'));
6、查看日志分析的结果,通过查询v$logmnr_contents可以查询到

SELECT USERNAME,SQL_REDO,SQL_UNDO FROM
V$LOGMNR_CONTENTS WHERE USERNAME='TEST'
AND PERATION='DELETE';

输出结果同上一个实验.在这里就不在贴出结果。

7、结束LogMiner的分析。

execute dbms_logmnr.end_logmnr;

PL/SQL 过程已成功完成。

8、可以把这个文件从日志分析表中移除，从而不进行分析。

SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename =>'d:oracleora92rdbmsARC00038.001');

PL/SQL 过程已成功完成。

 

原文地址：，

        http://blog.chinaunix.net/u/1281/showart_1006178.html