LogMiner介绍
LogMiner功能
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