ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (oracle 详细分析redo(4))

oracle实验记录 (oracle 详细分析redo(4))

原创 Linux操作系统 作者:fufuh2o 时间:2009-10-23 14:29:25 0 删除 编辑

关于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/12020513/viewspace-617288/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427867