探索Oracle之LogMiner 最佳实践一

探索Oracle之LogMiner 最佳实践一

作者：吴伟龙PrudentWoo

Oracle LogMiner 是Oracle公司8i以后提供的一个很有用日志分析工具，该工具可以帮助我们非常好的通过在线/归档日志文件提取数据库的操作痕迹，不仅仅是对本库，同时也可以提取其它的数据库的重做日志文件和归档日志文件。因为该工具具有完全还原数据库所有的DML和DDL操纵语句能力，使得其本身具有很强的犯罪取证和痕迹追踪功能。但是这个工具一直是一位蒙纱羞女在默默的等待有为的新青年dba们发起一次一次的挑战，因为这个对于很多DBA来说可能在整个生涯中都很难会有机会用上，尤其是新一代的DBA，他的很多功能已经被flashback & RMAN备份恢复所部分替代，但是他也具有完全不可替代的部分，那就是前面提到的数据的取证和痕迹追踪，在今天的这篇文章中我将详细介绍LogMiner的使用方法和场景。

LogMiner分析工具其实是由一组PL/SQL包和一些动态视图（Oracle8i内置包的一部分）组成，它作为Oracle数据库的一部分来发布。但该工具在11g之前和其他Oracle内建工具相比使用起来显得有些复杂，主要原因是该工具没有提供任何的图形用户界面（GUI），当然oracle也很快意识到该问题，在11g的EM将LogMiner添加进去，大大降低了其使用难度。

示例：

这是一个LogMiner示例配置。在这个图中，位于波士顿的源数据库生成重做日志文件，这些文件被归档并发送到旧金山的数据库。已将LogMiner字典提取到这些重做日志文件中。LogMiner将实际分析重做日志文件的挖掘数据库位于旧金山。两边的数据库版本不要求一样。

LogMiner数据字典选项：

LogMiner需要一个字典来将对象id转换为对象名称，当它返回重做数据给你时。LogMiner为您提供了提供字典的三个选项:

使用Online Catalog： Oracle建议您将访问源数据库使用这个选项，同时创建重做日志文件，并在预期的表中没有对列定义进行更改。这是最高效、最容易使用的选项。

将LogMiner字典提取到重做日志文件中: Oracle建议您使用此选项时，不要期望访问重新创建日志文件的源数据库，或者您预期会对感兴趣的表中的列定义进行更改。

将LogMiner字典提取到一个平文本文件: 这个选项是为了向后兼容以前的版本而保留的。此选项并不保证事务一致性。Oracle建议您使用联机目录或从重做日志文件中提取字典。

我们既然已经了解了什么是LogMiner之后，那么我们现在来开始做实验，用实验证明我说的话是对的：

LogMiner组件检查：

首先我们要确认数据库是否已经拥有必须的包，如果没有则需要运行两个包：@?/rdbms/admin/dbmslm.sql脚本用来安装DBMS_LOGMNR包，这个包用户来分析日志文件；@?/rdbms/admin/dbmslmd.sql脚本用来安装DBMS_LOGMNR_D包，这个包主要是创建LogMiner所必须的数据字典。

SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/dbmslm.sql

Package created.


Grant succeeded.


Synonym created.

SQL> @?/rdbms/admin/dbmslmd.sql

Package created.


Synonym created.

SQL> r
  1  SELECT DISTINCT name
  2  FROM DBA_SOURCE
  3  WHERE type = 'PACKAGE'
  4  AND upper(text) LIKE upper('%logmnr%')
  5  order by name

NAME
-------------------------
DBMS_LOGMNR
DBMS_LOGMNR_D
DBMS_LOGMNR_INTERNAL
DBMS_LOGMNR_LOGREP_DICT
DBMS_LOGMNR_SESSION
LOGMNR_DICT_CACHE
LOGMNR_EM_SUPPORT

7 rows selected.

一、同库基于Redo的LogMiner

1、创建测试表：

SQL> conn wwl/wwl
Connected.

SQL> create table test02 (id number(10),name varchar2(200));
Table created.

SQL> insert into test02 values(1,'woo');
1 row created.

SQL> commit;
Commit complete.

SQL> update test02 set name='wwl' where name='woo';
1 row updated.

SQL> commit;
Commit complete.

3、查询当前的RedoLog File，定位刚才操作所在的RedoLog File:

SQL> select group#,archived,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
	 1 YES INACTIVE
	 2 YES INACTIVE
	 3 NO  CURRENT

4、至此，环境已经构建完成。

5、添加状态为CURRENT的RedoLog File到LogMiner中

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/DBSoft/oracle/oradata/woo/redo03.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.

6、启动LogMiner进行分析

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.

7、查看分析后的结果

SQL> set linesize 300
SQL> set pagesize 8096
SQL> col seg_name format a6
SQL> col seg_owner format a4
SQL> col sql_redo format a70
SQL> col sql_undo format a70
SQL> SELECT SEG_OWNER,SEG_NAME,SQL_REDO,SQL_UNDO
    2 FROM v$LOGMNR_CONTENTS
    3 WHERE SEG_NAME = 'TEST02' and seg_owner='WWL';  

