Oracle LogMiner 学习点滴

╰半橙微兮° 2022-07-12 14:28 188阅读 0赞

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还原展示出来了......