Oracle UNDO块

可以先查询出object_id，在trace文件中搜索

[email?protected] prod>col object_name for a20
[email?protected] prod>select OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED from dba_objects where OBJECT_name = ‘EMP‘;

OBJECT_NAME           OBJECT_ID OBJECT_TYPE         CREATED
-------------------- ---------- ------------------- -------------------
EMP                       73181 TABLE               2009-08-13 23:35:45?

?

*-----------------------------
* Rec #0x20  slt: 0x19  objn: 73181(0x00011ddd)  objd: 73181  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: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x0180000c.0003.1d ctl max scn: 0x0000.00104368 prv tx scn: 0x0000.0010436a
txn start scn: scn: 0x0000.0010444f logon user: 0
 prev brb: 25165834 prev bcl: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000097  hdba: 0x01000092
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content: 
col  5: [ 2]  c2 09?

计算最后一行col的raw值

[email?protected] prod>select utl_raw.cast_to_number(replace(‘C2 09‘,‘ ‘)) from dual;

UTL_RAW.CAST_TO_NUMBER(REPLACE(‘C209‘,‘‘))
------------------------------------------
                                       800?

800正是更新前的值。Oracle就是这样存储数据前镜像的。?

可以看到在record中也记录了该undo对应的前镜像的数据块的地址信息bdba 0x01000097。我们进行转换得到：0000000100?0000000000000010010111 可以得到是file 4，block 151。

[email?protected] prod>select a.table_name,a.tablespace_name,b.file_name from dba_tables a,dba_data_files b where a.tablespace_name=b.tablespace_name and b.file_id = ‘4‘;

TABLE_NAME                     TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------ ----------------------------------------------------------------------
T2                             USERS                          /u01/oradata/prod/users01.dbf
T1                             USERS                          /u01/oradata/prod/users01.dbf
EMP1                           USERS                          /u01/oradata/prod/users01.dbf
TEAM                           USERS                          /u01/oradata/prod/users01.dbf
CUSTOMERS                      USERS                          /u01/oradata/prod/users01.dbf
DIMENSION_EXCEPTIONS           USERS                          /u01/oradata/prod/users01.dbf
SUBCATEGORY_REF_LIST_NESTEDTAB USERS                          /u01/oradata/prod/users01.dbf
PRODUCT_REF_LIST_NESTEDTAB     USERS                          /u01/oradata/prod/users01.dbf
SALGRADE                       USERS                          /u01/oradata/prod/users01.dbf
BONUS                          USERS                          /u01/oradata/prod/users01.dbf
EMP USERS /u01/oradata/prod/users01.dbf
DEPT                           USERS                          /u01/oradata/prod/users01.dbf

12 rows selected.?

转储数据块

[email?protected] prod>alter system dump datafile 4 block 151;

System altered.

users01.dbf 转储信息如下：

Block header dump:  0x01000097
 Object id on Block? Y
 seg/obj: 0x11ddd  csc: 0x00.f587b  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000090 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.010.00000198  0x00c15ab0.0061.12  C---    0  scn 0x0000.000b6f31
0x02   0x000b.019.0000000a  0x0180000c.0003.23  ----    4  fsc 0x0000.00000000
bdba: 0x01000097
data_block_dump,data header at 0xe51664?

当我们修改数据的时候会对相应的数据加锁，更直接说该锁存在于数据块中，并且存在itl（事务槽信息），itl的详细内容：其中包括xid，uba，flag，lock status，scn
xid代表事务id（undo段号.槽位号.wrap），其中包括undo段号0x0001=1，槽位号010=10，wrap00000198=408。等于undo块中xid

uba，这正是该数据的前镜像信息。

我们查看一下事务视图进行一下确认。

[email?protected] prod>select xidusn,xid,name,ubasqn,ubarec,ubablk from v$transaction where xidusn=11;

    XIDUSN    XIDSLOT XID                  NAME                     UBASQN     UBAREC     UBAFIL     UBABLK
---------- ---------- -------------------- -------------------- ---------- ---------- ---------- ----------
        11         25 0B0019000A000000                                   3         35          6         12?

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!