ITPub博客

首页 > 数据库 > Oracle > Oracle11g使用LOGMNR挖掘日志

Oracle11g使用LOGMNR挖掘日志

Oracle 作者:startay 时间:2016-07-11 16:32:20 0 删除 编辑
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> 

  2. SQL> select * from v$version;


  3. BANNER

  4. --------------------------------------------------------------------------------


  5. Oracle Database 11g Enterprise Edition Release 11.2.0.3.- Production

  6. PL/SQL Release 11.2.0.3.- Production

  7. CORE 11.2.0.3.0 Production

  8. TNS for Linux: Version 11.2.0.3.- Production

  9. NLSRTL Version 11.2.0.3.- Production


  10. SQL>



1  添加补充日志

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

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


点击(此处)折叠或打开

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


  2. SUP SUP

  3. --- ---


  4. NO NO


  5. SQL>

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


  7. Database altered.

  8. SQL> 

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


  10. SUP SUP

  11. --- ---


  12. YES YES


  13. 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


  2. Package created.



  3. Grant succeeded.



  4. Synonym created.


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


  6. Package created.



  7. Synonym created.


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


  9. Package created.


  10. No errors.


  11. Grant succeeded.


  12. SQL>


3  分析redo日志

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

3.1 执行DDLDML操作

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

点击(此处)折叠或打开

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


  2. Table created.


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


  4. row created.


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


  6. row created.


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


  8. row created.


  9. SQL> commit;


  10. Commit complete.


  11. SQL> 

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


  13. row deleted.


  14. SQL> commit;


  15. Commit complete.


  16. SQL> 

  17. SQL> select * from hoegh;


  18.         ID NAME

  19. ---------- --------------------


  20.          1 hoegh

  21.        100 hoegh


  22. SQL>


3.2 查看redo日志路径

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

点击(此处)折叠或打开

  1. SQL> select * from v$log;

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

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

  7. SQL>
  8. SQL> select group#,member from v$logfile;

  9.     GROUP# MEMBER
  10. ---------- ----------------------------------------------------------------------------------------------------

  11.          3 /u01/app/oracle/oradata/PROD1/redo03.log
  12.          2 /u01/app/oracle/oradata/PROD1/redo02.log
  13.          1 /u01/app/oracle/oradata/PROD1/redo01.log

  14. SQL>




3.3 添加redo日志

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


点击(此处)折叠或打开

  1. SQL>

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


  3. PL/SQL procedure successfully completed.


  4. SQL>


3.4 开启LOGMNR挖掘日志

点击(此处)折叠或打开

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


  2. PL/SQL procedure successfully completed.


  3. 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';


  2. TO_CHAR(TIMESTAMP,' SQL_REDO

  3. ------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------

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

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

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

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

  8. 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';


  2. TO_CHAR(TIMESTAMP,' SQL_UNDO

  3. ------------------- ----------------------------------------------------------------------------------------------------

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

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

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

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

  8. 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> 

  2. SQL> exec dbms_logmnr.end_logmnr();


  3. PL/SQL procedure successfully completed.


  4. SQL>

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

请登录后发表评论 登录
全部评论

注册时间:2013-07-29

  • 博文量
    45
  • 访问量
    86825