Corrupt block

[naaron]

I have a corrupt block. The message in the alert log points to a file that doesn't exist and never existed. I have the hex address. I need to know how to convert the hex address to the file and block locations. I have triade the cdba procedure with no success. The address is 0x0180000a. Any help would be appreciated.

Nathan

[rajabalachandra]

can you post the cutting from your alert log.

[naaron]

Starting ORACLE instance (normal)
***
Corrupt block relative dba: 0x0180000a (file 1028, block 10)
Bad header found during buffer read
Data in bad block -
type: 8 format: 2 rdba: 0x0100000a
last change scn: 0x0000.529f119c seq: 0x1 flg: 0x0c
consistency value in tail: 0x119c0801
check value in block header: 0xcf58, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0180000a (file 1028, block 10) found same corrupted data

[LalooYadav]

If the file number does not appear in V$DATAFILE AND Absolute File # (AFN) is greater than the DB_FILES parameter value then it is probably a TEMPFILE. In this case the filename can be found using:
SELECT name FROM v$tempfile WHERE file#=(AFN - DB_FILES value);

Try following to get table/index name

SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER
FROM SYS.DBA_EXTENTS
WHERE FILE_ID = 1028
AND 10 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

[naaron]

It isn't a temp file. In fact 1028 is higher than our maxfiles size. There isn't a file with that number.

Nathan

[naaron]

Sorry 1028 is higher than our db_files which is set to 1022.

Nathan

[naaron]

It was a file in my temp tablspace. I learned that temp files start at the next value after db_files even though they the number doesn't show up in v$tempfile.

Nathan

[jmodic]

That's exactly what LalooYadav suggested to you.

[naaron]

LalooYadav suggested that it could be a temp file but the query he listed wouldn't work because there are no files in the database with 1028 as the file#. I just wanted to make people aware that temp files start at the next value after db_files even though the number in v$tempfile doesn't match what what is in the alert log.

Nathan

[kris109]

Why don't you use DBVERIFY (dbv)on all datafiles and tempfiles. Start at Block 1 and end at a block greater than 10. Use a shell script and tee the output to a file. That should ferret out the offending file out of its hole.