Oracle LogMiner 学习点滴

Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML(insert、update、delete等)语句，另外还可分析得到一些必要的回滚SQL语句。该工具特别适用于调试、审计或者回退某个特定的事务。

总的说来，LogMiner工具的主要用途有：
(1) 跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
(2). 回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。
(3) 优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。

1.安装LOGMNR包，需要本步骤没什么可多说的，先查看本地是否安装:

          select   DISTINCT NAME   from   dba_source   where   type= 'PACKAGE' and name IN ('DBMS_LOGMNR','DBMS_LOGMNR_D');

如果未安装安装下面3个脚本即可:

C:/>sqlplus /nolog
SQL> conn / as sysdba
SQL> @D:/oracle/product/10.2.0/db_2/RDBMS/ADMIN/dbmslm.sql
SQL> @D:/oracle/product/10.2.0/db_2/RDBMS/ADMIN/dbmslmd.sql
SQL> @D:/oracle/product/10.2.0/db_2/RDBMS/ADMIN/dbmslms.sql

 

2.创建数据字典,

先查看系统默认的字典文件存放目录

SQL> show parameter utl_file_dir;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string     

再修改字典文件存放系统文件目录为具体某个文件夹,如下设置为'd:/oracle/logmnr/'
SQL> alter system set utl_file_dir='d:/oracle/logmnr/' scope=both; 

 (scope=both,如果是生产库的话,建议使用scope=memory)

再次查看

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      D:/oracle/logmnr/

执行创建字典文件,时间稍微有点长..完成后可在目录下,看到生成都文件'dictionary.ora'
SQL> EXECUTE dbms_logmnr_d.build('dictionary.ora','d:/oracle/logmnr/');

 

 

3.创建要分析的日志文件列表

查看日志文件及其状态:

SQL> select s.group#,s.status,t.type,t.member from v$log s,v$logfile t where s.group#=t.group#;

    GROUP# STATUS           TYPE    MEMBER
---------- ---------------- ------- --------------------------------------------------------------------------------
         1 INACTIVE         ONLINE  D:/ORACLE/ORADATA/FZLGFM/REDO01.LOG
         2 INACTIVE         ONLINE  D:/ORACLE/ORADATA/FZLGFM/REDO02.LOG
         3 CURRENT          ONLINE  D:/ORACLE/ORADATA/FZLGFM/REDO03.LOG

Oracle的重作日志分为两种，在线（online）和离线（offline）归档日志文件，下面就分别来讨论这两种不同日志文件的列表创建。

(1)在线日志文件的分析:(以下操作添加日志分析1和2两个文件作为分析日志文件列表)

SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:/ORACLE/ORADATA/FZLGFM/REDO01.LOG',Options=>dbms_logmnr.new);
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:/ORACLE/ORADATA/FZLGFM/REDO02.LOG',Options=>dbms_logmnr.addfile);
或
SQL> begin
sys.dbms_logmnr.add_logfile(LogFileName=>'D:/ORACLE/ORADATA/FZLGFM/REDO02.LOG',options =>dbms_logmnr.addfile);
end;

例如我现在只想分析日志2文件,那么移除日志1文件,命令如下:

EXEC dbms_logmnr.add_logfile('D:/ORACLE/ORADATA/FZLGFM/REDO01.LOG',dbms_logmnr.removefile);

 

4.使用字典分析日志文件
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'d:/oracle/logmnr/dictionary.ora');

 

通过对过程DBMS_ LOGMNR.START_LOGMNR中几个不同参数的设置,可以缩小要分析日志文件的范围。

参数         参数类型          默认值       含义

 StartScn     数字型(Number)    0            分析重作日志中SCN>=StartScn日志文件部分ITPUB个人空间(GDp `+cE1X
 EndScn       数字型(Number)    0            分析重作日志中SCN<=EndScn日志文件部分
日期型(Date)      1998-01-01   分析重作日志中时间戳>=StartTime的日志文件部分
EndTime      日期型(Date)      2988-01-01   分析重作日志中时间戳<=EndTime的日志文件部分

 DictFileName 字符型(VARCHAR2)           字典文件，该文件包含一个数据库目录的快照。使用该文件可以使得到的分析结果是可以理解的文本形式，而非系统内部的16进制
 Options BINARY_INTEGER 0  系统调试参数，实际很少使用

 

5.查询结果
SQL> select scn,sql_redo from v$logmnr_contents;

6.退出logmnr
SQL> execute dbms_logmnr.end_logmnr;

 

 

为了理解清晰,重新建立个LOG并且查看操作,进行LOGMINER分析

(1)--增加一组日志文件

SQL> alter database add logfile group 4 ('D:/ORACLE/ORADATA/FZLGFM/REDO04.LOG') size 20M;

Database altered

 

(2)--切换到新的日志文件组

SQL> alter system switch logfile;

System altered

SQL> select s.group#,s.status,t.type,t.member from v$log s,v$logfile t where s.group#=t.group#;

    GROUP# STATUS           TYPE    MEMBER
---------- ---------------- ------- --------------------------------------------------------------------------------
         1 INACTIVE         ONLINE  D:/ORACLE/ORADATA/FZLGFM/REDO01.LOG
         2 INACTIVE         ONLINE  D:/ORACLE/ORADATA/FZLGFM/REDO02.LOG
         3 ACTIVE           ONLINE  D:/ORACLE/ORADATA/FZLGFM/REDO03.LOG
         4 CURRENT          ONLINE  D:/ORACLE/ORADATA/FZLGFM/REDO04.LOG

 

(3)操作表

SQL> create table logtest(id number,oper varchar2(30));

Table created

SQL> insert into logtest values (1,'insert data');

1 row inserted

SQL> insert into logtest values (2,'insert data two');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into logtest values (3,'insert data three rollback');

1 row inserted

SQL> rollback;

Rollback complete

SQL> insert into logtest values (4,'insert data four delete');

1 row inserted

SQL> delete from logtest where id=4;

1 row deleted

SQL> commit;

Commit complete

 

(4)查看当前SCN:

select checkpoint_change# from v$database;
select name,checkpoint_change# from v$datafile;

 

 

(5)然后使用LOGMINER:

 

 EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:/ORACLE/ORADATA/FZLGFM/REDO04.LOG',Options=>dbms_logmnr.new);

 

execute dbms_logmnr.start_logmnr(dictfilename=>'d:/oracle/logmnr/dictionary.ora');

 

SQL> SELECT scn,TIMESTAMP,LOG_ID,sql_redo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='LOGTEST';

       SCN TIMESTAMP       LOG_ID SQL_REDO
---------- ----------- ---------- --------------------------------------------------------------------------------
1161497160 2010-08-09         248 create table logtest(id number,oper varchar2(30))
                                 
                                  ;

1161497160 2010-08-09         248 insert into "SYS"."LOGTEST"("ID","OPER") values ('1','insert data');
1161497160 2010-08-09         248 insert into "SYS"."LOGTEST"("ID","OPER") values ('2','insert data two');
1161497160 2010-08-09         248 insert into "SYS"."LOGTEST"("ID","OPER") values ('3','insert data three rollback
1161497160 2010-08-09         248 delete from "SYS"."LOGTEST" where ROWID = 'AAAIg9AABAAARlaAAC';
1161497160 2010-08-09         248 insert into "SYS"."LOGTEST"("ID","OPER") values ('4','insert data four delete');
1161497161 2010-08-09         248 delete from "SYS"."LOGTEST" where "ID" = '4' and "OPER" = 'insert data four dele

7 rows selected

可以看到我们的操作都记录在当前LOG,并且通过LOGMINER还原展示出来了......