oracle 的flashback version query和logminer

有时候需要知道已经做过的DML的详细信息，比如说执行的语句，语句提交的时间点等，我们可以通过两种方式查询，logminer和version query。

做了如下操作

创建一个表

SCOTT@DCSSS > create table test_logmnr (id number, val varchar2(10));

Table created.

插入两个记录，要注意的是VALUE用的是大写字母，等下看看从logmnr中找回来的是大写还是小写。

SCOTT@DCSSS > insert into test_logmnr VALUES(1,'first');

1 row created.

SCOTT@DCSSS > insert into test_logmnr VALUES(2,'second');

1 row created.

SCOTT@DCSSS > commit;

Commit complete.

删除记录

SCOTT@DCSSS > delete from test_logmnr;

2 rows deleted.

SCOTT@DCSSS > commit;

Commit complete.

SCOTT@DCSSS >

现在操作完毕，先看一下logmnr的查询

用logminer要首先确定log，也就是你要用logminer去查看哪个log

SYS@DCSSS > select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       7915  524288000          2 YES INACTIVE            1.1656E+12 19-JUN-12
         2          1       7916  524288000          2 YES INACTIVE            1.1656E+12 19-JUN-12
         3          1       7917  524288000          2 NO  CURRENT             1.1656E+12 19-JUN-12

SYS@DCSSS > select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         3         ONLINE  /u11/oradata/DCSSS/redo03_1.rdo                                                  NO
         3         ONLINE  /u11/oradata/DCSSS/redo03_2.rdo                                                  NO
         2         ONLINE  /u11/oradata/DCSSS/redo02_1.rdo                                                  NO
         2         ONLINE  /u11/oradata/DCSSS/redo02_2.rdo                                                  NO
         1         ONLINE  /u11/oradata/DCSSS/redo01_1.rdo                                                  NO
         1         ONLINE  /u11/oradata/DCSSS/redo01_2.rdo                                                  NO

看上面的查询，发现当前的log，是group3，但不知道当时做操作的时候log应该是哪个。所以再做下面的查询

SYS@DCSSS > select group#,members,status,to_char(first_time,'YYYY-MM-DD HH24-MI-SS') from v$log;

    GROUP#    MEMBERS STATUS           TO_CHAR(FIRST_TIME,
---------- ---------- ---------------- -------------------
         1          2 INACTIVE         2012-06-19 07-36-46
         2          2 INACTIVE         2012-06-19 10-41-46
         3          2 CURRENT          2012-06-19 13-51-46

把date类型用to_char处理一下，就清晰很多了，这样根据时间可以知道，第三行的log就是需要的，再结合v$logfile，得到该log。

接下来，用logminer处理该log。 需要做的步骤是

1. 添加log

2.执行logminer进行分析

3.查询结果

下面逐步进行

1.添加log

SYS@DCSSS > exec dbms_logmnr.add_logfile( logfilename=>'/u11/oradata/DCSSS/redo03_1.rdo' , options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

如果是第一个添加的log文件，options部分是dbms_logmnr.new。

如果是增加多个文件，从第二个开始，options部分是dbms_logmnr.addfile

2.执行logminer进行分析

SYS@DCSSS > exec dbms_logmnr.start_logmnr( options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

注意，这里可以加上starttime 和 endtime参数，这样如果redo太大，我们可以只分析某个时间段

3.查询结果

查询结果要查询v$logmnr_contents

SYS@DCSSS > SELECT TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') AS TIME , USERNAME , SQL_REDO,TABLE_name FROM V$LOGMNR_CONTENTS WHERE USERNAME='SCOTT' and table_name='TEST_LOGMNR' ORDER BY TIME;

TIME                USERNAME   SQL_REDO                                                                         TABLE_NAME
------------------- ---------- -------------------------------------------------------------------------------- ---------------
2012-06-19 15:00:41 SCOTT      create table test_logmnr (id number, val varchar2(10));                          TEST_LOGMNR
2012-06-19 15:01:52 SCOTT      insert into "SCOTT"."TEST_LOGMNR"("ID","VAL") values ('1','first');              TEST_LOGMNR
2012-06-19 15:02:10 SCOTT      insert into "SCOTT"."TEST_LOGMNR"("ID","VAL") values ('2','second');             TEST_LOGMNR
2012-06-19 15:02:25 SCOTT      delete from "SCOTT"."TEST_LOGMNR" where "ID" = '1' and "VAL" = 'first' and ROWID TEST_LOGMNR
                                = 'AABPSQAANAAAOY0AAA';

2012-06-19 15:02:25 SCOTT      delete from "SCOTT"."TEST_LOGMNR" where "ID" = '2' and "VAL" = 'second' and ROWI TEST_LOGMNR
                               D = 'AABPSQAANAAAOY0AAB';

上面是通过 logminer来查看，如果想快速的查看，可以通过flashback version query来查询。原理就是查询undo数据，但是一个db的undo保存期限是有一定时间的，而且undo的空间也有限，所以这种情况下，有可能因为时间太久等查不到，具体查询如下。

1. 对test_logmnr 执行一个DML

SYS@DCSSS > INSERT INTO SCOTT.TEST_LOGMNR VALUES(9,'NINE');

1 row created.

SYS@DCSSS > COMMIT;

Commit complete.

2. 进行version query

SYS@DCSSS > SELECT ID,VAL,VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_OPERATION FROM SCOTT.TEST_LOGMNR
  2  VERSIONS BETWEEN TIMESTAMP
  3  TO_TIMESTAMP('2012-06-19 16:59:00','YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2012-06-19 17:00:00','YYYY-MM-DD HH24:MI:SS')
  4  ORDER BY VERSIONS_STARTTIME
  5  /

        ID VAL        VERSIONS_STARTTIME                       VERSIONS_ENDTIME                         VERSIONS_OPERATION
---------- ---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
         9 NINE       19-JUN-12 04.59.29 PM                                                             I
         8 eight