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 看看追踪到的信息
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';
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. |
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
DBA求职布道者
◆ DB最新咨询 | ◆ DB求职面经 |
| ◆ DB干货推送 | ◆ DB最新技术 |
| ◆ DB职业发展与规划 | ◆ DB进阶思路与方法 |
长按二维码点选（识别图中二维码） |