当前位置: 首页 > ASM, BBED > 正文

bbed修改ASM中的块(最新版本)

前天在客户现场遇到ASM中的一个数据块损坏,数据库打开后,在5分钟内,数据库就自动关闭了,由于特殊的原因,当时没有使用bbed来直接修改块,采用了其它的方式来正常打开数据库。

下面是测试10G环境中怎么修改ASM中的块,部分步骤在11G中仍然有效。

 

1,创建测试表

www.htz.pw > create table scott.htz tablespace users as select * from dba_objects;

 

Table created.

查询表区的信息

www.htz.pw > @extent.sql

Enter value for owner: scott

Enter value for segment_name: htz

Enter value for tablespace_name:

 

                      FILE                  BLOCK

OWNER:SEGMENT_NAME      ID   FNO  EXTENT_ID BEGIN_END    BLOCKS SUM_BLOCKS BYTES(KB)  SUM_BYTES

————————– —– ———- ——————- ———- ——— ———-

SCOTT.HTZ                4     4          0 57~64             8          8        64      65536

                         4     4          1 65~72             8         16        64     131072

                         4     4          2 73~80             8         24        64     196608

                         4     4          3 81~88             8         32        64     262144

                         4     4          4 89~96             8         40        64     327680

                         4     4          5 97~104            8         48        64     393216

                         4     4          6 105~112           8         56        64     458752

                         4     4          7 113~120           8         64        64     524288

                         4     4          8 121~128           8         72        64     589824

                         4     4          9 129~136           8         80        64     655360

                         4     4         10 137~144           8         88        64     720896

                         4     4         11 145~152           8         96        64     786432

                         4     4         12 153~160           8        104        64     851968

                         4     4         13 161~168           8        112        64     917504

                         4     4         14 169~176           8        120        64     983040

                         4     4         15 177~184           8        128        64    1048576

                         4     4         16 265~392         128        256      1024    2097152

                         4     4         17 393~520         128        384      1024    3145728

                         4     4         18 521~648         128        512      1024    4194304

                         4     4         19 649~776         128        640      1024    5242880

                         4     4         20 777~904         128        768      1024    6291456

                         4     4         21 1673~1800       128        896      1024    7340032

                         4     4         22 1801~1928       128       1024      1024    8388608

                         4     4         23 1929~2056       128       1152      1024    9437184

                         4     4         24 2057~2184       128       1280      1024   10485760

                         4     4         25 2185~2312       128       1408      1024   11534336

                         5     5         26 9~136           128        128      1024    1048576

                         5     5         27 137~264         128        256      1024    2097152

                         5     5         28 265~392         128        384      1024    3145728

                         5     5         29 393~520         128        512      1024    4194304

                         5     5         30 521~648         128        640      1024    5242880

                         5     5         31 649~776         128        768      1024    6291456

                         5     5         32 777~904         128        896      1024    7340032

                         5     5         33 905~1032        128       1024      1024    8388608

                         5     5         34 1033~1160       128       1152      1024    9437184

                         4     4         35 2313~2440       128       1536      1024   12582912

 

                      FILE                  BLOCK

OWNER:SEGMENT_NAME      ID   FNO  EXTENT_ID BEGIN_END    BLOCKS SUM_BLOCKS BYTES(KB)  SUM_BYTES

————————– —– ———- ——————- ———- ——— ———-

SCOTT.HTZ                4     4         36 2441~2568       128       1664      1024   13631488

*********************                                   ——-            ———

Total:                                                     2816                22528

这里通过extent脚本主要是想找到块是位置那个数据文件中的那一个extent,用于后面确认块在asm磁盘中的那个块上。

www.htz.pw > select  rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid)  block from scott.htz where owner=’SCOTT’;

 

ROWID                     FNO      BLOCK

—————— ———- ———-

AAAMpMAAEAAAAC1ABE          4        181

AAAMpMAAEAAAANLAAM          4        843

AAAMpMAAEAAAANLAAN          4        843

AAAMpMAAEAAAANLAAO          4        843