SEG_ SEG_NA SQL_REDO								   SQL_UNDO
---- ------ ---------------------------------------------------------------------- ----------------------------------------------------------------------
WWL  TEST02 create table test02 (id number(10),name varchar2(200));
WWL  TEST02 insert into "WWL"."TEST02"("ID","NAME") values ('1','woo'); 	   delete from "WWL"."TEST02" where "ID" = '1' and "NAME" = 'woo' and ROW
										   ID = 'AAAVXrAAEAAAACvAAA';

WWL  TEST02 update "WWL"."TEST02" set "NAME" = 'wwl' where "NAME" = 'woo' and ROWI update "WWL"."TEST02" set "NAME" = 'woo' where "NAME" = 'wwl' and ROWI
	    D = 'AAAVXrAAEAAAACvAAA';						   D = 'AAAVXrAAEAAAACvAAA';

8、我们可以看到在这里已经清晰的显示了之前所做所有操作的详细SQL语句，包括创建test02表，插入一条ID 为 1 和 将ID=1 更新为 ID=2。完成了整个操作的重演。

二、同库基于ArchiveLog File的LogMiner

1、创建测试表：

SQL> conn wwl/wwl
Connected.

SQL> create table test03(id number(10),name varchar2(200));

Table created.

SQL> insert into test03 values(1,'wwl');

1 row created.

SQL> insert into test03 values(2,'wwl');

1 row created.

SQL> insert into test03 values(3,'pru');

1 row created.

SQL> insert into test03 values(4,'pru');

1 row created.


增删改操作：
SQL> insert into test03 values(4,'pru');

1 row created.

SQL> update test03 set name='woo' where id=2;

1 row updated.

SQL> delete from test03 where id=4;

1 row deleted.

SQL> commit;

2、检查刚才数据：

SQL> col name format a4
SQL> select * from test03;

	ID NAME
---------- ----
	 1 wwl
	 2 woo
	 3 pru

3、查询当前的RedoLog File，定位刚才操作所在的RedoLog File:

SQL> select thread#,sequence#,bytes,status from v$log;

   THREAD#  SEQUENCE#	   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
	 1	   46	52428800 NO  CURRENT
	 1	   44	52428800 YES INACTIVE
	 1	   45	52428800 YES ACTIVE

4、查询归档日志

SQL> select SEQUENCE#,name,status from v$archived_log where name is not null and sequence#=45;

 SEQUENCE# NAME 										 S
---------- ------------------------------------------------------------------------------------- -
	45 /DBSoft/oracle/fast_recovery_area/WOO/archivelog/2017_11_21/o1_mf_1_45_f17jvko7_.arc  A

5、添加归档日志做离线分析：

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/DBSoft/oracle/fast_recovery_area/WOO/archivelog/2017_11_21/o1_mf_1_45_f17jvko7_.arc',options=>dbms_logmnr.NEW);

PL/SQL procedure successfully completed.

6、启动LogMiner分析进程

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

7、查询分析结果：

SQL> col SQL_REDO format a100
SQL> col SQL_UNDO format a100
SQL> SELECT SEG_OWNER,SQL_UNDO
2 FROM v$LOGMNR_CONTENTS
3 WHERE SEG_NAME = 'TEST03' and seg_owner='WWL';

SEG_ SEG_NA SQL_REDO
---- ------ ----------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
WWL  TEST03 create table test03(id number(10),name varchar2(200));


WWL  TEST03 insert into "WWL"."TEST03"("ID",'wwl');
delete from "WWL"."TEST03" where "ID" = '1' and "NAME" = 'wwl' and ROWID = 'AAAVXsAAEAAAAC3AAA';

WWL  TEST03 insert into "WWL"."TEST03"("ID","NAME") values ('2','wwl');
delete from "WWL"."TEST03" where "ID" = '2' and "NAME" = 'wwl' and ROWID = 'AAAVXsAAEAAAAC3AAB';

WWL  TEST03 insert into "WWL"."TEST03"("ID","NAME") values ('3','pru');
delete from "WWL"."TEST03" where "ID" = '3' and "NAME" = 'pru' and ROWID = 'AAAVXsAAEAAAAC3AAC';

WWL  TEST03 insert into "WWL"."TEST03"("ID","NAME") values ('4','pru');
delete from "WWL"."TEST03" where "ID" = '4' and "NAME" = 'pru' and ROWID = 'AAAVXsAAEAAAAC3AAD';

WWL  TEST03 update "WWL"."TEST03" set "NAME" = 'woo' where "NAME" = 'wwl' and ROWID = 'AAAVXsAAEAAAAC3AAB';
update "WWL"."TEST03" set "NAME" = 'wwl' where "NAME" = 'woo' and ROWID = 'AAAVXsAAEAAAAC3AAB';

WWL  TEST03 delete from "WWL"."TEST03" where "ID" = '4' and "NAME" = 'pru' and ROWID = 'AAAVXsAAEAAAAC3AAD';
insert into "WWL"."TEST03"("ID",'pru');

7 rows selected.

8、完了之后记得结束LogMiner:

SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.

注意：

SQL>select * from V$LOGMNR_CONTENTS;
select* from V$LOGMNR_CONTENTS
ERRORat line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selectingfrom
v$logmnr_contents

视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个会话的生命期中存在。这是因为所有的LogMiner存储都在PGA内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。

可以创建临时表解决这个问题

SQL>create table logmnr_tb1 as select  *from V$LOGMNR_CONTENTS;
Table created.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!