oracle logmnr使用

logminer的用途 日志文件中存放着所有进行数据库恢复的数据，记录了针对数据库结构的每一个变化，也就是对数据库操作的所有DML语句。
logminer 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。
总的说来，logminer工具的主要用途有：
　1、跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
　2、回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。

　3、优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。

安装logmgr需要以SYS用户执行以下两个包：
SYS@PROD4> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
SYS@PROD4> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

创建数据字典文件：
SYS@PROD4> show parameter utl;
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines     string
utl_file_dir     string
SYS@PROD4> 
SYS@PROD4> alter system set utl_file_dir='/u01/app/logminer' scope=spfile;
System altered.
SYS@PROD4> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD4> startup
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size    2926320 bytes
Variable Size  440404240 bytes
Database Buffers   75497472 bytes
Redo Buffers    5459968 bytes
Database mounted.
Database opened.

SYS@PROD4>  show parameter utl_file_dir  

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir     string /u01/app/logminer

SYS@PROD4> exec dbms_logmnr_d.build( 'dictionary.ora', '/u01/app/logminer'); 

PL/SQL procedure successfully completed.

分析之前开启附加日志：
SYS@PROD4> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;  
SUPPLEME
--------
IMPLICIT

SYS@PROD4> alter database add supplemental log data;  
Database altered.

SYS@PROD4> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;  

SUPPLEME
--------
YES

创建要分析的日志文件：
SYS@PROD4> select * from v$log;  
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
1    1      16   52428800   512 1 YES
INACTIVE       2120534 21-DEC-15      2258122 21-SEP-16 0

2    1      17   52428800   512 1 YES
INACTIVE       2258122 21-SEP-16      2376251 21-SEP-16 0

3    1      18   52428800   512 1 NO
CURRENT       2376251 21-SEP-16   2.8147E+14 0

SYS@PROD4> set linesize 300
SYS@PROD4> col member for a50
SYS@PROD4> select * from v$logfile; 

    GROUP# STATUS  TYPE    MEMBER      IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
3   ONLINE  /u01/app/oracle/oradata/PROD4/redo03.log      NO   0
2   ONLINE  /u01/app/oracle/oradata/PROD4/redo02.log      NO   0
1   ONLINE  /u01/app/oracle/oradata/PROD4/redo01.log      NO   0

SYS@PROD4> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD4/redo01.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SYS@PROD4> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD4/redo02.log', dbms_logmnr.addfile);  

PL/SQL procedure successfully completed.

SYS@PROD4> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD4/redo03.log', dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

开启分析：
（1）无限制条件
SYS@PROD4> exec dbms_logmnr.start_logmnr( dictfilename=>'/u01/app/logminer/dictionary.ora'); 

PL/SQL procedure successfully completed.

（2）有限制条件
通过对过程DBMS_ LOGMNR.START_LOGMNR中几个不同参数的设置（参数含义见表1），可以缩小要分析日志文件的范围。通过设置起始时间和终止时间参数我们可以限制只分析某一时间范围的日志。

如下面的例子，我们仅仅分析2004年9月18日的日志，：
SQL> EXECUTE dbms_logmnr.start_logmnrdictfilename => '/u01/app/logminer/dictionary.ora',starttime => to_date('2006-02-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),endtime => to_date(''2004-9-18 23:59:59','YYYY-MM-DD HH24:MI:SS '));
也可以通过设置起始SCN和截至SCN来限制要分析日志的范围：
SQL> EXECUTE dbms_logmnr.start_logmnr( dictfilename => '/u01/app/logminer/dictionary.ora', startscn => 20, endscn => 50);

参数	参数类型	默认值	含义
StartScn	数字型（Number）	0	分析重作日志中SCN≥StartScn日志文件部分
EndScn	数字型（Number）	0	分析重作日志中SCN≤EndScn日志文件部分
StartTime	日期型(Date)	1998-01-01	分析重作日志中时间戳≥StartTime的日志文件部分
EndTime	日期型(Date)	2988-01-01	分析重作日志中时间戳≤EndTime的日志文件部分
DictFileName	字符型(VARCHAR2)	0	字典文件，该文件包含一个数据库目录的快照。使用该文件可以使得到的分析结果是可以理解的文本形式，

提供LogMiner字典有3种选项:
1、使用源数据库数据字典（Online Catalog）
使用源数据库分析重做日志或归档日志时，如果要分析表的结构没有发生任何变化，Oracle建议使用该选项分析重做日志和归档日志。为了使LogMiner使用当前数据库的数据字典，启动LogMiner时应执行如下操作：
SQL> execute dbms_logmnr.start_logmnr (options=>dbms_logmnr.dict_from_online_catalog);
需要注意，dbms_logmnr.dict_from_online_catalog要求数据库必须处于open状态，并且该选项只能用于跟踪DML操作，而不能用于跟踪DDL操作。（本人一般用这种查DML操作）
2、摘取LogMiner字典到重做日志
使用分析数据库分析重做日志或归档日志，或者被分析表的结构发生改变时，Oracle建议使用该选项分析重做日志和归档日志。为了摘取LogMiner字典到重做日志，要求源数据库必须处于archivelog模式，并且该数据库处于open状态。示例如下：
SQL> execute dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);
（本人比较少用这种方式）
3、摘取LogMiner字典到字典文件
字典文件用于存放对象ID号和对象名信息，该选项是为了与早期版本兼容而保留的。需要注意，使用字典文件分析重做日志时，如果要分析新建的对象。必须重新建立字典文件。如下所示：
SQL> execute dbms_logmnr_d.build ('dict.ora'，'d:\demo'，dbms_logmnr_d.store_in_flat_file);
（本人一般用这种方式来查DDL的操作记录，如数据库没有配置utl_file_dir参数，需要配置后需要重启数据库）

