logMiner 日志挖掘 测试

时间:2023-01-06 07:43:05

LogMiner介绍

Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具,使用该工具可以轻松获得Oracle 重做日志文件(归档日志文件)中的具体内容,LogMiner分析工具实际上是由一组PL/SQL包和一些动态视图组成,它作为Oracle数据库的一部分来发布,是oracle公司提供的一个完全免费的工具。

LogMiner功能

日志文件中存放着所有进行数据库恢复的数据,记录了针对数据库结构的每一个变化,也就是对数据库操作的所有DML语句。
在Oracle 8i之前,Oracle没有提供任何协助数据库管理员来读取和解释重作日志文件内容的工具。系统出现问题,对于一个普通的数据管理员来讲,唯一可以作的工作就是将所有的日志文件打包,然后发给Oracle公司的技术支持,然后静静地等待Oracle 公司技术支持给我们最后的答案。然而从8i以后,Oracle提供了这样一个强有力的工具-LogMiner。
LogMiner工具的主要用途有:
1. 跟踪数据库的变化:可以离线的跟踪数据库的变化,而不会影响在线系统的性能。
2. 回退数据库的变化:回退特定的变化数据,减少point-in-time recovery的执行。
3. 优化和扩容计划:可通过分析日志文件中的数据以分析数据增长模式。
4.  准确定位错误发生的时间点,然后采取相应的补救措施。
5.  还原表,将表恢复到先前的一个状态,然后用已存档的日志文件向前回滚。
6.  性能协调和容量规划。
7.  事后审核。

LogMiner注意事项

1.  重做日志文件必须和运行logminer的数据库有相同的字符集。

2.  数据库必须运行在archivelog模式下。

3.  不能在共享服务器环境中做logminer操作。

4.  只能在8i以及以后的版本使用。

5.  logminer不支持索引组织表、long、lob及集合类型。

6.  原数据库平台必须和分析数据库平台一样。

LogMiner测试(此过程不需关闭数据库)

环境:oracle 11.2.0.1.0 rac(11g开始支持em界面操作日志挖掘了,找机会测试一下)

平台:Linux rac2.localdomain 2.6.18-128.el5 #1 SMP Wed Dec 17 11:42:39 EST 2008 i686 i686 i386 GNU/Linux

1.创建DBMS_LOGMNR程序包和数据字典(必须使用sys用户执行)

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 20 11:45:05 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/dbmslm.sql

Package created.


Grant succeeded.


Synonym created.

SQL> @?/rdbms/admin/dbmslmd.sql

Package created.


Synonym created.

2.检查数据库SUPPLEMENTAL_LOG_DATA_MIN状态

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES

-----10G 新特性,如果不开启,这只能分析出DDl语句,无法分析DML语句

如果不是YES,就需要开启。

alter database add SUPPLEMENTAL_LOG_DATA_MIN log data;

3.产生测试文件

SQL> alter system switch logfile;

System altered.

SQL> create table t (a varchar2(10));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit
  2  ;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------

+FRA/rac/archivelog/2013_03_20/thread_2_seq_182.316.810559721
+FRA/rac/archivelog/2013_03_20/thread_2_seq_183.317.810559731
+FRA/rac/archivelog/2013_03_20/thread_1_seq_202.318.810561485
+FRA/rac/archivelog/2013_03_20/thread_1_seq_203.319.810561645

4.将新生成的日志文件添加到LOGMINER列表。

其中的options有三种取值,
dbms_logmnr.new 用于建一个日志分析表
dbms_logmnr.addfile 用于加入用于分析的的日志文件
dbms_logmnr.removefile用于移出用于分析的日志文件

SQL> exec dbms_logmnr.add_logfile('+FRA/rac/archivelog/2013_03_20/thread_1_seq_203.319.810561645',dbms_logmnr.new);


PL/SQL procedure successfully completed.

加入完毕后使用select filename from v$logmnr_logs;查看。

5.为logminer制定将要使用的联机目录
SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

