oracle 详细分析redo（4）

最新推荐文章于 2023-03-22 16:05:39 发布

ciyu1946

最新推荐文章于 2023-03-22 16:05:39 发布

阅读量148

点赞数

文章标签：数据库

关于redo 结构:

SQL> conn xh/a831115;
已连接。
SQL> select * from test;

A
----------
a

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> col member format a40
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO02.LOG

1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO01.LOG

SQL> update test set a='b';

已更新 1 行。~~~~~~~~~~~~~~~~~~~~~~~~~~~~未commit

SQL>

SQL> select xidusn ,ubafil,UBABLK from v$transaction;

XIDUSN UBAFIL UBABLK
---------- ---------- ----------
3 2 2194

可以看到这个事务使用的 undo block 是 file 2,block 2194

SQL> select * from v$rollname where usn=3;

USN NAME
---------- ------------------------------
3 _SYSSMU3$

SQL> select header_block,header_file from dba_segments where segment_name='_SYSS
MU3$';

HEADER_BLOCK HEADER_FILE
------------ -----------
41 2
SQL> select object_id from user_objects where object_name='TEST';

OBJECT_ID
----------
54147

SQL> alter system dump logfile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\REDO03.L
og';

系统已更改。

根据 object_id找到相关信息
可以看到这个redo record中有 3个 change vector（比如CHANGE #1）

REDO RECORD - Thread:1 RBA: 0x000003.00000beb.0010 LEN: 0x01c0 VLD: 0x0d
SCN: 0x0000.0045246b SUBSCN: 1 10/21/2009 09:23:22
CHANGE #1 TYP:0 CLS: 1 AFN:4 DBA:0x010001c4 OBJ:54147 SCN:0x0000.00445bbf SEQ: 1 OP:11.19
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0003.021.00000542 uba: 0x00800892.0281.18
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 7
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010001c4 hdba: 0x010001c3
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 1] 62

CHANGE #2 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x0000.00452413 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0021 sqn: 0x00000542 flg: 0x0012 siz: 152 fbi: 0
            uba: 0x00800892.0281.18    pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:22 AFN:2 DBA:0x00800892 OBJ:4294967295 SCN:0x0000.00452412 SEQ: 2 OP:5.1
ktudb redo: siz: 152 spc: 4888 flg: 0x0012 seq: 0x0281 rec: 0x18
            xid: 0x0003.021.00000542
ktubl redo: slt: 33 rci: 0 opc: 11.1 objn: 54147 objd: 54147 tsn: 4
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x00800892.0281.16
prev ctl max cmt scn: 0x0000.00451e95 prev tx cmt scn: 0x0000.00451e9b
txn start scn: 0x0000.004523eb logon user: 61 prev brb: 0 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 7
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010001c4 hdba: 0x010001c3
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 1] 61

分析可以从 AFN:看出是对那个文件的修改,AFN 绝对文件号

