DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION

DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION

DBMS_REPAIR EXAMPLE SCRIPT WITH PARTITION (Doc ID 1436664.1)
DBMS_REPAIR SCRIPT (Doc ID 556733.1)

Run sqlplus with SYS user

1. Run sqlplus. Example:

sqlplus '/ as sysdba'

Instructions
run the script from sqlplus

Script
2. Use the DBMS_REPAIR (CHECK_OBJECT/FIX_CORRUPT_BLOCKS/SKIP_CORRUPT_BLOCKS ) to find, mark and skip corrupted blocks.

REM Used to create the REPAIR_TABLE from 10g and Above SYSAUX can be used as 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 Used to identified the corrupted blocks for Owner.Table (partition_name):

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

REM Used to list corrupted blocks (Optional).

Select * from REPAIR_TABLE;

REM Used to mark the identified blocks as corrupted blocks:

DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&OWNER',
OBJECT_NAME=> '&TABLE',
partition_name => '&PARTITION_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 Used to skip the corrupted blocks:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&OWNER',
OBJECT_NAME => '&TABLE',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/

3. Saving data on the table by skipping the corrupted blocks.

Export/Truncate/Import table/partition==> &OWNER.&TABLE_NAME partition==> &PARTITION_NAME

To export Table/Partition, run (From OS Prompt)

exp userid=/

file= tables=:

rows=y log=
To truncate the Table/Partition, run from sqlplus:

ALTER TABLE &OWNER.&TABLE_NAME
TRUNCATE PARTITION &PARTITION_NAME update global indexes;
To import Table/Partition, run (From OS Prompt)

imp userid=/

file= tables=:

rows=y ignore=y log=
4. Rebuild any index which might become unusable after the truncate is executed and no maintained by
update global indexes option.

SQL> select owner, index_name, index_type, status, table_name , table_owner from dba_indexes
Where table_owner = '&OWNER' and
Table_name = '&TABLE_NAME' and status = 'UNUSABLE';
To rebuild the index, run:

SQL> alter index &OWNER.&INDEX_NAME rebuild;
5. Remove the skip flag, run from sqlplus

execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(SCHEMA_NAME=>'&OWNER',OBJECT_NAME=>'&TABLE_NAME',flags=>sys.dbms_repair.NOSKIP_FLAG);