APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.1.0.2 [Release 8.1.7 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 03-DEC-2014***

GOAL

Fix a block corruption in a TEMPORARY segment.

Example 1: Alert Log may show ORA-1578 followed by the corrupt object information with "SEGMENT TYPE = Temporary Segment":

								Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_8086.trc  (incident=2446468):

ORA-01578: ORACLE data block corrupted (file # 1707, block # 233066810)

ORA-01110: data file 1707: '/oracle/dbs/tools.dbf'

Wed Sep 24 16:33:09 2014

Corrupt Block Found

         TSN = 73, TSNAME = TOOLS

         RFN = 1024, BLK = 2646152, RDBA = 2646152

         OBJN = 0, OBJD = 124839000, OBJECT = TOOLS, SUBOBJECT =

         SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment
							
								in this example tablespace TOOLS is a PERMANENT tablespace (dba_tablespaces.contents='PERMANENT').

Example 2: Alert Log may not show any errors but message:

								Corrupt Block Found

         TSN = 23, TSNAME = TEMP

         RFN = 1, BLK = 4608, RDBA = 4198912

         OBJN = 298432, OBJD = 4198912, OBJECT = , SUBOBJECT =

         SEGMENT OWNER = , SEGMENT TYPE =

SOLUTION

Fix Corruption in a temporary segment stored in the TEMPORARY tablespace

For a block corruption in the TEMPORARY tablespace and if the corruption is consistently reported for the same block in the alert log, create a NEW temporary tablespace and switch all users to that tablespace then DROP the affected Temporary Tablespace.

Fix Corruption in a temporary segment stored in a PERMANENT tablespace

1. Confirm that the corrupt block is the Segment Header

If the same corrupt block is consistently reported in the alert log like in Example 1 (SMON is consistently reporting the same block number), then the corrupt block could be the segment header and the next query should return a row:

							select segment_name, segment_type

from dba_segments

where tablespace_name = '&TSNAME'

  and header_file = &FILE_NUMBER

  and header_block = &BLK;

In the Example 1 above it is:

							select segment_name, segment_type

  from dba_segments

where tablespace_name = 'TOOLS'

  and header_file = 1707

  and header_block = 233066810;

SEGMENT_NAME         SEGMENT_TYPE

-------------------- ------------------

1707.233066810       TEMPORARY

2. Fix the Corruption

If the above query returns a row then fix the corruption by using dbms_space_admin:

							exec dbms_space_admin.segment_corrupt('&TSNAME', &RFN, &BLK)

exec dbms_space_admin.segment_drop_corrupt('&TSNAME', &RFN, &BLK)

exec dbms_space_admin.tablespace_rebuild_bitmaps('&TSNAME')

In our example it is:

							select relative_fno

from   dba_data_files

where  tablespace_name = 'TOOLS'

 and   file_id = 1707;

RELATIVE_FNO

------------

        1024

Then remove the temporary Segment and rebuild the tablespace bitmap:

							exec dbms_space_admin.segment_corrupt('TOOLS', 1024, 233066810)

exec dbms_space_admin.segment_drop_corrupt('TOOLS', 1024, 233066810)

exec dbms_space_admin.tablespace_rebuild_bitmaps('TOOLS')

Additional information:

Performance of dbms_space_admin.tablespace_rebuild_bitmaps: the execution time of dbms_space_admin.tablespace_rebuild_bitmaps normally takes a few minutes; it depends of the number of extents (not the tablespace size) and it does not depend of how long it takes to query dba_extents as rebuilding the bitmap is an operation within the datafile which does not access the Data Dictionary. For estimation, the time is about the same of executing dbms_space_admin.tablespace_verify.

Use Relative File Number for procedures dbms_space_admin.segment_corrupt and dbms_space_admin.segment_drop_corrupt: for the &RFN in the example above make sure to use the Relative File Number (value of dba_data_files column relative_fno); otherwise error ORA-3209 is produced:

							ERROR at line 1:

ORA-03209: DBMS_ADMIN_PACKAGE invalid file/block specification

ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 26

ORA-06512: at line 1

3. Optional step: Format the block

Fixing the corruption solves the errors and messages in the alert log. The block can still be reported by other tools like DBVerify or RMAN as corrupt but as the block is free it will not impact the database operations; it will be re-formatted with no errors when reused by another segment. Optionally, the block can be forced to be reused by using the procedure in Doc ID 336133.1

REFERENCES

NOTE:336133.1 - How to Format Corrupted Block Not Part of Any Segment
NOTE:422039.1 - ORA-600 [5463] While SMON is Doing Temporary Segment Drop
NOTE:1088018.1 - Master Note for Handling Oracle Database Corruption Issues