目标：理顺事务的整个流程

一个事务的整个流程,datafile,undo,redo的内容

1 数据准备

2 发起一个更改事务

3 跟踪undo 找到file_id, blockid

4 Dump回滚段头的信息及块信息

5 Dump 回滚段 redo record 数据块

6 Dump数据文件

7 提交事务commit

8 跟踪redo entry 所在的文件号与数据块号

9 结论

10 未解决的问题

1 数据准备

user: test

data tablespace: TS_TEST

datafile:

SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='TS_TEST';

FILE_NAME TABLESPACE_NAME

-------------------------------------------------------------------------------- ------------------------------

/opt/oracle/oradata/demo/ts_test01.dbf TS_TEST

/opt/oracle/oradata/demo/ts_test02.dbf TS_TEST

table: create table t2 (id number, name varchar2(20));

data:

SQL> insert into t2 values(1,'gdut');

1 row inserted

SQL> commit;

Commit complete

undo tablespace:UNDOTBS1

/opt/oracle/oradata/demo/undotbs01.dbf

当前使用的redo:

SQL> select a.group#, a.status, b.member from v$log a, v$logfile b where a.group# = b.group#;

GROUP# STATUS MEMBER

---------- ---------------- --------------------------------------------------------------------------------

1 INACTIVE /opt/oracle/oradata/demo/redo01.log

2 CURRENT /opt/oracle/oradata/demo/redo02.log

3 ACTIVE /opt/oracle/oradata/demo/redo03.log

2 发起一个更改事务

事务开始前先记下当前SCN号，给后面生成redo log dump时使用

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)

------------------------------------

461359

update t2 set name='mouse' where id=1;

这个事务对应的undo块是哪个呢?

3 跟踪undo 找到file_id, blockid

SQL> select xidusn, xidslot, xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC

---------- ---------- ---------- ---------- ---------- ---------- ----------

6 27 47 2 95 23 36

XIDUSN：回滚段Number

XIDSLOT：事务槽号

XIDSQN：事务序列号

UBAFIL：UBA所在文件号

UBABLK：UBA所在块号

UBASQN：UBA序列号

UBAREC：UBA在Block中的位置

SQL> select file_id, file_name,tablespace_name from dba_data_files where tablespace_name='UNDOTBS1';

FILE_ID FILE_NAME TABLESPACE_NAME

---------- -------------------------------------------------------------------------------- ------------------------------

2 /opt/oracle/oradata/demo/undotbs01.dbf UNDOTBS1

FILE_ID = UBAFIL = 2

XIDUSN = SEGMENT_ID：回滚段Number=6

(查询当前事务使用的rollback segment ?)

SQL> select file_id,block_id,SEGMENT_ID,SEGMENT_NAME from dba_rollback_segs where SEGMENT_ID=6;

FILE_ID BLOCK_ID SEGMENT_ID SEGMENT_NAME

---------- ---------- ---------- ------------------------------

2 89 6 _SYSSMU6$

block_id: ID number of the block containing the segment header

这里的block_id = 89 与 UBABLK：UBA所在块号 =95 不一样

block_id 一个是segment header 所在的block id

UBABLK: undo block address block number 这个是生成的undo record所在的数据块地址

4 Dump回滚段头的信息及块信息 (怎么dump具体哪个块呢?)

alter system dump undo header '_SYSSMU6$';

index state cflags wrap# uel scn dba parent-xid nub stmt_num

0x18 9 0x00 0x002f 0x0011 0x0000.0006aca8 0x00800060 0x0000.000.00000000 0x00000001 0x00000000

0x19 9 0x00 0x002f 0x0012 0x0000.0006b11c 0x00800060 0x0000.000.00000000 0x00000001 0x00000000

0x1a 9 0x00 0x002f 0x001c 0x0000.0006acb2 0x00800060 0x0000.000.00000000 0x00000001 0x00000000

0x1b 10 0x80 0x002f 0x0000 0x0000.00070a55 0x0080005f 0x0000.000.00000000 0x00000001 0x00000000

