目標：理順事務的整個流程

一個事務的整個流程,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 ?

一個事務的整個流程,datafile,undo,redo的內容

相關文章