首页 » ORACLE 9i-23ai » 还原data block dumps实际值

还原data block dumps实际值

  • 2012/05/04
  • ORACLE 9i-23ai
  • 745 views
  • 还原data block dumps实际值已关闭评论

前天看了小荷的一个案例因为丢了表上的数据，从索引block中找回了值

转储了oracle block的值，如何得到它真正表中的值，也算 是dump(val,16)的逆运算

sys@ORCL>conn anbob/anbob
Connected.
anbob@ORCL>create table testdump(id number,name varchar2(10),ctime date);

Table created.

anbob@ORCL>insert into testdump values(918,'anbob',sysdate);

1 row created.

anbob@ORCL>select * from testdump;

ID NAME       CTIME
---------- ---------- -------------------
918 anbob      2012-05-04 09:39:23

anbob@ORCL>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fno,dbms_rowid.rowid_block_number(rowid) blkno
2  from testdump;

FNO      BLKNO
---------- ----------
15    2676436

sys@ORCL>alter system  dump datafile 15 block 2676436;

System altered.

trace file 的部分内容

Start dump data blocks tsn: 13 file#: 15 minblk 2676436 maxblk 2676436
buffer tsn: 13 rdba: 0x03e8d6d4 (15/2676436)
Block header dump:  0x03e8d6d4
Object id on Block? Y
seg/obj: 0xf58a9  csc: 0x03.74dc2b6  itc: 2  flg: E  typ: 1 - DATA
brn: 0  bdba: 0x3e8d6d1 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.013.0000eb55  0x00800060.58d9.08  --U-    1  fsc 0x0000.074dc31d
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0xbe72464
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0be72464
bdba: 0x03e8d6d4
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f83
avsp=0x1f6f
tosp=0x1f6f
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f83
block_row_dump:
tab 0, row 0, @0x1f83
tl: 21 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 3]  c2 0a 13
col  1: [ 5]  61 6e 62 6f 62
col  2: [ 7]  78 70 05 04 0a 28 18
end_of_block_dump
End dump data blocks tsn: 13 file#: 15 minblk 2676436 maxblk 2676436

–分析ump
根据seg/obj: 0xf58a9我们先确认是这个对象

sys@ORCL>select object_id from dba_objects where owner='ANBOB' and object_name='TESTDUMP';

OBJECT_ID
----------
1005737

sys@ORCL>select to_number('f58a9','xxxxxxx') from dual;

TO_NUMBER('F58A9','XXXXXXX')
----------------------------
1005737

sys@ORCL>select objd,ts#,status from v$bh where file#=15 and block#=2676436;

OBJD        TS# STATUS
---------- ---------- -------
1005737         13 xcur

根据bdba: 0x03e8d6d4 也可以确认到那个块

anbob@ORCL>variable dba varchar2(30)
anbob@ORCL>exec :dba := dbms_utility.make_data_block_address(15,2676436);

PL/SQL procedure successfully completed.

anbob@ORCL>print dba

DBA
--------------------------------
65590996

anbob@ORCL>select to_char(65590996,'xxxxxxxxxxx') from dual;

TO_CHAR(6559
------------
3e8d6d4

现在我们向回还原表中的值

col  0: [ 3]  c2 0a 13
col  1: [ 5]  61 6e 62 6f 62
col  2: [ 7]  78 70 05 04 0a 28 18

第一列为number，看到老白有这种推算方法
0a 转换成10进制-1，13同理，然后再合并，我们试一下

anbob@ORCL>select to_number('0a','xxxxxxx')-1||to_number('13','xxxxx')-1 from dual;

TO_NUMBER('0A','XXXXXXX')-1||TO_NUMBER('13','XXXXX')-1
------------------------------------------------------
918

我们再试下接着推算负数

anbob@ORCL>select dump(-98,16) from dual;

DUMP(-98,16)
--------------------
Typ=2 Len=3: 3e,3,66

anbob@ORCL>select dump(-98571,16) from dual;

DUMP(-98571,16)
---------------------------
Typ=2 Len=5: 3c,5c,10,1e,66

anbob@ORCL>select 101-to_number('3','xxxxxxx') from dual;

101-TO_NUMBER('3','XXXXXXX')
----------------------------
98

anbob@ORCL>select 101-to_number('5c','xxxxxxx'),101-to_number('10','xxxxxxx'),101-to_number('1e','xxxxxxx') from dual;

101-TO_NUMBER('5C','XXXXXXX') 101-TO_NUMBER('10','XXXXXXX') 101-TO_NUMBER('1E','XXXXXXX')
----------------------------- ----------------------------- -----------------------------
9                            85                            71

往回走是去最后的66，和第一组3e中间用101-（16输为10进制）再组合再一起

有点意思，放开oracle的内部原理不说，其实我们可以用一个ORACLE的函数很方便就可以算出

anbob@ORCL>SELECT utl_raw.cast_to_number(replace('3c,5c,10,1e,66',',')) value FROM dual;

VALUE
----------
-98571

anbob@ORCL>SELECT utl_raw.cast_to_number(replace('c2 0a 13',' ')) value FROM dual;

VALUE
----------
918

第二列为字符col  1: [ 5]  61 6e 62 6f 62
对于字符就是把每位ascII码转换为16进制

anbob@ORCL>select chr(to_number('61','xxxxxx')) from dual;

CH
--
a

anbob@ORCL>select chr(to_number('6e','xxxxxx')) from dual;

CH
--
n

anbob@ORCL>select chr(to_number('62','xxxxxx')) from dual;

CH
--
b

anbob@ORCL>select utl_raw.CAST_TO_VARCHAR2('616e626f62') from dual;

anbob@ORCL>select utl_raw.CAST_TO_VARCHAR2('616e626f62') from dual;

UTL_RAW.CAST_TO_VARCHAR2('616E626F62')
----------------------------------------------------
anbob

第三列是date类型col  2: [ 7]  78 70 05 04 0a 28 18 实值为2012-05-04 09:39:23

anbob@ORCL>select to_number(val16,'xxxxx') val10 from (select regexp_substr('78 70 05 04 0a 28 18','[^ ]+',1,rownum) val16  from dual connect by rownum<=7);

VAL10
----------
120
112
5
4
10
40
24

7 rows selected.

年份-100，月日原值，时分秒-1再组合
note:
dump block 中只会记录col 值和长度，而不会记录col datatype,列类型在数据字典中。

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：