ITPub博客

首页 > 数据库 > Oracle > [20190104]bbed手动修改数据.txt

[20190104]bbed手动修改数据.txt

原创 Oracle 作者:lfree 时间:2019-01-04 16:03:11 0 删除 编辑

[20190104]bbed手动修改数据.txt


--//下午没事,测试看看bbed手动修改数据看看.如果修改信息等长,原地修改就是了.

--//如果不等长比较麻烦,测试第2种情况.并且在块中有空间能容纳修改信息.

--//如果直接修改比较麻烦,如果特殊修复可以在一台好的数据库上建立相同的表结果(注意字符集,大小头问题),

--//插入数据,然后想办法引入对应的数据块中.

--//我的测试仅仅一台机器,也可以演示操作过程,不要在生产系统做这样的操作!!


1.环境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


SCOTT@book> create table empy as select * from emp where rownum<=2;

Table created.


SCOTT@book> select rowid,empy.* from empy;

ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

AAAWHyAAEAAAAILAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

AAAWHyAAEAAAAILAAB       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30


SCOTT@book> @ rowid AAAWHyAAEAAAAILAAA

    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT

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

     90610          4        523          0  0x100020B           4,523                alter system dump datafile 4 block 523 ;


SCOTT@book> select rowid,emp.* from emp where empno=7839;

ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

AAAVREAAEAAAACXAAI       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10


SCOTT@book> @ rowid AAAVREAAEAAAACXAAI

    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT

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

     87108          4        151          8  0x1000097           4,151                alter system dump datafile 4 block 151 ;


--//测试想办法将dba=4,151的EMPNO=7839的记录修改dba=4,523的empno=7369的记录.


2.首先确定导出记录长度以及偏移量:


BBED> x /rnccntnnn dba 4,151 *kdbr[8]

rowdata[197]                                @7818

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

flag@7818: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@7819: 0x01

cols@7820:    8

col    0[3] @7821: 7839

col    1[4] @7825: KING

col    2[9] @7830: PRESIDENT

col    3[0] @7840: *NULL*

col    4[7] @7841: 1981-11-17 00:00:00

col    5[2] @7849: 5000

col    6[0] @7852: *NULL*

col    7[2] @7853: 10


--//7853+3-1-7818+1  = 38,确定记录长度是38,从offset=7818开始.

BBED> dump /v offset 7818 count 39

 File: /mnt/ramdisk/book/users01.dbf (4)

 Block: 151                               Offsets: 7818 to 7856                            Dba:0x01000097

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

 2c010803 c24f2804 4b494e47 09505245 53494445 4e54ff07 77b50b11 01010102 l ,....O(.KING.PRESIDENT..w.......

 c233ff02 c10b2c                                                         l .3....,

<32 bytes per line>)


SCOTT@book> @ bbvi 4 151

BVI_COMMAND

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

bvi -b 1236992 -s 8192 /mnt/ramdisk/book/users01.dbf

xxd -c16 -g 2 -s 1236992 -l 8192 /mnt/ramdisk/book/users01.dbf

dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=151 count=1 of=4_151.dd conv=notrunc 2>/dev/null

od -j 1236992 -N 8192 -t x1 -v /mnt/ramdisk/book/users01.dbf

hexdump -s 1236992 -n 8192 -C -v /mnt/ramdisk/book/users01.dbf

alter system dump datafile '/mnt/ramdisk/book/users01.dbf' block 151;

alter session set events 'immediate trace name set_tsn_p1 level 5';

alter session set events 'immediate trace name buffer level 16777367';

9 rows selected.


--//1236992+7818 =1244810,确定在文件的总偏移量1244810.可以执行命令如下:

$ xxd -c39 -g 8 -s 1244810 -l 39 /mnt/ramdisk/book/users01.dbf

