数据库坏块，还有什么不懂？

Oracle 作者： 数据与人 2020-06-20 20:05 1,498 浏览 评论

一、数据块检查


坏块检查
可以通过RMAN来检查数据文件是否存在坏块：

针对整个数据库

  • Rman> backup validate check logical database ;

针对特定的数据文件

  • Rman> backup validate check logical datafile ;

完成以后查询视图

  • SQL>Select * from v$database_block_corruption ;

或者可以通过dbv工具检查坏块：

  • $ dbv userid={system/password} file={full path filename} logfile={output filename}

二、检查坏块是否属于某个对象

针对少量坏块

查询dba_extents并复核坏块不属于任何对象：

  • 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;

如果坏块不属于任何对象，复核一下这个块是否存在于
dbafreespace：

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

针对大量坏块

通过以下查询语句句来判断块是否为空块或者被使用：

set lines 200 pages 10000
col segment_name format a30

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#, 
greatest(e.block_id, c.block#) corr_start_block#, 
least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#, 
least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted, 
null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#, 
header_block corr_start_block#, 
header_block corr_end_block#, 
1 blocks_corrupted, 
'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#, 
greatest(f.block_id, c.block#) corr_start_block#, 
least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#, 
least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted, 
'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;


三、数据块修复

修复空坏块

如果rman在读到坏块的时候不会报错，则可以通过rman来修复坏块
对包含坏块数据文件进行一次rman备份来确定该方法适用：

RMAN> backup check logical datafile 7 format '/oradata/backup/%U' tag 'CORRUPT_BLK_FILE_BKP';
Starting backup at 21-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oradata/ora11gR2/demo01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAY-12
channel ORA_DISK_1: finished piece 1 at 21-MAY-12
piece handle=/oradata/backup/1jnbhl5c_1_1 tag=CORRUPT_BLK_FILE_BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAY-12

确保备份空间充足，可以使用format子句指定备份的位置。
另外，即使备份成功，也要检查确认备份中不存坏块：

SQL> select BP.HANDLE, BP.COMPLETION_TIME, BC.FILE#, BC.BLOCK#, BC.BLOCKS, BC.MARKED_CORRUPT, BC.CORRUPTION_TYPE  2  
       from V$BACKUP_PIECE BP, V$BACKUP_CORRUPTION BC  3  
      where BP.SET_COUNT = BC.SET_COUNT 
        and  4        BP.SET_STAMP = BC.SET_STAMP 
        and  5        BP.TAG = 'CORRUPT_BLK_FILE_BKP';
        
no rows selected

如果该查询有返回结果，则无法使用rman修复空坏块。
如果没有返回结果，表示rman的优化算法跳过了这些没有被使用的块，则当使用这个备份片恢复数据文件时，这些块会被格式化，可以通过以下步骤修复坏块：

将数据文件还原到别的位置：

RMAN> run {
2> set newname for datafile 7 to '/oradata/ora11gR2/demo01_RESTORED.dbf';
3> restore datafile 7 from tag 'CORRUPT_BLK_FILE_BKP';
4> }

executing command: SET NEWNAME

Starting restore at 21-MAY-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oradata/ora11gR2/demo01_RESTORED.dbf
channel ORA_DISK_1: reading from backup piece /oradata/backup/1jnbhl5c_1_1
channel ORA_DISK_1: piece handle=/oradata/backup/1jnbhl5c_1_1 tag=CORRUPT_BLK_FILE_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03F

inished restore at 21-MAY-12

使用dbv来验证还原出的文件没有坏块：

$ dbv file=/oradata/ora11gR2/demo01_RESTORED.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Mon May 21 12:27:21 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oradata/ora11gR2/demo01_RESTORED.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 12799
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 775154 (0.775154)

使用BLOCKRECOVER命令来修复坏块

这个命令将使用还原出的数据文件中的格式化过的空块来修复空坏块：

RMAN> blockrecover datafile 7 block 150 FROM DATAFILECOPY;

Starting recover at 21-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring block(s) from datafile copy /oradata/ora11gR2/demo01_RESTORED.dbf

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 21-MAY-12

运行dbv命令来验证原先的数据文件已经没有坏块：


[oracle@vmOraLinux6 ~]$ dbv file=/oradata/ora11gR2/demo01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Mon May 21 12:30:15 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata/ora11gR2/demo01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 356
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 152
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 12292
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 775154 (0.775154)

另外，也可以使用rman来验证：

RMAN> backup validate check logical datafile 7;

Starting backup at 21-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oradata/ora11gR2/demo01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              12292        12801           775154  
File Name: /oradata/ora11gR2/demo01.dbf  
Block Type Blocks Failing Blocks Processed  
---------- -------------- ----------------  
Data       0              356  
Index      0              0  
Other      0              152

Finished backup at 21-MAY-12

如果无法使用rman修复，则可以使用如下方法修复

使用sys或system用户创建一张表，可以使用nologging选项避免生成日志，使用pctfree 99来加速格式化：

SQL> create table s 
( n number,       
c varchar2(4000)     
) nologging tablespace <tablespace name having the corrupt block> pctfree 99;

验证这个表被创建在了正确的表空间：

SQL> select segment_name,tablespace_name 
       from user_segments      
      where segment_name='S' ;

如果

deferred_segment_creation=true，

则查询dba_tables：

SQL>select table_name,tablespace_name 
      from user_tables     
     where table_name='S' ;

创建⼀个触发器，当坏块被重用时会抛出⼀个异常:

1、显示输入blocknumber时，输入坏块号，
2、显示输入filenumber时，输入坏块所在数据文件的文件号 (rfile# value from v$datafile)

CREATE OR REPLACE TRIGGER corrupt_trigger   
   AFTER INSERT ON s   
   REFERENCING OLD AS p_old NEW AS new_p   
   FOR EACH ROW 
DECLARE   
   corrupt EXCEPTION; 
BEGIN   
   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber) 
   and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN      
   RAISE corrupt;   
  END IF; 
EXCEPTION   
  WHEN corrupt THEN      
    RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted'); 
END;
/

通过dba_free_space查询空坏块extent的大小

SQL> Select BYTES 
       from dba_free_space 
     where file_id=<file no> 
     and <corrupt block no> between block_id 
     and block_id + blocks -1;
     
     BYTES
     ----------------  
     65536

这里是64k，则分配一个64k的extent：

SQL> alter table s
allocate extent (DATAFILE 'E:\xxxx\test.ORA' SIZE 64K);

若为64k的n倍，则使用这个循环语句来分配extent：

BEGIN
for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table s allocate extent (DATAFILE '||'''E:\xxxx\test.ORA''' ||'SIZE 64K) ';
end loop;
end ;
/

不断的分配，直到坏块被包含在s表中。可以通过以下查询来验证：

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

注意以下几点：
1、如果表空间是ASSM的，则可能需要多次执行分配空间的命令或使用多张表；
2、建议将autoextend设置为off；
3、如果数据库版本为10.2.0.4/11.1.0.7，在ASSM的表空房间手工分配空间，会触发Bug 6647480

向s表中插入数据，当有数据被插入到坏块时，触发器会被触发：

Begin  
  FOR i IN 1..1000000000 loop    
    for j IN 1..1000 loop      
      Insert into s VALUES(i,'x');    
    end loop;    
    commit;  
  END LOOP;
END;

使用rman来验证坏块已经被修复：略

删除刚刚使用的表：

SQL> DROP TABLE s ;

切换几次日志，并做一次checkpoint：

SQL>Alter system switch logfile ;  --> 执⾏多次
SQL>Alter system checkpoint ;

最后，删除触发器器：

SQL> DROP trigger corrupt_trigger ;

四、修复坏数据块

坏块属于索引

如果坏块属于索引，则删除并重新创建索引即可：

SQL> DROP index <index_name> ;
SQL> CREATE index <index_name> ON ......;

坏块属于表

当查询全表时不会报错，则可以通过以下方法修复坏块：

1、shrink这张表；
2、在同⼀个表空间move这张表；
3、将这张表rename，然后CTAS这张表(create table as select)

如果查询全表时报错，则需要使⽤用DBMS_REPAIR包来修复，修复用的脚本如下：

REM Create the repair table in a given tablespace:

BEGIN  DBMS_REPAIR.ADMIN_TABLES (  
  TABLE_NAME => 'REPAIR_TABLE',  
  TABLE_TYPE => dbms_repair.repair_table,  
  ACTION => dbms_repair.create_action,  
  TABLESPACE => '&tablespace_name');
END;/

REM Identify corrupted blocks for schema.object (it also can be done at partition level with parameter PARTITION_NAME):

set serveroutput on
DECLARE num_corrupt INT;
BEGIN  
  num_corrupt := 0;  
  DBMS_REPAIR.CHECK_OBJECT (  
  SCHEMA_NAME => '&schema_name',  
  OBJECT_NAME => '&object_name',  
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',  
  corrupt_count => num_corrupt);  
  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
from REPAIR_TABLE;

REM Mark the identified blocks as corrupted ( Soft Corrupt - reference Note 1496934.1 )
DECLARE num_fix INT;
BEGIN  
  num_fix := 0;  
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (  
  SCHEMA_NAME => '&schema_name',  
  OBJECT_NAME=> '&object_name',  
  OBJECT_TYPE => dbms_repair.table_object,  
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',  
  FIX_COUNT=> num_fix);  DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/
REM Allow future DML statements to skip the corrupted blocks:

BEGIN  
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (  
  SCHEMA_NAME => '&schema_name',  
  OBJECT_NAME => '&object_name',  
  OBJECT_TYPE => dbms_repair.table_object,  
  FLAGS => dbms_repair.SKIP_FLAG);
END;
/

此文参考文献

详细请参考：

Note 556733.1DBMS_REPAIR SCRIPT

坏块属于LOB段：

NOTE 293515.1 ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors

其他

Note 453278.1Error DBV-201 Marked Corrupt For Invalid Redo Application
Note 1459778.1Use RMAN to format corrupt data block which is not part of any object
Note 336133.1How to Format Corrupted Block Not Part of Any Segment
Note 556733.1DBMS_REPAIR SCRIPT
Note 293515.1ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors

数据库坏块，还有什么不懂？插图

更多内容请关注微信公众号：数据与人

数据库坏块，还有什么不懂？插图1
打赏微海报分享
文章标签：

为您推荐

SQL 的执行顺序，你搞清楚了吗？

Delete、Truncate、Drop，千万别用错了。

MySQL Update语句一个非常经典的“坑”

数据库性能优化八大方案，你知道几个？

SQL 语句中 left join 后用 on 还是 where ，区别大了！

如果是MySQL引起的CPU消耗过大，你会如何优化？

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注