Oracle How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY

Posted by PDSERVICE on Nov 15, 2015 In

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;

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

You are here

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