Oracle11g使用LOGMNR挖掘日志

  • Hoegh

    2015-08-07 16:02:24
  • Oracle
  • 原创
    Oracle 11g可以使用LOGMNR在线分析和挖掘日志，使用当前在线的数据字典，非常方便；很多时候拿LOGMNR来追踪一些误操作也是很有效的方式。
    总的说来，LogMiner工具的主要用途有：

l跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。

l回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。

l优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式

   
    如果要使用LOGMNR分析日志，我们需要安装LogMiner工具。下面分享一下我安装LogMiner工具并使用LOGMNR的DICT_FROM_ONLINE_CATALOG分析REDO日志的过程。

0  环境准备

    我们在Oracle11g进行操作。

点击(此处)折叠或打开

  1. SQL>

  3. SQL> select * from v$version;


  6. BANNER

  8. --------------------------------------------------------------------------------


  11. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

  13. PL/SQL Release 11.2.0.3.0 - Production

  15. CORE 11.2.0.3.0 Production

  17. TNS for Linux: Version 11.2.0.3.0 - Production

  19. NLSRTL Version 11.2.0.3.0 - Production


  22. SQL>



1  添加补充日志

    如果我们需要捕获DML，就必须确保数据库的补充日志功能是打开的。（前面几天的测试中，由于没有添加该日志一直都没有成功，所以这一步尤为关键。）

    首先我们通过查询v$database动态视图来获取补充日志的相关信息，如果数据库没有打开补充日志功能，我们可以通过执行alter database add supplemental log…语句来添加补充日志。

点击(此处)折叠或打开

  1. SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;


  4. SUP SUP

  6. --- ---


  9. NO NO


  12. SQL>

  14. SQL> alter database add supplemental log data(primary key,unique index) columns;


  17. Database altered.

  19. SQL>

  21. SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;


  24. SUP SUP

  26. --- ---


  29. YES YES


  32. SQL>


2  安装LogMiner工具

    要安装LogMiner工具，必须首先要运行下面三个脚本，并且这三个脚本必须以SYS用户身份运行。

l  $ORACLE_HOME/rdbms/admin/dbmslm.sql

l  $ORACLE_HOME/rdbms/admin/dbmslmd.sql

l  $ORACLE_HOME/rdbms/admin/dbmslms.sql

    其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件；第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件；第三个脚本用来创建dbms_logmnr_session包并授权。

点击(此处)折叠或打开

  1. SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql


  4. Package created.



  8. Grant succeeded.



  12. Synonym created.


  15. SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql


  18. Package created.



  22. Synonym created.


  25. SQL> @$ORACLE_HOME/rdbms/admin/dbmslms.sql


  28. Package created.


  31. No errors.


  34. Grant succeeded.


  37. SQL>


3  分析redo日志

    首先我们需要执行一些DDL或DML操作作为分析素材，然后使用LOGMNR工具进行挖掘、分析。

3.1 执行DDL或DML操作

    首先创建一个hoegh表，插入3条数据并提交；然后，删除一条记录，并提交。

点击(此处)折叠或打开

  1. SQL> create table hoegh(id number,name varchar2(20));


  4. Table created.


  7. SQL> insert into hoegh values(1,'hoegh');


  10. 1 row created.


  13. SQL> insert into hoegh values(10,'hoegh');


  16. 1 row created.


  19. SQL> insert into hoegh values(100,'hoegh');


  22. 1 row created.


  25. SQL> commit;


  28. Commit complete.


  31. SQL>

  33. SQL> delete from hoegh where id=10;


  36. 1 row deleted.


  39. SQL> commit;


  42. Commit complete.


  45. SQL>

  47. SQL> select * from hoegh;


  50.         ID NAME

  52. ---------- --------------------


  55.          1 hoegh

  57.        100 hoegh


  60. SQL>


3.2 查看redo日志路径

    通过查询v$log和v$logfile两个动态视图来获取当前状态为Current的redo日志文件路径。

