ORACLE data block corrupted

[strafe]

I am not that familiar with Oracle and Encoutered the following message on Oracle 8 running on NT:<br>
<br>
... Please contact your support group for assistance. [NA000/1578] The ODBC driver returned the following additional information: [[Microsoft][ODBC driver for Oracle][Oracle]ORA-01578: ORACLE data block corrupted (file # 6, block # 13422)<br>
ORA-01110: data file 6: 'H:\ORC2'] <br>
<br>
I know which data block is corrupt, but I can't figure out how I would go about fixing it. I can't seem to find any repair db commands or such. Any ideas or thoughts would be much appreciated. <br>
<br>
Also, we have to use the MS ODBC driver in this case.<br>

 

[TimG]

Whew! No fun!<br>
<br>
There's no telling what caused the corruption, but your approach toward fixing it can depend on exactly what object that database block is sitting in the middle of.<br>
<br>
First of all, the generic approach to fixing any database corruption is to restore from backup. If you are in ARCHIVELOG mode, then after the restore, perform a full recovery. Now, this begs the question: how do I know my backups do not also have the corruption on them? The Oracle8 Recovery Manager (RMAN) product is capable of detecting corruption during backups; no other backup solution (anywhere!) is capable of this. If you don't use RMAN (if you're on Oracle8, you should!), then you're only option to protect your backups is to periodically &quot;touch&quot; every single database block holding table data in your database. One way to do this is using &quot;EXP FULL=Y FILE=/dev/null&quot;; there are others...<br>
<br>
Anyway, doing a restore and recover is the last resort. If the corrupted object is neither a table nor a rollback segment and you don't get many of these errors, then possibly you can just drop the object, recreate it, and go on your merry way. To determine which object got corrupted and what it is, use:<br>
<br>
SELECT OWNER, OBJECT_TYPE, OBJECT_NAME<br>
FROM DBA_EXTENTS<br>
WHERE FILE_ID = fff<br>
AND bbbb BETWEEN BLOCK_ID AND (BLOCK_ID+(BLOCKS-1));<br>
<br>
If it is a table, table partition, cluster, index-organized table, or rollback segment, then I strongly recommend testing a restore/recovery of the affected tablespace to a test database to ensure that the backup itself isn't corrupted (unless you're using RMAN). Then, if the restore/recover goes OK to test, then go ahead and do this database. If this is already a test database, then skip that step, of course...<br>
<br>
If the object is an index or index partition, then just drop the object and recreate it, and hope that the problem doesn't recur again. If the object is a temporary segment, then don't worry about it; it'll be dropped soon...<br>
<br>
If the query comes back without a return, then the object in which the corruption had resided has already been dropped...<br>
<br>
If you're on Oracle8i, check out the new DBMS_REPAIR supplied package. There is a column in DBA_TABLES named SKIP_CORRUPT, which can be set TRUE or FALSE from a procedure in DBMS_REPAIR. You'd use this if you wanted to just skip over the corrupted block without fixing it; otherwise, you'd have to set &quot;event 10231&quot; in your &quot;init.ora&quot; and bounce your database; call Oracle Support for the syntax for the event...<br>
<br>
Rama Velpuri's &quot;Backup & Recovery Handbook&quot; has more information as well...<br>
<br>
When in doubt, log a TAR...<br>
<br>
Hope this helps...<br>
<br>
-Tim