前天在客户现场遇到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脚本将asm块COPY来文件系统并修改
|
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这个脚本来实现。 下面是通过oracle来dump块，主要是为了验证脚本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 VOL5的29515这个块
下面将磁盘中的块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了。
bbed修改ASM中的块(最新版本)：等您坐沙发呢！