logminer日志挖掘技术

Logminer是Oracle推出的一项日志挖掘技术和工具，可用于分析对数据库的DML操作，获取操作的REDO SQL和UNDO SQL。它既可以分析在线日志，也可以分析离线日志，既可以分析自身数据库的日志,也可以分析其它数据库的日志。利用获取到的这些SQL，可以实现对特定事务的审计、重做和还原。可以建立源库到目标库的应用，源库和目标库可以是同一个，也可以是不同的。如果不同，要求目标库数据库版本高于或等于源库，字符集要相同，操作系统、硬件平台要相同。

使用Logminer之前可能需要确认Oracle是否已装有Logminer分析包，可以DBA身份登录系统，查看系统中是否存在运行Logminer所需要的dbms_logmnr、dbms_logmnr_d这两个包，如果没有则需要先安装Logminer工具，需要以DBA用户身份运行以下两个脚本：

$ORACLE_HOME/rdbms/admin/dbmslm.sql

$ORACLE_HOME/rdbms/admin/dbmslmd.sql

其中第一个脚本用来创建dbms_logmnr包，该包用来分析日志文件，第二个脚本用来创建dbms_logmnr_d包，该包用来创建数据字典文件。

一、Logminer的使用方法

源库应在归档模式，并开启补充日志功能。非归档模式不是不可以做日志挖掘，但由于日志没有归档，因此只能对联机日志做挖掘。补充日志也不是必需，但如果不启用，则解析后很多有用的信息都没有。

查看存档模式和补充日志状态是否开启

select log_mode, supplemental_log_data_min from v$database;

LOG_MODE     SUPPLEME

------------ --------

ARCHIVELOG   NO

开启补充日志

alter database add supplemental log data;

可单独创建一个表空间，用于记录Logminer的信息，这样不占用系统表空间

create tablespace logminer datafile 'd:\oradata\mes\logminer01.dbf' size 500m;

execute dbms_logmnr_d.set_tablespace('logminer');

可单独创建一个负责日志挖掘的用户并授权

create user logminer identified by logminer default tablespace logminer;

grant dba to logminer;

在使用Logminer工具分析redo log之前，应使用dbms_logmnr_d包将数据字典导出为一个文本文件。该字典文件是可选的，但是如果没有它，Logminer解释出来的语句中关于数据字典的部分（如表名、列名等）和数值都将是16进制的形式，难以理解。创建数据字典的目的是让Logminer引用到涉及内部数据字典中的部分时，将它们解释为实际的名字。如果要分析的数据库的表有变化，影响到数据字典变化时，就需要重新创建字典文件。另外一种情况是，在分析另一个数据库的redo log时，也必须重新生成一次被分析数据库的数据字典文件。

创建数据字典文件目录

alter system set utl_file_dir='d:\dict' scope=spfile;

这里增加了一个dict的目录用于存放日志挖掘的数据字典文件。稍后在开始日志挖掘之前我们会创建和更新数据字典文件。

重启数据库

shutdown immediate

startup

做几个具有代表意义的操作，以便后面观察日志是如何记录的

create table scott.emp1 tablespace users as select * from scott.emp;

insert into scott.emp1 values(1001, 'LIULN', 'ANALYST', 7369, '2012-05-01 00:00:00', '4000', '', 20);

update scott.emp1 set sal = 5000 where empno = 1001;

commit;

delete from scott.emp1 where empno = 1001;

rollback;

create index scott.idx_emp1_empno on scott.emp1(empno) tablespace users;

select * from scott.emp1 where empno = 1001;

insert into scott.emp values(1001, 'LIULN', 'ANALYST', 7369, '2012-05-01 00:00:00', '4000', '', 20);

update scott.emp set sal = 5000 where empno = 1001;

delete from scott.emp where empno = 1001;

commit;

select * from scott.emp;

创建和更新数据字典文件到目录中

begin

    dbms_logmnr_d.build(dictionary_filename => 'dict.ora',

                        dictionary_location => 'd:\dict');

end;

/

