logmnr的问题

aaaaaaaa2000

SQL> select GROUP#  ,THREAD#  ,SEQUENCE#  , MEMBERS   , ARCHIVED ,STATUS   from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1         31          1 YES INACTIVE
         2          1         32          1 NO  CURRENT
         3          1         30          1 YES INACTIVE

SQL> connect clm/clm
Connected.
SQL> delete from test;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> connect /as sysdba
Connected.
SQL> select GROUP#  ,THREAD#  ,SEQUENCE#  , MEMBERS   , ARCHIVED ,STATUS   from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1         31          1 YES INACTIVE
         2          1         32          1 NO  CURRENT
         3          1         30          1 YES INACTIVE

SQL> desc v$logfile;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GROUP#                                             NUMBER
STATUS                                             VARCHAR2(7)
TYPE                                               VARCHAR2(7)
MEMBER                                             VARCHAR2(513)
IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> select  GROUP#      ,MEMBER   from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         1
/home/oracle/app/oradata/ora10/redo01.log

         2
/home/oracle/app/oradata/ora10/redo02.log

         3
/home/oracle/app/oradata/ora10/redo03.log



SQL> execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/ora10/redo02.log',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.



SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/app/logs/dict.ora');

PL/SQL procedure successfully completed.


SQL> create table te1 as select * from GV$LOGMNR_CONTENTS ;

Table created.

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

但是现在使用logmnr得到的结果是这样的;

select t.sql_redo,t.sql_undo from te1 t where t.table_name='TEST';

           SQL_REDO        SQL_UNDO
1        insert into "CLM"."TEST"("ID") values ('2');        delete from "CLM"."TEST" where "ID" = '2' and ROWID = 'AAAL5WAAEAAAAAMAAG';

为啥删除了5行数据结果只显示了delete from test where id=2这一条那？

aaaaaaaa2000

难道需要打开附加日志才能显示正确吗？

kevinlin.ora

如果没有设置最小附加日志，挖掘信息会有遗漏，详见ORACLE support文档：Effect of Supplemental Logging on LogMiner with Example [ID 750198.1]，介绍了最小附加日志对日志挖掘结果的影响。

kevinlin.ora

原帖由 aaaaaaaa2000 于 2012-2-21 14:22 发表
难道需要打开附加日志才能显示正确吗？

建议在使用ORACLE提供的功能前，阅读相关配置说明文档。

aaaaaaaa2000

多谢版主的解答！！！！！

Maclean Liu(刘相兵

dbms_logmnr Unsupported SQLREDO

Question:

Try Testing Oracle Logminer



SQL> create table maclean (t1 varchar2(100)) tablespace users;

Table created.

SQL> insert into maclean values (‘MACLEAN’);

1 row created.

SQL> commit;

Commit complete.

after start logmnr:
sql> …add log file ….
sql> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
sql> select sql_redo from v$logmnr_contents;
create table maclean (t1 varchar2(100)) tablespace users;
Unsupported
commit;
In the system, some statement will got sqltext from sqlredo column and other is ‘Unsupported’. The got sqltext have INSERT, DELETE, UPDATE; and ‘Unsupported’ sql have INSERT, DELETE, UPDATE too.



Answer:

Have you enabled supplemental logging prior to mining the redo / archive logs ? If not then please enable the supplemental logging.

To enable minimal supplemental logging execute the following SQL statement:
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

If you had not enabled supplemental logging earlier then the redo logs will not contain sufficient information to mine the logs. Additionally logminer will not always populate all the fields of the v$logmnr_contents this is because the redo may/may not have all the information that we need for every column. Adding Supplemental Logging will help in more info being logged in the redo being generated, helping populate more values.



Exactly, supplemental logging is mandatory, Oracle recommends that you at least enable minimal supplemental logging for LogMiner. By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging prior to generating log files which will be analyzed by LogMiner.
Once supplemental logging is enabled the log file generated later on will be elgible for log miner.



You must enable at least database minimal supplemental logging prior to generate log files which will be analyzed by LogMiner.

http://www.oracledatabase12g.com ... ported-sqlredo.html

lxhbww

楼主是你理解错了，没仔细看，真正这个是最后insert 2的语句，也就是抓到是最后的一条增加2的，而不是删除的
而你看到的删除语句，是对应增加的反操作恢复语句。