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 |
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 #554640 is a reply to message #554635] |
Wed, 16 May 2012 01:38 |
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 |
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 |
|
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
|
|
|
Goto Forum:
Current Time: Fri Nov 29 02:43:25 CST 2024
|