Logminer的字典模式除了以上使用外部OS文件的方式外，还有以下两种方式：

1）使用在线字典，适用于在源库做Logminer

begin

    dbms_logmnr_d.build(options => dbms_logmnr.dict_from_online_catalog);

end;

/

2）把字典放到在线日志文件，适用于源库与目标库不同这样的方式

begin

    dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

end;

/

可以查看字典存放到了哪个归档日志中

select name from v$archived_log where dictionary_begin = 'YES';

select name from v$archived_log where dictionary_end = 'YES';

查看当前操作对应的联机日志文件

col member for a50

select t1.sequence#, t2.member from v$log t1, v$logfile t2 where t1.group# = t2.group# and t1.status = 'CURRENT' and rownum = 1;

 SEQUENCE# MEMBER

---------- --------------------------------------------------

       214 D:\ORADATA\MES\REDO01.LOG

把要分析的日志文件加进来，添加第一个日志时，options选项用new，再添加则用addfile，如要删除则用removefile。

conn logminer/logminer

begin

    dbms_logmnr.add_logfile(logfilename => 'd:\oradata\mes\redo01.log',

                            options     => dbms_logmnr.new);

end;

/

挖掘日志

begin

    dbms_logmnr.start_logmnr(dictfilename => 'd:\dict\dict.ora');

end;

/

可以按时间缩小日志挖掘范围，如

begin

    dbms_logmnr.start_logmnr(starttime    => to_date('2017-04-22 12:00:00', 'yyyy-mm-dd hh24:mi:ss'),

                             endtime      => to_date('2017-04-22 13:00:00', 'yyyy-mm-dd hh24:mi:ss'),

                             dictfilename => 'd:\dict\dict.ora')

end;

/

也可以按SCN号缩小日志挖掘范围，如

begin

    dbms_logmnr.start_logmnr(startscn     => 6239000,

                             endscn       => 6240000,

                             dictfilename => 'd:\dict\dict.ora')

end;

/

还可以加入options选项对日志挖掘做一些限定，如

begin

    dbms_logmnr.start_logmnr(dictfilename => 'd:\dict\dict.ora',

                             Options      => dbms_logmnr.committed_data_only +

                                             dbms_logmnr.no_sql_delimiter +

                                             dbms_logmnr.no_rowid_in_stmt);

end;

/

dbms_logmnr.committed_data_only：只挖掘已提交的事务。

dbms_logmnr.no_sql_delimiter：去掉redo_sql和undo_sql最后的分号，这个在以CURSOR方式循环执行解析出的SQL时会很方便。

dbms_logmnr.no_rowid_in_stmt：去掉redo_sql和undo_sql中的rowid，适用于在源库以外的其它库重新执行解析出的SQL,因为相应的rowid在目标库是不存在的。

从v$logmnr_contents获取变更信息。注意视图v$logmnr_contents中的分析结果仅在我们运行过程dbms_logmnr.add_logfile和dbms_logmnr.start_logmnr这个会话的生命期中存在。这是因为所有的Logminer都存储在PGA内存中，因此其它进程是看不到它的。而当使用过程dbms_logmnr.end_logmnr终止日志分析时，PGA内存区域将被清除，分析结果也随之不再存在。另外由于每次查询v$logmnr_contents视图时都会实际触发一次日志解析，也就是说这个视图的内容不是start_logmnr生成的，而是每次查询时生成的，为此可以生成一个实体表留住结果，这样可以大幅度降低对系统的开销。

create table logminer.lgmr_contents as

select scn, to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp, operation, operation_code, rollback,

       seg_owner, seg_name, seg_type_name, table_space, row_id, username, machine_name,

       substr(substr(session_info, instr(session_info, 'OS_program_name=')), 17) program,

       thread#, rel_file#, data_blk#, data_obj#, sql_redo, sql_undo

  from v$logmnr_contents

 where seg_owner = 'SCOTT';

按时间先后顺序查询一下变更信息

select * from logminer.lgmr_contents order by scn desc;

