sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle遇到未格式化的坏块的解决办法

时间:2016-12-19 10:10   来源:Oracle研究中心   作者:网络   点击:

Oracle研究中心案例分析:运维DBA反映Oracle RAC在做迁移时遇到未格式化的坏块,结合案例通过BBED+DD和alter table xxx allocate extent来解决。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 如何修复未格式化的坏块?

最近需要迁移的3套rac数据中,检查发现都出现了坏块,而且部分坏块是未格式化的坏块,例如:

*** SESSION ID:(1439.509) 2012-11-06 11:15:42.559
Start dump data blocks tsn: 7 file#: 34 minblk 3819968 maxblk 3819968
buffer tsn: 7 rdba: 0x24cc49c0 (147/805312)
scn: 0x001a.0e76f311 seq: 0x01 flg: 0x04 tail: 0xf3110601
frmt: 0x02 chkval: 0xf5c7 type: 0x06=trans data
Hex dump of corrupt header 4 = CORRUPT
Dump of memory from 0x000000011044D000 to 0x000000011044D014
11044D000 06A20000 24CC49C0 0E76F311 001A0104  [....$.I..v......]
11044D010 F5C70000                             [....]           
Hex dump of corrupt block
Dump of memory from 0x000000011044D014 to 0x000000011044EFFC
11044D010          01000000 0000D71A 0E76F311      [.........v..]
11044D020 001A2484 00023200 24CC4492 0014000D  [..$...2.$.D.....]
11044D030 013EBCB7 015D1FF9 9BB43300 8000001A  [.>...]....3.....]
......省略部分内容
11044EFD0 3D02C102 2C000306 C519232C 591706C5  [=...,.....#,Y...]
11044EFE0 18285F04 3102C102 2C000306 C519232C  [.(_.1...,.....#,]
11044EFF0 591606C5 18285F04 3002C102           [Y....(_.0...]   
End dump data blocks tsn: 7 file#: 34 minblk 3819968 maxblk 3819968

可以看到该坏块数据是写紊乱了,将file 147 block 805315的数据写入到file 34 block 3819968 中了。
通过如下sql查询可以发现属于未格式化的坏块,不属于任何对象:

select segment_name, segment_type, owner
  from dba_extents
where file_id = < Absolute file number >
   and < corrupted block
number > between block_id and block_id + blocks - 1;

select *
  from dba_free_space
where file_id = < Absolute file number >
   and < corrupted block
number > between block_id and block_id + blocks - 1;

虽然未格式化的坏块可能不会存在什么危险,在后面有业务数据插入时会进行将其格式化掉,但是由于坏块的存在,导致rman备份无法成功,进而也就影响到我们这次的迁移测试了。

我这里利用vmware环境来模拟该问题,并给出解决方案,供大家参考!

—-方法1 利用alter table xxx allocate extent

SQL> l
  1* SELECT file#,name,bytes/1024/1024 FROM v$datafile ORDER BY 1
SQL> /

     FILE# NAME                                                         BYTES/1024/1024
---------- ------------------------------------------------------------ ---------------
         1 /home/ora10g/oradata/roger/system01.dbf                                  770
         2 /home/ora10g/oradata/roger/undotbs01.dbf                                 640
         3 /home/ora10g/oradata/roger/sysaux01.dbf                                  320
         4 /home/ora10g/oradata/roger/users01.dbf                                298.75
         5 /home/ora10g/oradata/roger/roger01.dbf                                   100
         6 /home/ora10g/oradata/roger/undotb2_01.dbf                                  1
         7 /home/ora10g/oradata/roger/test1.dbf                                      10
         8 /home/ora10g/oradata/roger/sqlt_01.dbf                                    80
         9 /home/ora10g/oradata/roger/undotbs03.dbf                                 500

9 ROWS selected.

SQL> SELECT file_id,BLOCK_ID,BLOCKS FROM dba_free_space WHERE file_id=5;

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         5       1009         24
         5       2185      10616

SQL> SELECT * FROM (SELECT OWNER,SEGMENT_NAME,FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS FROM dba_extents WHERE 
  2  file_id=5 ORDER BY 4 DESC) WHERE rownum < 5;

OWNER       SEGMENT_NAME         FILE_ID  EXTENT_ID   BLOCK_ID     BLOCKS
----------- ----------------- ---------- ---------- ---------- ----------
GGS         GGS_DDL_HIST               5         23       2057        128
GGS         GGS_DDL_HIST               5         22       1929        128
GGS         GGS_DDL_HIST               5         21       1801        128
GGS         GGS_DDL_HIST               5         20       1673        128

从上面信息可以看出,目前datafile 5中所分配的最大block号为2057+128-1=2184,换句话说以后的block都是空块。
我这里利用bbed来制造一个未格式化的坏块,也就是尚未利用的坏块(可能是已经分配但没有数据).

这里利用bbed随便修改一下,制造一个坏块如下:

[ora10g@killdb ~]$ dbv file=/home/ora10g/oradata/roger/roger01.dbf blocksize=8192 start=2000 end=2500

DBVERIFY: Release 10.2.0.5.0 - Production on Wed Nov 7 04:18:43 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger01.dbf
Page 2200 is marked corrupt
Corrupt block relative dba: 0x01400898 (file 5, block 2200)
Bad header found during dbv:
Data in bad block:
type: 58 format: 2 rdba: 0x01400bb8
last change scn: 0x0000.004d307d seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x307d3a01
check value in block header: 0x968f
block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined         : 501
Total Pages Processed (Data) : 121
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 379
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 5374889 (0.5374889)

下面我们来想办法格式化这个未格式化的坏块:

SQL> conn roger/roger
Connected.

SQL> CREATE TABLE test_block AS SELECT * FROM dba_objects WHERE 1=2;
TABLE created.

SQL> SELECT SUM(bytes/1024/1024) FROM dba_free_space WHERE file_id=5;
SUM(BYTES/1024/1024)
--------------------
              83.125

SQL> SELECT blocks FROM v$datafile WHERE file#=5;

    BLOCKS
----------
     12800

SQL> SELECT (12800-2200)*8192/1024/1024 FROM dual;

(12800-2200)*8192/1024/1024
---------------------------
                    82.8125

SQL> ALTER TABLE test_block move tablespace roger;
TABLE altered.

SQL> ALTER TABLE test_block allocate extent(datafile '/home/ora10g/oradata/roger/roger01.dbf' SIZE 10m);
TABLE altered.

SQL> ALTER TABLE test_block nologging;
TABLE altered.

SQL> INSERT INTO test_block SELECT * FROM dba_objects;
51079 ROWS created.

SQL> /
51079 ROWS created.

SQL> /
51079 ROWS created.

SQL> /
51079 ROWS created.

SQL> /
51079 ROWS created.

SQL> commit;
Commit complete.

SQL> ALTER system checkpoint;
System altered.

SQL> ALTER system switch logfile;
System altered.

SQL> /
System altered.

SQL> l
  1  SELECT owner,SEGMENT_NAME,EXTENT_ID,BLOCK_ID,BLOCKS
  2* FROM dba_extents WHERE SEGMENT_NAME='TEST_BLOCK' ORDER BY 3
SQL> /

OWNER          SEGMENT_NAME     EXTENT_ID   BLOCK_ID     BLOCKS
-------------- --------------- ---------- ---------- ----------
ROGER          TEST_BLOCK               0       1009          8
ROGER          TEST_BLOCK               1       2185        128
ROGER          TEST_BLOCK               2       2313        128
ROGER          TEST_BLOCK               3       2441        128
ROGER          TEST_BLOCK               4       2569        128
ROGER          TEST_BLOCK               5       2697        128
ROGER          TEST_BLOCK               6       2825        128
ROGER          TEST_BLOCK               7       2953        128
ROGER          TEST_BLOCK               8       3081        128
ROGER          TEST_BLOCK               9       3209        128
ROGER          TEST_BLOCK              10       3337        128
ROGER          TEST_BLOCK              11       3465        128
ROGER          TEST_BLOCK              12       3593        128
ROGER          TEST_BLOCK              13       3721        128
ROGER          TEST_BLOCK              14       3849        128
ROGER          TEST_BLOCK              15       3977        128
ROGER          TEST_BLOCK              16       4105        128
ROGER          TEST_BLOCK              17       4233        128
ROGER          TEST_BLOCK              18Oracleoracleplus.net       4361        128
ROGER          TEST_BLOCK              19       4489        128
ROGER          TEST_BLOCK              20       4617        128
ROGER          TEST_BLOCK              21       4745        128
ROGER          TEST_BLOCK              22       4873        128
ROGER          TEST_BLOCK              23       5001        128
ROGER          TEST_BLOCK              24       5129        128
ROGER          TEST_BLOCK              25       5257        128
ROGER          TEST_BLOCK              26       5385        128
ROGER          TEST_BLOCK              27       5513        128
ROGER          TEST_BLOCK              28       5641        128

29 ROWS selected.
可以看到2200这个坏块已经包含在extent里面了,此时,我们来dbv检查一下。

SQL> !
[ora10g@killdb ~]$  dbv file=/home/ora10g/oradata/roger/roger01.dbf blocksize=8192 start=2000 end=2500

DBVERIFY: Release 10.2.0.5.0 - Production on Wed Nov 7 04:30:00 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger01.dbf
DBVERIFY - Verification complete

Total Pages Examined         : 501
Total Pages Processed (Data) : 431
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 70
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 5519642 (0.5519642)

我们可以看到,该未格式化的坏块已经被修复了,最后我们再将创建的临时表删除即可。
[ora10g@killdb ~]$ exit
exit

SQL> drop table test_block purge;
Table dropped.


—-方法2 利用dd+bbed修复

先制造一个未格式化的坏块:
BBED> set file 5 block 2500
        FILE#           5
        BLOCK#          2500

BBED> modify /x 100 offset 14
Warning: contents of previous BIFILE will be lost. Proceed  (Y/N) y
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 2500             Offsets:   14 to   33           Dba:0x014009c4
------------------------------------------------------------------------
0100a0e4 00000100 000011e5 00001639 54000000

<32 bytes per line>

BBED> sum apply
Check value for File 5, Block 2500:
current = 0xe4a0, required = 0xe4a0

BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 2500

Block 2500 is corrupt
Corrupt block relative dba: 0x014009c4 (file 0, block 2500)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x014009c4
last change scn: 0x0000.00543916 seq: 0x1 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x39160604
check value in block header: 0xe4a0
block checksum disabled


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           : 2

[ora10g@killdb ~]$ dbv file=/home/ora10g/oradata/roger/roger01.dbf start=2000 end=3000

DBVERIFY: Release 10.2.0.5.0 - Production on Wed Nov 7 04:49:28 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger01.dbf
Page 2500 is influx - most likely media corrupt
Corrupt block relative dba: 0x014009c4 (file 5, block 2500)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x014009c4
last change scn: 0x0000.00543916 seq: 0x1 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x39160604
check value in block header: 0xe4a0
block checksum disable

DBVERIFY - Verification complete

Total Pages Examined         : 1001
Total Pages Processed (Data) : 922
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 78
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Highest block SCN            : 5519647 (0.5519647)

下面我们使用dd来复制一个正常的未格式化block,将该坏块替换掉。

[ora10g@killdb ~]$ dd if=/home/ora10g/oradata/roger/roger01.dbf   of=/tmp/dd_block skip=2502 bs=8192 count=1
1+0 records in
1+0 records out
[ora10g@killdb ~]$
[ora10g@killdb ~]$ dd if=/tmp/dd_block  of=/home/ora10g/oradata/roger/roger01.dbf seek=2500 bs=8192 count=1 conv=notrunc
1+0 records in
1+0 records out
[ora10g@killdb ~]$

很显然,我们copy一个block过来替换以后,还不行,还得进行修改,此时你dbv检查可以看到仍然是坏块,如下:

[ora10g@killdb ~]$ dbv file=/home/ora10g/oradata/roger/roger01.dbf start=2000 end=3000

DBVERIFY: Release 10.2.0.5.0 - Production on Wed Nov 7 04:51:34 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger01.dbf
Page 2500 is marked corrupt
Corrupt block relative dba: 0x014009c4 (file 5, block 2500)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x014009c6
last change scn: 0x0000.00543916 seq: 0x4 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x39160604
check value in block header: 0xfe93
computed block checksum: 0x0

DBVERIFY - Verification complete

Total Pages Examined         : 1001
Total Pages Processed (Data) : 922
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 78
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 5519647 (0.5519647)

下面我们利用bbed再进行适当修改即可,如下:

BBED> set file 5 block 2501
        FILE#           5
        BLOCK#          2501

BBED> d /v count 20
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 2501    Offsets:    0 to   19  Dba:0x014009c5
-------------------------------------------------------
06a20000 c5094001 16395400 00000404 l ..@..9T.....
d52b0000                            l ..

<16 bytes per line>

BBED> set file 5 block 2500
        FILE#           5
        BLOCK#          2500

BBED> modify /x c4 offset 4
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 2500             Offsets:    4 to   33           Dba:0x014009c4
------------------------------------------------------------------------
c4094001 16395400 00000404 93fe0000 01000000 11e50000 16395400 0000

<32 bytes per line>

BBED> sum apply
Check value for File 5, Block 2500:
current = 0xfe91, required = 0xfe91

BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 2500

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

[ora10g@killdb ~]$ dbv file=/home/ora10g/oradata/roger/roger01.dbf start=2000 end=3000

DBVERIFY: Release 10.2.0.5.0 - Production on Wed Nov 7 05:13:29 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 1001
Total Pages Processed (Data) : 923
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 78
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 5519647 (0.5519647)
我们可以看到成功修复了该坏块。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle遇到未格式化的坏块的解决办法

原文唯一网址:http://www.oracleplus.net/arch/1413.html

Oracle研究中心

关键词:

Oracle坏块

Oracle坏块的解决办法

Oracle坏块未格式化的原因