AAAMpMAAEAAAANLAAP          4        843

AAAMpMAAEAAAANLAAQ          4        843

AAAMpMAAEAAAANLAAR          4        843

AAAMpMAAEAAAAcDAAj          4       1795

AAAMpMAAEAAAAiSAAm          4       2194

AAAMpMAAEAAAAiSAAn          4       2194

AAAMpMAAEAAAAiSAAo          4       2194

AAAMpMAAEAAAAiSAAp          4       2194

AAAMpMAAEAAAAiSAAq          4       2194

AAAMpMAAEAAAAiSAAr          4       2194

这里我们以843这个块为例来操作。修改AAAMpMAAEAAAANLAAO 这行中的owner值,将SCOTT改修改为HUANG

查看rowid的信息

www.htz.pw > @rowid_to_info.sql

Enter value for rowid: AAAMpMAAEAAAANLAAO

ROWID_TYPE:  1

OBJECT_NUMBER:  51788

RELATIVE_FNO:  4

BLOCK_NUMBER:  843

ROW_NUMBER:  14 bbed修改的时候要用到

 

PL/SQL procedure successfully completed.

 

 

查询数据文件的路径

www.htz.pw > /

 

     FILE# NAME

———- ———————————————–

         4 +DATA_E/asm19g/datafile/users.262.853485885

         3 +DATA_E/asm19g/datafile/sysaux.264.853485883

         2 +DATA_E/asm19g/datafile/undotbs1.263.853485885

         1 +DATA_E/asm19g/datafile/system.265.853485883

2,使用copy脚本将asmCOPY来文件系统并修改

www.htz.pw > @asm_copy_block_to_file.sql

Enter value for asm_datafile_name: +DATA_E/asm19g/datafile/users.262.853485885

Enter value for block_id_in_datafile: 843

Enter value for number_of_blocks: 1

Enter value for filesystem_filename: /tmp/843.dmp

 

PL/SQL procedure successfully completed.

这里直接实现的asm_copy_block_to_file.sql这个脚本来实现。

下面是通过oracledump块,主要是为了验证脚本COPY出来的块是否正确

www.htz.pw > alter system dump datafile 4 block 843;

 

System altered.

 

www.htz.pw > oradebug setmypid

Statement processed.

www.htz.pw > oradebug tracefile_name;

/oracle/app/oracle/admin/asm19g/udump/asm19g_ora_4363.trc

 

 

[oracle@www.htz.pw sql]$ls -l /tmp/843.dmp

-rw-r—– 1 oracle dba 16384 Jul 21 10:40 /tmp/843.dmp

 

Block header dump:  0x0100034b

 Object id on Block? Y

 seg/obj: 0xca4c  csc: 0x00.61125  itc: 3  flg: E  typ: 1 – DATA

     brn: 0  bdba: 0x100030a ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.00061125

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x03   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

 

data_block_dump,data header at 0x116d9a7c

===============

tsiz: 0x1f80   

hsiz: 0x8a

pbl: 0x116d9a7c

bdba: 0x0100034b

     76543210  

flag=——–  

ntab=1

nrow=60

frre=-1

fsbo=0x8a

fseo=0x4a5

avsp=0x41b

tosp=0x41b

 

下面使用bbed来修改指定的行的内容

 

BBED> x /rccn *kdbr[14]

rowdata[5498]                               @6811   

————-

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

lock@6812: 0x00

cols@6813:   13

 

col    0[5] @6814: SCOTT

col    1[3] @6820: EMP

col    2[0] @6824: *NULL*

col    3[4] @6825: 51575

col    4[4] @6830: 51575

 

 

BBED> set offset 6815这里是6814加上长度就可以了

        OFFSET          6815

 

BBED> set count 10

        COUNT           10

 

BBED> dump

 File: /tmp/843.dmp (0)

 Block: 1                Offsets: 6815 to 6824           Dba:0x00000000

————————————————————————

 53434f54 5403454d 50ff

 

 <32 bytes per line>

 

 