0x1c 9 0x00 0x002f 0x000f 0x0000.0006acb7 0x00800060 0x0000.000.00000000 0x00000001 0x00000000

0x1d 9 0x00 0x002f 0x0026 0x0000.0006b164 0x0080005f 0x0000.000.00000000 0x00000001 0x00000000

0x1e 9 0x00 0x002f 0x0001 0x0000.0006da8e 0x0080005f 0x0000.000.00000000 0x00000001 0x00000000

0x1f 9 0x00 0x002f 0x001d 0x0000.0006b162 0x0080005f 0x0000.000.00000000 0x00000001 0x00000000

0x20 9 0x00 0x002f 0x0022 0x0000.0006b15e 0x0080005f 0x0000.000.00000000 0x00000001 0x00000000

注意：state：10为active, 9为inactive

dba：为回滚块的地址，根据这个地址，我们可以计算出file#及block#

(怎么样计算的 --- 前10位文件号，22位数据块号)

0x0080005f

0000 0000 10 00 0000 0000 0101 1111

得出文件号为2

数据块号95

当然这个值可以不用计算，v$transaction的ubafil，ubablk分别为file#, block#。

5 dump 回滚段 redo record 数据块

alter system dump datafile 2 block 95;

SQL> select object_id from dba_objects where object_name='T2';

OBJECT_ID

----------

6038

在dump中根据object_id找到对应的段

*-----------------------------

* Rec #0x24 slt: 0x1b objn: 6038(0x00001796) objd: 6038 tblspc: 3(0x00000003)

* Layer: 11 (Row) opc: 1 rci 0x00

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000

*-----------------------------

uba: 0x0080005f.0017.23 ctl max scn: 0x0000.00064bc0 prv tx scn: 0x0000.00064ee3

KDO undo record:

KTB Redo

op: 0x03 ver: 0x01

op: Z

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x0100381a hdba: 0x01003819

itli: 2 ispac: 0 maxfr: 4863

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0

ncol: 2 nnew: 1 size: -1

col 1: [ 4] 67 64 75 74 (旧值)

End dump data blocks tsn: 1 file#: 2 minblk 95 maxblk 95

SQL> select chr(to_number(67,'xx')),chr(to_number(64,'xx')),chr(to_number(75,'xx')), chr(to_number(74,'xx')) from dual;

CH CH CH CH

-- -- -- --

g d u t

这与v$transaction中查询出来的相符合

SQL> select xidusn, xidslot, xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC

---------- ---------- ---------- ---------- ---------- ---------- ----------

6 27 47 2 95 23 36 = 0x24(16进制)

6 Dump数据文件

数据文件有没有内容，决定于DBWr进程有没有将data buffer cache写出

找到表所有的数据文件与数据块号

SQL> select file_id, block_id startblock, blocks, block_id+blocks-1 endblock from dba_extents where segment_name='T2';

FILE_ID STARTBLOCK BLOCKS ENDBLOCK

---------- ---------- ---------- ----------

4 14361 8 14368

Alter system dump datafile 4 block min 14361 block max 14368;

(dump数据文件整个Segment,是否可以只dump一个block? )

Start dump data blocks tsn: 3 file#: 4 minblk 14361 maxblk 14368

buffer tsn: 3 rdba: 0x01003819 (4/14361)

scn: 0x0000.00070a2c seq: 0x01 flg: 0x00 tail: 0x0a2c1001

frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x0100381b ext#: 0 blk#: 1 ext size: 7

#blocks in seg. hdr's freelists: 1

#blocks below: 1

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 1 obj#: 6038 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x0100381a length: 7

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1

SEG LST:: flg: USED lhd: 0x0100381a ltl: 0x0100381a

buffer tsn: 3 rdba: 0x0100381a (4/14362)

scn: 0x0000.00070a55 seq: 0x01 flg: 0x00 tail: 0x0a550601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x0100381a

Object id on Block? Y

seg/obj: 0x1796 csc: 0x00.70a55 itc: 2 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0005.00b.00000029 0x0080004c.0227.2c C--- 0 scn 0x0000.00070a2f

