« Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO

Write fast and efficient PLSQL - DBMS_PROFILER »

Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR

Posted by Zahid on August 29, 2010.

It is quite often that you get blocks in a data file physically corrupt. As a proactive DBA you should always be ready for such kind of things. e.g. if you have proper backups of the data files you can recover the corrupt block from those backups. IF you have user managed backups, you can recover the whole data file and get the corrupted block recovered with it. If you have an RMAN backup, you can use a powerful RMAN feature called BMR (BLOCK MEDIA RECOVERY). Using BMR you can tell RMAN an individual block to recover from a backup instead of recovering the whole data file. However, there may exist scenarios where you don't have a backup at all. You may have lost it, got it deleted or may be the backup got corrupted itself. This article demonstrate how to repair a corrupt block to make the objects usable, that are using that block.

Bear in mind that repairing a corrupt block using DBMS_REPAIR, does not restore data in those blocks. It is just a mean of marking those blocks as corrupt blocks and skip them when data in the read from the object containing corrupt blocks. The data in the corrupt blocks id lost forever.

Lets first get a block corrupted and see its effects on the objects using that block.

create tablespace ts_corrupt
datafile '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'
size 10m autoextend on next 5m
extent management local
segment space management auto
/

Tablespace created.

create user usr_corrupt identified by usr_corrupt 
default tablespace ts_corrupt
quota unlimited on ts_corrupt
/

User created.

grant connect , resource to usr_corrupt
/

Grant succeeded.

conn usr_corrupt/usr_corrupt
Connected.

create table t_corrupt as select * from all_objects
/

Table created.

create index tc_idx 
on t_corrupt (object_name)
/

Index created.

SQL> exit

We have a tablespace ts_corrupt, which has a table named t_corrupt owned by the user usr_corrupt and there is an index on one of the columns in the table. Lets see where in the data file this table is sitting.

$ sqlplus / as sysdba

select segment_name , header_file , header_block      
from dba_segments
where segment_name = 'T_CORRUPT'
and   owner = 'USR_CORRUPT';

SEGMENT_NAME                 HEADER_FILE HEADER_BLOCK
---------------------------- ----------- ------------
T_CORRUPT                              6           11

SQL> exit

The header of the table is in block 11, so if the block 12 or so are corrupted we will get an error when we try to select data from the table. Lets corrupt block 12 using the "dd" command in Linux.
DISCLAIMER: The dd command given below is just for learning purposes and should only be used on testing systems. I will not take any responsibility of any consequences or loss of data caused by this command.

$ cd /d01/apps/oradata/oraxpo
$ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=12 << EOF
> Make it Corrupt.
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 8.9e-05 seconds, 191 kB/s

$ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=13 << EOF
> Make it Corrupt.
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.0002 seconds, 85 kB/s

$ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=14 << EOF
> Make it Corrupt.
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 5.6e-05 seconds, 304 kB/s

We have injected our crap into the block # 12,13 and 14 of the datafile "/d01/apps/oradata/oraxpo/ts_corrupt01.dbf" using "dd" command. After this command successfully executed these blocks are now corrupt.

$ sqlplus / as sysdba

SQL> alter system flush buffer_cache;

System altered.

/* 
   We may need to flush the buffer_cache because if the block 12 is 
   in the buffer_cache already it will not be read from the data file.
*/

SQL> conn usr_corrupt/usr_corrupt 
Connected.
SQL> select * from t_corrupt;
select * from t_corrupt
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'


SQL> exit

The error that we get due to a block corruption is very clear. It will tell you the file and block#.

Repairing a block corruption starts with creating two admin tables "REPAIR_TABLE" and "ORPHAN_TABLE".

REPAIR_TABLE:

This table is filled with the information about the corrupt blocks by the DBMS_REPAIR.CHECK_OBJECT procedure.

ORPHAN_KEY_TABLE:

This table is filled with the information about any orphan keys in indexes due to corrupt blocks in the table by the DBMS_REPAIR.DUMP_ORPHAN_KEYS procedure.

DBMS_REPAIR.ADMIN_TABLES()

performs actions (create,drop,purge) with special tables which are later on used for the corrupt block repair.

table_name

=> Name of the table to process.

table_type

=> Type of the table being processed. e.g. DBMS_REPAIR.REPAIR_TABLE or DBMS_REPAIR.ORPHAN_TABLE

action

=> the action to perform with the table mentioned in the first argument. e.g. DBMS_REPAIR.CREATE_ACTION, DBMS_REPAIR.DROP_ACTION or DBMS_REPAIR.PURGE_ACTION.

tablespace

=> The tablespace to use when creating the special table.

$ sqlplus / 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;
/

PL/SQL procedure successfully completed.

-- We just created the REPAIR_TABLE in the USERS tablespace.

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


BEGIN
  DBMS_REPAIR.ADMIN_TABLES 
  (
     TABLE_NAME => 'ORPHAN_KEY_TABLE',
     TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
     ACTION     => DBMS_REPAIR.CREATE_ACTION,
     TABLESPACE => 'USERS'
  );
END;
/

PL/SQL procedure successfully completed.

-- We just created the ORPHAN_KEY_TABLE in the USERS tablespace.

SQL> desc ORPHAN_KEY_TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHEMA_NAME                               NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 IPART_NAME                                         VARCHAR2(30)
 INDEX_ID                                  NOT NULL NUMBER
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 PART_NAME                                          VARCHAR2(30)
 TABLE_ID                                  NOT NULL NUMBER
 KEYROWID                                  NOT NULL ROWID
 KEY                                       NOT NULL ROWID
 DUMP_TIMESTAMP                            NOT NULL DATE