BBED> modify /x 4855414e(select dump(‘HUANG’,16) from dual;可以得到值)

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /tmp/843.dmp (0)

 Block: 1                Offsets: 6815 to 6824           Dba:0x00000000

————————————————————————

 4855414e 5403454d 50ff

 

 <32 bytes per line>

 

BBED> set offset +4

        OFFSET          6819

 

BBED> modify /x 47

 File: /tmp/843.dmp (0)

 Block: 1                Offsets: 6819 to 6828           Dba:0x00000000

————————————————————————

 4703454d 50ff04c3 0610

 

 <32 bytes per line>

 

BBED> dump offset 6815

 File: /tmp/843.dmp (0)

 Block: 1                Offsets: 6815 to 6824           Dba:0x00000000

————————————————————————

 4855414e 4703454d 50ff

 

 <32 bytes per line>

 

 

BBED> dump /v

 File: /tmp/843.dmp (0)

 Block: 1       Offsets: 6815 to 6824  Dba:0x00000000

——————————————————-

 4855414e 4703454d 50ff              l HUANG.EMP.

 

 <16 bytes per line>

 

 

BBED> x /rccn *kdbr[14]

rowdata[5498]                               @6811   

————-

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

lock@6812: 0x00

cols@6813:   13

 

col    0[5] @6814: HUANG

col    1[3] @6820: EMP

col    2[0] @6824: *NULL*

col    3[4] @6825: 51575

col    4[4] @6830: 51575

 

 

BBED> sum apply

Check value for File 0, Block 1:

current = 0x076e, required = 0x076e

 

BBED> verify

DBVERIFY – Verification starting

FILE = /tmp/843.dmp

BLOCK = 1

 

Block 1 is corrupt

Corrupt block relative dba: 0x01000001 (file 0, block 1)

Bad header found during verification

Data in bad block:

 type: 6 format: 2 rdba: 0x0100034b

 last change scn: 0x0000.0006116f seq: 0x2 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x116f0602

 check value in block header: 0x76e

 computed block checksum: 0x0

这里看到verify报错是因为rdba地址与计算的地址不一致导致的,如果是坏块,这里建议先修改rdba地址为01000001后,再修改verify,用于验证其它地方是否有报错,无报错手,再将rdba地址修改回来。

 

DBVERIFY – Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 0

Total Blocks Failing   (Data) : 0

Total Blocks Processed (Index): 0

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 1

Total Blocks Influx           : 0

 

使用asm_copy_block_to_asm.sql将修改的块copy到数据文件中

这里在copy之前建议将数据文件offline,防止修改数据块。

www.htz.pw > @asm_copy_block_to_asm.sql

Enter value for filesystem_filename: /tmp/843.dmp

Enter value for asm_datafile_name: +DATA_E/asm19g/datafile/users.262.853485885

Enter value for block_id_in_datafile: 843

 

PL/SQL procedure successfully completed.

 

 

www.htz.pw >  select  rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid)  block from scott.htz where owner=‘HUANG’;

 

ROWID                     FNO      BLOCK

—————— ———- ———-

AAAMpMAAEAAAANLAAO          4        843

这里看到已经从SCOTT修改到HUANG

3,使用dd来实现COPY功能

使用dd将数据块COPY来文件系统,我们需要构建一个OS块,关于OS块的构建,方法很简单,可以直接从其它的地方DD一个块过来就可以了,这里我实现使用之前的那个OS

 

下面测试是将HUANG更改为ZHONG

查找843这个块公交车

www.htz.pw > @asm_block_by_datafile_block.sql                      

Enter value for block: 843                                         

Enter value for file_number: 262                                   

Enter value for file_type: datafile                                

Enter value for filename:                                          

                                                                   

DATAFILE                       DISK_NUMBER                         

NUMBER_NAME                    DISK_NMAE                 DISK_BLOCK

—————————— ————————- ———-

262.USERS.262.853485885        3.VOL5                         29515

此脚本只适用10G数据库,不适用11G的环境

这里可以看到843这个块位于DISK VOL529515这个块

 

