分类目录归档：Oracle

ORACLE update 操作内部原理

发表于 2012 年 8 月 12 日由惜分飞

对于oracle的update操作,在数据块中具体是如何出来,是直接更新原来值,还是通过插入新值修改指针的方法实现.下面通过证明:
模拟表插入数据

SQL> create table t_xifenfei(id number,name varchar2(10));

Table created.

SQL> insert into t_xifenfei values(1,'XFF');

1 row created.

SQL> insert into t_xifenfei values(2,'CHF');

1 row created.

SQL> commit; 

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select id,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei;

        ID ROWID                 REL_FNO    BLOCKNO      ROWNO
---------- ------------------ ---------- ---------- ----------
         1 AAASc+AAEAAAACvAAA          4        175          0
         2 AAASc+AAEAAAACvAAB          4        175          1

SQL> alter system dump datafile 4 block 175;

System altered.

SQL>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_24625.trc

数据存储对应16进制值

SQL> select dump(1,'16') from dual;

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

SQL> select dump(2,'16') from dual;

DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3

SQL> select dump('XFF','16') FROM DUAL;

DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46

SQL> SELECT DUMP('CHF','16') FROM DUAL;

DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46

得出第一条记录对应值为:02c10203584646;第二条记录对应值为:02c10303434846

dump 数据块得到记录

bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f84
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f8e    ---->8078
0x14:pri[1]     offs=0x1f84    ---->8068
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 3]  58 46 46
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

bbed查看相关记录

BBED> p kdbr
sb2 kdbr[0]                                 @118      8078 <--第一条row directory指针位置
sb2 kdbr[1]                                 @120      8068 <--第二条row directory指针位置

BBED> p *kdbr[0] 
rowdata[10]
-----------
ub1 rowdata[10]                             @8178     0x2c

BBED> x /rnc  
rowdata[10]                                 @8178    
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x01
cols@8180:    2

col    0[2] @8181: 1 
col    1[3] @8184: XFF


BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8168     0x2c

BBED> x /rnc
rowdata[0]                                  @8168    
----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x01
cols@8170:    2

col    0[2] @8171: 2 
col    1[3] @8174: CHF


BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8168 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c010202 c1030343 48462c01 0202c102 03584646 010650e5 

 <32 bytes per line>

这里可以得到结论如下:
1.数据是从块的底部开始往上存储
2.在每一条记录的头部分别有flag/lock/cols对应这里的2c0102
3.这里的偏移量和dump出来的数据可以看出来两条记录是连续在一起(偏移量分别为:8168和8178)

更新一条记录

SQL> update t_xifenfei set name='XIFENFEI' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 175;

System altered.

SQL> select dump('XIFENFEI','16') from dual;

DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49

我们可以但看到值有XFF改变为XIFENFEI,存储长度变大

dump数据块信息

bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f75
avsp=0x1f69
tosp=0x1f69
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f75    ---->8053
0x14:pri[1]     offs=0x1f84    ---->8068
block_row_dump:
tab 0, row 0, @0x1f75
tl: 15 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  58 49 46 45 4e 46 45 49
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

通过对比第一次dump出来的数据块发现:row 0的值和偏移量发生了变化

bbed查看相关记录

BBED> set file 4 block 175
        FILE#           4
        BLOCK#          175

BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175                                   Dba:0x010000af
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[2]                                @118     

 ub1 freespace[8031]                        @122     

 ub1 rowdata[35]                            @8153    

 ub4 tailchk                                @8188    

BBED> p kdbr
sb2 kdbr[0]                                 @118      8053   <--第一条row directory指针位置
sb2 kdbr[1]                                 @120      8068   <--第二条row directory指针位置

BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15]                             @8168     0x2c

BBED> x /rnc
rowdata[15]                                 @8168    
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    2

col    0[2] @8171: 2 
col    1[3] @8174: CHF


BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8153     0x2c


BBED> x /r
rowdata[0]                                  @8153    
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155:    2

col    0[2] @8156:  0xc1  0x02 
col    1[8] @8159:  0x58  0x49  0x46  0x45  0x4e  0x46  0x45  0x49 

BBED> set count 64
        COUNT           64

 <32 bytes per line>

BBED> d /v
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175     Offsets: 8153 to 8191  Dba:0x010000af
-------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
 000202c1 03034348 462c0002 02c10203 l ......CHF,......
 58464602 068de8                     l XFF....

 <16 bytes per line>

