概述

本文档是笔者阅读Oracle官方文档Oracle Database Utilities 11g Release 2 (11.2) E22490-05的读书笔记，主要介绍了logmnr数据挖掘的若干个实例。

环境信息

操作系统版本

Microsoft Windows Version 6.1.7601

数据库版本

Oracle 11g Release 11.2.0.1.0

准备工作

构建一张测试表，后续的实验均基于这张测试表。

  1. 复制scott用户emp中的部分数据
1
2
3
SQL> create table scott.tab_emp as 
select ename,sal from scott.emp;
表已创建。
  1. 查看测试表的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select * from scott.tab_emp;
ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
14 rows selected.

日志挖掘实例

查看最后归档的重做日志文件的更改

  1. 定位最后归档的重做日志文件路径
    假设我们要查看的操作是刚刚发生的，该日志记录在最新的归档日志文件里。
1
2
3
4
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
--------------------------------------------------------------------------------
E:\ORACLE\ARCH_LOG\ARC0000000021_0831311361.0001
  1. 指定要分析的日志文件列表
1
2
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'E:\ORACLE\ARCH_LOG\ARC0000000021_0831311361.0001',OPTIONS=>DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
  1. 开始挖掘
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
  1. 查看v$logmnr_contents
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- --------------------------------------------------------------------------------
   1075293 SYS      ALTER TABLE "SCOTT"."TAB_EMP" RENAME TO "BIN$tQR5Z7zsQLWDs5RPAA3kFg==$0" ;
   1075296 SYS      drop table scott.tab_emp AS "BIN$tQR5Z7zsQLWDs5RPAA3kFg==$0" ;
   1075320 SYS      create table scott.tab_emp as select ename,sal from scott.emp;
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('SMITH','800');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('ALLEN','1600');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('WARD','1250');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('JONES','2975');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('MARTIN','1250');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('BLAKE','2850');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('CLARK','2450');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('SCOTT','3000');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('KING','5000');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('TURNER','1500');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('ADAMS','1100');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('JAMES','950');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('FORD','3000');
   1075330 SYS      insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('MILLER','1300');
  1. 结束挖掘会话
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

查看最后归档的重做日志文件中已经提交了的更改

前面的例子中，分析出的数据包含了未提交的SQL语句，本例将只查询出已经提交的SQL语句。

  1. 查看测试表的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select * from scott.tab_emp;
ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
14 rows selected.
  1. 执行SQL语句
1
2
3
4
5
6
7
8
9
10
SQL> delete from scott.tab_emp where ename in ('TURNER','ADAMS');
2 rows deleted.
SQL> rollback;
Rollback complete.
SQL> update scott.tab_emp set sal=8888 where ename='SCOTT';
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
  1. 定位最后归档的重做日志文件路径
1
2
3
4
5
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
------------------------------------------------------
E:\ORACLE\ARCH_LOG\ARC0000000023_0831311361.0001

  1. 添加待分析的日志文件
1
2
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'E:\ORACLE\ARCH_LOG\ARC0000000023_0831311361.0001',OPTIONS=>DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
  1. 开始分析日志
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
  1. 查看v$logmnr_contents
1
2
3
4
5
6
7
8
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- ----------------------------------------------------------------------------------------------------------------
   1082062 UNKNOWN  delete from "SCOTT"."TAB_EMP" where "ENAME" = 'TURNER' and "SAL" = '1500' and ROWID = 'AAASNgAAEAAAAJ7AAJ';
   1082062 UNKNOWN  delete from "SCOTT"."TAB_EMP" where "ENAME" = 'ADAMS' and "SAL" = '1100' and ROWID = 'AAASNgAAEAAAAJ7AAK';
   1082088 UNKNOWN  insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('ADAMS','1100');
   1082088 UNKNOWN  insert into "SCOTT"."TAB_EMP"("ENAME","SAL") values ('TURNER','1500');
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP" set "SAL" = '8888' where "SAL" = '3000' and ROWID = 'AAASNgAAEAAAAJ7AAH';

可见，这种分析方式下，Oracle把回滚前后的日志也分析出来了，若只想分析已经提交的事务，须添加COMMITTED_DATA_ONLY参数。

  1. 分析已提交的事务
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
PL/SQL procedure successfully completed.
  1. 查看v$logmnr_contents
1
2
3
4
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- ----------------------------------------------------------------------------------------------------
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP" set "SAL" = '8888' where "SAL" = '3000' and ROWID = 'AAASNgAAEAAAAJ7AAH';

可见，同样的查询语句，logmnr已经把没有提交的delete语句给过滤掉了，只分析出已经提交的update语句。

格式化重构的SQL语句

前面的例子中，分析出的SQL语句均未经格式化，阅读起来非常困难，更不用说从一大堆SQL日志中找出需要的语句了。通过指定PRINT_PRETTY_SQL参数，可以将输出SQL语句格式化，但须引起注意的是，该参数可能会导致重构的语句无法直接执行。

  1. 分析日志，格式化输出的SQL语句
1
2
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
  1. 查看v$logmnr_contents
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> SELECT scn,username,sql_redo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_REDO
---------- -------- ---------------------------------------------------------------------
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP"
                      set
                        "SAL" = 8888
                      where
                        "SAL" = 3000 and
                        ROWID = 'AAASNgAAEAAAAJ7AAH';
SQL> SELECT scn,username,sql_undo from v$logmnr_contents where table_name ='TAB_EMP';
       SCN USERNAME SQL_UNDO
---------- -------- ----------------------------------------------------------------------
   1082098 UNKNOWN  update "SCOTT"."TAB_EMP"
                      set
                        "SAL" = 3000
                      where
                        "SAL" = 8888 and
                        ROWID = 'AAASNgAAEAAAAJ7AAH';

在重做日志文件中使用logmnr目录

前面的例子中，日志挖掘均在与生成该日志相同的数据库上进行的，。

  1. 分析日志，格式化输出的SQL语句
1
2
3
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.

增补资料

若发现部分语句分析不出来，或者语句不完整的情况，请参看Oracle官方的解释。

  • Ensure that database was in minimum supplemental logging at the time that the redo information was created；
  • Ensure that all archive redo logs containing the necessary redo information have been added to the LogMiner session.

即，确保数据库运行在附加日志信息模式(Supplemental log data)下，且已经把相关的日志添加到日志分析队列中。
如何确保数据库运行在附加日志信息模式(Supplemental log data)下？

1
2
3
4
5
6
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered
SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
YES