下面将磁盘中的块dd出来跟之前ASM复制出来的块进行对比

[oracle@www.htz.pw sql]$./asm_find_asmlib_disk_by_kfed.sh

ASMLIB disk name: /dev/oracleasm/disks/VOL1

ASM disk name:

Device path: /dev/sdc1

ASMLIB disk name: /dev/oracleasm/disks/VOL2

ASM disk name:

Device path: /dev/sdc2

ASMLIB disk name: /dev/oracleasm/disks/VOL3

ASM disk name:

Device path: /dev/sdc3

ASMLIB disk name: /dev/oracleasm/disks/VOL5

ASM disk name:

Device path: /dev/sdc5

ASMLIB disk name: /dev/oracleasm/disks/VOL6

ASM disk name:

Device path: /dev/sdc6

ASMLIB disk name: /dev/oracleasm/disks/VOL7

ASM disk name:

Device path: /dev/sdc7

ASMLIB disk name: /dev/oracleasm/disks/VOL8

ASM disk name:

Device path: /dev/sdc8

ASMLIB disk name: /dev/oracleasm/disks/VOL9

ASM disk name:

Device path: /dev/sdc9

 

[root@www.htz.pw ~]#dd if=/dev/sdc5 bs=8192 skip=29515 count=1 |od -x|more

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 0.034031 seconds, 241 kB/s

0000000 a206 0000 034b 0100 116f 0006 0000 0402

0000020 076e 0000 0001 0000 ca4c 0000 1125 0006

0000040 0000 0000 0003 0032 030a 0100 ffff 0000

0000060 0000 0000 0000 0000 0000 0000 8000 0000

0000100 1125 0006 0000 0000 0000 0000 0000 0000

0000120 0000 0000 0000 0000 0000 0000 0000 0000

*

0000160 0000 0000 0000 0000 0000 0000 0100 003c

0000200 ffff 008a 04a5 041b 041b 0000 003c 1f1c

0000220 1ebf 1e59 1dfb 1d93 1d32 1cdd 1c7d 1c1e

0000240 1bbe 1b64 1b0e 1abc 1a6d 1a1f 19ce 197e

0000260 192b 18ad 1832 17ba 173b 16c1 164d 15ca

0000300 1549 14c0 143c 13b7 1340 12c8 124c 11d2

0000320 1158 10d6 104c 0fc9 0f53 0ed0 0e48 0dc1

0000340 0d49 0cce 0c59 0bde 0b66 0aec 0a6f 09f9

0000360 0980 090e 0899 081f 07a0 0726 06a9 0627

0000400 05a2 0525 04a5 0000 0000 0000 0000 0000

0000420 0000 0000 0000 0000 0000 0000 0000 0000

 

 

[root@www.htz.pw ~]#od -x /tmp/843.dmp |more

0000000 a200 0000 0000 ffc0 0000 0000 0000 0000

0000020 fa67 0000 2000 0000 0001 0000 7c7d 7a7b

0000040 81a0 0000 0000 0000 0000 0000 0000 0000

0000060 0000 0000 0000 0000 0000 0000 0000 0000

*

0020000 a206 0000 034b 0100 116f 0006 0000 0402

0020020 076e 0000 0001 0000 ca4c 0000 1125 0006

0020040 0000 0000 0003 0032 030a 0100 ffff 0000

0020060 0000 0000 0000 0000 0000 0000 8000 0000

0020100 1125 0006 0000 0000 0000 0000 0000 0000

0020120 0000 0000 0000 0000 0000 0000 0000 0000

*

0020160 0000 0000 0000 0000 0000 0000 0100 003c

0020200 ffff 008a 04a5 041b 041b 0000 003c 1f1c

0020220 1ebf 1e59 1dfb 1d93 1d32 1cdd 1c7d 1c1e

0020240 1bbe 1b64 1b0e 1abc 1a6d 1a1f 19ce 197e

0020260 192b 18ad 1832 17ba 173b 16c1 164d 15ca

0020300 1549 14c0 143c 13b7 1340 12c8 124c 11d2