6.查询v$logmnr_logs及v$logmnr_contents视图。

SQL> select low_time,high_time,low_scn,next_scn from v$logmnr_logs;

LOW_TIME            HIGH_TIME              LOW_SCN   NEXT_SCN
------------------- ------------------- ---------- ----------
2013-03-20 11:58:04 2013-03-20 12:00:44    2393218    2395680

SQL> select username,sql_redo,sql_undo from v$logmnr_contents;

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
UNKNOWN    set transaction read write;
UNKNOWN
UNKNOWN    insert into "GG"."GGS_MARKER"( delete from "GG"."GGS_MARKER"
           "SEQNO","FRAGMENTNO","OPTIME", where "SEQNO" = '631' and "FRA
           "TYPE","SUBTYPE","MARKER_TEXT" GMENTNO" = '1' and "OPTIME" =
           ) values ('631','1','2013-03-2 '2013-03-20 12:00:01' and "TYP
           0 12:00:01','DDL','DDLINFO',', E" = 'DDL' and "SUBTYPE" = 'DD
           C1=''create table t \(a varcha LINFO' and "MARKER_TEXT" = ',C
           r2\(10\)\) '',');              1=''create table t \(a varchar
                                          2\(10\)\) '',' and ROWID = 'AA
                                          ASHIAAEAAAAEPAAD';

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------