从这里可以看到
1.这里可以看到三个值(XFF,CHF,XIFENFEI)均存在,但是通过p kdbr和dump block不能看到,因为row directory中无指针指定到该值上
2.也是通过row directory指针使得我们从原先看到的第一条记录处于数据块最底部变成了现在相对而言的数据部分最上层,
3.绝大多数情况:数据库更新一条记录,不是直接修改数据值,而是重新插入一条新记录,然后修改row directory指针指定到新的offset上
4.不是直接update,而是insert+指针来实现,这样做的好处:1)如果修改记录update值的长度发生变化(变大或者变小)那么该值之前的数据都要发生变动,对数据库来说成本太高.2)如果直接更新值可能导致其他数据变动,使得其他行受到影响.
5.由于是修改row directory指针,所以该处理方法的rowid值不会发生变化

发表在 Oracle | 标签为 bbed | 一条评论

使用oradebug修改数据库scn

发表于 2012 年 8 月 11 日由惜分飞

闲着无事看到几篇文章介绍了使用oradebug修改数据库scn的案例,这里也做了两个测试,发现该功能确实很巧妙,通过修改内存中的scn值,然后写入控制文件和数据文件,实现修改scn的方法,不过同样该方法的危害性极大,这里仅供测试使用,生产环境切不可乱使用,可能引起很严重后果
数据库版本信息

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select '惜分飞' XIFENFEI FROM DUAL;

XIFENF
------
惜分飞

在open库中修改scn

SQL> oradebug setmypid
Statement processed.

--查看当前scn
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 0007A09F 00000019 00000000 00000000 00000000 00000000 20009034
SQL>  select CHECKPOINT_CHANGE# a from v$datafile;

         A
----------
    499314
    499314
    499314
    499314

SQL> select dbms_flashback.get_system_change_number a from dual;

         A
----------
    499877

SQL> select to_number('7A09F','xxxxxxxxx') from dual;

TO_NUMBER('7A09F','XXXXXXXXX')
------------------------------
                        499871

--修改内存中scn值(十进制)
SQL>  oradebug poke 0x20009228 4 8
BEFORE: [20009228, 2000922C) = 00000000
AFTER:  [20009228, 2000922C) = 00000008
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000008 0007A0D8 00000052 00000000 00000000 00000000 00000000 20009034

SQL> col a for 999999999999999
SQL> select dbms_flashback.get_system_change_number a from dual;

         A
------------
34360238301

SQL> select to_number('8','xx')*4294967296+to_number('0007A0D8','xxxxxxxx') a from dual;

               A
----------------
     34360238296

--做一个checkpoint为了内存中的scn值写入控制文件和数据文件
SQL>  alter system checkpoint;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

SQL> col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile;

               A
----------------
     34360238496
     34360238496
     34360238496
     34360238496

SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     34360238496
     34360238496
     34360238496
     34360238496

在mount库中修改scn

SQL> startup mount
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
--因为数据库是mount状态不能看到scn值
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 20009034
SQL>  col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     34360240739
     34360240739
     34360240739
     34360240739

--求出WRAP SCN值
SQL> select 34360240739/4294967296 from dual;

34360240739/4294967296
----------------------
            8.00011697

--修改内存中scn值(十六进制)
SQL> oradebug poke 0x20009228 4 0x0000000a
BEFORE: [20009228, 2000922C) = 00000000
AFTER:  [20009228, 2000922C) = 0000000A
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000000 00000000 00000000 00000000 00000000 00000000 20009034

SQL> alter database open;

Database altered.

SQL> select dbms_flashback.get_system_change_number a from dual
  2  ;

               A
----------------
     42949673074

--注意:使用此种方法修改BASE SCN如果不指定,会从0开始计数
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000077 0000001C 00000000 00000000 00000000 00000000 20009034
SQL> select to_number('A','xx')*4294967296+to_number('00000077','xxxxxxxx') a from dual;

               A
----------------
     42949673079

SQL> alter system checkpoint;

System altered.

SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     42949673095
     42949673095
     42949673095
     42949673095

SQL> select CHECKPOINT_CHANGE# a from v$datafile;

               A
----------------
     42949673095
     42949673095
     42949673095
     42949673095

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     42949673231
     42949673231
     42949673231
     42949673231

在oradebug推进scn的过程中，需要注意不同平台，不同位数的ORACLE数据库可能推进方式有一定的区别，操作前最好在系统平台位数上进行测试，否则有可能导致恢复后果更加麻烦

发表在非常规恢复 | 标签为 oradebug poke, oradebug scn | 3 条评论

17813235971

QQ 咨询

分类目录归档：Oracle

利用bbed找回ORACLE更新前值

ORACLE update 操作内部原理

使用oradebug修改数据库scn

文章分类

最近发表

分类目录归档：Oracle

利用bbed找回ORACLE更新前值

ORACLE update 操作内部原理

使用oradebug修改数据库scn

标签云

文章分类

最近发表