0020320 1158 10d6 104c 0fc9 0f53 0ed0 0e48 0dc1

0020340 0d49 0cce 0c59 0bde 0b66 0aec 0a6f 09f9

0020360 0980 090e 0899 081f 07a0 0726 06a9 0627

0020400 05a2 0525 04a5 0000 0000 0000 0000 0000

0020420 0000 0000 0000 0000 0000 0000 0000 0000

这里我们只需要对比dba地址就可以了。

 

[root@www.htz.pw ~]#dd if=/dev/sdc5 of=/tmp/843.dmp skip=29515 bs=8192 count=1 seek=1

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 4.1e-05 seconds, 200 MB/s

 

bbed修改块的内容

BBED>  x /rcc *kdbr[14]

rowdata[5498]                               @6811   

————-

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

lock@6812: 0x00

cols@6813:   13

 

col    0[5] @6814: HUANG

col    1[3] @6820: EMP

col    2[0] @6824: *NULL*

col    3[4] @6825: .L

col    4[4] @6830: .L

col    5[5] @6835: TABLE

col    6[7] @6841: xn…+.

col    7[7] @6849: xn…+.

col   8[19] @6857: 2010-04-20:08:42:07

col    9[5] @6877: VALID

col   10[1] @6883: N

col   11[1] @6885: N

col   12[1] @6887: N

 

 

BBED> set mode edit

        MODE            Edit

 

BBED> modify /x 5a484f4e

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) 

BBED-00311: unable to edit without a BIFILE

 

 

BBED> set count 10

BBED> modify /x 5a484f4e

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /tmp/843.dmp (0)

 Block: 1                Offsets: 6815 to 7326           Dba:0x00000000

————————————————————————

 5a484f4e 4703454d 50ff04c3 06104c04 c306104c 05544142 4c450778 6e041409

BBED> set offset +4

        OFFSET          6819

 

BBED> set count 10

        COUNT           10

 

BBED> modify /x 47

 File: /tmp/843.dmp (0)

 Block: 1                Offsets: 6819 to 6828           Dba:0x00000000

————————————————————————

 4703454d 50ff04c3 0610

 

 <32 bytes per line>

 

BBED>

 

 

 

BBED> sum apply

Check value for File 0, Block 1:

current = 0x1b73, required = 0x1b73

 

BBED> verify

DBVERIFY – Verification starting

FILE = /tmp/843.dmp

BLOCK = 1

 

Block 1 is corrupt

Corrupt block relative dba: 0x01000001 (file 0, block 1)

Bad header found during verification

Data in bad block:

 type: 6 format: 2 rdba: 0x0100034b

 last change scn: 0x0000.0006116f seq: 0x2 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x116f0602

 check value in block header: 0x1b73

 computed block checksum: 0x0

 

 

DBVERIFY – Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 0

Total Blocks Failing   (Data) : 0

Total Blocks Processed (Index): 0

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 1

Total Blocks Influx           : 0

 

 

[root@www.htz.pw ~]#dd if=/tmp/843.dmp of=/dev/sdc5 skip=1 bs=8192 count=1 seek=29515 conv=notrunc

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 6.2e-05 seconds, 132 MB/s

这里千万要记住,conv=notrunc,不然后面的数据就被切断了,丢失了。

已经看到数据了

 

www.htz.pw > select  rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid)  block from scott.htz where owner=’ZHONG’;

 

ROWID                     FNO      BLOCK

—————— ———- ———-

AAAMpMAAEAAAANLAAO          4        843

这里已经看到数据已经从HUANG更改到ZHONG了。

本文固定链接: http://www.htz.pw/2014/08/18/bbed%e4%bf%ae%e6%94%b9asm%e4%b8%ad%e7%9a%84%e5%9d%97%e6%9c%80%e6%96%b0%e7%89%88%e6%9c%ac.html | 认真就输

该日志由 huangtingzhong 于2014年08月18日发表在 ASM, BBED 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: bbed修改ASM中的块(最新版本) | 认真就输
关键字: ,