变更信息内容还是比较丰富的，包含了操作时的SCN和时间戳、操作类别包括DML和DDL、构建的redo是否用于回滚、操作对象的信息、记录的ROWID、登录用户和计算机名及程序、REDO和UNDO语句等。

再来重点看一下REDO信息

col sql_redo for a100

select scn, sql_redo from logminer.lgmr_contents order by scn desc;

       SCN SQL_REDO

---------- ----------------------------------------------------------------------------------------------------

   1751090 delete from "SCOTT"."EMP" where "EMPNO" = '1001' and "ENAME" = 'LIULN' and "JOB" = 'ANALYST' and "MG

           R" = '7369' and "HIREDATE" = TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '50

           00' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAAF7JAAEAAAAFVAAC';

   1751088 update "SCOTT"."EMP" set "SAL" = '5000' where "SAL" = '4000' and ROWID = 'AAAF7JAAEAAAAFVAAC';

   1751084 insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('100

           1','LIULN','ANALYST','7369',TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'4000',NULL,'20'

           );

   1751061 create index scott.idx_emp1_empno on scott.emp1(empno) tablespace users;

   1751048 insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('10

           01','LIULN','ANALYST','7369',TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'5000',NULL,'20

           ');

   1751044 delete from "SCOTT"."EMP1" where "EMPNO" = '1001' and "ENAME" = 'LIULN' and "JOB" = 'ANALYST' and "M

           GR" = '7369' and "HIREDATE" = TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '5

           000' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAAF8DAAEAAAAGNAAA';

   1751037 update "SCOTT"."EMP1" set "SAL" = '5000' where "SAL" = '4000' and ROWID = 'AAAF8DAAEAAAAGNAAA';

   1751033 insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('10

           01','LIULN','ANALYST','7369',TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'4000',NULL,'20

           ');

   1751021 Unsupported

   1751016 create table scott.emp1 tablespace users as select * from scott.emp;

可以看出，日志中记录的REDO SQL和原先执行的SQL在文本表达上有所不同，这是为了记录变更之前的数据，构建UNDO信息，并且所有的update和delete都是基于ROWID的。实际中如要将这些SQL重新应用，特别是应用在不同库中，需要做一些技巧性处理。

停止日志挖掘

begin

    dbms_logmnr.end_logmnr;

end;

/

删除补充日志

alter database drop supplemental log data;

删除之前试验创建的表

drop table logminer.lgmr_contents purge;

drop table scott.emp1 purge;

二、Logminer相关视图

除了上面用于查看日志挖掘结果的v$logmnr_contents视图外，还有以下几个有关logminer的视图。

查看用于logminer的日志列表

col filename for a50

select filename, type, db_id, db_name, low_time, high_time, low_scn, next_scn from v$logmnr_logs;

FILENAME                                           TYPE         DB_ID DB_NAME  LOW_TIME            HIGH_TIME              LOW_SCN   NEXT_SCN

-------------------------------------------------- ------- ---------- -------- ------------------- ------------------- ---------- ----------

d:\oradata\mes\redo03.log                          ONLINE  2023487221 MES      2017-04-24 21:34:54 1988-01-01 00:00:00    1538785 2.8147E+14

查看logminer设定的参数信息

select start_date, start_scn, end_date, end_scn, options from v$logmnr_parameters;

START_DATE           START_SCN END_DATE               END_SCN    OPTIONS

------------------- ---------- ------------------- ---------- ----------

                       1538785 2111-01-01 00:59:59          0          0

查看logminer利用的数据字典信息

select db_id, db_name, db_created, to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp, dictionary_scn, filename from v$logmnr_dictionary;

     DB_ID DB_NAME   DB_CREATED          TIMESTAMP            DICTIONARY_SCN FILENAME

---------- --------- ------------------- -------------------- -------------- --------------------------------------------------

2023487221 MES       2017-03-29 13:40:05 2017-04-24 21:45:27         1538894 d:\dict\dict.ora

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28974745/viewspace-2138078/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2138078/