oracle故障处理一则

使用 R DBMS_REPAIR 程序包丢弃损坏数据块

conn / as sysdba

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'USERS');

END;

/

DECLARE

num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => 'TEST',

OBJECT_NAME => 'USERS',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

CORRUPT_COUNT => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

/

number corrupt: 1

PL/SQL 过程已成功完成。

col corrupt_description format a30

col repair_description format a30

select object_name,block_id,corrupt_type,marked_corrupt,corrupt_description,repair_description

from repair_table;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION REPAIR_DESCRIPTION

第 26 页

USERS 12 6148 TRUE mark block software corrupt

DECLARE

num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => 'TEST',

OBJECT_NAME=> 'USERS',

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;

/

num fix: 0

PL/SQL 过程已成功完成。

现在这步骤可省略，已在 CHECK_OBJECT 时完成（练习环境：10.2.1.00）

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'TEST',

OBJECT_NAME => 'USERS',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.skip_flag);

END;

/

select count(*) from test.users;

COUNT(*)

23

标记了错误块，并使扫描时跳过

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.drop_action);

END;

/