观察分析结果：
SYS@PROD4> select filename from v$logmnr_dictionary;  

FILENAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/logminer/dictionary.ora

SYS@PROD4>  select * from v$logmnr_parameters;  

START_DAT REQUIRED_ END_DATE   START_SCN REQUIRED_START_SCN    END_SCN  OPTIONS INFO       STATUS  CON_ID
--------- --------- --------- ---------- ------------------ ---------- ---------- -------------------------------- ---------- ----------
   01-JAN-11 2120534  0     0 0    0       0

SYS@PROD4> select filename from v$logmnr_logs;  

FILENAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD4/redo01.log
/u01/app/oracle/oradata/PROD4/redo02.log
/u01/app/oracle/oradata/PROD4/redo03.log

SYS@PROD4> @1
select operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='ROSAN' and seg_name='TEST';

OPERATION SQL_REDO    SQL_UNDO
-------------------------------- -------------------------------------------------- --------------------------------------------------
INSERT insert into "ROSAN"."TEST"("ID","NAME") values ('1 delete from "ROSAN"."TEST" where "ID" = '1' and "N
','aa');    AME" = 'aa' and ROWID = 'AAAWxyAAGAAAADfAAA';

DELETE delete from "ROSAN"."TEST" where ROWID = 'AAAWxyAA
GAAAADfAAA';

INSERT insert into "ROSAN"."TEST"("ID","NAME") values ('1 delete from "ROSAN"."TEST" where "ID" = '1' and "N
','aa');    AME" = 'aa' and ROWID = 'AAAWxyAAGAAAADfAAA';

INSERT insert into "ROSAN"."TEST"("ID","NAME") values ('2 delete from "ROSAN"."TEST" where "ID" = '2' and "N
','bb');    AME" = 'bb' and ROWID = 'AAAWxyAAGAAAADfAAB';

UPDATE update "ROSAN"."TEST" set "ID" = '3' where "ID" =  update "ROSAN"."TEST" set "ID" = '2' where "ID" =
'2' and ROWID = 'AAAWxyAAGAAAADfAAB';    '3' and ROWID = 'AAAWxyAAGAAAADfAAB';

SYS@PROD4>  exec dbms_logmnr.end_logmnr; 

PL/SQL procedure successfully completed.

实验例子：
SYS@PROD4> show user;
USER is "SYS"
SYS@PROD4> conn rosan
Enter password: 
Connected.
ROSAN@PROD4> 
ROSAN@PROD4> truncate table test;

Table truncated.

ROSAN@PROD4> insert into test values(1,'aaa');

1 row created.

ROSAN@PROD4> insert into test values(2,'bbb');

1 row created.

ROSAN@PROD4> commit;

Commit complete.

ROSAN@PROD4> update test set id=3 where id=2;

1 row updated.

ROSAN@PROD4> commit;

Commit complete.

ROSAN@PROD4> alter system switch logfile;

System altered.

ROSAN@PROD4> conn /as sysdba
Connected.
SYS@PROD4> exec dbms_logmnr.add_logfile('/u01/app/oracle/fast_recovery_area/PROD4/archivelog/2016_09_21/o1_mf_1_19_cy4lhc2s_.arc',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SYS@PROD4> exec dbms_logmnr.start_logmnr(dictfilename=>'/u01/app/logminer/dictionary.ora');

PL/SQL procedure successfully completed.

SYS@PROD4> select filename from v$logmnr_dictionary; 

FILENAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/logminer/dictionary.ora

SYS@PROD4> col filename for a50
SYS@PROD4> /

FILENAME
--------------------------------------------------
/u01/app/logminer/dictionary.ora

SYS@PROD4>  select * from v$logmnr_parameters;

START_DAT REQUIRED_ END_DATE   START_SCN REQUIRED_START_SCN    END_SCN  OPTIONS INFO       STATUS  CON_ID
--------- --------- --------- ---------- ------------------ ---------- ---------- -------------------------------- ---------- ----------
   01-JAN-11 2403889  0     0 0    0       0

SYS@PROD4>  select filename from v$logmnr_logs;

FILENAME
--------------------------------------------------
/u01/app/oracle/fast_recovery_area/PROD4/archivelo
g/2016_09_21/o1_mf_1_19_cy4lhc2s_.arc

SYS@PROD4> get 1
  1* select operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='ROSAN' and seg_name='TEST';
SYS@PROD4> @1

OPERATION SQL_REDO    SQL_UNDO
-------------------------------- -------------------------------------------------- --------------------------------------------------
DDL truncate table test;
INSERT insert into "ROSAN"."TEST"("ID","NAME") values ('1 delete from "ROSAN"."TEST" where "ID" = '1' and "N
','aaa');    AME" = 'aaa' and ROWID = 'AAAWyFAAGAAAADfAAA';

INSERT insert into "ROSAN"."TEST"("ID","NAME") values ('2 delete from "ROSAN"."TEST" where "ID" = '2' and "N
','bbb');    AME" = 'bbb' and ROWID = 'AAAWyFAAGAAAADfAAB';

UPDATE update "ROSAN"."TEST" set "ID" = '3' where "ID" =  update "ROSAN"."TEST" set "ID" = '2' where "ID" =
'2' and ROWID = 'AAAWyFAAGAAAADfAAB';    '3' and ROWID = 'AAAWyFAAGAAAADfAAB';

SYS@PROD4> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30192548/viewspace-2125283/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30192548/viewspace-2125283/