分类： Oracle
2009-09-10 13:16: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 那么就输入下列命令：