点击(此处)折叠或打开

  1. SQL> select * from v$log;

  3.     GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
  4. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

  6.          1 1 7 52428800 512 1 NO INACTIVE 898138 14-OCT-14 918641 07-AUG-15
  7.          2 1 8 52428800 512 1 NO ACTIVE 918641 07-AUG-15 919289 07-AUG-15
  8.          3 1 9 52428800 512 1 NO CURRENT 919289 07-AUG-15 2.8147E+14

  10. SQL>
  11. SQL> select group#,member from v$logfile;

  13.     GROUP# MEMBER
  14. ---------- ----------------------------------------------------------------------------------------------------

  16.          3 /u01/app/oracle/oradata/PROD1/redo03.log
  17.          2 /u01/app/oracle/oradata/PROD1/redo02.log
  18.          1 /u01/app/oracle/oradata/PROD1/redo01.log

  20. SQL>


3.3 添加redo日志

    Oracle的LogMiner可以分析在线（online）和归档（offline）两种日志文件，加入分析日志文件使用dbms_logmnr.add_logfile过程，第一个文件使用dbms_logmnr.NEW参数，后面文件使用dbms_logmnr.ADDFILE参数。

点击(此处)折叠或打开

  1. SQL>

  3. SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD1/redo03.log',options=>dbms_logmnr.new);


  6. PL/SQL procedure successfully completed.


  9. SQL>


3.4 开启LOGMNR挖掘日志

点击(此处)折叠或打开

  1. SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);


  4. PL/SQL procedure successfully completed.


  7. SQL>

3.5 查询挖掘结果

    到现在为止，我们已经分析得到了redo日志文件中的内容。动态性能视图v$logmnr_contents包含LogMiner分析得到的所有的信息。这个信息量可能很大，因此我们可以通过限定用户名和表名来查询特定表的操作。

3.5.1 查询redo信息

点击(此处)折叠或打开

  1. SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents where username='SCOTT' and table_name='HOEGH';


  4. TO_CHAR(TIMESTAMP,' SQL_REDO

  6. ------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------

  8. 2015-08-07 13:44:15 create table hoegh(id number,name varchar2(20));

  10. 2015-08-07 13:44:26 insert into "SCOTT"."HOEGH"("ID","NAME") values ('1','hoegh');

  12. 2015-08-07 13:44:39 insert into "SCOTT"."HOEGH"("ID","NAME") values ('10','hoegh');

  14. 2015-08-07 13:44:45 insert into "SCOTT"."HOEGH"("ID","NAME") values ('100','hoegh');

  16. 2015-08-07 13:44:54 delete from "SCOTT"."HOEGH" where "ID" = '10' and "NAME" = 'hoegh' and ROWID = 'AAASwNAAEAAAAIOAAB


3.5.2 查询undo信息

点击(此处)折叠或打开

  1. SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_undo from v$logmnr_contents where username='SCOTT' and table_name='HOEGH';


  4. TO_CHAR(TIMESTAMP,' SQL_UNDO

  6. ------------------- ----------------------------------------------------------------------------------------------------

  8. 2015-08-07 13:44:15

  10. 2015-08-07 13:44:26 delete from "SCOTT"."HOEGH" where "ID" = '1' and "NAME" = 'hoegh' and ROWID = 'AAASwNAAEAAAAIOAAA';

  12. 2015-08-07 13:44:39 delete from "SCOTT"."HOEGH" where "ID" = '10' and "NAME" = 'hoegh' and ROWID = 'AAASwNAAEAAAAIOAAB';

  14. 2015-08-07 13:44:45 delete from "SCOTT"."HOEGH" where "ID" = '100' and "NAME" = 'hoegh' and ROWID = 'AAASwNAAEAAAAIOAAC';

  16. 2015-08-07 13:44:54 insert into "SCOTT"."HOEGH"("ID","NAME") values ('10','hoegh


3.6 停止LOGMNR挖掘日志

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

点击(此处)折叠或打开

  1. SQL>

  3. SQL> exec dbms_logmnr.end_logmnr();


  6. PL/SQL procedure successfully completed.


  9. SQL>


hoegh
15.8.7
-- The End --
请使用浏览器的分享功能分享到微信等