UNKNOWN    insert into "GG"."GGS_MARKER"( delete from "GG"."GGS_MARKER"
           "SEQNO","FRAGMENTNO","OPTIME", where "SEQNO" = '631' and "FRA
           "TYPE","SUBTYPE","MARKER_TEXT" GMENTNO" = '2' and "OPTIME" =
           ) values ('631','2','2013-03-2 '2013-03-20 12:00:01' and "TYP
           0 12:00:01','DDL','DDLINFO',', E" = 'DDL' and "SUBTYPE" = 'DD
           C5=''631'',,B2='''',,G4='''',, LINFO' and "MARKER_TEXT" = ',C
           B3=''SYS'',,B4=''T'',,C12='''' 5=''631'',,B2='''',,G4='''',,B
           ,,C13='''',,B5=''TABLE'',,B6=' 3=''SYS'',,B4=''T'',,C12='''',
           'CREATE'',,B7=''631'',,B8=''GG ,C13='''',,B5=''TABLE'',,B6=''
           .GGS_DDL_HIST'',,B9=''SYS'',,C CREATE'',,B7=''631'',,B8=''GG.

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           7=''11.2.0.1.0'',,C8=''11.2.0. GGS_DDL_HIST'',,B9=''SYS'',,C7
           0.0'',,C9='''',,C10=''1'',,C11 =''11.2.0.1.0'',,C8=''11.2.0.0
           =''rac1'',,G3=''NONUNIQUE'',,C .0'',,C9='''',,C10=''1'',,C11=
           15=''YES'',,C14=''NO'',,C20='' ''rac1'',,G3=''NONUNIQUE'',,C1
           NO'',,C17(''1'')=''NLS_LANGUAG 5=''YES'',,C14=''NO'',,C20=''N
           E'',,C18(''1'')=''AMERICAN'',, O'',,C17(''1'')=''NLS_LANGUAGE
           C17(''2'')=''NLS_TERRITORY'',, '',,C18(''1'')=''AMERICAN'',,C
           C18(''2'')=''AMERICA'',,C17('' 17(''2'')=''NLS_TERRITORY'',,C
           3'')=''NLS_CURRENCY'',,C18(''3 18(''2'')=''AMERICA'',,C17(''3
           '')=''$'',,C17(''4'')=''NLS_IS '')=''NLS_CURRENCY'',,C18(''3'
           O_CURRENCY'',,C18(''4'')=''AME ')=''$'',,C17(''4'')=''NLS_ISO

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           RICA'',,C17(''5'')=''NLS_NUMER _CURRENCY'',,C18(''4'')=''AMER
           IC_CHARACTERS'',,C18(''5'')='' ICA'',,C17(''5'')=''NLS_NUMERI
           .\,'',,C17(''6'')=''NLS_CALEND C_CHARACTERS'',,C18(''5'')=''.
           AR'',,C18(''6'')=''GREGORIAN'' \,'',,C17(''6'')=''NLS_CALENDA
           ,,C17(''7'')=''NLS_DATE_FORMAT R'',,C18(''6'')=''GREGORIAN'',
           '',,C18(''7'')=''yyyy-mm-dd hh ,C17(''7'')=''NLS_DATE_FORMAT'
           24:mi:ss'',,C17(''8'')=''NLS_D ',,C18(''7'')=''yyyy-mm-dd hh2
           ATE_LANGUAGE'',,C18(''8'')=''A 4:mi:ss'',,C17(''8'')=''NLS_DA
           MERICAN'',,C17(''9'')=''NLS_SO TE_LANGUAGE'',,C18(''8'')=''AM
           RT'',,C18(''9'')=''BINARY'',,C ERICAN'',,C17(''9'')=''NLS_SOR
           17(''10'')=''NLS_TIME_FORMAT'' T'',,C18(''9'')=''BINARY'',,C1

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           ,,C18(''10'')=''HH.MI.SSXFF AM 7(''10'')=''NLS_TIME_FORMAT'',
           '',,C17(''11'')=''NLS_TIMESTAM ,C18(''10'')=''HH.MI.SSXFF AM'
           P_FORMAT'',,C18(''11'')=''DD-M ',,C17(''11'')=''NLS_TIMESTAMP
           ON-RR HH.MI.SSXFF AM'',,C17('' _FORMAT'',,C18(''11'')=''DD-MO
           12'')=''NLS_TIME_TZ_FORMAT'',, N-RR HH.MI.SSXFF AM'',,C17(''1
           C18(''12'')=''HH.MI.SSXFF AM T 2'')=''NLS_TIME_TZ_FORMAT'',,C
           ZR'',,C17(''13'')=''NLS_TIMEST 18(''12'')=''HH.MI.SSXFF AM TZ
           AMP_TZ_FORMAT'',,C18(''13'')=' R'',,C17(''13'')=''NLS_TIMESTA
           'DD-MON-RR HH.MI.SSXFF AM TZR' MP_TZ_FORMAT'',,C18(''13'')=''
           ',,C17(''14'')=''NLS_DUAL_CURR DD-MON-RR HH.MI.SSXFF AM TZR''
           ENCY'',,C18(''14'')=''$'',,C17 ,,C17(''14'')=''NLS_DUAL_CURRE

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           (''15'')=''NLS_COMP'',,C18(''1 NCY'',,C18(''14'')=''$'',,C17(
           5'')=''BINARY'',,C17(''16'')=' ''15'')=''NLS_COMP'',,C18(''15
           'NLS_LENGTH_SEMANTICS'',,C18(' '')=''BINARY'',,C17(''16'')=''
           '16'')=''BYTE'',,C17(''17'')=' NLS_LENGTH_SEMANTICS'',,C18(''
           'NLS_NCHAR_CONV_EXCP'',,C18('' 16'')=''BYTE'',,C17(''17'')=''
           17'')=''FALSE'',,C19=''17'',') NLS_NCHAR_CONV_EXCP'',,C18(''1
           ;                              7'')=''FALSE'',,C19=''17'',' a
                                          nd ROWID = 'AAASHIAAEAAAAEPAAE
                                          ';

UNKNOWN    set transaction read write;

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
UNKNOWN    update "SYS"."OBJ$" set "OBJ#" update "SYS"."OBJ$" set "OBJ#"
            = '1', "DATAOBJ#" = '74459',   = '1', "DATAOBJ#" = '74454',
           "TYPE#" = '0', "CTIME" = TO_DA "TYPE#" = '0', "CTIME" = TO_DA
           TE('2009-08-13 23:00:54', 'yyy TE('2009-08-13 23:00:54', 'yyy
           y-mm-dd hh24:mi:ss'), "MTIME"  y-mm-dd hh24:mi:ss'), "MTIME"
           = TO_DATE('2013-03-20 12:00:01 = TO_DATE('2013-03-16 10:11:15
           ', 'yyyy-mm-dd hh24:mi:ss'), " ', 'yyyy-mm-dd hh24:mi:ss'), "
           STIME" = TO_DATE('2009-08-13 2 STIME" = TO_DATE('2009-08-13 2
           3:00:54', 'yyyy-mm-dd hh24:mi: 3:00:54', 'yyyy-mm-dd hh24:mi:
           ss'), "STATUS" = '0', "FLAGS"  ss'), "STATUS" = '0', "FLAGS"
           = '0', "OID$" = NULL, "SPARE1" = '0', "OID$" = NULL, "SPARE1"

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
            = '0', "SPARE2" = '65535' whe  = '0', "SPARE2" = '65535' whe
           re "OBJ#" = '1' and "DATAOBJ#" re "OBJ#" = '1' and "DATAOBJ#"
            = '74454' and "OWNER#" = '0'   = '74459' and "OWNER#" = '0'
           and "NAME" = '_NEXT_OBJECT' an and "NAME" = '_NEXT_OBJECT' an
           d "NAMESPACE" = '1' and "SUBNA d "NAMESPACE" = '1' and "SUBNA
           ME" IS NULL and "TYPE#" = '0'  ME" IS NULL and "TYPE#" = '0'
           and "CTIME" = TO_DATE('2009-08 and "CTIME" = TO_DATE('2009-08
           -13 23:00:54', 'yyyy-mm-dd hh2 -13 23:00:54', 'yyyy-mm-dd hh2
           4:mi:ss') and "MTIME" = TO_DAT 4:mi:ss') and "MTIME" = TO_DAT
           E('2013-03-16 10:11:15', 'yyyy E('2013-03-20 12:00:01', 'yyyy
           -mm-dd hh24:mi:ss') and "STIME -mm-dd hh24:mi:ss') and "STIME

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           " = TO_DATE('2009-08-13 23:00: " = TO_DATE('2009-08-13 23:00:
           54', 'yyyy-mm-dd hh24:mi:ss')  54', 'yyyy-mm-dd hh24:mi:ss')
           and "STATUS" = '0' and "REMOTE and "STATUS" = '0' and "REMOTE
           OWNER" IS NULL and "LINKNAME"  OWNER" IS NULL and "LINKNAME"
           IS NULL and "FLAGS" = '0' and  IS NULL and "FLAGS" = '0' and
           "OID$" IS NULL and "SPARE1" =  "OID$" IS NULL and "SPARE1" =
           '0' and "SPARE2" = '65535' and '0' and "SPARE2" = '65535' and
            "SPARE3" = '0' and ROWID = 'A  "SPARE3" = '0' and ROWID = 'A
           AAAASAABAAAADxAAb';            AAAASAABAAAADxAAb';

UNKNOWN

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
UNKNOWN    commit;
UNKNOWN    insert into "SYS"."OBJ$"("OBJ# delete from "SYS"."OBJ$" where
           ","DATAOBJ#","OWNER#","NAME","  "OBJ#" = '74454' and "DATAOBJ
           NAMESPACE","SUBNAME","TYPE#"," #" = '74454' and "OWNER#" = '0
           CTIME","MTIME","STIME","STATUS ' and "NAME" = 'T' and "NAMESP
           ","REMOTEOWNER","LINKNAME","FL ACE" = '1' and "SUBNAME" IS NU
           AGS","OID$","SPARE1","SPARE2", LL and "TYPE#" = '2' and "CTIM
           "SPARE3","SPARE4","SPARE5","SP E" = TO_DATE('2013-03-20 12:00
           ARE6") values ('74454','74454' :01', 'yyyy-mm-dd hh24:mi:ss')
           ,'0','T','1',NULL,'2',TO_DATE(  and "MTIME" = TO_DATE('2013-0
           '2013-03-20 12:00:01', 'yyyy-m 3-20 12:00:01', 'yyyy-mm-dd hh

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           m-dd hh24:mi:ss'),TO_DATE('201 24:mi:ss') and "STIME" = TO_DA
           3-03-20 12:00:01', 'yyyy-mm-dd TE('2013-03-20 12:00:01', 'yyy
            hh24:mi:ss'),TO_DATE('2013-03 y-mm-dd hh24:mi:ss') and "STAT
           -20 12:00:01', 'yyyy-mm-dd hh2 US" = '1' and "REMOTEOWNER" IS
           4:mi:ss'),'1',NULL,NULL,'0',NU  NULL and "LINKNAME" IS NULL a
           LL,'6','1','0',NULL,NULL,NULL) nd "FLAGS" = '0' and "OID$" IS
           ;                               NULL and "SPARE1" = '6' and "
                                          SPARE2" = '1' and "SPARE3" = '
                                          0' and "SPARE4" IS NULL and "S
                                          PARE5" IS NULL and "SPARE6" IS
                                           NULL and ROWID = 'AAAAASAABAA

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
                                          APt7AAD';

UNKNOWN    set transaction read write;
UNKNOWN
UNKNOWN    set transaction read write;
UNKNOWN
UNKNOWN    commit;
UNKNOWN    insert into "SYS"."SEG$"("FILE delete from "SYS"."SEG$" where
           #","BLOCK#","TYPE#","TS#","BLO  "FILE#" = '1' and "BLOCK#" =
           CKS","EXTENTS","INIEXTS","MINE '86912' and "TYPE#" = '3' and
           XTS","MAXEXTS","EXTSIZE","EXTP "TS#" = '0' and "BLOCKS" = '8'

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           CT","USER#","LISTS","GROUPS","  and "EXTENTS" = '1' and "INIE
           BITMAPRANGES","CACHEHINT","SCA XTS" = '8' and "MINEXTS" = '1'
           NHINT","HWMINCR","SPARE1","SPA  and "MAXEXTS" = '2147483645'
           RE2") values ('1','86912','3', and "EXTSIZE" = '128' and "EXT
           '0','8','1','8','1','214748364 PCT" = '0' and "USER#" = '0' a
           5','128','0','0','0','0','2147 nd "LISTS" = '0' and "GROUPS"
           483645','0','0','74454','43253 = '0' and "BITMAPRANGES" = '21
           77',NULL);                     47483645' and "CACHEHINT" = '0
                                          ' and "SCANHINT" = '0' and "HW
                                          MINCR" = '74454' and "SPARE1"
                                          = '4325377' and "SPARE2" IS NU

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
                                          LL and ROWID = 'AAAAAIAABAAADm
                                          PAAL';

UNKNOWN    commit;
UNKNOWN    set transaction read write;
UNKNOWN
UNKNOWN
UNKNOWN    commit;
UNKNOWN    set transaction read write;
UNKNOWN
UNKNOWN    commit;

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
UNKNOWN    insert into "SYS"."TAB$"("OBJ# delete from "SYS"."TAB$" where
           ","DATAOBJ#","TS#","FILE#","BL  "OBJ#" = '74454' and "DATAOBJ
           OCK#","BOBJ#","TAB#","COLS","C #" = '74454' and "TS#" = '0' a
           LUCOLS","PCTFREE$","PCTUSED$", nd "FILE#" = '1' and "BLOCK#"
           "INITRANS","MAXTRANS","FLAGS", = '86912' and "BOBJ#" IS NULL
           "AUDIT$","ROWCNT","BLKCNT","EM and "TAB#" IS NULL and "COLS"
           PCNT","AVGSPC","CHNCNT","AVGRL = '1' and "CLUCOLS" IS NULL an
           N","AVGSPC_FLB","FLBCNT","ANAL d "PCTFREE$" = '10' and "PCTUS
           YZETIME","SAMPLESIZE","DEGREE" ED$" = '40' and "INITRANS" = '
           ,"INSTANCES","INTCOLS","KERNEL 1' and "MAXTRANS" = '255' and
           COLS","PROPERTY","TRIGFLAG","S "FLAGS" = '1' and "AUDIT$" = '

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           PARE1","SPARE2","SPARE3","SPAR ------------------------------
           E4","SPARE5","SPARE6") values  --------' and "ROWCNT" IS NULL
           ('74454','74454','0','1','8691  and "BLKCNT" IS NULL and "EMP
           2',NULL,NULL,'1',NULL,'10','40 CNT" IS NULL and "AVGSPC" IS N
           ','1','255','1','------------- ULL and "CHNCNT" IS NULL and "
           -------------------------',NUL AVGRLN" IS NULL and "AVGSPC_FL
           L,NULL,NULL,NULL,NULL,NULL,NUL B" IS NULL and "FLBCNT" IS NUL
           L,NULL,NULL,NULL,NULL,NULL,'1' L and "ANALYZETIME" IS NULL an
           ,'1','536870912','0','736',NUL d "SAMPLESIZE" IS NULL and "DE
           L,NULL,NULL,NULL,TO_DATE('2013 GREE" IS NULL and "INSTANCES"
           -03-20 04:00:01', 'yyyy-mm-dd  IS NULL and "INTCOLS" = '1' an

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           hh24:mi:ss'));                 d "KERNELCOLS" = '1' and "PROP
                                          ERTY" = '536870912' and "TRIGF
                                          LAG" = '0' and "SPARE1" = '736
                                          ' and "SPARE2" IS NULL and "SP
                                          ARE3" IS NULL and "SPARE4" IS
                                          NULL and "SPARE5" IS NULL and
                                          "SPARE6" = TO_DATE('2013-03-20
                                           04:00:01', 'yyyy-mm-dd hh24:m
                                          i:ss') and ROWID = 'AAAAACAABA
                                          AAUg/AAJ';


USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
UNKNOWN    insert into "SYS"."COL$"("OBJ# delete from "SYS"."COL$" where
           ","COL#","SEGCOL#","SEGCOLLENG  "OBJ#" = '74454' and "COL#" =
           TH","OFFSET","NAME","TYPE#","L  '1' and "SEGCOL#" = '1' and "
           ENGTH","FIXEDSTORAGE","PRECISI SEGCOLLENGTH" = '10' and "OFFS
           ON#","SCALE","NULL$","DEFLENGT ET" = '0' and "NAME" = 'A' and
           H","DEFAULT$","INTCOL#","PROPE  "TYPE#" = '1' and "LENGTH" =
           RTY","CHARSETID","CHARSETFORM" '10' and "FIXEDSTORAGE" = '0'
           ,"SPARE1","SPARE2","SPARE3","S and "PRECISION#" IS NULL and "
           PARE4","SPARE5","SPARE6") valu SCALE" IS NULL and "NULL$" = '
           es ('74454','1','1','10','0',' 0' and "DEFLENGTH" IS NULL and
           A','1','10','0',NULL,NULL,'0',  "DEFAULT$" IS NULL and "INTCO

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           NULL,NULL,'1','0','852','1','0 L#" = '1' and "PROPERTY" = '0'
           ','0','10',NULL,NULL,NULL);     and "CHARSETID" = '852' and "
                                          CHARSETFORM" = '1' and "SPARE1
                                          " = '0' and "SPARE2" = '0' and
                                           "SPARE3" = '10' and "SPARE4"
                                          IS NULL and "SPARE5" IS NULL a
                                          nd "SPARE6" IS NULL and ROWID
                                          = 'AAAAACAABAAAUg/AA/';

UNKNOWN    create table t (a varchar2(10)
           );

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------

UNKNOWN    update "SYS"."SEG$" set "TYPE# update "SYS"."SEG$" set "TYPE#
           " = '5', "BLOCKS" = '8', "EXTE " = '3', "BLOCKS" = '8', "EXTE
           NTS" = '1', "INIEXTS" = '8', " NTS" = '1', "INIEXTS" = '8', "
           MINEXTS" = '1', "MAXEXTS" = '2 MINEXTS" = '1', "MAXEXTS" = '2
           147483645', "EXTSIZE" = '128', 147483645', "EXTSIZE" = '128',
            "EXTPCT" = '0', "USER#" = '0'  "EXTPCT" = '0', "USER#" = '0'
           , "LISTS" = '0', "GROUPS" = '0 , "LISTS" = '0', "GROUPS" = '0
           ', "BITMAPRANGES" = '214748364 ', "BITMAPRANGES" = '214748364
           5', "CACHEHINT" = '0', "SCANHI 5', "CACHEHINT" = '0', "SCANHI
           NT" = '0', "HWMINCR" = '74454' NT" = '0', "HWMINCR" = '74454'

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           , "SPARE1" = '4325377' where " , "SPARE1" = '4325377' where "
           FILE#" = '1' and "BLOCK#" = '8 FILE#" = '1' and "BLOCK#" = '8
           6912' and "TYPE#" = '3' and "T 6912' and "TYPE#" = '5' and "T
           S#" = '0' and "BLOCKS" = '8' a S#" = '0' and "BLOCKS" = '8' a
           nd "EXTENTS" = '1' and "INIEXT nd "EXTENTS" = '1' and "INIEXT
           S" = '8' and "MINEXTS" = '1' a S" = '8' and "MINEXTS" = '1' a
           nd "MAXEXTS" = '2147483645' an nd "MAXEXTS" = '2147483645' an
           d "EXTSIZE" = '128' and "EXTPC d "EXTSIZE" = '128' and "EXTPC
           T" = '0' and "USER#" = '0' and T" = '0' and "USER#" = '0' and
            "LISTS" = '0' and "GROUPS" =   "LISTS" = '0' and "GROUPS" =
           '0' and "BITMAPRANGES" = '2147 '0' and "BITMAPRANGES" = '2147

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
           483645' and "CACHEHINT" = '0'  483645' and "CACHEHINT" = '0'
           and "SCANHINT" = '0' and "HWMI and "SCANHINT" = '0' and "HWMI
           NCR" = '74454' and "SPARE1" =  NCR" = '74454' and "SPARE1" =
           '4325377' and "SPARE2" IS NULL '4325377' and "SPARE2" IS NULL
            and ROWID = 'AAAAAIAABAAADmPA  and ROWID = 'AAAAAIAABAAADmPA
           AL';                           AL';

UNKNOWN    commit;
UNKNOWN    set transaction read write;
UNKNOWN    insert into "SYS"."T"("A") val delete from "SYS"."T" where "A
           ues ('1');                     " = '1' and ROWID = 'AAASLWAAB

USERNAME   SQL_REDO                       SQL_UNDO
---------- ------------------------------ ------------------------------
                                          AAAVOBAAA';

UNKNOWN    commit;

31 rows selected.


7.关闭logmnr。

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

----------参考《数据库构架分析与实战攻略》


补充:

Oracle中创建跟踪客户端IP地址的触发器

若果要让v$logmnr_contents中的session_info记录客户端ip,但SESSION_INFO中我们并不能直接看到IP,
不过我们还是有办法的,因为这个SESSION_INFO里面的内容其实是日志从V$SESSION视图里提取的,我们可以
在生产数据库中创建一个追踪客户端IP地址的触发器:

create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
/

现在,我们就可以在V$SESSION视图的CLIENT_INFO列中看到新登录的客户端IP地址了。那么现在就可以在
session_info 中看客户端的ip了

select SID,SERIAL#,USERNAME, PROGRAM,client_info from v$session