ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DML操作之数据块分析

DML操作之数据块分析

原创 Linux操作系统 作者:ora_pascal1982 时间:2013-08-29 09:37:29 0 删除 编辑
简述:本文主要就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博客 ” ,链接:http://blog.itpub.net/29121762/viewspace-769473/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2013-08-16

  • 博文量
    9
  • 访问量
    29045