联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在哪些情况下,数据库不启动附加日志不能正常的被Logminer捕获到的,这里做了一个简单的测试,说明在不启用附加日志的情况下,很多操作不能被捕获,不仅仅是行迁移的数据记录.当然本实验仅供参考,因为在不同的数据库版本,不同的平台,甚至不同的操作都可能出现不同的结果.如果想要数据库日志通过Logminer获得较为完整的sql语句,强烈建议打开附加日志(当然会产生多一点日志,可能增加磁盘io的负担,凡事都有两面性,则其善而从之)
数据库版本
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
插入数据
--为了减少测试redo影响,切换归档日志
SQL> alter system switch logfile;
System altered.
SQL> show user;
USER is "CHF"
--当前scn
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949934814
--当前redo logfile
SQL> select member from v$logfile where group# in(
2 select group# from v$log where status='CURRENT');
MEMBER
------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo03.log
--创建测试表并插入数据
SQL> create table xifenfei(id number,name varchar2(4000));
Table created.
SQL> insert into xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into xifenfei values(2,'XIFENFEI');
1 row created.
SQL> insert into xifenfei values(3,'XiFenFei');
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949934864
--数据存储的datafile 和blocknum
SQL> select id,rowid,dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_num,
2 dbms_rowid.rowid_block_number(rowid) block_num from xifenfei;
ID ROWID FILE_NUM BLOCK_NUM
---------- ------------------ ---------- ----------
1 AAAMuvAAJAAAAmkAAA 9 2468
2 AAAMuvAAJAAAAmkAAB 9 2468
3 AAAMuvAAJAAAAmkAAC 9 2468
--dump数据块
SQL> alter system dump datafile 9 block 2468;
System altered.
--dump datablock内容
Block header dump: 0x024009a4
Object id on Block? Y
seg/obj: 0xcbaf csc: 0x0a.3ff09 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24009a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.019.0000010d 0x00800b85.0111.2f --U- 3 fsc 0x0000.0003ff0e
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xcf6c464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0cf6c464
bdba: 0x024009a4
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f6b
avsp=0x1f53
tosp=0x1f53
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f89
0x14:pri[1] offs=0x1f7a
0x16:pri[2] offs=0x1f6b
block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 8] 78 69 66 65 6e 66 65 69
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 8] 58 49 46 45 4e 46 45 49
tab 0, row 2, @0x1f6b
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 8] 58 69 46 65 6e 46 65 69
--可以清楚的看到这三条记录都存在一个数据块中,并未发生行迁移等情况
--dump redo log
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo03.log'
2 scn min 42949934814 scn max 42949934864;
System altered.
--dump redo logfile内容
CHANGE #9 TYP:0 CLS: 4 AFN:9 DBA:0x024009a3 OBJ:52143 SCN:0x000a.0003ff0e SEQ: 1 OP:13.28
Low HWM
Highwater:: 0x024009a9 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
lfdba: 0x024009a1 CHANGE #10 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ: 1 OP:11.2
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0008.019.0000010d uba: 0x00800b85.0111.2d
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 15
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] c1 02
col 1: [ 8] 78 69 66 65 6e 66 65 69
CHANGE #11 TYP:0 CLS:31 AFN:2 DBA:0x00800079 OBJ:4294967295 SCN:0x000a.0003fe8f SEQ: 1 OP:5.2
ktudh redo: slt: 0x0019 sqn: 0x0000010d flg: 0x0012 siz: 108 fbi: 0
uba: 0x00800b85.0111.2d pxid: 0x0000.000.00000000
CHANGE #12 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ: 2 OP:11.2
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800b85.0111.2e
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 15
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 8] 58 49 46 45 4e 46 45 49
CHANGE #13 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ: 3 OP:11.2
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800b85.0111.2f
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 15
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] c1 04
col 1: [ 8] 58 69 46 65 6e 46 65 69
--这里可以看到，只有redo的信息，没有太多undo信息(因为是插入数据)
--使用Logminer
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo03.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
TABLE_NAME SQL_REDO
-------------------------------- -------------------------------------------------------
XIFENFEI create table xifenfei(id number,name varchar2(4000));
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--这里可以明确的看到,Logminer没有找到任何关于这个表的dml操作,也就是说三条insert都没有被找到
说明:在redo中已经包含了insert操作的相关记录,但是因为没有启用附加日志,是的Logminer不能正常获得相关操作语句
简单更新操作
SQL> alter system switch logfile;
System altered.
SQL> conn chf/xifenfei
Connected.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949941538
SQL> select member from v$logfile where group# in(
2 select group# from v$log
3 where status='CURRENT');
MEMBER
---------------------------------------------------------------
/u01/oracle/oradata/XFF/redo01.log
SQL> update xifenfei set name='www.xifenfei.com' where id=1;
1 row updated.
SQL> update xifenfei set name='WWW.XIFENFEI.COM' WHERE ID=2;
1 row updated.
SQL> update xifenfei set name='www.orasos.com' where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949941552
SQL> alter system dump datafile 9 block 2468;
System altered.
--dump datablock
Block header dump: 0x024009a4
Object id on Block? Y
seg/obj: 0xcbaf csc: 0x0a.4192a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24009a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.019.0000010d 0x00800b85.0111.2f C--- 0 scn 0x000a.0003ff0e
0x02 0x0003.004.0000014e 0x0080002b.01a6.3b --U- 3 fsc 0x0000.0004192d
data_block_dump,data header at 0xdf83464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0df83464
bdba: 0x024009a4
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f28
avsp=0x1f3d
tosp=0x1f3d
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f54
0x14:pri[1] offs=0x1f3d
0x16:pri[2] offs=0x1f28
block_row_dump:
tab 0, row 0, @0x1f54
tl: 23 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f3d
tl: 23 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [16] 57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
tab 0, row 2, @0x1f28
tl: 21 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [14] 77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo01.log'
2 scn min 42949941538 scn max 42949941552;
System altered.
--dump redo log
REDO RECORD - Thread:1 RBA: 0x000013.00000002.0010 LEN: 0x0408 VLD: 0x0d
SCN: 0x000a.0004192d SUBSCN: 1 09/26/2012 23:31:27
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0003ff0e SEQ: 5 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0003.004.0000014e uba: 0x0080002b.01a6.39
Block cleanout record, scn: 0x000a.0004192a ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x000a.0003ff0e
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 251
ncol: 2 nnew: 1 size: 8
col 1: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
CHANGE #2 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000a.00041781 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0004 sqn: 0x0000014e flg: 0x0012 siz: 136 fbi: 0
uba: 0x0080002b.01a6.39 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ: 1 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080002b.01a6.3a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 12
ncol: 2 nnew: 1 size: 8
col 1: [16] 57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
CHANGE #4 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ: 2 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080002b.01a6.3b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 12
ncol: 2 nnew: 1 size: 6
col 1: [14] 77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
--OP:11.5 Update Row Piece
--包含了修改后的值(后镜像)
CHANGE #5 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000a.0004192d SEQ: 1 OP:5.4
ktucm redo: slt: 0x0004 sqn: 0x0000014e srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080002b.01a6.3b ext: 0 spc: 416 fbi: 0
--OP:5.4 Commit transaction (transaction table update)
CHANGE #6 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.00041780 SEQ: 2 OP:5.1
ktudb redo: siz: 136 spc: 750 flg: 0x0012 seq: 0x01a6 rec: 0x39
xid: 0x0003.004.0000014e
ktubl redo: slt: 4 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080002b.01a6.37
prev ctl max cmt scn: 0x000a.00040ff1 prev tx cmt scn: 0x000a.00041076
txn start scn: 0x0000.00000000 logon user: 59 prev brb: 8391493 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 251
ncol: 2 nnew: 1 size: -8
col 1: [ 8] 78 69 66 65 6e 66 65 69
CHANGE #7 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.0004192d SEQ: 1 OP:5.1
ktudb redo: siz: 96 spc: 612 flg: 0x0022 seq: 0x01a6 rec: 0x3a
xid: 0x0003.004.0000014e
ktubu redo: slt: 4 rci: 57 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080002b.01a6.39
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -8
col 1: [ 8] 58 49 46 45 4e 46 45 49
CHANGE #8 TYP:0 CLS:22 AFN:2 DBA:0x0080002b OBJ:4294967295 SCN:0x000a.0004192d SEQ: 2 OP:5.1
ktudb redo: siz: 96 spc: 514 flg: 0x0022 seq: 0x01a6 rec: 0x3b
xid: 0x0003.004.0000014e
ktubu redo: slt: 4 rci: 58 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080002b.01a6.3a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -6
col 1: [ 8] 58 69 46 65 6e 46 65 69
--OP:5.1 Undo block or undo segment header
--包含了前镜像(修改前的值,其实就是undo中记录)
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
no rows selected
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--Logminer无任何记录，证明没有被捕获到
说明:在redo中已经包含了insert操作的相关记录,但是因为没有启用附加日志,是的Logminer不能正常获得相关操作语句
行迁移情况
SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949943145
SQL> select member from v$logfile where group# in(
2 select group# from v$log
3 where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo03.log
--制造行迁移
SQL> update xifenfei set name=lpad('F',4000,'F') WHERE ID=1;
1 row updated.
SQL> update xifenfei set name=lpad('X',4000,'X') WHERE ID=2;
1 row updated.
SQL> update xifenfei set name=lpad('C',4000,'C') WHERE ID=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949943162
SQL> select id,rowid,dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_num,
2 dbms_rowid.rowid_block_number(rowid) block_num from xifenfei;
ID ROWID FILE_NUM BLOCK_NUM
---------- ------------------ ---------- ----------
1 AAAMuvAAJAAAAmkAAA 9 2468
2 AAAMuvAAJAAAAmkAAB 9 2468
3 AAAMuvAAJAAAAmkAAC 9 2468
SQL> alter system dump datafile 9 block 2472;
System altered.
--dump datablock
Block header dump: 0x024009a4
Object id on Block? Y
seg/obj: 0xcbaf csc: 0x0a.41f6e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24009a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02f.00000113 0x00800085.011b.41 --U- 3 fsc 0x000c.00041f78
0x02 0x0003.004.0000014e 0x0080002b.01a6.3b C--- 0 scn 0x000a.0004192d
data_block_dump,data header at 0xec3f464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0ec3f464
bdba: 0x024009a4
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x31
avsp=0x19
tosp=0x25
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0xfef
0x14:pri[1] offs=0x31
0x16:pri[2] offs=0xfda
block_row_dump:
tab 0, row 0, @0xfef
tl: 4009 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [4000]
46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
tab 0, row 1, @0x31
tl: 4009 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [4000]
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
tab 0, row 2, @0xfda
tl: 9 fb: --H----- lb: 0x1 cc: 0
nrid: 0x024009a8.0 <--发生行迁移，指向下一个迁移数据块
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2468 maxblk 2468
--找到下个数据块的block num
SQL> select to_number('9a8','xxxxx') from dual;
TO_NUMBER('9A8','XXXXX')
------------------------
2472
SQL> alter system dump datafile 9 block 2472;
System altered.
Block header dump: 0x024009a8
Object id on Block? Y
seg/obj: 0xcbaf csc: 0x0a.3ff09 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24009a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02f.00000113 0x00800085.011b.40 --U- 1 fsc 0x0000.00041f78
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
data_block_dump,data header at 0xec3f47c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0ec3f47c
bdba: 0x024009a8
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfd1
avsp=0xfbd
tosp=0xfbd
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xfd1
block_row_dump:
tab 0, row 0, @0xfd1
tl: 4015 fb: ----FL-- lb: 0x1 cc: 2
hrid: 0x024009a4.2 <--迁移对应的起点数据块
col 0: [ 2] c1 04
col 1: [4000]
43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2472 maxblk 2472
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo03.log'
2 scn min 42949943145 scn max 42949943162;
System altered.
--dump redo log
REDO RECORD - Thread:1 RBA: 0x000015.00000002.0010 LEN: 0x1180 VLD: 0x0d
SCN: 0x000a.00041f6e SUBSCN: 1 09/27/2012 00:36:34
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.0004192d SEQ: 4 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0008.02f.00000113 uba: 0x00800085.011b.3d
Block cleanout record, scn: 0x000a.00041f6e ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x000a.0004192d
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 174
ncol: 2 nnew: 1 size: 3986
col 1: [4000]
46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
CHANGE #2 TYP:0 CLS:31 AFN:2 DBA:0x00800079 OBJ:4294967295 SCN:0x000a.00041ebd SEQ: 1 OP:5.2
ktudh redo: slt: 0x002f sqn: 0x00000113 flg: 0x0012 siz: 168 fbi: 0
uba: 0x00800085.011b.3d pxid: 0x0000.000.00000000
--OP:5.2 Update rollback segment header
CHANGE #3 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041ebc SEQ: 1 OP:5.1
ktudb redo: siz: 168 spc: 862 flg: 0x0012 seq: 0x011b rec: 0x3d
xid: 0x0008.02f.00000113
ktubl redo: slt: 47 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800085.011b.3c
prev ctl max cmt scn: 0x000a.000416cd prev tx cmt scn: 0x000a.000416d0
txn start scn: 0x0000.00000000 logon user: 59 prev brb: 8388734 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0008.019.0000010d uba: 0x00800b85.0111.2f
flg: C--- lkc: 0 scn: 0x000a.0003ff0e
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 174
ncol: 2 nnew: 1 size: -3986
col 1: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
REDO RECORD - Thread:1 RBA: 0x000015.0000000c.0010 LEN: 0x10d4 VLD: 0x05
SCN: 0x000a.00041f71 SUBSCN: 1 09/27/2012 00:36:40
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f6e SEQ: 1 OP:5.1
ktudb redo: siz: 104 spc: 692 flg: 0x0022 seq: 0x011b rec: 0x3e
xid: 0x0008.02f.00000113
ktubu redo: slt: 47 rci: 61 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800085.011b.3d
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: -3986
col 1: [16] 57 57 57 2e 58 49 46 45 4e 46 45 49 2e 43 4f 4d
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f6e SEQ: 1 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800085.011b.3e
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 1 ckix: 12
ncol: 2 nnew: 1 size: 3986
col 1: [4000]
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
REDO RECORD - Thread:1 RBA: 0x000015.00000015.010c LEN: 0x1098 VLD: 0x01
SCN: 0x000a.00041f74 SUBSCN: 2 09/27/2012 00:36:46
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f74 SEQ: 1 OP:5.1
ktudb redo: siz: 60 spc: 516 flg: 0x0022 seq: 0x011b rec: 0x40
xid: 0x0008.02f.00000113
ktubu redo: slt: 47 rci: 63 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a8 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a8 OBJ:52143 SCN:0x000a.0003ff0e SEQ: 1 OP:11.2
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0008.02f.00000113 uba: 0x00800085.011b.40
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a8 hdba: 0x024009a3
itli: 1 ispac: 24 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4015
fb: ----FL-- lb: 0x1 cc: 2 <--这里没有H表明是发生了行迁移过来的记录(对应的flag可以转化为0x0c)
hrid: 0x024009a4.2
null: --
col 0: [ 2] c1 04
col 1: [4000]
43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
REDO RECORD - Thread:1 RBA: 0x000015.0000001e.0078 LEN: 0x0124 VLD: 0x01
SCN: 0x000a.00041f74 SUBSCN: 3 09/27/2012 00:36:46
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800085 OBJ:4294967295 SCN:0x000a.00041f74 SEQ: 2 OP:5.1
ktudb redo: siz: 124 spc: 454 flg: 0x0022 seq: 0x011b rec: 0x41
xid: 0x0008.02f.00000113
ktubu redo: slt: 47 rci: 64 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800085.011b.3f
KDO Op code: ORP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 21
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] c1 04
col 1: [14] 77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f74 SEQ: 1 OP:11.6
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800085.011b.41
KDO Op code: ORP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 9
fb: --H----- lb: 0x1 cc: 0 <--这里可以看到对应的块只有header信息无L,也就是发生了行迁移
nrid: 0x024009a8.0 <--通block dump说明
null:
--OP:11.6 Overwrite Row Piece
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo03.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
SQL> col sql_redo for a80
SQL> col TABLE_NAME for a15
SQL> set lines 134
SQL> select TABLE_NAME,sql_redo from v$Logminer_contents where scn>=42949943145 and scn<=42949943162;
TABLE_NAME SQL_REDO
--------------- --------------------------------------------------------------------------------
XIFENFEI update "CHF"."XIFENFEI" set "NAME" = 'XXXX……XXXXX' where "NAME" = 'WWW.XIFENFEI.COM'
and ROWID = 'AAAMuvAAJAAAAmkAAB';
XIFENFEI Unsupported
XIFENFEI Unsupported
XIFENFEI update "CHF"."XIFENFEI" set "ID" = NULL, "NAME" = NULL where "ID" = '3' and "NAM
E" = 'www.orasos.com' and ROWID = 'AAAMuvAAJAAAAmkAAC';
commit;
7 rows selected.
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--获得了第二条记录(第一条没有任何记录,第三条因为行迁移,所以出现了update更新相关列为null,从而没有被Logminer正在的捕获)
说明：1)在发生行迁移之时,Logminer不能获得正常的sql语句,而是直接提示Unsupported;2)不发生行迁移也不一定能够获得update语句
删除操作
SQL> conn chf/xifenfei
Connected.
SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949953508
SQL> select member from v$logfile where group# in(
2 select group# from v$log
3 where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo01.log
SQL> delete from xifenfei where id=1;
1 row deleted.
SQL> delete from xifenfei where id=2;
1 row deleted.
SQL> delete from xifenfei where id=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949953524
--原始数据所在block
SQL> alter system dump datafile 9 block 2468;
System altered.
--发生行迁移的block
SQL> alter system dump datafile 9 block 2472;
System altered.
--dump block 内容
Block header dump: 0x024009a4
Object id on Block? Y
seg/obj: 0xcbaf csc: 0x0a.447e9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24009a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02f.00000113 0x00800085.011b.41 C--- 0 scn 0x000a.00041f78
0x02 0x0004.014.000000da 0x00800398.00bf.02 --U- 3 fsc 0x1f55.000447f2
data_block_dump,data header at 0xdcb9464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0dcb9464
bdba: 0x024009a4
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x31
avsp=0x25
tosp=0x1f80
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0xfef
0x14:pri[1] offs=0x31
0x16:pri[2] offs=0xfda
block_row_dump:
tab 0, row 0, @0xfef
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x31
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 2, @0xfda
tl: 2 fb: --HD---- lb: 0x2
Block header dump: 0x024009a8
Object id on Block? Y
seg/obj: 0xcbaf csc: 0x0a.447ef itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24009a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02f.00000113 0x00800085.011b.40 C--- 0 scn 0x000a.00041f78
0x02 0x0004.014.000000da 0x00800399.00bf.01 --U- 1 fsc 0x0fad.000447f2
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
data_block_dump,data header at 0xdcb947c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0dcb947c
bdba: 0x024009a8
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xfd1
avsp=0xfbd
tosp=0x1f6c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xfd1
block_row_dump:
tab 0, row 0, @0xfd1
tl: 2 fb: ---DFL-- lb: 0x2
end_of_block_dump
--通过数据块dump证明，记录确实已经被删除
--dump redo logfile
SQL> alter system dump logfile '/u01/oracle/oradata/XFF/redo01.log'
2 scn min 42949953508 scn max 42949953524;
System altered.
--dump redolog
REDO RECORD - Thread:1 RBA: 0x000016.0000000b.0010 LEN: 0x1170 VLD: 0x0d
SCN: 0x000a.000447e9 SUBSCN: 1 09/27/2012 20:03:36
CHANGE #1 TYP:2 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.00041f78 SEQ: 1 OP:11.3
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0004.014.000000da uba: 0x00800397.00bf.06
Block cleanout record, scn: 0x000a.000447e9 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x000a.00041f78
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
--OP:11.3 Drop Row Piece
CHANGE #2 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447ae SEQ: 1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x000000da flg: 0x0012 siz: 4172 fbi: 0
uba: 0x00800397.00bf.06 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:24 AFN:2 DBA:0x00800397 OBJ:4294967295 SCN:0x000a.000447ad SEQ: 1 OP:5.1
ktudb redo: siz: 4172 spc: 5804 flg: 0x0012 seq: 0x00bf rec: 0x06
xid: 0x0004.014.000000da
ktubl redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800397.00bf.05
prev ctl max cmt scn: 0x000a.00043852 prev tx cmt scn: 0x000a.00043862
txn start scn: 0x0000.00000000 logon user: 59 prev brb: 8389522 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0003.004.0000014e uba: 0x0080002b.01a6.3b
flg: C--- lkc: 0 scn: 0x000a.0004192d
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4009
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 02
col 1: [4000]
46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46 46
…………
REDO RECORD - Thread:1 RBA: 0x000016.00000014.0010 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447e9 SUBSCN: 1 09/27/2012 20:03:36
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.00041f74 SEQ: 1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 3 newstate: 3
REDO RECORD - Thread:1 RBA: 0x000016.00000015.0010 LEN: 0x1100 VLD: 0x05
SCN: 0x000a.000447ed SUBSCN: 1 09/27/2012 20:03:42
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447e9 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x000a siz: 4108 fbi: 80
uba: 0x00800398.00bf.01 pxid: 0x0000.000.00000000
CHANGE #2 TYP:1 CLS:24 AFN:2 DBA:0x00800398 OBJ:4294967295 SCN:0x000a.000447ec SEQ: 1 OP:5.1
ktudb redo: siz: 4108 spc: 1630 flg: 0x000a seq: 0x00bf rec: 0x01
xid: 0x0004.014.000000da
ktubu redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00800397
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800397.00bf.06
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 4009
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [4000]
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
…………
CHANGE #3 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.000447e9 SEQ: 1 OP:11.3
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800398.00bf.01
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
REDO RECORD - Thread:1 RBA: 0x000016.0000001d.0190 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447ed SUBSCN: 1 09/27/2012 20:03:42
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.000447e9 SEQ: 1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 3 newstate: 5
REDO RECORD - Thread:1 RBA: 0x000016.0000001e.0010 LEN: 0x0118 VLD: 0x05
SCN: 0x000a.000447ee SUBSCN: 1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS:24 AFN:2 DBA:0x00800398 OBJ:4294967295 SCN:0x000a.000447ed SEQ: 1 OP:5.1
ktudb redo: siz: 96 spc: 4040 flg: 0x0022 seq: 0x00bf rec: 0x02
xid: 0x0004.014.000000da
ktubu redo: slt: 20 rci: 1 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800398.00bf.01
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) size/delt: 9
fb: --H----- lb: 0x0 cc: 0
nrid: 0x024009a8.0
null:
CHANGE #2 TYP:0 CLS: 1 AFN:9 DBA:0x024009a4 OBJ:52143 SCN:0x000a.000447ed SEQ: 1 OP:11.3
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800398.00bf.02
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a4 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2)
REDO RECORD - Thread:1 RBA: 0x000016.0000001e.0128 LEN: 0x10fc VLD: 0x01
SCN: 0x000a.000447f0 SUBSCN: 1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000a.000447ed SEQ: 1 OP:5.2
ktudh redo: slt: 0x0014 sqn: 0x00000000 flg: 0x000a siz: 4100 fbi: 84
uba: 0x00800399.00bf.01 pxid: 0x0000.000.00000000
CHANGE #2 TYP:1 CLS:24 AFN:2 DBA:0x00800399 OBJ:4294967295 SCN:0x000a.000447ef SEQ: 1 OP:5.1
ktudb redo: siz: 4100 spc: 3942 flg: 0x000a seq: 0x00bf rec: 0x01
xid: 0x0004.014.000000da
ktubu redo: slt: 20 rci: 0 opc: 11.1 objn: 52143 objd: 52143 tsn: 9
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00800398
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a8 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 4015
fb: ----FL-- lb: 0x0 cc: 2
hrid: 0x024009a4.2
null: --
col 0: [ 2] c1 04
col 1: [4000]
43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
…………
CHANGE #3 TYP:2 CLS: 1 AFN:9 DBA:0x024009a8 OBJ:52143 SCN:0x000a.00041f78 SEQ: 1 OP:11.3
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0004.014.000000da uba: 0x00800399.00bf.01
Block cleanout record, scn: 0x000a.000447ef ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x000a.00041f78
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x024009a8 hdba: 0x024009a3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
REDO RECORD - Thread:1 RBA: 0x000016.00000027.00b4 LEN: 0x0044 VLD: 0x01
SCN: 0x000a.000447f0 SUBSCN: 1 09/27/2012 20:03:45
CHANGE #1 TYP:0 CLS: 8 AFN:9 DBA:0x024009a1 OBJ:52143 SCN:0x000a.000447ed SEQ: 1 OP:13.22
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 7 newstate: 5
--可以看到redo部分没有太多记录,而undo部分的信息比较全(因为是delete操作)
--Logminer操作
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
SQL_REDO
--------------------------------------------------------------------------------
delete from "CHF"."XIFENFEI" where "ID" = '2' and "NAME" = 'XXXXXXXXXXXXXXXXXXXX
…………
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' and ROWID = 'AAAMu
vAAJAAAAmkAAB';
Unsupported
Unsupported
SQL> EXEC dbms_logmnr.END_Logminer;
PL/SQL procedure successfully completed.
--也只是捕获了第二条记录,第一条无任何信息,第三条因为行迁移所以提示Unsupported
说明:我们可以看到对于delete操作,有部分不能被Logminer正常捕获,行迁移的直接提示Unsupported
启用数据库附加日志
SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;
Table dropped.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949959788
SQL> select member from v$logfile where group# in(
2 select group# from v$log where status='CURRENT');
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/XFF/redo02.log
SQL> create table xifenfei(id number,name varchar2(4000));
Table created.
SQL> insert into xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into xifenfei values(2,'XIFENFEI');
1 row created.
SQL> insert into xifenfei values(3,'XiFenFei');
1 row created.
SQL> commit;
Commit complete.
SQL> update xifenfei set name='www.xifenfei.com' where id=1;
1 row updated.
SQL> update xifenfei set name='WWW.XIFENFEI.COM' WHERE ID=2;
1 row updated.
SQL> update xifenfei set name='www.orasos.com' where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update xifenfei set name=lpad('F',4000,'F') WHERE ID=1;
1 row updated.
SQL> update xifenfei set name=lpad('X',4000,'X') WHERE ID=2;
1 row updated.
SQL> update xifenfei set name=lpad('C',4000,'C') WHERE ID=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from xifenfei where id=1;
1 row deleted.
SQL> delete from xifenfei where id=2;
1 row deleted.
SQL> delete from xifenfei where id=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select to_char(dbms_flashback.get_system_change_number,'9999999999999') scn from dual;
SCN
--------------
42949959845
SQL> conn / as sysdba
Connected.
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/XFF/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_Logminer(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select sql_redo from v$Logminer_contents where SEG_NAME='XIFENFEI';
SQL_REDO
--------------------------------------------------------------------------------
create table xifenfei(id number,name varchar2(4000));
insert into "CHF"."XIFENFEI"("ID","NAME") values ('1','xifenfei');
insert into "CHF"."XIFENFEI"("ID","NAME") values ('2','XIFENFEI');
insert into "CHF"."XIFENFEI"("ID","NAME") values ('3','XiFenFei');
update "CHF"."XIFENFEI" set "NAME" = 'www.xifenfei.com' where "NAME" = 'xifenfei
' and ROWID = 'AAAMwEAAJAAAAmkAAA';
update "CHF"."XIFENFEI" set "NAME" = 'WWW.XIFENFEI.COM' where "NAME" = 'XIFENFEI
' and ROWID = 'AAAMwEAAJAAAAmkAAB';
update "CHF"."XIFENFEI" set "NAME" = 'www.orasos.com' where "NAME" = 'XiFenFei'
and ROWID = 'AAAMwEAAJAAAAmkAAC';
update "CHF"."XIFENFEI" set "NAME" = 'FFFFFFF…………FFFF' where
"NAME" = 'www.xifenfei.com' and ROWID = 'AAAMwEAAJAAAAmkAAA';
update "CHF"."XIFENFEI" set "NAME" = 'XXXXXXXXX…………XX' where
"NAME" = 'WWW.XIFENFEI.COM' and ROWID = 'AAAMwEAAJAAAAmkAAB';
update "CHF"."XIFENFEI" set "ID" = '3', "NAME" = 'CCCCC…………CCCCCCCCC'
where "ID" = '3' and "NAME"= 'www.orasos.com' and ROWID = 'AAAMwEAAJAAAAmkAAC';
delete from "CHF"."XIFENFEI" where "ID" = '1' and "NAME" = 'FFFFFF…………FF'
and ROWID = 'AAAMwEAAJAAAAmkAAA';
delete from "CHF"."XIFENFEI" where "ID" = '2' and "NAME" = 'XX…………XXX'
and ROWID = 'AAAMwEAAJAAAAmkAAB';
delete from "CHF"."XIFENFEI" where "ID" = '3' and "NAME" = 'CCCCCCCC…………CCC'
and ROWID = 'AAAMwEAAJAAAAmkAAC';
19 rows selected.
测试证明,启动附加日志后,数据库的相关操作都能够捕获,包括行迁移
加我QQ
Vae,
没有太多好的方法
如果源数据库未打开附加日志，在这种情况下logminer无法正常挖掘，有什么方法查看用户执行的具体操作吗？