Home » RDBMS Server » Server Administration » block corruption, segment not found (ORACLE 11.2.0.1, Red Hat 5.6)
block corruption, segment not found [message #554630] Wed, 16 May 2012 01:10 Go to next message
sahadba
Messages: 59
Registered: September 2009
Location: Pune
Member

Hi,

I have a database where view
v$database_block_corruption
shows a corrupted block

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4     756652          1         5.5157E+12 CORRUPT


I am not able to get the relevant segment from the above information

SQL> select segment_name, segment_type, owner
  2  from dba_extents
  3  where file_id = 4
  4  and 756652 between block_id
  5  and block_id + blocks -1;

no rows selected


DBVERIFY Summary
DBVERIFY - Verification complete

Total Pages Examined         : 3932160
Total Pages Processed (Data) : 3119107
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 755048
Total Pages Failing   (Index): 70
Total Pages Processed (Other): 5352
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 52653
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 655636777 (1293.655636777)


I have uploaded the complete logfile.

Below is a part of logfile

DBVERIFY - Verification starting : FILE = /prd/dvp/ora/oradata/LHF/disk06/gds_t01_01.dbf
Block Checking: DBA = 21728172, Block Type = KTB-managed data block
**** kdxcoavs = -84 < 0, avail = 3129
---- end index block validation
Page 756652 failed with check code 6401 
##Please not here that 756652 is the same block# mentioned in v$database_block_corruption 



here i tried finding the OBJECT ID as below

SELECT dbms_utility.data_block_address_block(21728172) "BLOCK", dbms_utility.data_block_address_file(21728172) "FILE" FROM dual;

     BLOCK       FILE
---------- ----------
    756652          5


Now for the same BLOCK it is giving different File, again the segment_name or segment_id could not be found from the above information.

Please advice on how to proceed.
Re: block corruption, segment not found [message #554635 is a reply to message #554630] Wed, 16 May 2012 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If the block does not belong to any object there is nothing to do, nothing to care, the block will be reformatted when it will be reallocated.

Regards
Michel
Re: block corruption, segment not found [message #554640 is a reply to message #554635] Wed, 16 May 2012 01:38 Go to previous messageGo to next message
sahadba
Messages: 59
Registered: September 2009
Location: Pune
Member

Thanks Michel,

I found a query that shows that it is a "FREE BLOCK"

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

OWNER                          SEGMENT_TYPE       SEGMENT_NAME
------------------------------ ------------------ ---------------------------------------------------------------------------------
PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ---------- ----------------- --------------- ---------------- --------------

                                        4            756652          756652                1 Free Block


Thanks again

Re: block corruption, segment not found [message #554652 is a reply to message #554640] Wed, 16 May 2012 03:52 Go to previous messageGo to next message
sahadba
Messages: 59
Registered: September 2009
Location: Pune
Member

Hi,

Is there a way where we can create a dummy table such that it uses the "Free Block". Once created would Drop the table and this would format the block.

Below is the tablespace information

SQL> select file_id, tablespace_name, file_name, round(bytes/1024/1024/1024,2) from dba_Data_Files where tablespace_name='GDS_T01'
  2  ;

   FILE_ID TABLESPACE_NAME                FILE_NAME                                                    ROUND(BYTES/1024/1024/1024,2)
---------- ------------------------------ ------------------------------------------------------------ -----------------------------
         4 GDS_T01                        /prd/dvp/ora/oradata/LHF/disk06/gds_t01_01.dbf                                          30
         7 GDS_T01                        /prd/dvp/ora/oradata/LHF/disk06/gds_t01_02.dbf                                          32
         8 GDS_T01                        /prd/dvp/ora/oradata/LHF/disk06/gds_t01_03.dbf                                          32
         9 GDS_T01                        /prd/dvp/ora/oradata/LHF/disk06/gds_t01_04.dbf                                          32
        10 GDS_T01                        /prd/dvp/ora/oradata/LHF/disk06/gds_t01_05.dbf                                           4

SQL> select tablespace_name, round(sum(bytes/1024/1024/1024),2) GB from dba_Data_Files where tablespace_name='GDS_T01' group by tablespace_name;

TABLESPACE_NAME                        GB
------------------------------ ----------
GDS_T01                               130

SQL> select tablespace_name, round(sum(bytes/1024/1024/1024),2) GB from dba_free_space where tablespace_name='GDS_T01' group by tablespace_name;

TABLESPACE_NAME                        GB
------------------------------ ----------
GDS_T01                             97.41


Please Advice
Re: block corruption, segment not found [message #554654 is a reply to message #554652] Wed, 16 May 2012 04:28 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No, there is no way, you can create a table and allocated extents until it reaches the block.
Note that you can choose the file when you allocate new extent, this is only thing you can choose.

Regards
Michel
Previous Topic: oracle database login error
Next Topic: To write Bangla Language in Oracle 10gR1 , 10gR2 and Express .(2 Merged)
Goto Forum:
  


Current Time: Fri Nov 29 02:43:25 CST 2024