分类： Oracle
2007-02-23 22:30:41
我们都知道，Oracle 的联机日志（Online redo log）和归档日志（Archived redo log）中记录了每一个数据库事务（transaction），也就是说，这些日志文件中保存了自Oracle运行以来所有的数据修改操作，但是，在 Oracle 8以及以前的版本中，并没有提供任何手段可以让DBA来发掘这里面的内容。这正像一座富矿，却没有手段开采，只能任其沉睡。现在情况已经改变，从 Oracle 8i开始，提供了一个工具：logminer，可以用来分析这些日志文件。
本文就是结合偶的这次经历，来介绍这个工具的使用。
本文分三部分：第一部分，介绍如何设置、使用logminer；第二部分，介绍一些logminer的实际应用；第三部分，介绍logminer 的两个特别技巧；最后把偶自创的分析logminer的shell脚本共享给大家。希望能够有所帮助啦。呵呵
第一部分：设置logminer
一， 首先，建立一个字典文件(dictionary file)。
Oracle 的日志文件中，对于表等用户对象( Object)，并不是保存名字，而是保存一个ID 号。建立字典文件的目的就是使logminer在分析时可以将Object ID翻译成我们所熟悉的对象名。
建立字典文件之前，先要确保数据库的初始化参数 UTL_FILE_DIR 已经正确地设置。在sqlplus 下键入 show
parameters utl_file_dir,
可以看到该参数的当前设置。如果没有值，必须修改数据库的initsid.ora文件，将utl_file_dir
指向一个你想用来保存字典文件的路径。本例中，设置 UTL_FILE_DIR=/oracle/admin/orcl/logs。
创建字典文件的语句如下例：
begin sys.dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora', dictionary_location =>'/oracle/admin/orcl/logs');
end;
其中，dictionary.ora是字典文件的文件名（大家可以任意起啦）。
整个创建过程，可能需要十几分钟到一个小时，视该数据库的object 个数以及繁忙程度而定。偶也不知道我这次到底花了多长时间，反正是等了一个小时还没好，偶就回家了，第二天来看的时候，已经创建好了。^_^
完成后，会在/oracle/admin/orcl/logs目录下看到一个名为dictionary.ora的文件。
二， 选取要分析的文件
日志文件和归档日志文件的数量是非常多的。以偶公司的数据库而言，每半小时要产生一个50M的日志文件，数据量非常之大。因此事实上不可能把所有的日志文
件都分析一遍（你要做也行，不过要保证有足够的空间和时间，并且不怕影响数据库性能），通常可以选取你感兴趣的时间段内的日志进行分析。
选取日志文件的操作如下例：
begin sys.dbms_logmnr.add_logfile (logfilename =>'/oradata/orcl/redo01.log', options=>sys.dbms_logmnr.NEW);
end;
一次只能选取一个文件。若要增加文件，使用下例：
begin sys.dbms_logmnr.add_logfile (logfilename =>'/oradata/orcl/redo01.log', options=>sys.dbms_logmnr.ADDFILE);
end;
若想去掉一个已经选取或增加的文件，使用REMOVEFILE:
begin sys.dbms_logmnr.add_logfile (logfilename =>'/oradata/orcl/redo01.log', options=>sys.dbms_logmnr.REMOVEFILE);
end;
如此反复操作，可以把所有要分析的文件都选取进去。
三， 进行分析
选取好所有需要分析的文件后，执行下面的命令，开始分析：
begin sys.dbms_logmnr.start_logmnr (dictfilename =>'dictionary.ora');
end;
注意，这里的dictionary.ora就是前面创建的字典文件名。
分析过程根据所选取文件的数据量，可能需要几个小时。有时候，DBA可能并不需要这些日志文件中所有的数据，那么能否只分析部分数据呢？Oracle 容许你只分析指定时间段或者指定SCN段的数据，语法示例如下：
begin sys.dbms_logmnr.start_logmnr (dictfilename
=>'dictionary.ora'，starttime =>to_date('01-Aug-2001 08:30:00',
'DD-MON-YYYY HH:MI S'),endtime => to_date('01-Aug-2001 08:45:00',
'DD-MON-YYYY HH:MI S'));
end;
或者，
begin sys.dbms_logmnr.start_logmnr (dictfilename =>'dictionary.ora'，startscn =>100,endscn =>500);
end;
分析结束后，所分析到的数据可以从一个名为 V$LOGMNR_CONTENTS的视图中查询到。我们就可以应用这个视图中的内容来达成目的。
第二部分：应用logminer
应用范例一：跟踪一个特定用户
DBA对某一个具有数据修改权限的用户产生了疑问，想了解该用户在最近几天对数据做了什么样的修改。审计（Audit）只能知道该用户修改了哪些表，无法
确认所修改的内容（Trigger Audit 可以确认内容，但严重影响系统性能）。使用logminer 可以做到。
在按照第一部分的说明进行操作后，查询v$logmnr_contents视图：
SELECT sql_redo, sql_undo FROM v$logmnr_contents WHERE seg_owner = 'Sigurd' AND seg_name = 'SALARY';
这个语句查询用户Sigurd 对Salary 表进行过什么样的修改。可能的输出如下：
SQL_REDO SQL_UNDO
-------- --------
delete * from SALARY insert into SALARY
where EMPNO = 12345 ( NAME,EMPNO,SAL)
and ROWID = 'AAABOOAABAAEPCABA'; values ('Sigurd', 12345,500);
insert into SALARY(NAME, EMPNO, SAL) delete * from SALARY
values('Sigurd',12345,2500); where EMPNO = 12345
and ROWID = ‘AAABOOAABAAEPCABA';
2 rows selected
可以看到，Sigurd 对自己的工资进行了修改，将修改前的500提高到了修改后的2500 .
注意update操作在日志中是被纪录成一个delete操作外加一个insert操作。从SQL_UNDO列中可以看到update前的内容，
Sigurd的薪水是500。从SQL_REDO列看到，新的薪水是2500，其他数据未变。如果在查询中加入 timestamp
这个字段，更可以看到修改所发生的具体时间。
应用范例二：统计表的修改次数
统计一段时间内各表的修改次数很有意义。修改特别频繁的表，即所谓的‘热点’表，数据库运行期间主要的事务都与它们有关。DBA可以据此对这些表进行优
化，比如提高他们的INITRANS,MAXTRANS设置，增加PCTFREE 等，以提高修改操作的性能。甚至可以从此发现数据库性能的瓶颈。
SELECT seg_owner, seg_name, count(*) AS Hits FROM
v$logmnr_contents WHERE seg_name NOT LIKE '%$' GROUP BY seg_owner, seg_name;
输出示例如下：
SEG_OWNER SEG_NAME Hits
--------- -------- ----
CUST ACCOUNT 384
SCOTT EMP 12
SYS DONOR 12
UNIV DONOR 234
UNIV EXECDONOR 3250
UNIV MEGADONOR 32
上面的SEG_NAME就是表名，SEG_OWNER是表的拥有者。HITS是分析的时间内该表修改（DELETE 或者 INSERT） 的次数。可以看到execdonor 这个表修改次数明显高于其他表。
应用范例三：抢救被错误修改的数据
笔者曾遇到这样的事情：自己公司开发的一段代码，其中有个BUG, 一条UPDATE语句没有写WHERE条件。QA没有能测试出来，LANUCH
后两个小时，发现不对，数据已经被错误地修改。因为只涉及到一个表，而且数据不是太多，于是笔者就是用了logminer将数据找回。具体方法类似范例
一：
SELECT sql_redo, sql_undo FROM v$logmnr_contents WHERE username = 'HR' AND tablename = 'WORKEXP';
SQL_REDO 列显示的是所做的操作（SQL 语句），SQL_UNDO 列显示的是要恢复该操作所需要的SQL语句，只要顺序执行SQL_UNDO的内容，就可以恢复到修改前的数据。
使用这个方法恢复数据，好处在于不会有数据损失。否则只能做 incomplete recovery，在笔者这种情况下至少损失两个小时的数据，而且必须停机几个小时。
由于所有应用中，其实都是利用v$logmnr_contents这个视图，因此在此介绍一下该视图中特别有用的几个字段：
Seg_name: 表名;
Scn: SCN 号码
Sql_redo: 所作的sql 语句
Sql_undo: 对应sql_redo, 恢复用的sql 语句
Timestamp: sql 发出的具体时间
Operation: sql 的类型，分为INSERT, UPDATE, START(set …), COMMIT(commit), INTERNAL等
Session#: 发出该操作的 session
其他字段请参考Oracle reference.pdf 手册。
第三部分：两个特别技巧
一， 如何在Oracle 8 数据库中使用logminer?
Logminer是Oracle 8i 才提供的工具，但如果你还有一个数据库是Oracle 8
的，能否使用呢？回答是可以的。经过笔者试验，只要在Oracle 8数据库中建立 DBMS_LOGMNR_D 这个package,
就可以使用logminer. 察看 Oracle 8i 可以知道，在$ORACLE_HOME/rdbms/admin
下面有一个名为dbmslmd.sql 的script, 这正是产生dbms_logmnr_d 这个package的script.
因此，只要拷贝一个8i的dbmslmd.sql, 然后用sys用户在 Oracle 8
数据库中执行后，就可以像本文第一部分那样使用logminer 了。
二， 如何用logminer 分析另一个Oracle数据库所产生的归档日志文件？
你可能有两台Oracle
数据库，一台配置了logminer，另一台没有。或者另一台数据库暂时不能访问，但可以取到它的归档日志文件，能否对这些文件进行分析呢？可以，但要求
比较苛刻。首先必修二者运行的平台
(platform)相同，而且必须另一台数据库事先曾创建过字典文件，而且在创建后字符集没有过变化。满足条件的话，把另一台数据库要分析的归档日志文
件和它的字典文件一起拷贝过来，就可以分析。
当然这两台数据库都必须至少是 8以上的版本。
以上就是笔者使用 Oracle 8i logminer 工具的一些经验，写出来与大家共享。建议感兴趣的读者仔细研究 V$logmnr_contents 这个视图，找到更多的应用用途。
最后把我此次查找过程所用到的脚本，给大家共享一下：
在执行下列脚本内容之前，你得先执行这句sql，以生成数据字典文件；
begin sys.dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location =>'/oracle/admin/orcl/logs');
end;
#!/bin/sh
i=$1
while [ $i -le $2 ]
do
sqlplus '/ as sysdba' <
end;
/
begin sys.dbms_logmnr.start_logmnr (dictfilename =>'/oracle/admin/oseb/logs/dictionary.ora');
end;
/
create table admin.log_$i as SELECT * FROM v\$logmnr_contents WHERE seg_owner = 'GMDBA' AND SEG_NAME = 'BS_ANNOUNCEMENT';
EOF
i=` expr $i + 1 `
done
我开始一下子加了好多日志文件去分析，结果v$logmnr_contents的表实在太大了，根本查不出我想要的数据，所以我这个脚本是用来指定一个日志文件的序号范围，然后以这个日志文件的序号作为后缀，生成对应的临时表，再到这个临时表里去查出我想要的数据。
该脚本执行时得输入2个参数，第一个参数是日志文件的序号起点，第二个参数是日志文件的序号终点。例如，你想查1250到1260号的日志文件，假设脚本文件名为：logminer.sh 那么就输入下列命令：
./logminer.sh 1250 1260
