10g 开始 LOGMNR 越来越好用了

最新推荐文章于 2021-04-16 04:41:04 发布
最新推荐文章于 2021-04-16 04:41:04 发布
阅读量850 收藏
点赞数
分类专栏： ----Backup & Recovery
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cainiaofly/article/details/8706309
版权

最近为了帮项目上的哥们儿跟踪一个诡异的问题，想知道一个FORM具体做了啥操作，因此考虑用logmnr跟踪一下。 特此将logmnr的用法和相关问题整理于此。


场景搭建：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


SQL> show user
USER is "SYS"
SQL> create user pjh identified by "123";

User created.

SQL> grant connect,resource to pjh;

Grant succeeded.

SQL> conn pjh/123;
Connected.
SQL> create table t1 (id number);

Table created.

SQL> declare
v number;
begin
v:=0;
for i in 1..100 loop
insert into t1 (id) values (v);
v:=v+1;
end loop;
END;
/  

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
       100

SQL>


一，10g开始日志挖掘简化了

三步走：指定日志，在线挖掘，结束挖掘

 
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> set wrap off
SQL> set linesize 150
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          55   52428800       512        2 NO  CURRENT             3196934 22-MAR-13     2.8147E+14
     2        1          53   52428800       512        2 YES INACTIVE             3180428 22-MAR-13        3180432 22-MAR-13
     3        1          54   52428800       512        2 YES INACTIVE             3180432 22-MAR-13        3196934 22-MAR-13

SQL> select * from v$logfile;
rows will be truncated


    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ---------------------------------------------------------------------------------------------------------------------------
     3       ONLINE  /u02/test3/redo03a.log
     3       ONLINE  /u03/test3/redo03b.log
     2       ONLINE  /u02/test3/redo02a.log
     2       ONLINE  /u03/test3/redo02b.log
     1       ONLINE  /u02/test3/redo01a.log
     1       ONLINE  /u03/test3/redo01b.log
     4       STANDBY /u02/test3/standby_redo04a.log
     4       STANDBY /u03/test3/standby_redo04b.log
     5       STANDBY /u02/test3/standby_redo05a.log
     5       STANDBY /u03/test3/standby_redo05b.log
     6       STANDBY /u02/test3/standby_redo06a.log

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ---------------------------------------------------------------------------------------------------------------------------
     6       STANDBY /u03/test3/standby_redo06b.log
     7       STANDBY /u02/test3/standby_redo07a.log
     7       STANDBY /u03/test3/standby_redo07b.log

14 rows selected.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEME SUP SUP
-------- --- ---
YES     NO  NO

