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=<owner>/<password> file=<export_file.dmp> tables=<TABLE_NAME>:<PARTITION_NAME> rows=y log=<export_file.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=<owner>/<password> file=<export_file.dmp> tables=<TABLE_NAME>:<PARTITION_NAME> rows=y ignore=y log=<import_file.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);