012fe8a: 2c010803c24f2804 4b494e4709505245 534944454e54ff07 77b50b1101010102 c233ff02c10b2c  ,...翺(.KING.PRESIDENT.w?.....?.?,


3.确定导入的偏移量:

BBED> p dba 4,523 kdbr

sb2 kdbr[0]                                 @142      8026

sb2 kdbr[1]                                 @144      7983

--//查询最小的kdbr[N]值.这里对应kdbr[1].


BBED> x /rnccntnnn dba 4,523 *kdbr[1]

rowdata[0]                                  @8107

----------

flag@8107: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8108: 0x00

cols@8109:    8


col    0[3] @8110: 7499

col    1[5] @8114: ALLEN

col    2[8] @8120: SALESMAN

col    3[3] @8129: 7698

col    4[7] @8133: 1981-02-20 00:00:00

col    5[2] @8141: 1600

col    6[2] @8144: 300

col    7[2] @8147: 30


--//8107-38  = 8069,可以确定导入偏移量是8069.


4.生成bbed执行代码:

$ xxd -c1 -g 1 -s 1244810 -l 38 /mnt/ramdisk/book/users01.dbf | cut -c10-11 | xargs -I{} echo assign /x offset @ = {}

assign /x offset @ = 2c

assign /x offset @ = 01

assign /x offset @ = 08

assign /x offset @ = 03

assign /x offset @ = c2

assign /x offset @ = 4f

assign /x offset @ = 28

assign /x offset @ = 04

assign /x offset @ = 4b

assign /x offset @ = 49

assign /x offset @ = 4e

assign /x offset @ = 47

assign /x offset @ = 09

assign /x offset @ = 50

assign /x offset @ = 52

assign /x offset @ = 45

assign /x offset @ = 53

assign /x offset @ = 49

assign /x offset @ = 44

assign /x offset @ = 45

assign /x offset @ = 4e

assign /x offset @ = 54

assign /x offset @ = ff

assign /x offset @ = 07

assign /x offset @ = 77

assign /x offset @ = b5

assign /x offset @ = 0b

assign /x offset @ = 11

assign /x offset @ = 01

assign /x offset @ = 01

assign /x offset @ = 01

assign /x offset @ = 02

assign /x offset @ = c2

assign /x offset @ = 33

assign /x offset @ = ff

assign /x offset @ = 02

assign /x offset @ = c1

assign /x offset @ = 0b


--//保存文件,利用vim的increment.vim插件执行如下:%Inc s8069 i1,生成如下代码,开头手工加入set dba 4,523.

--//插件可以在如下链接下载:

set dba 4,523

assign /x offset 8069 = 2c

assign /x offset 8070 = 01

assign /x offset 8071 = 08

assign /x offset 8072 = 03

assign /x offset 8073 = c2

assign /x offset 8074 = 4f

assign /x offset 8075 = 28

assign /x offset 8076 = 04

assign /x offset 8077 = 4b

assign /x offset 8078 = 49

assign /x offset 8079 = 4e

assign /x offset 8080 = 47

assign /x offset 8081 = 09

assign /x offset 8082 = 50

assign /x offset 8083 = 52

assign /x offset 8084 = 45

assign /x offset 8085 = 53

assign /x offset 8086 = 49

assign /x offset 8087 = 44

assign /x offset 8088 = 45

assign /x offset 8089 = 4e

assign /x offset 8090 = 54

assign /x offset 8091 = ff

assign /x offset 8092 = 07

assign /x offset 8093 = 77

assign /x offset 8094 = b5

assign /x offset 8095 = 0b

assign /x offset 8096 = 11

assign /x offset 8097 = 01

assign /x offset 8098 = 01

assign /x offset 8099 = 01

assign /x offset 8100 = 02

assign /x offset 8101 = c2

assign /x offset 8102 = 33

assign /x offset 8103 = ff

assign /x offset 8104 = 02

assign /x offset 8105 = c1

assign /x offset 8106 = 0b


--//执行如上代码后,检查:

BBED> x /rnccntnnn dba 4,523 offset 8069

freespace[7923]                             @8069

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

flag@8069: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8070: 0x01

cols@8071:    8


col    0[3] @8072: 7839

col    1[4] @8076: KING

col    2[9] @8081: PRESIDENT

col    3[0] @8091: *NULL*

col    4[7] @8092: 1981-11-17 00:00:00

col    5[2] @8100: 5000

col    6[0] @8103: *NULL*

col    7[2] @8104: 10

--//OK正确.现在修改偏移量.


BBED> p dba 4,523 kdbr

sb2 kdbr[0]                                 @142      8026

sb2 kdbr[1]                                 @144      7983


--//注意修改kdbr行目录偏移是相对偏移要减去kbh的地址.当前块kdbh位于124.

BBED> map dba 4,523

 File: /mnt/ramdisk/book/users01.dbf (4)

 Block: 523                                   Dba:0x0100020b

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

 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0

 struct ktbbh, 96 bytes                     @20

 struct kdbh, 14 bytes                      @124

 struct kdbt[1], 4 bytes                    @138

 sb2 kdbr[2]                                @142

 ub1 freespace[7961]                        @146

 ub1 rowdata[81]                            @8107

 ub4 tailchk                                @8188

--//8069-124 = 7945,在修改前先设置原来的记录删除标志.

BBED> x /rnccntnnn dba 4,523 *kdbr[0]

rowdata[43]                                 @8150

-----------

flag@8150: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8151: 0x00

cols@8152:    8


col    0[3] @8153: 7369

col    1[5] @8157: SMITH

col    2[5] @8163: CLERK

col    3[3] @8169: 7902

col    4[7] @8173: 1980-12-17 00:00:00

col    5[2] @8181: 800

col    6[0] @8184: *NULL*

col    7[2] @8185: 20


assign offset 8150 flag=0x3c;

assign kdbr[0]=7945;


BBED> assign offset 8150 =0x3c;

ub1 rowdata[0]                              @8150     0x3c


BBED> assign kdbr[0]=7945;

sb2 kdbr[0]                                 @142      7945


BBED> x /rnccntnnn dba 4,523 *kdbr[0]

freespace[7923]                             @8069

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

flag@8069: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8070: 0x01

cols@8071:    8


col    0[3] @8072: 7839

col    1[4] @8076: KING

col    2[9] @8081: PRESIDENT

col    3[0] @8091: *NULL*

col    4[7] @8092: 1981-11-17 00:00:00

col    5[2] @8100: 5000

col    6[0] @8103: *NULL*

col    7[2] @8104: 10


BBED> assign offset 8070 =0x0;

ub1 freespace[0]                            @8070     0x00

--//取消原来记录的lock标志.


BBED> sum apply dba 4,523

Check value for File 4, Block 523:

current = 0xb55d, required = 0xb55d

--//ok现在已经修改完成.


BBED> verify dba 4,523

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 523


Block Checking: DBA = 16777739, Block Type = KTB-managed data block

data header at 0x2049e7c

kdbchk: bad row offset slot 0 offs 7945 fseo 7983 dtl 8168 bhs 104

Block 523 failed with check code 6135

--//昏一堆错误.


BBED> p kdbh

struct kdbh, 14 bytes                       @124

   ub1 kdbhflag                             @124      0x00 (NONE)

   sb1 kdbhntab                             @125      1

   sb2 kdbhnrow                             @126      2

   sb2 kdbhfrre                             @128     -1

   sb2 kdbhfsbo                             @130      22

   sb2 kdbhfseo                             @132      7983

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   

   sb2 kdbhavsp                             @134      7961

   sb2 kdbhtosp                             @136      7961


BBED> assign dba 4,523 kdbh.kdbhfseo=7945;

sb2 kdbhfseo                                @132      7945


BBED> sum apply dba 4,523

Check value for File 4, Block 523:

current = 0xb57b, required = 0xb57b


BBED> verify dba 4,523

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 523


--//OK.


SCOTT@book> alter system flush buffer_cache;

System altered.


SCOTT@book> select rowid,empy.* from empy;

ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

AAAWHyAAEAAAAILAAA       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

AAAWHyAAEAAAAILAAB       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30


--//AAAWHyAAEAAAAILAAA的EMPNO=7839.对应前面的情况记录已经修改.

SCOTT@book> select rowid,empy.* from empy;

ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

AAAWHyAAEAAAAILAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

AAAWHyAAEAAAAILAAB       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2305532/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2854
  • 访问量
    6641926