所以change#1 记录对datafile 4的修改,dba是对应的块
DBA:0x010001c4
SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('0001c4','xx
xxxxx'));

PL/SQL 过程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('0001c4','xx
xxxx'));

PL/SQL 过程已成功完成。

SQL> print file#

FILE#
----------
4

SQL> print blk#

BLK#
----------
452

SQL> select file#,block# from (select dbms_rowid.rowid_relative_fno(rowid) file#
,dbms_rowid.rowid_block_number(rowid) block# from xh.test);

FILE# BLOCK#
---------- ----------
4 452

可以看出这个 change vector记录的是修改datafile 4,block 452的记录正是，修改test 表的记录

uba: 0x00800892.0281.18

0281是序号，18是条目号

uba 是记录了这个块修改后的 undo记录放在undo 那个块中
SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('00892','xxx
xxxx'));

PL/SQL 过程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('00892','xxx
xxx'));

PL/SQL 过程已成功完成。

SQL> print file#

FILE#
----------
2

SQL> print blk#

BLK#
----------
2194

可以看到修改test后的旧值（a）放在undo file 2 ,block 2194中

col 0: [ 1] 62 就是 undo记录中，记录修改后的值(upate test set a='b')

SQL> select chr(to_number(62,'xx')) from dual;

CH
--
b

KDO Op code: 21 d代表事务进行操作的类型代码 21 代表 update

xid: 0x0003.021.00000542 对应着

SQL> select to_number('21','xxxxxx') from dual;

TO_NUMBER('21','XXXXXX')
------------------------
33

SQL> select to_number('542','xxxxxx') from dual;

TO_NUMBER('542','XXXXXX')
-------------------------
1346

SQL>

SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;（user xh session执行）

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 33 1346

XIDUSN NUMBER Undo segment number
XIDSLOT NUMBER Slot number
XIDSQN NUMBER Sequence number

Array Update of 1 rows: 也显示了 update 1 rows

bdba: 0x010001c4 ：也表示修改的 test的数据块与dba一样（data block address）
是 file 4,block 452

hdba: 0x010001c3：表示的是test表的 segment header block

SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('1c3','xxxxx
xx'));

PL/SQL 过程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('1c3','xxxxx
x'));

PL/SQL 过程已成功完成。

SQL> print file#

FILE#
----------
4

SQL> print blk#

BLK#
----------
451

SQL> select header_file,header_block from dba_segments where segment_name='TEST'
;

HEADER_FILE HEADER_BLOCK
----------- ------------
4 451

基本change #1的重要信息了解完了，可以清楚的看到redo中都记录了些什么

CHANGE #2 AFN:2可以看到是对 undo 的修改的记录

DBA:0x00800029 OBJ:4294967295

SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('800029','xx
xxxxx'));

PL/SQL 过程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('800029','xx
xxxx'));

PL/SQL 过程已成功完成。

SQL> print file#

FILE#
----------
2

SQL> print blk#

BLK#
----------
41

这正是对undo segment header的修改，要修改事务表（存undo segment header）

uba: 0x00800892.0281.18 :记录了放 test旧值的undo block 所在地址，与change#1中的一样

change#3:
CHANGE #3 TYP:0 CLS:22 AFN:2 DBA:0x00800892
anf 表示这个还是记录的undo file的信息，DBA表示的是哪个undo 块

SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('800892','xx
xxxxx'));

PL/SQL 过程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('800892','xx
xxxx'));

PL/SQL 过程已成功完成。

SQL> print file#

FILE#
----------
2

SQL> print blk#

BLK#
----------
2194

可以看到这个块就是记录test 旧值得块，chang#3 记录了对这个undo 块的修改,所以redo是保护undo的

ktubl redo: slt: 33 rci: 0 opc: 11.1 objn: 54147 objd: 54147 tsn: 4

OBJD可以看到是记录的对象是xh.test

slt: 33=XIDSLOT NUMBER Slot number

logon user: 61 登陆操作的user id

SQL> select username from dba_users where user_id=61;

USERNAME
------------------------------
XH

KDO Op code: 21：表示还是一个update操作

bdba: 0x010001c4 hdba: 0x010001c3
BDBA:记录的是fil 4 block 452 表示是test表的数据块地址,hdba 是file 4,block 451 表示是test segment header block

col 0: [ 1] 61：表示修改前的值

SQL> select chr(to_number(61,'xx')) from dual;

CH
--
a

SQL> alter system dump datafile 2 block 2194;

系统已更改。

根据object_id找
dump log file中 change#1中uba: 0x00800892.0281.18，change#3中dba 可以现在dump datablock trace中 rec 0x18 为uba中18 表示slot号

* Rec #0x18 slt: 0x21 objn: 54147(0x0000d383) objd: 54147 tblspc: 4(0x00000004)
* 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: 0x00800892.0281.16 ctl max scn: 0x0000.00451e95 prv tx scn: 0x0000.00451e9b
txn start scn: scn: 0x0000.004523eb logon user: 61
prev brb: 0 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 7
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010001c4 hdba: 0x010001c3
itli: 2 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 1] 61~~~~~~~~~~~~~~~~~~~~~~~记录旧得值 a

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

关于redo 结构补充（用户commit 时间）

1.update 后马上commit

SQL> conn xh/a831115;
已连接。
SQL> select * from test;

A
----------
b

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

SQL> col member format a40
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO02.LOG

1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO01.LOG

SQL> update test set a='e';

已更新 1 行。

SQL> commit;~~~~~~~~~~~~如果update 后，紧接着 commit

SQL> alter system dump logfile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\REDO01.L
og';

系统已更改。

SQL> select object_id from user_objects where object_name='TEST';

OBJECT_ID
----------
     54147
SCN: 0x0000.0047ea28 SUBSCN: 8 10/22/2009 13:24:26
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x010001c4 OBJ:54147 SCN:0x0000.0047e97c SEQ: 2 OP:11.19
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0002.010.00000572    uba: 0x008006cc.04e3.1a
Block cleanout record, scn: 0x0000.0047ea22 ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x0000.0047e97c
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 8
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010001c4 hdba: 0x010001c3
itli: 1 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 1] 65～～～～～新值
CHANGE #2 TYP:0 CLS:19 AFN:2 DBA:0x00800019 OBJ:4294967295 SCN:0x0000.0047e9e1 SEQ: 2 OP:5.2
ktudh redo: slt: 0x0010 sqn: 0x00000572 flg: 0x0012 siz: 176 fbi: 0
            uba: 0x008006cc.04e3.1a    pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:19 AFN:2 DBA:0x00800019 OBJ:4294967295 SCN:0x0000.0047ea28 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0010 sqn: 0x00000572 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x008006cc.04e3.1a ext: 2 spc: 4558 fbi: 0
CHANGE #4 TYP:0 CLS:20 AFN:2 DBA:0x008006cc OBJ:4294967295 SCN:0x0000.0047e9e1 SEQ: 3 OP:5.1
ktudb redo: siz: 176 spc: 4736 flg: 0x0012 seq: 0x04e3 rec: 0x1a
            xid: 0x0002.010.00000572
ktubl redo: slt: 16 rci: 0 opc: 11.1 objn: 54147 objd: 54147 tsn: 4
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x008006cc.04e3.17
prev ctl max cmt scn: 0x0000.0047e4f2 prev tx cmt scn: 0x0000.0047e52f
txn start scn: 0x0000.00000000 logon user: 61 prev brb: 8390340 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x000a.014.00000453 uba: 0x00800bc0.037a.2a
                      flg: C---    lkc: 0     scn: 0x0000.0047e8e8
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 8
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010001c4 hdba: 0x010001c3
itli: 1 ispac: 0 maxfr: 4858
vect = 0
col 0: [ 1] 64～～～～～～～～～=旧值

具体内容就不解释了，可以看到这个redo record 产生了 4个 change vector，其中 change#2 ,chang#3都是修改 undo segment header 产生的 change vector
可以清楚的看到change#3是commit后的记录( sta: 9 提交标记为9 代表已经提交)

2.实验 update后过一会commit

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED~~~~~~~~~~~~~~做了clear
         2 CURRENT
         3 UNUSED~~~~~~~~~~~~~~做了clear

SQL> alter system switch logfile;

系统已更改。

SQL> alter system checkpoint;

系统已更改。

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 UNUSED

SQL> col member format a40
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------
         3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
           REDO03.LOG

2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO02.LOG

1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\
REDO01.LOG

SQL> update test set a='bbb';

已更新 1 行。

SQL> select object_id from user_objects where object_name='TEST';

OBJECT_ID
----------
54147

SQL> select xidusn ,ubafil,UBABLK from v$transaction;

XIDUSN UBAFIL UBABLK
---------- ---------- ----------
9 2 523

SQL> select * from v$rollname where usn=9;

USN NAME
---------- ------------------------------
9 _SYSSMU9$

SQL> select header_block,header_file from dba_segments where segment_name='_SYSS
MU9$';

HEADER_BLOCK HEADER_FILE
------------ -----------
137 2

事务表存储在 undo segment header file 2,block 137

SQL> alter system dump logfile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\REDO01.L
og';

系统已更改。

REDO RECORD - Thread:1 RBA: 0x00003e.00000019.0010 LEN: 0x01d0 VLD: 0x0d
SCN: 0x0000.0047effc SUBSCN: 13 10/22/2009 14:05:25
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x010001c4 OBJ:54147 SCN:0x0000.0047efa0 SEQ: 1 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0009.005.0000058e    uba: 0x0080020b.0209.26
Block cleanout record, scn: 0x0000.0047efe2 ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x0000.0047efa0
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010001c4 hdba: 0x010001c3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 8
ncol: 1 nnew: 1 size: 1
col 0: [ 3] 62 62 62
CHANGE #2 TYP:0 CLS:33 AFN:2 DBA:0x00800089 OBJ:4294967295 SCN:0x0000.0047efb5 SEQ: 2 OP:5.2
ktudh redo: slt: 0x0005 sqn: 0x0000058e flg: 0x0012 siz: 152 fbi: 0
            uba: 0x0080020b.0209.26    pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:34 AFN:2 DBA:0x0080020b OBJ:4294967295 SCN:0x0000.0047efb5 SEQ: 28 OP:5.1
ktudb redo: siz: 152 spc: 3380 flg: 0x0012 seq: 0x0209 rec: 0x26
            xid: 0x0009.005.0000058e
ktubl redo: slt: 5 rci: 0 opc: 11.1 objn: 54147 objd: 54147 tsn: 4
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x0080020b.0209.0a
prev ctl max cmt scn: 0x0000.0047e801 prev tx cmt scn: 0x0000.0047e80c
txn start scn: 0x0000.0047ee52 logon user: 61 prev brb: 8388965 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.010.00000572 uba: 0x008006cc.04e3.1a
                      flg: C---    lkc: 0     scn: 0x0000.0047ea28
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010001c4 hdba: 0x010001c3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 8
ncol: 1 nnew: 1 size: -1
col 0: [ 2] 62 62

此时 commit

SQL> alter system dump logfile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\XHTEST\REDO01.L
og';

系统已更改。

SQL> commit;

查看dump 多了一个 record

REDO RECORD - Thread:1 RBA: 0x00003e.000001c5.0010 LEN: 0x008c VLD: 0x05
SCN: 0x0000.0047f11a SUBSCN: 1 10/22/2009 14:06:59
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 OBJ:4294967295 SCN:0x0000.0047effc SEQ: 1 OP:5.4
ktucm redo: slt: 0x0005 sqn: 0x0000058e srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080020b.0209.26 ext: 2 spc: 3226 fbi: 0

这个 record 只有一个change vector 是针对undo segment header的当commit时记录事务表tnb中 sta:9 表示提交

SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('800089','xx
xxxx'));

PL/SQL 过程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('800089','x
xxxxxxx'));

PL/SQL 过程已成功完成。

SQL> print file#

FILE#
----------
2

SQL> print block#
SP2-0552: 未声明绑定变量 "BLOCK#"。
SQL> print blk#

BLK#
----------
137

如果要再进一步深入可以 dump下 undo segment header 查看下事务表

state 9代表提交，10代表active
index就是 slot号码
dba :对应的undo block address

TRN TBL::
index state cflags wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
   0x00    9    0x00 0x058e 0x0017 0x0000.0047ef60 0x0080020b 0x0000.000.00000000 0x00000001   0x00000000 1256191265
   0x01    9    0x00 0x058e 0x0020 0x0000.0047ecce 0x00800166 0x0000.000.00000000 0x00000001   0x00000000 1256190197

.............................................................................................................................
0x2f 9 0x00 0x058d 0x000d 0x0000.0047ef0d 0x00800168 0x0000.000.00000000 0x00000002 0x00000000 1256191257

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24217871/viewspace-684382/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24217871/viewspace-684382/

ciyu1946

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 详细分析redo（4）

关于redo 结构:SQL> conn xh/a831115;已连接。SQL> select * from test;A----------aSQL> select group#,status fr...
复制链接

扫一扫