0x02 0x0006.01b.0000002f 0x0080005f.0017.24 ---- 1 fsc 0x0000.00000000

data_block_dump,data header at 0x3fb985c

===============

tsiz: 0x1fa0

hsiz: 0x14

pbl: 0x03fb985c

bdba: 0x0100381a

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f89

avsp=0x1f80

tosp=0x1f80

0xe:pti[0] nrow=1 offs=0

0x12:pri[0] offs=0x1f89

block_row_dump:

tab 0, row 0, @0x1f89

tl: 12 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 2] c1 02

col 1: [ 5] 6d 6f 75 73 65

end_of_block_dump

############################################################

Itl：记录了该数据块上发生过的transaction。

Xid：xidusn.xidslot.xidsqn

Uba：uba.ubasqn.ubarec

Xid和Uba的内容和V$transactio中的内容一致。

#########################################################

SQL> select

2 chr(to_number('6d','xx')),

3 chr(to_number('6f','xx')),

4 chr(to_number(75,'xx')),

5 chr(to_number(73,'xx')),

6 chr(to_number(65,'xx')) from dual;

CH CH CH CH CH

-- -- -- -- --

m o u s e

说明数据文件中保存的是新值，即说明了DBWRn 进程写数据文件与事务有没有Commit没有直接关系。

7 提交事务commit

SQL session 1 > commit;

8 跟踪redo entry 所在的文件号与数据块号 (这里不清楚如何做)

SQL> select a.group#, a.status, b.member from v$log a, v$logfile b where a.group# = b.group#;

GROUP# STATUS MEMBER

---------- ---------------- --------------------------------------------------------------------------------

1 INACTIVE /opt/oracle/oradata/demo/redo01.log

2 CURRENT /opt/oracle/oradata/demo/redo02.log

3 ACTIVE /opt/oracle/oradata/demo/redo03.log

当前活动为2 号文件

依据SCN号来dump redo log file

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)

------------------------------------

461731

找到事务提交后的SCN号

Dump logfile

alter system dump logfile '/opt/oracle/oradata/demo/redo02.log' scn min 461359 scn max 461731;

或直接dump整个logfile

alter system dump logfile '/opt/oracle/oradata/demo/redo02.log'

DUMP OF REDO FROM FILE '/opt/oracle/oradata/demo/redo02.log'

Opcodes *.*

DBA's: (file # 0, block # 0) thru (file # 65534, block # 4194303)

RBA's: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff

SCN's scn: 0x0000.00070a2f thru scn: 0x0000.00070ba3

Times: creation thru eternity

FILE HEADER:

Software vsn=153092096=0x9200000, Compatibility Vsn=153092096=0x9200000

Db ID=3448616406=0xcd8db5d6, Db Name='DEMO'

Activation ID=3448610774=0xcd8d9fd6

Control Seq=222=0xde, File size=409600=0x64000

File Number=2, Blksiz=512, File Type=2 LOG

descrip:"Thread 0001, Seq# 0000000051, SCN 0x000000070a37-0xffffffffffff"

thread: 1 nab: 0xffffffff seq: 0x00000033 hws: 0x1 eot: 1 dis: 0

reset logs count: 0x29fe8516 scn: 0x0000.00000001

Low scn: 0x0000.00070a37 12/08/2009 14:54:21

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

Enabled scn: 0x0000.00000001 12/02/2009 11:14:39

Thread closed scn: 0x0000.00070a37 12/08/2009 14:54:21

Log format vsn: 0x8000000 Disk cksum: 0x32ef Calc cksum: 0x32ef

Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00

Most recent redo scn: 0x0000.00000000

Largest LWN: 0 blocks

End-of-redo stream : No

Unprotected mode

Miscellaneous flags: 0x0

REDO RECORD - Thread:1 RBA: 0x000033.00000002.0010 LEN: 0x00b8 VLD: 0x02

SCN: 0x0000.00070a37 SUBSCN: 1 12/08/2009 14:54:21

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1

Block Written - afn: 2 rdba: 0x00800019(2,25)

scn: 0x0000.00070a08 seq: 0x01 flg:0x04

