problem regarding block corruption
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: problem regarding block corruption

  1. #1
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78

    Red face

    Hi,
    We are using Oracle version 7.3.4 enterprise edition.
    Recently , I have noticed a problem , a oracle block is corrupted in datafile 8 '/../peerindex.dbf' . The datafile corresponds to index tablespace . Now I want to find out
    to know which index is corrupted ? How to find out that ?
    How to solve the problem ?

    Is Dbverify(dbv) and dbms_repair utility available on UNIX platform under this version ?



  2. #2
    Join Date
    Oct 2000
    Posts
    467
    i think dbms_repair is only in versions 8i onwards.
    You can run analyze ..validate structure on the objects and find out.
    Vinit

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    dbv (dbverify) is available since a long time ago, definitely it is available in 7.3.4.

    BTW, how did you discover dhe block being corupted? If it was repoprted by oracle via some ORA- error message during some query, than the error should also be recorded in your alert log, together with the exact block location (datafile number, block number). Check your alert log, once you discover the block location it should be easy to define to which segment this block belongs to.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Hi,

    The corruption appears in some situations, dbv, export ...

    Oracle reformat all blocks when it use, but in indexes case not. Then you can drop the index, create a temporary table on index tablespace, insert data in this table, and when Oracle gets this corrupted block it will format. Then you will drop the temporary table and recreate the index.

    Hope that helps

    Angel

  5. #5
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Hi again,

    If you execute the Oracle utility dbv on this file, it done to you a block_id or various. With this you will be able to know wich objects have this corrupted blocks with:

    select file_name,file_id
    from dba_data_files
    where file_name like '%your_file%';

    With this file_id:

    select owner,segment_name,segment_type
    from dba_extents
    where file_id= your_file_id
    and number_corrupted_block_from_dbv between block_id
    and block_id+bocks -1;

    Hope that helps

    Angel

  6. #6
    Join Date
    Oct 2000
    Posts
    467
    Originally posted by aarroyob
    Hi,

    The corruption appears in some situations, dbv, export ...

    Oracle reformat all blocks when it use, but in indexes case not. Then you can drop the index, create a temporary table on index tablespace, insert data in this table, and when Oracle gets this corrupted block it will format. Then you will drop the temporary table and recreate the index.

    Hope that helps

    Angel
    i don't think loading data to a predefined block is possible. This is managed internally by Oracle (based on freelists. )

    Vinit

  7. #7
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    [/B][/QUOTE]

    i don't think loading data to a predefined block is possible. This is managed internally by Oracle (based on freelists. )

    [/B][/QUOTE]

    Hi vinit,

    I didnīt say you load data to a predefined block, only load data, but if you are loading data, in anywhere oracle will get this corrupted block. I know is not very funny but ..... I donīt know other solution beside recreate the database :-)

    Regards

    Angel

  8. #8
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78

    corruption of block

    Thanks to all of U.
    but the funniest thing is that Oracle has not reported this eror either in alert log or in any trace files . Then I did down the database, remove all background trace files and alert log file,
    check the the value of background_dump_dest . After that I did startup the database and try to run the same query . This
    time once again reports corruption of block and I looked for the error message in trace as well as alert log , but no message . why so ?

  9. #9
    Join Date
    Jul 2001
    Posts
    181
    try udump

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width