Database Resources
- RDBMS Server
- Database Administration
- Performance Tuning
- Backup and Recovery
- Oracle Utilities
- SQL and PLSQL
- Network Administration
- Client Applications
- Miscellaneous Stuff

Oracle's technology Sites

http://technet.oracle.com

http://metalink.oracle.com

http://asktom.oracle.com/

http://www.oramag.com

Category     : Database Resources -> Backup and Recovery

DB Version  : Oracle 8i / 9i

OS Details    : Sun Solaris9

In most places, the development and QA database environments are not in archivelog mode. Under those conditions, how do we deal with block corruptions? The most common block corruption error that comes up is the ORA-1578 error which reports the file number and the block number that's corrupted. Starting 8i, it is possible to retrieve data from tables that have corrupted blocks. It can be done with the help of dbms_repair package or by setting event 10231.

(1) Using the DBMS_REPAIR package:

The DBMS_REPAIR package can be created by running the dbmsrpr.sql script. On unix, the script to install the package can be found under $ORACLE_HOME/rdbms/admin location.

Step 1: Create a table to hold information about blocks that are corrupted:

exec dbms_repair.admin_tables(table_type=>DBMS_REPAIR.REPAIR_TABLE,DBMS_
REPAIR.CREATE_ACTION,tablespace=>'users')

Step 2: Check the objects and populate the repair_table with corrupt block information:

SQL> variable corrupt_cnt number;

SQL> exec dbms_repair.CHECK_OBJECT(schema_name=>'DEVUSER',

object_name=>'DEVOBJECT',CORRUPT_COUNT =>:CORRUPT_CNT)

-- This should return the total number of corrupt blocks identified

SQL> print corrupt_cnt

Step 3: Try fixing the corrupted blocks:

SQL> variable fix_cnt number;

SQL> exec dbms_repair.fix_corrupt_blocks(schema_name=>'DEVUSER',

object_name=>'DEVOBJECT',FIX_COUNT =>:FIX_CNT)

-- This should return the total number of corrupt blocks that were fixed

SQL> print fix_cnt

If for some reason oracle is unable to fix the corrupt blocks then the option available is to salvage the uncorrupted blocks from the table. Step 4 below explains how.

Step 4: Saving uncorrupted blocks:

If corrupted blocks cannot be repaired then we are left with the only option of trying to save uncorrupted blocks.

-- Have oracle mark the table to skip over corrupt blocks

SQL> exec dbms_repair.skip_corrupt_blocks(schema_name=>'DEVUSER',object_name=> 'DEVOBJECT')

Backup the uncorrupted blocks by doing a Create Table As Select (CTAS) or export and then you can rename the table later.

You can disable the skipping of corrupt blocks on the table by doing this:

SQL> exec dbms_repair.skip_corrupt_blocks(schema_name=>'DEVUSER',object_name=> 'DEVOBJECT', flags=>dbms_repair.noskip_flag)

(2) Using event 10231:

Alternatively, corrupted blocks can be skipped by setting event 10231 for the session or the database. At the session level, the event can be set as follows:

SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER,LEVEL 10';

The event can be turned off by doing this:

SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT OFF';

So the next time the developer complains of block corruption, all is not lost. Oracle provides us of ways to either fix the corruption of save what's left!