DBMS_REPAIR.CHECK_OBJECT()

checks an objects for possible corruptions and report the corruptions into the REPAIR_TABLE.

schema_name

=> Owner of the object to check.

object_name

=> Name of the object to check.

repair_table_name

=> Name of the REPAIR_TABLE.

corrupt_count

=> An OUT parameter where the number of corrupt blocks are reported.

SET SERVEROUTPUT ON
DECLARE 
  vCorruptBlocks INT := 0;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME => 'T_CORRUPT',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  vCorruptBlocks
  );
  DBMS_OUTPUT.PUT_LINE('Number of blocks corrupted: ' || TO_CHAR (vCorruptBlocks));
END;
/
Number of blocks corrupted: 3

PL/SQL procedure successfully completed.

-- The procedure found three blocks corrupt and has filled the information in REPAIR_TABLE.

column object_name format a10
column repair_description format a28
set lines 10000
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;

OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- ----------------------------
T_CORRUPT          12         6148 TRUE       mark block software corrupt
T_CORRUPT          13         6148 TRUE       mark block software corrupt
T_CORRUPT          14         6148 TRUE       mark block software corrupt

As you may see in the output above the MARKED_CORRUPT column shows TRUE for all three corruptions. It seems that CHECK_OBJECT procedure is capable of marking the corrupt blocks in the scenario we have demonstrated. However, if the MARKED_CORRUPT column shows FALSE for some corrupted blocks, you may use DBMS_REPAIR.FIX_CORRUPT_BLOCKS procedure to mark the blocks as corrupt.

DBMS_REPAIR.FIX_CORRUPT_BLOCKS()

fix any corrupted blocks in specified object by marking them as software corrupt.

schema_name

=> Owner of the object to fix.

object_name

=> Name of the object to fix.

object_type

=> Type of the object to fix.

repair_table_name

=> Name of the REPAIR_TABLE.

fix_count

=> An OUT parameter where the number of blocks fixed are reported.

SET SERVEROUTPUT ON
DECLARE 
  vBlocksFixed INT := 0;
BEGIN 
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME=> 'T_CORRUPT',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT => vBlocksFixed
  );
  DBMS_OUTPUT.PUT_LINE('Number of blocks fixed: ' || TO_CHAR(vBlocksFixed));
END;
/
Number of blocks fixed: 0

PL/SQL procedure successfully completed.

Number of blocks fixed are 0 in my case as the corrupt blocks are already marked by the CHECK_OBJECT procedure.

If the corrupted object was a table, then it may be very possible that there was an index on the table which had entries of data that is corrupt in the table now. Those entries are orphan now.

DBMS_REPAIR.DUMP_ORPHAN_KEYS()

dump any orphan keys in indexes caused by the corruption in a table.

schema_name

=> Owner of the object to process.

object_name

=> Name of the object to process.

object_type

=> Type of the object to process. e.g. DBMS_REPAIR.INDEX_OBJECT.

repair_table_name

=> Name of the REPAIR_TABLE.

orphan_table_name

=> Name of the ORPHAN_TABLE.

key_count

=> An OUT parameter where the number of orphan keys dumped are reported.

SET SERVEROUTPUT ON
DECLARE 
  vOrphanKeys INT := 0;
BEGIN
  DBMS_REPAIR.DUMP_ORPHAN_KEYS 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME => 'TC_IDX',
     OBJECT_TYPE => DBMS_REPAIR.INDEX_OBJECT,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
     KEY_COUNT => vOrphanKeys
  );
  DBMS_OUTPUT.PUT_LINE('Number of orphan keys: ' || TO_CHAR(vOrphanKeys));
END;
/
Number of orphan keys: 241

PL/SQL procedure successfully completed.

-- 241 Orphan keys are dumped to the ORPHAN_TABLE.

Even after the corrupt blocks are marked corrupt, you will not be able to access them unless you set the object to skip any corrupted blocks using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS.

SQL> select * from usr_corrupt.t_corrupt;
select * from usr_corrupt.t_corrupt
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'

SQL> select * from usr_corrupt.t_corrupt where object_name = 'DUAL';
select * from usr_corrupt.t_corrupt where object_name = 'DUAL'
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS()

skip any corrupted blocks in specified object when data is read from it.

schema_name

=> Owner of the object to process.

object_name

=> Name of the object to process.

object_type

=> Type of the object to process.

flags

=> What to do with the object. e.g. DBMS_REPAIR.SKIP_FLAG or DBMS_REPAIR.NOSKIP_FLAG

BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME => 'T_CORRUPT',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     FLAGS => DBMS_REPAIR.SKIP_FLAG
  );
END;
/

PL/SQL procedure successfully completed.

Now lets see if our repair works or not.

-- Rebuild the index to be used with the object repaired.

SQL> ALTER INDEX USR_CORRUPT.TC_IDX REBUILD;

Index altered.

select owner , table_name , skip_corrupt
from   dba_tables
where  table_name = 'T_CORRUPT';

OWNER                          TABLE_NAME                     SKIP_COR
------------------------------ ------------------------------ --------
USR_CORRUPT                    T_CORRUPT                      ENABLED

/*
 The SKIP_CORRUPT column in dba_tables shows whether corrupt blocks are skipped 
 or not on reading data from the object. 
*/

SQL> select count(*) from usr_corrupt.t_corrupt;

  COUNT(*)
----------
     40692

SQL> select * from usr_corrupt.t_corrupt where object_name = 'DUAL';

no rows selected

-- The row where "object_name='DUAL'" is lost.