逻辑坏块修复

突然。。。有一天，某个库报错
ORA-1578 ORACLE data block corrupted (file # 5, block # 48353)
版本：11.2.0.3

Error:  ORA-01578 (ORA-1578)
Text:    ORACLE data block corrupted (file # %s, block # %s)
Cause: The data block indicated was corrupt. This was a physical
        corruption, also called a media corruption. The cause is unknown
        but is most likely external to the database. If ORA-26040 is also
        signaled, the corruption is due to NOLOGGING or UNRECOVERABLE
        operations.
Action: The general method to repair a media corrupt block is to restore
        a backup and recover the backup. For databases in ARCHIVELOG
        mode, use block media recovery or media recovery. In some situations,
        you can also drop the segment and re-create it. For example, you can
        drop an index and re-create the index.

索引有逻辑坏块的话，重建即可，以下是表中有坏块的修复过程

SQL> select constraint_name,table_name from user_constraints where table_name like 'VPX_TEXT_ARRAY_OLD';

no rows selected

SQL> select * from V$backup_corruption;

no rows selected

SQL> analyze table VPXADMIN.VPX_TEXT_ARRAY_OLD  validate structure;

Table analyzed.

SQL>  select * from dba_extents where file_id = 5 and  48353 between block_id AND block_id + blocks - 1;

OWNER							SEGMENT_NAME							   PARTITION_NAME		       		SEGMENT_TYPE	  TABLESPACE_NAME				 EXTENT_ID    FILE_ID	BLOCK_ID      BYTES	BLOCKS RELATIVE_FNO		
------------------------------  ----------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
VPXADMIN						VPX_TEXT_ARRAY_OLD										      					TABLE		  VPX								18	    5	   48256    1048576	   128		  5


SQL> select owner, segment_name, segment_type, partition_name
from   dba_segments
where  header_file = &AFN
  and  header_block = &BL;  
Enter value for afn: 5
old   3: where	header_file = &AFN
new   3: where	header_file = 5
Enter value for bl: 48353
old   4:   and	header_block = &BL
new   4:   and	header_block = 48353

no rows selected


SQL> BEGIN
  DBMS_REPAIR.ADMIN_TABLES (
  TABLE_NAME => 'REPAIR_TABLE',
  TABLE_TYPE => dbms_repair.repair_table,
  ACTION => dbms_repair.create_action,
  TABLESPACE => 'VPX');
END;
/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type
from dba_objects
where object_name like '%REPAIR_TABLE';  2    3  

OWNER							OBJECT_NAME																			OBJECT_TYPE
------------------------------	--------------------------------------------------------------------------------	-------------------
SYS								REPAIR_TABLE 																			TABLE
SYS								DBA_REPAIR_TABLE																		VIEW



SQL> declare
begin
dbms_repair.admin_tables (
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => 'VPX');  
end;
/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type
from dba_objects
where object_name like '%ORPHAN_KEY_TABLE';  2    3  

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SYS
ORPHAN_KEY_TABLE
TABLE

SYS
DBA_ORPHAN_KEY_TABLE
VIEW

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------


SQL>  set serveroutput on
SQL> DECLARE num_corrupt INT;
BEGIN
  num_corrupt := 0;
  DBMS_REPAIR.CHECK_OBJECT (
  SCHEMA_NAME => 'VPXADMIN',
  OBJECT_NAME => 'VPX_TEXT_ARRAY_OLD',
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  corrupt_count => num_corrupt);
  DBMS_OUTPUT.PUT_LINE('number corru  2    3    4    5    6    7    8    9  pt: ' || TO_CHAR (num_corrupt));
END;
/ 10   11  
number corrupt: 1

PL/SQL procedure successfully completed.

SQL> desc repair_table
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID				   NOT NULL NUMBER
 TABLESPACE_ID				   NOT NULL NUMBER
 RELATIVE_FILE_ID			   NOT NULL NUMBER
 BLOCK_ID				   NOT NULL NUMBER
 CORRUPT_TYPE				   NOT NULL NUMBER
 SCHEMA_NAME				   NOT NULL VARCHAR2(30)
 OBJECT_NAME				   NOT NULL VARCHAR2(30)
 BASEOBJECT_NAME				    VARCHAR2(30)
 PARTITION_NAME 				    VARCHAR2(30)
 CORRUPT_DESCRIPTION				    VARCHAR2(2000)
 REPAIR_DESCRIPTION				    VARCHAR2(200)
 MARKED_CORRUPT 			   NOT NULL VARCHAR2(10)
 CHECK_TIMESTAMP			   NOT NULL DATE
 FIX_TIMESTAMP					    DATE
 REFORMAT_TIMESTAMP				    DATE

SQL> select object_name, block_id, corrupt_type, marked_corrupt,
corrupt_description, repair_description
from repair_table;  2    3  

OBJECT_NAME			 BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
VPX_TEXT_ARRAY_OLD		    48353	  6148 TRUE

mark block software corrupt


SQL>  declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => 'VPXADMIN',
object_name => 'VPX_TEXT_ARRAY_OLD',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => fix_count);
dbms_output  2    3    4    5    6    7    8    9   10   11  .put_line('fix count: ' || to_char(fix_count));
end;
/ 12   13  
fix count: 0

PL/SQL procedure successfully completed.

SQL> select object_name, block_id, marked_corrupt
from repair_table;  2  

OBJECT_NAME			 BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
VPX_TEXT_ARRAY_OLD		    48353 TRUE

SQL> select index_name from dba_indexes where table_name in (select distinct object_name from repair_table);

INDEX_NAME
------------------------------
BIN$pDASWUAXVS3gUwEAAH8fcw==$0
BIN$pDASWUAVVS3gUwEAAH8fcw==$0
SYS_IL0000082462C00003$$
BIN$pDASWUAUVS3gUwEAAH8fcw==$0
BIN$pDASWUAWVS3gUwEAAH8fcw==$0

SQL>  declare
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
  SCHEMA_NAME => 'VPXADMIN',
  OBJECT_NAME => 'VPX_TEXT_ARRAY_OLD',
  OBJECT_TYPE => dbms_repair.table_object,
  FLAGS => dbms_repair.SKIP_FLAG);
END;
/  2    3    4    5    6    7    8    9  

PL/SQL procedure successfully completed.

SQL> select table_name, skip_corrupt from dba_tables where table_name = 'VPX_TEXT_ARRAY_OLD';

TABLE_NAME		       SKIP_COR
------------------------------ --------
VPX_TEXT_ARRAY_OLD	       ENABLED



SQL> ALTER TABLE VPXADMIN.VPX_TEXT_ARRAY_OLD MOVE ;

Table altered.