oracle 日志挖掘初探

 

 

Logminer是oracle从8i开始提供的用于分析重做日志信息的工具，包括DBMS_LOGMNR和DBMS_LOGMNR_D两个package。可以分析redo log file 以及archive log file

 

 

1 日志挖掘

[oracle@uplooking admin]$ pwd /opt/oracle/product/11.2.0/db/rdbms/admin [oracle@uplooking admin]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 11:13:39 2016   Copyright (c) 1982, 2011, Oracle.  All rights reserved.   Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> @dbmslm.sql                                             Package created.     Grant succeeded.     Synonym created.   SQL> @dbmslmd.sql   Package created.     Synonym created.   SQL>

 

 

 

2 参数的设置

SQL> show parameter utl_file   NAME                     TYPE    VALUE ------------------------------------ ----------- ------------------------------ utl_file_dir                 string SQL> alter system set utl_file_dir='*' scope=spfile   2  /   System altered.   SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.   Total System Global Area 4275781632 bytes Fixed Size          2235208 bytes Variable Size       2298479800 bytes Database Buffers    1962934272 bytes Redo Buffers           12132352 bytes Database mounted. Database opened. ---- 生成数据字典文件 SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20090625.dat',dictionary_location => '/opt/oracle/oradata');   PL/SQL procedure successfully completed. ---- 指定表空间 SQL>  EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('users')    PL/SQL procedure successfully completed.   SQL>

 

3 增加要分析的日志

 

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/opt/oracle/oradata/hdp/archive1/1_49_914757739.dbf',Options=>dbms_logmnr.new);   PL/SQL procedure successfully completed.   SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/opt/oracle/oradata/hdp/archive1/1_49_914757739.dbf',Options=>dbms_logmnr.addfile);   PL/SQL procedure successfully completed.

 

 

 

 

4 挖掘

SQL>  execute dbms_logmnr.start_logmnr(dictfilename=>'/opt/oracle/oradata/dict20090625.dat');   PL/SQL procedure successfully completed.

 

 

5 生成在线数据字典

EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

 

 

 

6 查看挖掘出来的日志

SQL> select username,session#,sql_redo,operation from v$logmnr_contents where rownum<2   2  ;   USERNAME            SESSION# ------------------------------ ---------- SQL_REDO -------------------------------------------------------------------------------- OPERATION -------------------------------- UNKNOWN             0 insert into "SYS"."WRH$_MEMORY_TARGET_ADVICE"("SNAP_ID","DBID","INSTANCE_NUMBER" ,"MEMORY_SIZE","MEMORY_SIZE_FACTOR","ESTD_DB_TIME","ESTD_DB_TIME_FACTOR","VERSIO N") values ('104','1053250152','1','1024','.25','50','1.0001','0'); INSERT

 

 

备注：注意，v$logmnr_contents内容保存了日志的内容，只在当前会话有效，如果想长期保存分析，可以在当前会话用create table tablename asselect * from v$logmnr_contents语句来持久保存。

 

 

7 结束分析，释放PGA资源

SQL> exec dbms_logmnr.end_logmnr   PL/SQL procedure successfully completed.

 

 

 

 

8 基于DDL的日志挖掘

 

 

1 -- DDL数据挖掘举例 EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20160622.dat',dictionary_location => '/opt/oracle/oradata');   EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('users')   2 /***SQL> conn scott/123123 Connected. SQL> create table t as select * from emp   2  /   Table created.   3 SQL> drop table t;   Table dropped.***/   4 日志信息添加到数据字典中 exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/redo03.log',Options=>dbms_logmnr.new) exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/redo02.log',Options=>dbms_logmnr.new) exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/redo01.log',Options=>dbms_logmnr.new) exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby11.log',Options=>dbms_logmnr.new) exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby12.log',Options=>dbms_logmnr.new) exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby13.log',Options=>dbms_logmnr.new) exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby14.log',Options=>dbms_logmnr.new)   5 分析数据字典的信息 execute dbms_logmnr.start_logmnr(dictfilename=>'/opt/oracle/oradata/dict20160622.dat');   select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents       where seg_name='T'and seg_owner='SCOTT';

 

 

9 看看追踪到的信息

 

10  结束数据挖掘

exec dbms_logmnr.end_logmnr;

 

 

11 提供语句，可以查询到所有的日志。由于这里是dataguard，所以还多了standby redo日志

SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')' from v$logfile;   'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''')' -------------------------------------------------------------------------------- exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/redo03.log') exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/redo02.log') exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/redo01.log') exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby11.log') exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby12.log') exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby13.log') exec dbms_logmnr.add_logfile('/opt/oracle/oradata/hdp/dg/standby14.log')   7 rows selected.