Oracle Server - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2] Information in this document applies to any platform. ***Checked for relevance on 16-Mar-2012***
Segment header logical corruption can be introduced to any segment if ALLOCATE EXTENT operation aborts.
If Patch 8198906 has been installed this can be reported with error ORA-600 [ktsptrn_fix-extmap].
Occurrence
This issue is specific to segments stored in a Locally Managed Tablespace (LMT) that is using Automatic Segment Space Management; ASSM (dba_tablespaces.segment_space_management='AUTO').
LOB segments are more sensitive to be affected since extent allocation for this type of segments takes longer and are larger than usual.
The problem happens when manual ALLOCATE EXTENT is used and the user aborts the session using CTRL+C or if ALTER TABLE .. ALLOCATE EXTENT fails due to space constraint at tablespace level; e.g., ORA-1691, ORA-1653, ORA-1654, etc.
Example of syntax of a manual extent allocation for a table and for a LOB segment:
alter table (allocate extent (size ));
alter table modify lob () (allocate extent (size ));
Testcase script. example to reproduce this issue (please do not run it in a production environment as this will introduce the problem causing SMON to crash the instance):
grant CONNECT, RESOURCE to TC identified by DONOTUSE; drop table TC.T purge; create tablespace TC datafile 'TC.dbf' size 100M autoextend off; create table TC.T (i int, c clob) tablespace TC lob (c) store as (disable storage in row); alter table TC.T modify lob (C) (allocate extent (size 100M));
-- ORA-1691 hit during allocation introduces problem -- CNTRL-C during allocation introduces problem
alter table TC.T modify lob (C) (allocate extent (size 1M));
-- subsequent attempt to modify the table shows ORA-600 [kddummy_blkchk] since segment header is now logically corrupt.
drop table TC.T purge; drop tablespace TC including contents and datafiles; drop user TC;
1. If the datafile is not using AUTOEXTEND, it fails to allocate an EXTENT and introduces the logical corruption reporting error ORA-600 [kddummy_blkchk].
2. If DATAFILE is using AUTOEXTEND and it is unable to EXTEND, it fails to allocate an EXTENT and introduces the logical corruption reporting an ORA-1691: unable to extend lob segment. Alert log reports the additional errors described in this note.
Symptoms
User process can fail with the following exceptions reported in the database alert log file. Example:
Extent Map ----------------------------------------------------------------- 0x05c188c9 length:16 0x00000000 length: 0 <<< If db_block_checking is enabled or in 10g or higher versions the next errors prevent the zero dba in the extent map:
The trace file prints the block after image with the zero dba but it does not get propagated to disk. The zero dba is not the only logical corruption introduced by this issue in the segment header which remains as logically corrupt.
Workaround
DROP the affected segment.
The data from the TABLE and LOB should be still accessible using EXPDP/IMPDP, CTAS , index access (in case of a table), etc ...
If the error is still produced by SMON while cleaning the temporary segment, use the next procedures from DBMS_SPACE_ADMIN to clear the segment :
If dropping the table you receive an ora-600 with ora-600 [kdBlkCheckError] or ORA-600 [kddummy_blkchk] are produced by dropping the segment, disable checksum, drop the segment and re-enable the checksum by executing:
Per Note 8198906.8 its fix is included in RDBMS 11.2.0.1 (Base Release) and RDBMS 10.2.0.5 (Server Patch Set), therefore, it is mandatory to install Patch 9711859 on top of both versions.
