Email: service@parnassusdata.com 7 x 24 online support!
Oracle How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
Oracle How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
GOAL
The purpose of this note is to provide the instructions to identify the corrupt Object reported by the error ORA-1578 or tools like RMAN / DBVERIFY
FIX
Identify the Absolute File Number (AFN) and Block Number (BL)
The absolute and relative file numbers (RFN) are often the same but can differ (especially if the database has been migrated from Oracle7 or if
Transportable/Plugged Tablespaces are used). It is important to get the correct numbers for the AFN and RFN or you may end up salvaging the wrong object.
Getting the AFN from ORA-1578
The AFN is provided by the error ORA-1110 produced right after the ORA-1578. In the next example the AFN is 5 and BL is 34.
SQL> select * from scott.dept_view;
select * from scott.dept_view
* ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 11, block # 34)
ORA-01110: data file 5: '/home/oracle/oradata/users.dbf
Getting the AFN from DBVERIFY output
A corrupt block might be reported by dbverify in different ways. DBVERIFY normally provides the RDBA associated to the affected block. Then the RFN is used to
get the AFN in the query from dba_data_files below. Here are some examples:
RFN=11 BL=34:
Page 34 is marked corrupt
Corrupt block relative dba: 0x02c00022 (file 11, block 34)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x02c00022
last change scn: 0x0771.4eebe71c seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xe71c0602
check value in block header: 0xd3ce
computed block checksum: 0x2
Dbverify always reports the relative data block address (rdba/dba) in its output. In the above case the Relative dba is the hexadecimal value 0x02c00022 taken
from Message "Corrupt block relative dba: 0x02c00022 (file 11, block 34)". The rdba/dba provides the RFN. The RFN is then 11. Use the query from
dba_data_files below to get the AFN.
Another example from dbverify is:
RFN=11 BL=35:
Dbv output:
DBV-200: Block, dba 46137379, already marked corrupted"
SQL> select dbms_utility.data_block_address_file(&&rdba) RFN,
2 dbms_utility.data_block_address_block(&&rdba) BL
3 from dual;
Enter value for rdba: 46137379
RFN BL
---------- ----------
11 35
Get the AFN from the RFN using dba_data_files:
select file_id AFN, relative_fno, tablespace_name
from dba_data_files
where relative_fno=&RFN;
Example:
SQL> select file_id AFN, relative_fno, tablespace_name
2 from dba_data_files
3 where relative_fno=&RFN;
Enter value for rfn: 11
AFN RELATIVE_FNO TABLESPACE_NAME
---------- ------------ ------------------------------
5 11 USERS
The AFN is 5
Getting the AFN from RMAN
RMAN report corruptions in the v$database_block_corruption view.
The column FILE# in that view is the AFN. Column BLOCK# is BL.
Identify the Corrupt Object.
Once that the AFN is identified, run the next query to identify the corrupt Object:
select *
from dba_extents
where file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;
Example:
SQL> select *
2 from dba_extents
3 where file_id = &AFN
4 and &BL between block_id AND block_id + blocks - 1;
Enter value for afn: 5
Enter value for bl: 34
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ -------------- ------------ --------------- --------- ------- -------- ---------- ------ ------------
SCOTT DEPT TABLE USERS 0 5 33 65536 8 11
If the above query does not return rows, it can also be that the corrupted block is a segment header in a Locally Managed Tablespace (LMT). When the corrupted
block is a segment header block in a LMT, the above query produces a corruption message in the alert.log but the query does not not fail. In that case run this
query:
select owner, segment_name, segment_type, partition_name
from dba_segments
where header_file = &AFN
and header_block = &BL;
and header_block = &BL;
If the block belongs to a free EXTENT (not associated to an object) or if the block is in a TEMPFILE the above queries will return no data. For TEMPFILES the
"Segment Type" will be "TEMPORARY".
If the block belongs to a free extent it should appear in DBA_FREE_SPACE:
select *
from dba_free_space
where file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;
Note that in Oracle 10g and above when an ORA-1578 is produced, the alert log is also updated with the information of the corrupt object. Example:
Corrupt Block Found
TSN = 5, TSNAME = USERS
RFN = 11, BLK = 34, RDBA = 46137378
OBJN = 46107, OBJD = 36440, OBJECT = DEPT, SUBOBJECT =
SEGMENT OWNER = SCOTT, SEGMENT TYPE = Table Segment