实战:oracle 11g r2 LogMiner

大致流程！

 

oracle日志分析工具LogMiner使用

1.设置日期格式

alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual  ;

2.添加补充日志

如果数据库需要使用logminer，就应该添加,只有添加这个日志之后的才能捕获DML

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

 

3.开启归档

alter system set log_Archive_dest_1='location=e:\arch' scope=both;

shutdown immediate

startup mount

alter database archivelog;

alter database open;

 

4.安装LogMiner工具

要安装LogMiner工具，必须首先要运行下面这样两个脚本，这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

1.$ORACLE_HOME\RDBMS\ADMIN\dbmslm.sql
2. $ORACLE_HOME\RDBMS\ADMIN\dbmslmd.sql
3.$ORACLE_HOME\RDBMS\ADMIN\dbmslms.sql

@E:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmslm.sql

程序包已创建。

授权成功。

同义词已创建。

@E:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmslmd.sql
程序包已创建。

同义词已创建。

@E:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmslms.sql
程序包已创建

没有错误。

授权成功。

 

5.使用LogMiner工具

 
5.1、设置参数UTL_FILE_DIR

数据字典文件是一个文本文件，使用包DBMS_LOGMNR_D来创建。如果我们要分析的数据库中的表有变化，影响到库的数据字典也发生变化，这时就需要重新创建该字典文件。另外一种情况是在分析另外一个数据库文件的重作日志时，也必须要重新生成一遍被分析数据库的数据字典文件。

在ORACLE8I的时候，首先在init.ora初始化参数文件中，指定数据字典文件的位置，也就是添加一个参数UTL_FILE_DIR，该参数值为服务器中放置数据字典文件的目录。如：

UTL_FILE_DIR = (e:\test)

ORACLE9I后，推荐使用SPFILE启动，可以动态调整参数；

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      E:\APP\ADMINISTRATOR\PRODUCT\1
                                                 1.2.0\DBHOME_1\DATABASE\SPFILE
                                                 ORCL.ORA

 

SQL> alter system set utl_file_dir='e:\test' scope=spfile;

系统已更改。

shutdown immediate

SQL> startup force

ORACLE 例程已经启动。

 

Total System Global Area  289406976 bytes                                     

Fixed Size                  1248600 bytes                                     

Variable Size              96469672 bytes                                     

Database Buffers          188743680 bytes                                     

Redo Buffers                2945024 bytes                                     

数据库装载完毕。

数据库已经打开。

SQL> show parameter utl_file_dir;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
utl_file_dir                         string      e:\test

 

5.2创建数据字典文件

SQL>@e:\dbms_logmnr_d.build.txt

 

PL/SQL 过程已成功完成。

 

脚本dbms_logmnr_d.build.txt

BEGIN
dbms_logmnr_d.build(
dictionary_filename => 'logminer_dict.dat',
dictionary_location => 'E:\test');
END;
/

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

Oracle的重作日志分为两种，在线（online）和离线（offline）归档日志文件，我这里主要分析归档日志，在线日志原理一样。

 

---在线（online）

A.创建列表

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>'E:\app\Administrator\oradata\orcl\REDO01.LOG');

 

B.添加另外的日志文件到列表
SQL>execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename=>'E:\app\Administrator\oradata\orcl\REDO02.LOG');

SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename=>'E:\app\Administrator\oradata\orcl\REDO03.LOG');

也可以一次性完成：

BEGIN
dbms_logmnr.add_logfile(
'E:\app\Administrator\oradata\orcl\REDO01.LOG',
DBMS_LOGMNR.new );
dbms_logmnr.add_logfile(
'E:\app\Administrator\oradata\orcl\REDO02.LOG',
DBMS_LOGMNR.addfile );
dbms_logmnr.add_logfile(
'E:\app\Administrator\oradata\orcl\REDO03.LOG',
DBMS_LOGMNR.addfile );
END;
/

###说明：
dbms_logmnr.new --用于建一个日志分析表
　　dbms_logmnr.addfile --用于加，入用于分析的日志文件
　　dbms_logmnr.removefile --用于移出，用于分析的日志文件

 

删除
execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename =>'E:\app\Administrator\oradata\orcl\REDO03.LOG');
execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename =>'E:\app\Administrator\oradata\orcl\REDO02.LOG');
execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename =>'E:\app\Administrator\oradata\orcl\REDO01.LOG');

####说明：
sql> execute dbms_logmnr.add_logfile(logfilename => 'E:\app\Administrator\oradata\orcl\REDO03.LOG', option => dbms_logmnr.REMOVEFILE);

 

查看日志文件列表:

 

select db_name, thread_sqn,filename from v$logmnr_logs;

 

----离线（offline）归档日志文件
BEGIN
dbms_logmnr.add_logfile(
'E:\arch\ARC0000000008_0742739616.0001',
DBMS_LOGMNR.new );
dbms_logmnr.add_logfile(
'E:\arch\ARC0000000009_0742739616.0001',
DBMS_LOGMNR.addfile );
dbms_logmnr.add_logfile(
'E:\arch\ARC0000000010_0742739616.0001',
DBMS_LOGMNR.addfile );
END;
/

 

 

5.4启动LogMiner进行分析

5.4.1无限制条件

BEGIN
dbms_logmnr.start_logmnr(
dictfilename => 'E:\test\logminer_dict.dat'
);
END;
/

 

5.4.2 限制条件

BEGIN
dbms_logmnr.start_logmnr(
dictfilename => 'E:\test\logminer_dict.dat',
StartTime => to_date('2011-02-18 16:40:26','YYYY-MM-DD HH24:MI:SS'),
EndTime => to_date('2011-02-18 16:44:41','YYYY-MM-DD HH24:MI:SS ')
);
END;
/

 
5.5 观察分析结果（v$logmnr_contents）

到现在为止，我们已经分析得到了重作日志文件中的内容。动态性能视图v$logmnr_contents包含LogMiner分析得到的所有的信息。

SELECT sql_redo FROM v$logmnr_contents;

SELECT sql_redo FROM v$logmnr_contents where seg_name='T1';

SELECT sql_redo FROM v$logmnr_contents where username='scott'
and seg_name='scott.t1' and upper(operation)='delete';

SELECT sql_redo FROM v$logmnr_contents where seg_name='t1' and upper(operation)='delete';

SELECT sql_redo FROM v$logmnr_contents where username='SYS' and table_name='T1';

 

6.关闭LogMiner

可以把v$logmnr_contents视图的内容创建一个永久的数据库表将非常有帮助
sql> create table logmnr_contents as select * from v$logmnr_contents;
当完成了重做日志的检查，运行dbms_logmnr 中的end_logmnr
execute dbms_logmnr.end_logmnr();