Block Written - afn: 2 rdba: 0x00800030(2,48)

scn: 0x0000.00070a1f seq: 0x01 flg:0x04

Block Written - afn: 2 rdba: 0x00800089(2,137)

scn: 0x0000.0007090d seq: 0x01 flg:0x04

Block Written - afn: 2 rdba: 0x00800039(2,57)

scn: 0x0000.000709d6 seq: 0x01 flg:0x04

Block Written - afn: 2 rdba: 0x0080003f(2,63)

scn: 0x0000.000709d5 seq: 0x01 flg:0x04

Block Written - afn: 2 rdba: 0x00800029(2,41)

scn: 0x0000.00070a20 seq: 0x01 flg:0x04

Block Written - afn: 2 rdba: 0x00800090(2,144)

scn: 0x0000.0007090c seq: 0x01 flg:0x04

Block Written - afn: 2 rdba: 0x0080001c(2,28)

scn: 0x0000.00070a05 seq: 0x04 flg:0x04

Block Written - afn: 1 rdba: 0x00400e8d(1,3725)

scn: 0x0000.000709d6 seq: 0x01 flg:0x06

REDO RECORD - Thread:1 RBA: 0x000033.00000003.0010 LEN: 0x019c VLD: 0x01

SCN: 0x0000.00070a55 SUBSCN: 1 12/08/2009 14:55:50

CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.000704e9 SEQ: 1 OP:5.2

ktudh redo: slt: 0x001b sqn: 0x0000002f flg: 0x0012 siz: 108 fbi: 0

uba: 0x0080005f.0017.24 pxid: 0x0000.000.00000000

CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x0080005f SCN:0x0000.000704e4 SEQ: 1 OP:5.1

ktudb redo: siz: 108 spc: 4472 flg: 0x0012 seq: 0x0017 rec: 0x24

xid: 0x0006.01b.0000002f

ktubl redo: slt: 27 rci: 0 opc: 11.1 objn: 6038 objd: 6038 tsn: 3

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

0x00000000 prev ctl uba: 0x0080005f.0017.23

prev ctl max cmt scn: 0x0000.00064bc0 prev tx cmt scn: 0x0000.00064ee3

KDO undo record:

KTB Redo

op: 0x03 ver: 0x01

op: Z

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x0100381a hdba: 0x01003819

itli: 2 ispac: 0 maxfr: 4863

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0

ncol: 2 nnew: 1 size: -1

col 1: [ 4] 67 64 75 74

CHANGE #3 TYP:2 CLS: 1 AFN:4 DBA:0x0100381a SCN:0x0000.00070a2f SEQ: 1 OP:11.5

KTB Redo

op: 0x11 ver: 0x01

op: F xid: 0x0006.01b.0000002f uba: 0x0080005f.0017.24

Block cleanout record, scn: 0x0000.00070a55 ver: 0x01 opt: 0x02, entries follow...

itli: 1 flg: 2 scn: 0x0000.00070a2f

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x0100381a hdba: 0x01003819

itli: 2 ispac: 0 maxfr: 4863

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0

ncol: 2 nnew: 1 size: 1

col 1: [ 5] 6d 6f 75 73 65

CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20

session number = 11

serial number = 2274

transaction name =

REDO RECORD - Thread:1 RBA: 0x000033.00000004.0010 LEN: 0x0208 VLD: 0x01

SCN: 0x0000.00070a80 SUBSCN: 1 12/08/2009 14:57:58

CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 SCN:0x0000.00070a29 SEQ: 1 OP:5.2

ktudh redo: slt: 0x0026 sqn: 0x00000038 flg: 0x0412 siz: 108 fbi: 0

uba: 0x00800072.0016.0c pxid: 0x0000.000.00000000

CHANGE #2 TYP:0 CLS:30 AFN:2 DBA:0x00800072 SCN:0x0000.00070a25 SEQ: 2 OP:5.1

ktudb redo: siz: 108 spc: 6988 flg: 0x0012 seq: 0x0016 rec: 0x0c

xid: 0x0007.026.00000038