SQL> exec sys.dbms_logmnr.add_logfile('/u02/test3/redo01a.log',sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.


SQL> SELECT count(*),username,seg_owner,seg_name
FROM v$logmnr_contents
GROUP BY username,seg_owner,seg_name
order by seg_name,seg_owner  2    3    4  
  5  
SQL> /

  COUNT(*) USERNAME              SEG_OWNER               SEG_NAME
---------- ------------------------------ -------------------------------- ---------------------------------------------------------------------------
     1 PJH                  SYS                   COL$
     2 SYS                  SYS                   COL$
   473 UNKNOWN              SYS                   COL_USAGE$
     2 PJH                  SYS                   DEFERRED_STG$
.........
.........
 COUNT(*) USERNAME              SEG_OWNER               SEG_NAME
---------- ------------------------------ -------------------------------- ---------------------------------------------------------------------------
     2 SYS                  MDSYS                SDO_GEOR_DDL__TABLE$$
     2 PJH                  SYS                   SEG$
     3 SYS                  SYS                   SEG$
     1 SYS                  SYS                   SEQ$
    14 UNKNOWN              SYS                   SMON_SCN_TIME
     6 SYS                  SYS                   SYSAUTH$
 101 PJH                  PJH                   T1
     1 SYS                  PJH                   T1
     2 PJH                  SYS                   TAB$
     1 SYS                  SYS                   TAB$
     4 SYS                  SYS                   USER$
........
102 rows selected.


SQL> col sql_redo format a60
SQL> col sql_undo format a90
SQL> select sql_redo,sql_undo from v$logmnr_contents where seg_owner='PJH' and seg_name='T1'

SQL_REDO                                               SQL_UNDO
------------------------------------------------------ -----------------------------------------------------------------------------------------
drop table "PJH"."T1" cascade constraints purge force;
create table t1 (id number);
insert into "PJH"."T1"("ID") values ('0');             delete from "PJH"."T1" where "ID" = '0' and ROWID = 'AAAUGTAAEAAAAGGAAA';
insert into "PJH"."T1"("ID") values ('1');             delete from "PJH"."T1" where "ID" = '1' and ROWID = 'AAAUGTAAEAAAAGGAAB';
insert into "PJH"."T1"("ID") values ('2');             delete from "PJH"."T1" where "ID" = '2' and ROWID = 'AAAUGTAAEAAAAGGAAC';
insert into "PJH"."T1"("ID") values ('3');             delete from "PJH"."T1" where "ID" = '3' and ROWID = 'AAAUGTAAEAAAAGGAAD';
insert into "PJH"."T1"("ID") values ('4');             delete from "PJH"."T1" where "ID" = '4' and ROWID = 'AAAUGTAAEAAAAGGAAE';
insert into "PJH"."T1"("ID") values ('5');             delete from "PJH"."T1" where "ID" = '5' and ROWID = 'AAAUGTAAEAAAAGGAAF';
insert into "PJH"."T1"("ID") values ('6');             delete from "PJH"."T1" where "ID" = '6' and ROWID = 'AAAUGTAAEAAAAGGAAG';
insert into "PJH"."T1"("ID") values ('7');             delete from "PJH"."T1" where "ID" = '7' and ROWID = 'AAAUGTAAEAAAAGGAAH';
insert into "PJH"."T1"("ID") values ('8');             delete from "PJH"."T1" where "ID" = '8' and ROWID = 'AAAUGTAAEAAAAGGAAI';
insert into "PJH"."T1"("ID") values ('9');              delete from "PJH"."T1" where "ID" = '9' and ROWID = 'AAAUGTAAEAAAAGGAAJ';
insert into "PJH"."T1"("ID") values ('10');             delete from "PJH"."T1" where "ID" = '10' and ROWID = 'AAAUGTAAEAAAAGGAAK';
insert into "PJH"."T1"("ID") values ('11');             delete from "PJH"."T1" where "ID" = '11' and ROWID = 'AAAUGTAAEAAAAGGAAL';
insert into "PJH"."T1"("ID") values ('12');             delete from "PJH"."T1" where "ID" = '12' and ROWID = 'AAAUGTAAEAAAAGGAAM';
insert into "PJH"."T1"("ID") values ('13');             delete from "PJH"."T1" where "ID" = '13' and ROWID = 'AAAUGTAAEAAAAGGAAN';
insert into "PJH"."T1"("ID") values ('14');             delete from "PJH"."T1" where "ID" = '14' and ROWID = 'AAAUGTAAEAAAAGGAAO';
insert into "PJH"."T1"("ID") values ('15');             delete from "PJH"."T1" where "ID" = '15' and ROWID = 'AAAUGTAAEAAAAGGAAP';
insert into "PJH"."T1"("ID") values ('16');             delete from "PJH"."T1" where "ID" = '16' and ROWID = 'AAAUGTAAEAAAAGGAAQ';
insert into "PJH"."T1"("ID") values ('17');             delete from "PJH"."T1" where "ID" = '17' and ROWID = 'AAAUGTAAEAAAAGGAAR';
insert into "PJH"."T1"("ID") values ('18');             delete from "PJH"."T1" where "ID" = '18' and ROWID = 'AAAUGTAAEAAAAGGAAS';
insert into "PJH"."T1"("ID") values ('19');             delete from "PJH"."T1" where "ID" = '19' and ROWID = 'AAAUGTAAEAAAAGGAAT';
insert into "PJH"."T1"("ID") values ('20');             delete from "PJH"."T1" where "ID" = '20' and ROWID = 'AAAUGTAAEAAAAGGAAU';
insert into "PJH"."T1"("ID") values ('21');             delete from "PJH"."T1" where "ID" = '21' and ROWID = 'AAAUGTAAEAAAAGGAAV';
insert into "PJH"."T1"("ID") values ('22');             delete from "PJH"."T1" where "ID" = '22' and ROWID = 'AAAUGTAAEAAAAGGAAW';
insert into "PJH"."T1"("ID") values ('23');             delete from "PJH"."T1" where "ID" = '23' and ROWID = 'AAAUGTAAEAAAAGGAAX';
insert into "PJH"."T1"("ID") values ('24');             delete from "PJH"."T1" where "ID" = '24' and ROWID = 'AAAUGTAAEAAAAGGAAY';
insert into "PJH"."T1"("ID") values ('25');             delete from "PJH"."T1" where "ID" = '25' and ROWID = 'AAAUGTAAEAAAAGGAAZ';
insert into "PJH"."T1"("ID") values ('26');             delete from "PJH"."T1" where "ID" = '26' and ROWID = 'AAAUGTAAEAAAAGGAAa';
insert into "PJH"."T1"("ID") values ('27');             delete from "PJH"."T1" where "ID" = '27' and ROWID = 'AAAUGTAAEAAAAGGAAb';
insert into "PJH"."T1"("ID") values ('28');             delete from "PJH"."T1" where "ID" = '28' and ROWID = 'AAAUGTAAEAAAAGGAAc';
insert into "PJH"."T1"("ID") values ('29');             delete from "PJH"."T1" where "ID" = '29' and ROWID = 'AAAUGTAAEAAAAGGAAd';
insert into "PJH"."T1"("ID") values ('30');             delete from "PJH"."T1" where "ID" = '30' and ROWID = 'AAAUGTAAEAAAAGGAAe';
insert into "PJH"."T1"("ID") values ('31');             delete from "PJH"."T1" where "ID" = '31' and ROWID = 'AAAUGTAAEAAAAGGAAf';
insert into "PJH"."T1"("ID") values ('32');             delete from "PJH"."T1" where "ID" = '32' and ROWID = 'AAAUGTAAEAAAAGGAAg';
insert into "PJH"."T1"("ID") values ('33');             delete from "PJH"."T1" where "ID" = '33' and ROWID = 'AAAUGTAAEAAAAGGAAh';
insert into "PJH"."T1"("ID") values ('34');             delete from "PJH"."T1" where "ID" = '34' and ROWID = 'AAAUGTAAEAAAAGGAAi';
insert into "PJH"."T1"("ID") values ('35');             delete from "PJH"."T1" where "ID" = '35' and ROWID = 'AAAUGTAAEAAAAGGAAj';
insert into "PJH"."T1"("ID") values ('36');             delete from "PJH"."T1" where "ID" = '36' and ROWID = 'AAAUGTAAEAAAAGGAAk';
insert into "PJH"."T1"("ID") values ('37');             delete from "PJH"."T1" where "ID" = '37' and ROWID = 'AAAUGTAAEAAAAGGAAl';
insert into "PJH"."T1"("ID") values ('38');             delete from "PJH"."T1" where "ID" = '38' and ROWID = 'AAAUGTAAEAAAAGGAAm';
insert into "PJH"."T1"("ID") values ('39');             delete from "PJH"."T1" where "ID" = '39' and ROWID = 'AAAUGTAAEAAAAGGAAn';
insert into "PJH"."T1"("ID") values ('40');             delete from "PJH"."T1" where "ID" = '40' and ROWID = 'AAAUGTAAEAAAAGGAAo';
insert into "PJH"."T1"("ID") values ('41');             delete from "PJH"."T1" where "ID" = '41' and ROWID = 'AAAUGTAAEAAAAGGAAp';
insert into "PJH"."T1"("ID") values ('42');             delete from "PJH"."T1" where "ID" = '42' and ROWID = 'AAAUGTAAEAAAAGGAAq';
insert into "PJH"."T1"("ID") values ('43');             delete from "PJH"."T1" where "ID" = '43' and ROWID = 'AAAUGTAAEAAAAGGAAr';
insert into "PJH"."T1"("ID") values ('44');             delete from "PJH"."T1" where "ID" = '44' and ROWID = 'AAAUGTAAEAAAAGGAAs';
insert into "PJH"."T1"("ID") values ('45');             delete from "PJH"."T1" where "ID" = '45' and ROWID = 'AAAUGTAAEAAAAGGAAt';
insert into "PJH"."T1"("ID") values ('46');             delete from "PJH"."T1" where "ID" = '46' and ROWID = 'AAAUGTAAEAAAAGGAAu';
insert into "PJH"."T1"("ID") values ('47');             delete from "PJH"."T1" where "ID" = '47' and ROWID = 'AAAUGTAAEAAAAGGAAv';
insert into "PJH"."T1"("ID") values ('48');             delete from "PJH"."T1" where "ID" = '48' and ROWID = 'AAAUGTAAEAAAAGGAAw';
insert into "PJH"."T1"("ID") values ('49');             delete from "PJH"."T1" where "ID" = '49' and ROWID = 'AAAUGTAAEAAAAGGAAx';
insert into "PJH"."T1"("ID") values ('50');             delete from "PJH"."T1" where "ID" = '50' and ROWID = 'AAAUGTAAEAAAAGGAAy';
insert into "PJH"."T1"("ID") values ('51');             delete from "PJH"."T1" where "ID" = '51' and ROWID = 'AAAUGTAAEAAAAGGAAz';
insert into "PJH"."T1"("ID") values ('52');             delete from "PJH"."T1" where "ID" = '52' and ROWID = 'AAAUGTAAEAAAAGGAA0';
insert into "PJH"."T1"("ID") values ('53');             delete from "PJH"."T1" where "ID" = '53' and ROWID = 'AAAUGTAAEAAAAGGAA1';
insert into "PJH"."T1"("ID") values ('54');             delete from "PJH"."T1" where "ID" = '54' and ROWID = 'AAAUGTAAEAAAAGGAA2';
insert into "PJH"."T1"("ID") values ('55');             delete from "PJH"."T1" where "ID" = '55' and ROWID = 'AAAUGTAAEAAAAGGAA3';
insert into "PJH"."T1"("ID") values ('56');             delete from "PJH"."T1" where "ID" = '56' and ROWID = 'AAAUGTAAEAAAAGGAA4';
insert into "PJH"."T1"("ID") values ('57');             delete from "PJH"."T1" where "ID" = '57' and ROWID = 'AAAUGTAAEAAAAGGAA5';
insert into "PJH"."T1"("ID") values ('58');             delete from "PJH"."T1" where "ID" = '58' and ROWID = 'AAAUGTAAEAAAAGGAA6';
insert into "PJH"."T1"("ID") values ('59');             delete from "PJH"."T1" where "ID" = '59' and ROWID = 'AAAUGTAAEAAAAGGAA7';
insert into "PJH"."T1"("ID") values ('60');             delete from "PJH"."T1" where "ID" = '60' and ROWID = 'AAAUGTAAEAAAAGGAA8';
insert into "PJH"."T1"("ID") values ('61');             delete from "PJH"."T1" where "ID" = '61' and ROWID = 'AAAUGTAAEAAAAGGAA9';
insert into "PJH"."T1"("ID") values ('62');             delete from "PJH"."T1" where "ID" = '62' and ROWID = 'AAAUGTAAEAAAAGGAA+';
insert into "PJH"."T1"("ID") values ('63');             delete from "PJH"."T1" where "ID" = '63' and ROWID = 'AAAUGTAAEAAAAGGAA/';
insert into "PJH"."T1"("ID") values ('64');             delete from "PJH"."T1" where "ID" = '64' and ROWID = 'AAAUGTAAEAAAAGGABA';
insert into "PJH"."T1"("ID") values ('65');             delete from "PJH"."T1" where "ID" = '65' and ROWID = 'AAAUGTAAEAAAAGGABB';
insert into "PJH"."T1"("ID") values ('66');             delete from "PJH"."T1" where "ID" = '66' and ROWID = 'AAAUGTAAEAAAAGGABC';
insert into "PJH"."T1"("ID") values ('67');             delete from "PJH"."T1" where "ID" = '67' and ROWID = 'AAAUGTAAEAAAAGGABD';
insert into "PJH"."T1"("ID") values ('68');             delete from "PJH"."T1" where "ID" = '68' and ROWID = 'AAAUGTAAEAAAAGGABE';
insert into "PJH"."T1"("ID") values ('69');             delete from "PJH"."T1" where "ID" = '69' and ROWID = 'AAAUGTAAEAAAAGGABF';
insert into "PJH"."T1"("ID") values ('70');             delete from "PJH"."T1" where "ID" = '70' and ROWID = 'AAAUGTAAEAAAAGGABG';
insert into "PJH"."T1"("ID") values ('71');             delete from "PJH"."T1" where "ID" = '71' and ROWID = 'AAAUGTAAEAAAAGGABH';
insert into "PJH"."T1"("ID") values ('72');             delete from "PJH"."T1" where "ID" = '72' and ROWID = 'AAAUGTAAEAAAAGGABI';
insert into "PJH"."T1"("ID") values ('73');             delete from "PJH"."T1" where "ID" = '73' and ROWID = 'AAAUGTAAEAAAAGGABJ';
insert into "PJH"."T1"("ID") values ('74');             delete from "PJH"."T1" where "ID" = '74' and ROWID = 'AAAUGTAAEAAAAGGABK';
insert into "PJH"."T1"("ID") values ('75');             delete from "PJH"."T1" where "ID" = '75' and ROWID = 'AAAUGTAAEAAAAGGABL';
insert into "PJH"."T1"("ID") values ('76');             delete from "PJH"."T1" where "ID" = '76' and ROWID = 'AAAUGTAAEAAAAGGABM';
insert into "PJH"."T1"("ID") values ('77');             delete from "PJH"."T1" where "ID" = '77' and ROWID = 'AAAUGTAAEAAAAGGABN';
insert into "PJH"."T1"("ID") values ('78');             delete from "PJH"."T1" where "ID" = '78' and ROWID = 'AAAUGTAAEAAAAGGABO';
insert into "PJH"."T1"("ID") values ('79');             delete from "PJH"."T1" where "ID" = '79' and ROWID = 'AAAUGTAAEAAAAGGABP';
insert into "PJH"."T1"("ID") values ('80');             delete from "PJH"."T1" where "ID" = '80' and ROWID = 'AAAUGTAAEAAAAGGABQ';
insert into "PJH"."T1"("ID") values ('81');             delete from "PJH"."T1" where "ID" = '81' and ROWID = 'AAAUGTAAEAAAAGGABR';
insert into "PJH"."T1"("ID") values ('82');             delete from "PJH"."T1" where "ID" = '82' and ROWID = 'AAAUGTAAEAAAAGGABS';
insert into "PJH"."T1"("ID") values ('83');             delete from "PJH"."T1" where "ID" = '83' and ROWID = 'AAAUGTAAEAAAAGGABT';
insert into "PJH"."T1"("ID") values ('84');             delete from "PJH"."T1" where "ID" = '84' and ROWID = 'AAAUGTAAEAAAAGGABU';
insert into "PJH"."T1"("ID") values ('85');             delete from "PJH"."T1" where "ID" = '85' and ROWID = 'AAAUGTAAEAAAAGGABV';
insert into "PJH"."T1"("ID") values ('86');             delete from "PJH"."T1" where "ID" = '86' and ROWID = 'AAAUGTAAEAAAAGGABW';
insert into "PJH"."T1"("ID") values ('87');             delete from "PJH"."T1" where "ID" = '87' and ROWID = 'AAAUGTAAEAAAAGGABX';
insert into "PJH"."T1"("ID") values ('88');             delete from "PJH"."T1" where "ID" = '88' and ROWID = 'AAAUGTAAEAAAAGGABY';
insert into "PJH"."T1"("ID") values ('89');             delete from "PJH"."T1" where "ID" = '89' and ROWID = 'AAAUGTAAEAAAAGGABZ';
insert into "PJH"."T1"("ID") values ('90');             delete from "PJH"."T1" where "ID" = '90' and ROWID = 'AAAUGTAAEAAAAGGABa';
insert into "PJH"."T1"("ID") values ('91');             delete from "PJH"."T1" where "ID" = '91' and ROWID = 'AAAUGTAAEAAAAGGABb';
insert into "PJH"."T1"("ID") values ('92');             delete from "PJH"."T1" where "ID" = '92' and ROWID = 'AAAUGTAAEAAAAGGABc';
insert into "PJH"."T1"("ID") values ('93');             delete from "PJH"."T1" where "ID" = '93' and ROWID = 'AAAUGTAAEAAAAGGABd';
insert into "PJH"."T1"("ID") values ('94');             delete from "PJH"."T1" where "ID" = '94' and ROWID = 'AAAUGTAAEAAAAGGABe';
insert into "PJH"."T1"("ID") values ('95');             delete from "PJH"."T1" where "ID" = '95' and ROWID = 'AAAUGTAAEAAAAGGABf';
insert into "PJH"."T1"("ID") values ('96');             delete from "PJH"."T1" where "ID" = '96' and ROWID = 'AAAUGTAAEAAAAGGABg';
insert into "PJH"."T1"("ID") values ('97');             delete from "PJH"."T1" where "ID" = '97' and ROWID = 'AAAUGTAAEAAAAGGABh';
insert into "PJH"."T1"("ID") values ('98');             delete from "PJH"."T1" where "ID" = '98' and ROWID = 'AAAUGTAAEAAAAGGABi';
insert into "PJH"."T1"("ID") values ('99');             delete from "PJH"."T1" where "ID" = '99' and ROWID = 'AAAUGTAAEAAAAGGABj';

102 rows selected.


二，以什么判断记录先后，scn吗？  不完全是。

SQL> select RS_ID,SSN,SCN,seg_owner,to_char(scn_to_timestamp(SCN),'yyyy-mm-dd hh24:mi:ss') AS t,sql_redo from v$logmnr_contents where seg_owner='PJH' and seg_name='T1';

RS_ID                    SSN       SCN SEG_OWNER            T            SQL_REDO
-------------------------------- ---------- ---------- -------------------------------- ------------------- ------------------------------------------
 0x000037.00000fcc.0010           0    3200012 PJH                2013-03-22 14:59:08 drop table "PJH"."T1" cascade constraints
 0x000037.00001f49.019c           0    3200672 PJH                2013-03-22 15:01:56 create table t1 (id number);
 0x000037.00001f87.0010           0    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('0');
 0x000037.00001f87.01c8           1    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('1');
 0x000037.00001f88.00e0           2    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('2');
 0x000037.00001f88.01e8           3    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('3');
 0x000037.00001f89.0100           4    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('4');
 0x000037.00001f8a.0018           5    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('5');
 0x000037.00001f8a.0120           6    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('6');
 0x000037.00001f8b.0038           7    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('7');
 0x000037.00001f8b.0140           8    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('8');
 0x000037.00001f8c.0058           9    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('9');
 0x000037.00001f8c.0160          10    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('10')
 0x000037.00001f8d.0078          11    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('11')
 0x000037.00001f8d.0180          12    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('12')
 0x000037.00001f8e.0098          13    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('13')
 0x000037.00001f8e.01a0          14    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('14')
 0x000037.00001f8f.00b8          15    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('15')
 0x000037.00001f8f.01c0          16    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('16')
 0x000037.00001f90.00d8          17    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('17')
 0x000037.00001f90.01e0          18    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('18')
 0x000037.00001f91.00f8          19    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('19')
 0x000037.00001f92.0010          20    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('20')
 0x000037.00001f92.0118          21    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('21')
 0x000037.00001f93.0030          22    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('22')
 0x000037.00001f93.0138          23    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('23')
 0x000037.00001f94.0050          24    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('24')
 0x000037.00001f94.0158          25    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('25')
 0x000037.00001f95.0070          26    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('26')
 0x000037.00001f95.0178          27    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('27')
 0x000037.00001f96.0090          28    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('28')
 0x000037.00001f96.0198          29    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('29')
 0x000037.00001f97.00b0          30    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('30')
 0x000037.00001f97.01b8          31    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('31')
 0x000037.00001f98.00d0          32    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('32')
 0x000037.00001f98.01d8          33    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('33')
 0x000037.00001f99.00f0          34    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('34')
 0x000037.00001f9a.0010          35    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('35')
 0x000037.00001f9a.0118          36    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('36')
 0x000037.00001f9b.0030          37    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('37')
 0x000037.00001f9b.0138          38    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('38')
 0x000037.00001f9c.0050          39    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('39')
 0x000037.00001f9c.0158          40    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('40')
 0x000037.00001f9d.0070          41    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('41')
 0x000037.00001f9d.0178          42    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('42')
 0x000037.00001f9e.0090          43    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('43')
 0x000037.00001f9e.0198          44    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('44')
 0x000037.00001f9f.00b0          45    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('45')
 0x000037.00001f9f.01b8          46    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('46')
 0x000037.00001fa0.00d0          47    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('47')
 0x000037.00001fa0.01d8          48    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('48')
 0x000037.00001fa1.00f0          49    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('49')
 0x000037.00001fa2.0010          50    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('50')
 0x000037.00001fa2.0118          51    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('51')
 0x000037.00001fa3.0030          52    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('52')
 0x000037.00001fa3.0138          53    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('53')
 0x000037.00001fa4.0050          54    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('54')
 0x000037.00001fa4.0158          55    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('55')
 0x000037.00001fa5.0070          56    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('56')
 0x000037.00001fa5.0178          57    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('57')
 0x000037.00001fa6.0090          58    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('58')
 0x000037.00001fa6.0198          59    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('59')
 0x000037.00001fa7.00b0          60    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('60')
 0x000037.00001fa7.01b8          61    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('61')
 0x000037.00001fa8.00d0          62    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('62')
 0x000037.00001fa8.01d8          63    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('63')
 0x000037.00001fa9.00f0          64    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('64')
 0x000037.00001faa.0010          65    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('65')
 0x000037.00001faa.0118          66    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('66')
 0x000037.00001fab.0030          67    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('67')
 0x000037.00001fab.0138          68    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('68')
 0x000037.00001fac.008c          69    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('69')
 0x000037.00001fac.0194          70    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('70')
 0x000037.00001fad.00ac          71    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('71')
 0x000037.00001fad.01b4          72    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('72')
 0x000037.00001fae.00cc          73    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('73')
 0x000037.00001fae.01d4          74    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('74')
 0x000037.00001faf.00ec          75    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('75')
 0x000037.00001fb0.0010          76    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('76')
 0x000037.00001fb0.0118          77    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('77')
 0x000037.00001fb1.0030          78    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('78')
 0x000037.00001fb1.0138          79    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('79')
 0x000037.00001fb2.0050          80    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('80')
 0x000037.00001fb2.0158          81    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('81')
 0x000037.00001fb3.0070          82    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('82')
 0x000037.00001fb3.0178          83    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('83')
 0x000037.00001fb4.0090          84    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('84')
 0x000037.00001fb4.0198          85    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('85')
 0x000037.00001fb5.00b0          86    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('86')
 0x000037.00001fb5.01b8          87    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('87')
 0x000037.00001fb6.00d0          88    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('88')
 0x000037.00001fb6.01d8          89    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('89')
 0x000037.00001fb7.00f0          90    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('90')
 0x000037.00001fb8.0010          91    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('91')
 0x000037.00001fb8.0118          92    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('92')
 0x000037.00001fb9.0030          93    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('93')
 0x000037.00001fb9.0138          94    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('94')
 0x000037.00001fba.0050          95    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('95')
 0x000037.00001fba.0158          96    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('96')
 0x000037.00001fbb.0070          97    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('97')
 0x000037.00001fbb.0178          98    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('98')
 0x000037.00001fbc.0090          99    3200720 PJH                2013-03-22 15:02:20 insert into "PJH"."T1"("ID") values ('99')

102 rows selected.

常用的按执行顺序查看日志的方法：

SELECT RS_ID,SSN,SCN,seg_owner,to_char(scn_to_timestamp(SCN),'yyyy-mm-dd hh24:mi:ss') AS t,sql_redo,sql_undo,session_info,username FROM v$logmnr_contents
where seg_owner not like '%SYS%' or seg_name='T1'-- and username='TESTER1'  
order by scn desc,rs_id desc,ssn desc



三，在发生ddl或者drop （purg） 时，记录会有什么变化？

在表t1上依次执行了

alter table t1 add (name varchar2(50));

alter table t1 drop column name;

drop table t1；

flashback table t1 to before drop;

drop table t1 purge;


以下可以看到每次DDL或者drop操作，如何影响v$logmnr_contents中的信息：

SQL> select sql_redo,sql_undo from v$logmnr_contents where seg_owner='PJH' and seg_name='T1';


SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

insert into "PJH"."T1"("COL 1") values (HEXTORAW('80'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c102'));

insert into "PJH"."T1"("COL 1") values (HEXTORAW('c103'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c104'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c105'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c106'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c107'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c108'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c109'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c10a'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c10b'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c10c'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c10d'));
........
........
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c160'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c161'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c162'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c163'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c164'));
alter table t1 add (name varchar2(50));

110 rows selected.



SQL> select sql_redo,sql_undo from v$logmnr_contents where seg_owner='PJH' and seg_name='T1'

SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

insert into "PJH"."T1"("COL 1") values (HEXTORAW('80'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c102'));

insert into "PJH"."T1"("COL 1") values (HEXTORAW('c103'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c104'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c105'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c106'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c107'));
.......
.......
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15b'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15c'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15d'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15e'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15f'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c160'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c161'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c162'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c163'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c164'));
alter table t1 add (name varchar2(50));
alter table t1 drop column name;

111 rows selected.






SQL> select sql_redo,sql_undo from v$logmnr_contents where seg_owner='PJH';


SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('80'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c102'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c103'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c104'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c105'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c106'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c107'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c108'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c109'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c10a'));
.......
.......
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c158'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c159'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c15a'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c15b'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c15c'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c15d'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c15e'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c15f'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c160'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c161'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c162'));

SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c163'));
insert into "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0"("COL 1") values (HEXTORAW('c164'));
alter table t1 add (name varchar2(50));
alter table t1 drop column name;
ALTER TABLE "PJH"."T1" RENAME TO "BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0" ;
drop table t1 AS "BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0" ;

116 rows selected.




SQL> select sql_redo,sql_undo from v$logmnr_contents where seg_owner='PJH'


SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "PJH"."T1"("COL 1") values (HEXTORAW('80'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c102'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c103'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c104'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c105'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c106'));
.......
.......
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15d'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15e'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c15f'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c160'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c161'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c162'));

SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c163'));
insert into "PJH"."T1"("COL 1") values (HEXTORAW('c164'));
alter table t1 add (name varchar2(50));
alter table t1 drop column name;
ALTER TABLE "PJH"."T1" RENAME TO "BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0" ;
drop table t1 AS "BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0" ;
ALTER TABLE "PJH"."BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0" RENAME TO "T1" ;
flashback table "BIN$2H/Fa7cFGRfgQwEAAH/Esw==$0" to before drop;

118 rows selected.



SQL> select sql_redo,sql_undo from v$logmnr_contents where username='PJH'

SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

..........
set transaction read write;
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('80'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c102'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c103'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c104'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c105'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c106'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c107'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c108'));
.......
.......
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c153'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c154'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c155'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c156'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c157'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c158'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c159'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c15a'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c15b'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c15c'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c15d'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c15e'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c15f'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c160'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c161'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c162'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c163'));
insert into "UNKNOWN"."OBJ# 82324"("COL 1") values (HEXTORAW('c164'));
commit;
set transaction read write;
.........
.........
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set transaction read write;
commit;
drop table t1 purge;
........
........


四，为什么有时候会抓不到记录？

Minimal Supplemental Logging

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables. To enable minimal supplemental logging, execute the following SQL statement:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


五，为何没有username与session_info信息?

Cause

  1. If supplemental logging was not active at the time when the redo records were created, then LogMiner won't be able to obtain all the required information. TheOracle Database Utilities manual mentions:
    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.
    So, we have to enable supplemental logging by using a SQL statement similar to the following:
    SQL> CONNECT / AS SYSDBA
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

    Then the information necessary to populate the USERNAME and SESSION_INFO columns will be stored in the redo stream.

  2. The redo stream does not contain the USERNAME and SESSION_INFO data for every transaction. This information is only stored for the first transaction executed in the user's session. So in order to be able to see this information inV$LOGMNR_CONTENTS, all the redo generated during the entire session must be added to the mining session. Should this not be done, then theUSERNAME andSESSION_INFO columns will remain empty.

  3. LogMiner was first available in Oracle8i. If the COMPATIBLE instance parameter is set to a value lower than 8.1.0 you will not have access to its full functionality.

  4. In Oracle9i and lower releases of Oracle, the TRANSACTION_AUDITING instance parameter is set to TRUE by default. This causes the generation of a redo record containing the user logon name, username, session ID, and some operating system and client information. For each successive transaction in the session, Oracle will store only the session ID. These session IDs are linked back to the first record to retrieve user and session information.

    When TRANSACTION_AUDITING is set to FALSE, this redo record is not written and the user information is not available to LogMiner.

Solution

This can result from your database parameter settings and also from the method you are using to mine redo logs using LogMiner.

  1. Ensure that database was in minimum supplemental logging at the time that the redo information was created:
    SQL> SELECT name, supplemental_log_data_min FROM v$database;

    NAME                           SUPPLEME
    ------------------------------ --------
    M10202WA                       YES

  2. Ensure that all archive redo logs containing the necessary redo information have been added to the LogMiner session.

  3. Ensure that the COMPATIBLE initialization parameter is set to 8.1.0 or higher.
    SQL> show parameter compatible

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------
    compatible                           string      10.2.0.2.0

  4. For Oracle8i and Oracle9i only: ensure that the TRANSACTION_AUDITING instance parameter is set to TRUE (default).
    SQL> show parameter transaction_auditing

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------
    transaction_auditing                 boolean     TRUE




Bond的杂货铺
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
[Logmnr]使用logminer找回被误删的数据
不忘初心、方得始终
11-13 1984
LogMiner日志挖掘技术在有些时候可以被比作是命悬一线的救命稻草,比方说误删除数据(如delete执行不当),可以通过LogMiner挖掘日志文件将这些数据补插回来 使用LogMiner预览:LogMiner的原理就是找出日志文件(redo file),所对应的UNDO.如你新增1W行数据,在redo里以insert形式体现,对应的undo即为deleteLogMiner所有操作
LOGMNR详解
07-31
2. **使用字典分析日志文件**：使用`dbms_logmnr.start_logmnr`过程开始分析指定的日志文件。 ```sql SQL>execute dbms_logmnr.start_logmnr(dictfilename=>'d:\oracle\logmnr\dictionary.ora'); ``` 3. **查询...
undo change vector size oracle,ddl操作是否会产生undo?
weixin_39568233的博客
04-16 186
ddl是否会产生undo?这可能是每一个初学Oracle的人都会有的疑问；ddl操作又不能rollback回滚，要什么undo数据呢？事实是几乎每个ddl操作都会产生undo,我们来探究一下:SQL> select vs.name, ms.value2 from v$mystat ms, v$sysstat vs3 where ms.statistic# = vs.statisti...
alter table新增字段操作究竟有何影响？(上篇)
bisal的专栏
05-01 9886
很久以前就有个疑问，见过一些表设计时会留出几个reverse的字段，目的是为了以后扩展，但此时设计的字段类型、长度等都是预计的，未来是否可用，不好说，那为什么会这么做呢？可能的原因是：“我现在设定好字段，需要的时候直接用就行了，不需要新增字段的操作”。那么，问题就是，新增字段的操作究竟有什么影响？增加表字段的时候，是否会锁表？对DML、DDL有什么影响？如果搞清楚这些，才能对上面的问题给出科学的答
利用LOGMNR恢复DELETE的数据
xiaofan23z的专栏
04-21 1768
利用LOGMNR恢复DELETE的数据  (2011-07-31 13:20:44) ▼ 测试如下： create table ta.test3 as select * from user_objects; select count(*) from ta.test3; delet ta.test3; commit; 1、假设由于时间的问题，用闪回报错
logmnr 的使用详解
N
02-23 2536
select  * from v$log select * from v$logfile exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/aiki/redo03.log',dbms_logmnr.new); select count(1) from v$logmnr_contents; exec dbms_logmnr
oracle views 10g
12-18
### Oracle 10g 视图详解 #### 高可用性和恢复能力 Oracle 10g 的高可用性与恢复功能对于确保数据安全至关重要。以下列出的视图提供了关于备份、归档和恢复过程的关键信息。 1. **V$ARCHIVE**: 展示了归档日志的...
Oracle10G LogMiner的配置
05-07
Oracle 10G LogMiner 是Oracle数据库系统中一个强大的日志分析工具，它允许用户在不使用归档重做日志的情况下分析数据库的redo log files，获取对数据库更改历史的详细视图。LogMiner 提供了一种有效的方式来进行...
oracle 10g dataguard 详细配置步骤
09-11
### Oracle 10g DataGuard 详细配置步骤 在Oracle 10g中，DataGuard是一种高可用性解决方案，它可以创建并维护一个或多个物理、逻辑或快照副本数据库（称为standby数据库），这些副本与主数据库（primary database...
logmnr来分析归档日志操作.txt
11-01
SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); END; ``` 这里，`START_LOGMNR`过程接收一个参数： - `OPTIONS`：指定字典来源为在线数据字典。 ##### 3. 查询LogMiner...
Oracle Logminer 做数据恢复 说明示例
热门推荐
David Dai -- Focus on Oracle
06-19 1万+
在IBM DBA 小荷的blog上看到一个用Logminer 做数据恢复的例子。 虽然对Logminer 也了解一点，但是用Logminer 做恢复还真没用过，所以也测试一下。 原文链接地址如下：        客户的一次疏忽，DBA的一次噩梦       http://www.oracleblog.org/working-case/dba-always-bad-l
Logmnr 介绍
changyanmanman的专栏
11-14 2041
原文转自：http://blog.csdn.net/tianlesoftware/article/details/5604497 一.  Logminer 说明   Oracle LogMiner 的官网说明：       Using LogMiner to Analyze Redo Log Files        http://download.oracle.com/docs/cd/
oracle 10g logmnr的用法
weixin_34178244的博客
07-13 77
最近项目遇见一个表数据老是被莫名删除掉，想查出到底什么操作所致。想想oracle的logmnr可以解决这个问题，就研究了下，系统系RAC,数据老是在凌晨4点丢失，所以只能从归档分析，oracle 10g版本，之前只对logmnr有过初步了解，用起来还真的仔细研究研究，以下是简单的测试方法，其他方法暂时不做多描述。 安装logmnr视图及字典： SQL> @/opt/...
DDL与UNDO之间的关系
gguxxing008的专栏
06-12 1186
今天在网上找了些资料，整理了下DDL与UNDO之间的关系，通过后台发现DDL还是会产生一定的UNDO，实例演示如下： SQL> select vs.name, ms.value       from v$mystat ms, v$sysstat vs       where ms.statistic# = vs.statistic#       and name = 'undo chang
oracle数据字典详解：内部RDBMS(X$)表、数据字典表、动态性能(v$)视图、数据字典视图
haiross的专栏
11-18 2702
oracle数据字典详解，内部RDBMS(X$)表、数据字典表、动态性能(v$)视图、数据字典视图 分类： Single oracle basic 2013-11-08 17:25 404人阅读 评论(0) 收藏 举报 我们所说的数据字典由四部分组成：（1）内部RDBMS(X$)表（2）数据字典表（3）动态性能(v$)视图（4）数据字典视图   一．内部RDBM
oracle 11g如果应用表依赖的bootstrap$某些某表如obj$_col$数据损坏系列四
11-04 250
结论 1,测试环境为oracle 11.2.0.1 2,如果测试表没有构建索引，如果你在obj$中删除测试表对应记录，会提示 SQL> select a from t_booterr; selec...
Oracle 了解 DDL 操作与 REDO 的关系
weixin_30858241的博客
07-09 237
目录 了解 DDL 操作与 REDO 的关系 DDL是否会产生REDO 通过 10046 trace 来分析create 和drop 如果drop失败，redo的变化 了解 DDL 操作与 REDO 的关系 DDL是否会产生REDO 用到的SQL： ...
ORACLE LOGMNR简单而强大的工具
茄肥猫的窝
11-14 3275
LOGMNR简单而强大的工具 在Oracle数据库中，LGWR进程将数据库中进行的DML等操作信息记录在日志文件中，在归档模式下，日志文件还会写出到归档日志文件中。在数据库发生故障崩溃后，恢复时数据库可以根据日志信息来重演事务，完成恢复，从而保证成功提交的事务不丢失。 很多第三方工具以及Oracle的一些工作，都可以通过解析日志文件来实现数据复制和同步。Oracle随数据库软件提供了一
使用LOGMNR恢复已提交的数据
congpi2096的博客
10-27 149
使用LOGMNR恢复已提交的数据 1、安装LOGMNR 以as sysdba运行以下两个脚本： $ORACLE_HOME/rdbms/admin/dbmslm.sql; $ORACLE_HOME/rdbm...
LOGMNR安装与数据字典管理教程
5. **启动LOGMNR**：调用`EXECUTE dbms_logmnr.start_logmnr`开始日志解析，传入字典文件名。这是整个过程中最重要的一步，它会根据配置开始解析数据库日志，提取SQL语句执行的相关信息。 6. **分析和查询结果**：...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值