5 Replies Latest reply: Nov 19, 2012 3:15 AM by Helios-GunesEROL RSS

    corrupted segments

    902332
      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
          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....
            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
              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
                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
                  Hi;

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

                  Regard
                  Helios