简述：本文主要就Update操作，引发与之相关的redo，undo，数据文件中的数据块，来做分析。
使用技术：dump

流程：

1.获取当前的scn号

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

490880

2.查看当前日志，当前日志是第三组

SQL> select group#, sequence#,bytes/1024/1024, status ,first_change# from v$log;

GROUP# SEQUENCE# BYTES/1024/1024 STATUS FIRST_CHANGE#

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

1 19 50 INACTIVE 441628

2 20 50 INACTIVE 441876

3 21 50 CURRENT 478953

3.获取当前时间

SQL> alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE

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

2013-08-29 04:50:01

3.u1用户登录，使用update更新tab1的一条记录，并提交（日志从缓存中写入到日志文件）

SQL> select rowid ,t.* from tab1 t;

ROWID C1 C2 C3

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

AAADZnAAEAAAACEAAA 9 9 9

AAADZnAAEAAAACEAAB 10 10 10

AAADZnAAEAAAACHAAA 1 2 3

AAADZnAAEAAAACHAAB 11 22 33

AAADZnAAEAAAACHAAC 111 222 333

AAADZnAAEAAAACHAAD 1111 4444 3333

6 rows selected.

SQL> update tab1 set c2=29 where c1=9;

1 row updated.

SQL> commit;

Commit complete.

4.取第三组日志对应的文件做转储

SQL> select member from v$logfile;

MEMBER

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

/home/oracle/oracle/app/oradata/tdb/redo01.log

/home/oracle/oracle/app/oradata/tdb/redo02.log

/home/oracle/oracle/app/oradata/tdb/redo03.log

SQL> alter system dump logfile '/home/oracle/oracle/app/oradata/tdb/redo03.log' scn min 490880;

System altered.

1.获取其16进制的scn号，用来转储文件中做检索
SQL> select to_char('490980','xxxxx') from dual;

