This discussion is archived
5 Replies Latest reply: Nov 19, 2012 1:15 AM by Helios-GunesEROL RSS

corrupted segments

902332 Newbie
Currently Being Moderated
hi all,

one of my database block is corrupted. while performing validate check in rman i has given only datafile id and block id.

how we can find corrupted segments with these details. please let me know.

database is 11gr2 on linux 5.5


thanks
  • 1. Re: corrupted segments
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    You could use dbv

    Check
    http://blog.aristadba.com/?p=109

    Edited by: Osama_mustafa on Nov 19, 2012 11:01 AM
  • 2. Re: corrupted segments
    Aman.... Oracle ACE
    Currently Being Moderated
    Blocks within the segments only get corrupted. So if you are having the file# and the block#, you should start preparing plans to do the recovery.

    Aman....
  • 3. Re: corrupted segments
    973761 Newbie
    Currently Being Moderated
    Hello,

    check the table DBA_EXTENTS. This table has the SEGMENT_NAME, BLOCK_ID and FILE_ID. The column BLOCK_ID is the starting block of an extend so that you have to use the first entry with BLOCK_ID > your_block_id.

    Regards

    Johannes
  • 4. Re: corrupted segments
    Girish Sharma Guru
    Currently Being Moderated
    Is this what you are looking ?
    col ownr format a8 heading 'Owner' justify c
    col type format a8 heading 'Type' justify c trunc
    col name format a28 heading 'Segment Name' justify c
    col exid format 990 heading 'Extent#' justify c
    col fiid format 9990 heading 'File#' justify c
    col blid format 99990 heading 'Block#' justify c
    col blks format 999,990 heading 'Blocks' justify c
    SQL> ed
    Wrote file afiedt.buf
    
      1  select
      2  owner ownr,
      3  segment_name name,
      4  segment_type type,
      5  extent_id exid,
      6  file_id fiid,
      7  block_id blid,
      8  blocks blks
      9  from
     10  dba_extents
     11  where
     12  file_id = &file_id
     13  and block_id=&block_id
     14  order by
     15* block_id
    SQL> /
    Enter value for file_id: 2
    old  12: file_id = &file_id
    new  12: file_id = 2
    Enter value for block_id: 80048
    old  13: and block_id=&block_id
    new  13: and block_id=80048
    
     Owner           Segment Name           Type   Extent# File# Block#  Blocks
    -------- ---------------------------- -------- ------- ----- ------ --------
    SYS      WRI$_ADV_MESSAGE_GROUPS_PK   INDEX          5     2  80048        8
    
    SQL>
    It prompts fileid and blockid (which you have) and will tell you the name of segment (which you are looking for).

    Got from Internet, but missed to copy the link.

    Regards
    Girish Sharma
  • 5. Re: corrupted segments
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Please read my blog:
    http://heliosguneserol.wordpress.com/2012/07/03/ora-19566-exceeded-limit-of-0-corrupt-blocks/

    Regard
    Helios

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points