ktubl redo: slt: 38 rci: 0 opc: 11.1 objn: 516 objd: 514 tsn: 0

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

0x00000000 prev ctl uba: 0x00800072.0016.0a

prev ctl max cmt scn: 0x0000.00065e98 prev tx cmt scn: 0x0000.000661bb

KDO undo record:

KTB Redo

op: 0x04 ver: 0x01

op: L itl: xid: 0x0004.02f.0000002e uba: 0x0080003f.0015.27

flg: C--- lkc: 0 scn: 0x0000.000709d6

KDO Op code: DRP row dependencies Disabled

xtype: XA bdba: 0x00400e8d hdba: 0x00400e89

itli: 2 ispac: 0 maxfr: 4863

tabn: 1 slot: 35(0x23)

CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00400e8d SCN:0x0000.000709d6 SEQ: 1 OP:11.2

KTB Redo

op: 0x11 ver: 0x01

op: F xid: 0x0007.026.00000038 uba: 0x00800072.0016.0c

Block cleanout record, scn: 0x0000.00070a80 ver: 0x01 opt: 0x02, entries follow...

itli: 2 flg: 2 scn: 0x0000.000709d6

KDO Op code: IRP row dependencies Disabled

xtype: XA bdba: 0x00400e8d hdba: 0x00400e89

itli: 2 ispac: 0 maxfr: 4863

tabn: 1 slot: 35(0x23) size/delt: 26

fb: -CH-FL-- lb: 0x2 cc: 4 cki: 0

null: ----

col 0: [ 6] c5 0d 3d 1a 37 4d

col 1: [ 7] 78 6d 0c 08 0f 3a 3b

col 2: [ 1] 80

col 3: [ 4] c3 2f 0f 29

CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.19

session number = 5

serial number = 1

current username = SYS

login username =

client info =

OS username = oracle

Machine name = test02

OS terminal = UNKNOWN

OS process id = 18123

OS program name = oracle@test02 (SMON)

transaction name =

Recursive transaction

REDO RECORD - Thread:1 RBA: 0x000033.00000005.0028 LEN: 0x0054 VLD: 0x01

SCN: 0x0000.00070a81 SUBSCN: 1 12/08/2009 14:57:58

CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 SCN:0x0000.00070a80 SEQ: 1 OP:5.4

ktucm redo: slt: 0x0026 sqn: 0x00000038 srt: 0 sta: 9 flg: 0x2

ktucf redo: uba: 0x00800072.0016.0c ext: 1 spc: 6878 fbi: 0

REDO RECORD - Thread:1 RBA: 0x000033.00000006.0010 LEN: 0x0208 VLD: 0x01

SCN: 0x0000.00070b49 SUBSCN: 1 12/08/2009 15:07:58

CHANGE #1 TYP:0 CLS:19 AFN:2 DBA:0x00800019 SCN:0x0000.00070a08 SEQ: 1 OP:5.2

ktudh redo: slt: 0x001e sqn: 0x00000033 flg: 0x0412 siz: 108 fbi: 0

uba: 0x0080001d.00d5.06 pxid: 0x0000.000.00000000

CHANGE #2 TYP:0 CLS:20 AFN:2 DBA:0x0080001d SCN:0x0000.00070a07 SEQ: 3 OP:5.1

ktudb redo: siz: 108 spc: 7220 flg: 0x0012 seq: 0x00d5 rec: 0x06

xid: 0x0002.01e.00000033

ktubl redo: slt: 30 rci: 0 opc: 11.1 objn: 516 objd: 514 tsn: 0

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

0x00000000 prev ctl uba: 0x0080001c.00d5.42

prev ctl max cmt scn: 0x0000.0005d140 prev tx cmt scn: 0x0000.0005d400

KDO undo record:

KTB Redo

op: 0x04 ver: 0x01

op: L itl: xid: 0x0007.026.00000038 uba: 0x00800072.0016.0c

flg: C--- lkc: 0 scn: 0x0000.00070a81

KDO Op code: DRP row dependencies Disabled

xtype: XA bdba: 0x00400e8d hdba: 0x00400e89

itli: 2 ispac: 0 maxfr: 4863

