逻辑坏块修复

突然。。。有一天，某个库报错
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.