In my database there is two corrupted blocks are there .
I want to instruct my database to not to use that blocks
while table/index scan
IS there any way ?
Not really. I suggest you contact Oracle Tech Support to resolve this issue asap.
How do you find corrupt blocks in the database?
Also if the block is corrupt how does it affect the database?
[Edited by mybear on 01-24-2001 at 12:33 PM]
One of the ways is to do a full export.If there is any block corruption there will be errors in the export log...with the file id,block id details...
as Halo suggested contact Oracle support. As a temporary fix though you may want to try the following --
I believe that you found out about the corrupt data blocks by using DBV. It must have told you what blocks are corrupted.
Use dba_extents to find out what objects (table, index) are using those blocks.
You need to recreate those objects. For indexes it should be as simple as creating a new index; For tables you may try Export/import. In case of corrupt block, even Export may fail. In that case you may write a little pl/sql procedure to read one record at a time and insert it in a new temp table; Reading one record will give you error while reading the corrupt block (but will not terminate the procedure like Export may do); just skip that record.
Another thing to keep in mind is that if there is a corrupt block and you know about it, this may be the time to "Block" it from being used by any other new objects created in the database. You can do this by leaving those objects that contain the corrupt block intact; just rename them so that they sit there forever holding that corrupt block until you find a more permanent solution.
Of course it may not be as simple as I sounded (there may be constraints issues blocking you from renaming the tables)
Did you try DBMS_REPAIR.SKIP_CORRUPT_BLOCK?
Few more months, our life is gonna be little easier with oracle9i new feature 'block meadia recovery' which allows only corrupt blocks to be recovered while the table is online, with the coordinated effort of LogMiner utility.
[Edited by sreddy on 01-24-2001 at 01:41 PM]
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
I can not used DBMS_REPAIR.SKIP_CORRUPT_BLOCK, the reason is there is no object related to this currepted block .
I don't want any object to use these blocks in future while creating new objects .
I sthere any way to mark these blocks as unusable ?
there are 2 possibilities in blk.corruption cases
1. the corrupt blocks do not belong to any object(which is what u have told.
2. the corrupt blocks belong to a database object
if the blocks would have been of a db object it would not have been much of a bother to u, all u would have to do was to drop the object and restore it from uer backup or take any other route to get back uer data, which is pretty well documented, but since this is not uer case and that want to instruct oracle to forget that these blocks exist, then that would be asking for a lot, since these things are internal to the oracle kernel, we would have no way of controlling how oracle would pick blocks at its discretion, hence your only fallback is to get in touch with oracle support and sort out uer problem
Read for help on DBMS_REPAIR Package. This has a bunch of procedures you can use to deal with corrupt blocks. It seems like this utility is to repair corrupt blocks but for the time being until 8.1.7 it can only be used to mark corrupt blocks in your tables so that they can be skipped during FTS. You can also mark orphan keys in the indexes using this package. don't forget to rebuild indexes......
Click Here to Expand Forum to Full Width