tabn: 1 slot: 36(0x24)

CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00400e8d SCN:0x0000.00070a81 SEQ: 1 OP:11.2

KTB Redo

op: 0x11 ver: 0x01

op: F xid: 0x0002.01e.00000033 uba: 0x0080001d.00d5.06

Block cleanout record, scn: 0x0000.00070b49 ver: 0x01 opt: 0x02, entries follow...

itli: 2 flg: 2 scn: 0x0000.00070a81

KDO Op code: IRP row dependencies Disabled

xtype: XA bdba: 0x00400e8d hdba: 0x00400e89

itli: 2 ispac: 0 maxfr: 4863

tabn: 1 slot: 36(0x24) size/delt: 26

fb: -CH-FL-- lb: 0x2 cc: 4 cki: 0

null: ----

col 0: [ 6] c5 0d 3d 1a 3d 4d

col 1: [ 7] 78 6d 0c 08 10 08 3b

col 2: [ 1] 80

col 3: [ 4] c3 2f 11 2a

CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.19

session number = 5

serial number = 1

current username = SYS

login username =

client info =

OS username = oracle

Machine name = test02

OS terminal = UNKNOWN

OS process id = 18123

OS program name = oracle@test02 (SMON)

transaction name =

Recursive transaction

REDO RECORD - Thread:1 RBA: 0x000033.00000007.0028 LEN: 0x0054 VLD: 0x01

SCN: 0x0000.00070b4a SUBSCN: 1 12/08/2009 15:07:58

CHANGE #1 TYP:0 CLS:19 AFN:2 DBA:0x00800019 SCN:0x0000.00070b49 SEQ: 1 OP:5.4

ktucm redo: slt: 0x001e sqn: 0x00000033 srt: 0 sta: 9 flg: 0x2

ktucf redo: uba: 0x0080001d.00d5.06 ext: 0 spc: 7110 fbi: 0

REDO RECORD - Thread:1 RBA: 0x000033.00000008.0010 LEN: 0x0070 VLD: 0x01

SCN: 0x0000.00070ba3 SUBSCN: 1 12/08/2009 15:12:21

CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.00070a55 SEQ: 1 OP:5.4

ktucm redo: slt: 0x001b sqn: 0x0000002f srt: 0 sta: 9 flg: 0x12

ktucf redo: uba: 0x0080005f.0017.24 ext: 0 spc: 4362 fbi: 0

END OF REDO DUMP

----- Redo read statistics for thread 1 -----

Read rate (ASYNC): 3Kb in 0.11s => 0.00 Mb/sec

Longest record: 0Kb, moves: 0/7 (0%)

Change moves: 2/16 (12%), moved: 0Mb

----------------------------------------------

这么多redo record 哪个才是呢？

每一个redo record的时间应该是具体每个redo record产生时的时间，而不是 dump时间;

9 结论

从上面的过程来看，

Undo：分配事务槽及记录数据前镜像。

DataFile：记录事务信息及修改后的数据。

Redo：记录事务槽分配、回滚段信息、修改后数据信息、事务提交信息等。

10 未解决的问题

1 redo log dump文件的分析不清楚

参考 <及日志管理深入分析及性能调整_20091119>>

2 col 0: [ 6] c5 0d 3d 1a 01 5b

col 1: [ 7] 78 6d 0c 08 0e 1d 0d

col 2: [ 1] 80

col 3: [ 4] c3 2e 62 3c

转换成字符值 ?

select chr(to_number(63,'xx')),chr(to_number(64,'xx')),chr(to_number(66,'xx')) from dual;

SQL> select

2 chr(to_number('6d','xx')),

3 chr(to_number('6f','xx')),

4 chr(to_number(75,'xx')),

5 chr(to_number(73,'xx')),

6 chr(to_number(65,'xx')) from dual;

CH CH CH CH CH

-- -- -- -- --

m o u s e

3 redo record哪些情况下产生，如何跟踪 ?

4 查询当前事务使用的rollback segment ?

ITPub博客

一个事务的整个流程,datafile,undo,redo的内容

最新文章