TO_CHAR('490
------------
77de4

TO_NUMBER('77DE4','XXXXX')
--------------------------
                    490980


2.获取其日志记录，截取下来作分析
REDO RECORD - Thread:1 RBA: 0x000015.000097a1.0010 LEN: 0x0218 VLD: 0x05
SCN: 0x0000.00077de4 SUBSCN: 1 08/29/2013 04:51:03
(LWN RBA: 0x000015.000097a1.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00077de4)

3.分析chang#1
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.00077c28 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0020 sqn: 0x000000e7 flg: 0x0012 siz: 160 fbi: 0
            uba: 0x00c003ff.002a.1e    pxid: 0x0000.000.00000000

----------------------------------
分析其所在的数据文件（绝对数据文件）：
AFN:3
select name, file# from v$datafile;
3号文件是undo文件

分析其所在数据块与相对数据文件
DBA：0x00c00080
SQL> select to_number ('c00080','xxxxxx') from dual;

TO_NUMBER('C00080','XXXXXX')
----------------------------
                    12583040

SQL> select dbms_utility.data_block_address_file(12583040) as rfile,
2 dbms_utility.data_block_address_block(12583040) as block from dual;

     RFILE      BLOCK
---------- ----------
         3        128

通过上面分析，可以得知该数据块所在undo段
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE , TABLESPACE_NAME from dba_segments where header_file=3 and header_block=128 and relative_fno=3;

转出该数据块，存放的是事务表，但没有看明白
alter system dump datafile 3 block 128;

3.分析chang#2
CHANGE #2 TYP:0 CLS:18 AFN:3 DBA:0x00c003ff OBJ:4294967295 SCN:0x0000.00077c27 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 160 spc: 2460 flg: 0x0012 seq: 0x002a rec: 0x1e
            xid: 0x0001.020.000000e7
ktubl redo: slt: 32 rci: 0 opc: 11.1 [objn: 13927 objd: 13927 tsn: 4]
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x00c003ff.002a.1d
prev ctl max cmt scn: 0x0000.0007734c prev tx cmt scn: 0x0000.00077360
txn start scn: 0xffff.ffffffff logon user: 37 prev brb: 12583933 prev bcl: 0 BuExt idx: 0 flg2: 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: XA flags: 0x00000000 bdba: 0x01000084 hdba: 0x01000082
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
col 1: [ 2] c1 0a

-----------------------------------
取其数据文件与数据块：
DBA:0x00c003ff

select to_number ('c003ff','xxxxxx') from dual;

12583935

select dbms_utility.data_block_address_file(12583935) as rfile, dbms_utility.data_block_address_block(12583935) as block from dual;

     RFILE      BLOCK
---------- ----------
         3       1023

转出这个数据块：
alter system dump datafile 3 block 1023;

update操作前的数据快照：
==>undo记录
    *-----------------------------
    * Rec #0x1e slt: 0x20 objn: 13927(0x00003667) objd: 13927 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: 0x00c003ff.002a.1d ctl max scn: 0x0000.0007734c prv tx scn: 0x0000.00077360
    txn start scn: scn: 0x0000.00077de4 logon user: 37
    prev brb: 12583933 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: XA flags: 0x00000000 bdba: 0x01000084 hdba: 0x01000082
    itli: 2 ispac: 0 maxfr: 4858
    tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
    ncol: 3 nnew: 1 size: 0
    col 1: [ 2] c1 0a

SQL> set serveroutput on;
SQL>
SQL> declare n number;
2       begin
3       dbms_stats.convert_raw_value('c10a',n);
4       dbms_output.put_line(    n);
5       end;
6     /
9
（是修改前的值）

4.分析chang#3

CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x01000084 OBJ:13927 SCN:0x0000.0007764f SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0001.020.000000e7    uba: 0x00c003ff.002a.1e
Block cleanout record, scn: 0x0000.00077de4 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00077640
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000084 hdba: 0x01000082
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
col 1: [ 2] c1 1e
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number   = 41
serial number   = 109
transaction name =
version 186647296
audit sessionid 190001
Client Id =
login   username = U1
-------------------------------------------
根据DBA：0x01000084，获取数据文件与数据块，并转储：
alter system dump datafile 4 block 132;

4号文件的数据块，是修改后的数据块
    data_block_dump,data header at 0xb6ce6664
    ===============
    tsiz: 0x1f98
    hsiz: 0x18
    pbl: 0xb6ce6664
        76543210
    flag=--------
    ntab=1
    nrow=3
    frre=2
    fsbo=0x18
    fseo=0x1f74
    avsp=0x1f68
    tosp=0x1f68
    0xe:pti[0]      nrow=3 ffs=0
    0x12:pri[0]     ffs=0x1f8c
    0x14:pri[1]     ffs=0x1f80
    0x16:pri[2]     sfll=-1
    block_row_dump:
    tab 0, row 0, @0x1f8c
    tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
    col 0: [ 2] c1 0a
    col 1: [ 2] c1 1e
                    --》（修改后的值）
    col 2: [ 2] c1 0a
    tab 0, row 1, @0x1f80
    tl: 12 fb: --H-FL-- lb: 0x0 cc: 3
    col 0: [ 2] c1 0b
    col 1: [ 2] c1 0b
    col 2: [ 2] c1 0b
    end_of_block_dump


验证：
    --把dump反转回来
    set serveroutput on;

    declare n number;
     begin
     dbms_stats.convert_raw_value('c11e',n);
     dbms_output.put_line(    n);
     end;
   /
   29（修改后的值）

   SQL> select dump (29,16) from dual;

    DUMP(29,16)
    ------------------------------------
    Typ=2 Len=2: c1,1e

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

完毕

参考：
http://www.linuxidc.com/Linux/2009-12/23597.htm
http://blog.csdn.net/robinson1988/article/details/4664988

201308290943401.jpg

QQ图片2013082909460402.jpg

ITPub博客

DML操作之数据块分析

最新文章