日志挖掘—logminer

  在Oracle日常管理中会遇到需要知道某个表中的数据什么时候被修改的或者需要进行不完全恢复时，需要知道具体什么时候做的DML或DDL操作。Oracle的日志文件（redo log file)与归档日志文件（archive log file）中记录了每个数据更改的具体时间和系统改变号SCN，也就是说只要找到数据变更的具体时间或者SCN号再加上备份和日志文件就可以准确地恢复到任何需要恢复的时间。Oracle在运行过程中会产生大量的日志文件与归档日志文件，数据量非常大，因此不可能把所有的日志文件都分析一遍，通常会选取需要的时间段之内的日志进行分析，这就需要借助于必要的工具来完成这一操作，下面就介绍一下日志挖据工具logminer使用方法示例。

示例：

1.开启补全日志

SYS@BJ>alter database add supplemental log data;

Database altered.

2.开启归档

SYS@BJ>archive log list;
Database log mode              Archive Mode
Automatic archival               Enabled
Archive destination              USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence      8
Next log sequence to archive    10
Current log sequence             10

3.查看日志文件

SYS@BJ>select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------
NEXT_CHANGE# NEXT_TIME
------------ -------------------
 1          1         10   52428800        512          1 YES INACTIVE          1132383 2016-08-04 16:11:31
 1132427 2016-08-04 16:12:53


 2          1         11   52428800        512          1 YES INACTIVE          1132427 2016-08-04 16:12:53
     1132470 2016-08-04 16:13:44


 3          1         12   52428800        512          1 NO  CURRENT           1132470 2016-08-04 16:13:44
  2.8147E+14

4.切换日志

SYS@BJ>alter system archive log current; 

System altered.

SYS@BJ>select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
NEXT_CHANGE# NEXT_TIME
------------ -------------------
 1          1         13   52428800        512          1 NO  CURRENT           1132932 2016-08-04 16:23:19
  2.8147E+14

 2          1         11   52428800        512          1 YES INACTIVE          1132427 2016-08-04 16:12:53
1132470 2016-08-04 16:13:44

3           1         12   52428800        512          1 YES ACTIVE            1132470 2016-08-04 16:13:44
 1132932 2016-08-04 16:23:19

SYS@BJ>select member from v$logfile;
MEMBER
----------------------------------------
/u01/app/oracle/oradata/BJ/redo03.log
/u01/app/oracle/oradata/BJ/redo02.log
/u01/app/oracle/oradata/BJ/redo01.log

16:24:20 SYS@BJ>select name from v$archived_log; 
NAME
----------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/BJ/archivelog/2016_08_04/o1_mf_1_12_ct5yvqlj_.arc

SYS@BJ>select name from v$archived_log; 

NAME
----------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/BJ/archivelog/2016_08_04/o1_mf_1_12_ct5yvqlj_.arc

5. 添加日志，分析

SYS@BJ>exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/BJ/archivelog/2016_08_04/o1_mf_1_12_ct5yvqlj_.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SYS@BJ>execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/BJ/redo01.log',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

6. 执行logmnr 分析

SYS@BJ>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

7. 查询分析结果

SYS@BJ>select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='T1';

USERNAME   SCN TIMESTAMP         SQL_REDO
--------------- ---------- -------------------       ---------------------------------------
SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '10' and
                            "DNAME" = 'ACCOUNTING' and "LOC" = 'NEW YORK' and
                                   ROWID = 'AAAVpPAAEAAAAIjAAA';

SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '20' and
                                  "DNAME" = 'RESEARCH' and "LOC" = 'DALLAS' and
ROWID = 'AAAVpPAAEAAAAIjAAB';

SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '30' and
                                  "DNAME" = 'SALES' and "LOC" = 'CHICAGO' and
                                   ROWID = 'AAAVpPAAEAAAAIjAAC';

USERNAME   SCN TIMESTAMP        SQL_REDO
--------------- ---------- -------------------      -----------------------------------------

SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '40' and
                                  "DNAME" = 'OPERATIONS' and "LOC" = 'BOSTON' and 
                                   ROWID = 'AAAVpPAAEAAAAIjAAD';

SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '50' and
                                  "DNAME" = 'Beijing' and "LOC" = 'Shanghai' and 
                                   ROWID = 'AAAVpPAAEAAAAIlAAA';

SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '10' and
                                  "DNAME" = 'ACCOUNTING' and "LOC" = 'NEW YORK' and
                  ROWID = 'AAAVpPAAEAAAAImAAA';

USERNAME   SCN TIMESTAMP         SQL_REDO
--------------- ---------- -------------------       -----------------------------------------
SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '20' and
                                  "DNAME" = 'RESEARCH' and "LOC" = 'DALLAS' and 
                                   ROW ID = 'AAAVpPAAEAAAAImAAB';

SCOTT     1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '30' and
                                   "DNAME" = 'SALES' and "LOC" = 'CHICAGO' and 
                                    ROWID = 'AAAVpPAAEAAAAImAAC';

SCOTT     1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '40' and
"DNAME" = 'OPERATIONS' and "LOC" ='BOSTON' and
R OWID = 'AAAVpPAAEAAAAImAAD';

USERNAME   SCN TIMESTAMP        SQL_REDO
--------------- ---------- -------------------      -------------------------------------------
SCOTT    1132806 2016-08-04 16:20:04 delete from "SCOTT"."T1" where "DEPTNO" = '50' and
                                  "DNAME" = 'Beijing' and "LOC" = 'Shanghai' and 
                                   ROWID = 'AAAVpPAAEAAAAImAAE';

SCOTT    1132843 2016-08-04 16:20:52 insert into "SCOTT"."T1"("DEPTNO","DNAME","LOC") v
                                  alues ('1','China','Beijing');

SCOTT    1132866 2016-08-04 16:21:31 insert into "SCOTT"."T1"("DEPTNO","DNAME","LOC") v
                                  alues ('2','Henan','Zhengzhou');                                 
12 rows selected.

8.结束日志分析

SYS@BJ>execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

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

转载于:http://blog.itpub.net/31362743/viewspace-2123449/