Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.3This problem can occur on any platform.
Symptoms
When starting the database after a crash or for normal maintenance after a truncate table, the error below will occur 100 times and then the instance will crash.
The errors are caused because SMON is trying to clean a temp segment in a tablespace by looking at the bitmap index in the tablespace. The bitmap index is corrupted and this causes SMON to fail when trying to clean up the segment.
From the alert.log:
Errors in file /oracle/bdump/orcl_smon_17269.trc:
ORA-00600: internal error code, arguments: [5463], [], [], [], [], [], [], []
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 53 out of maximum 100 non-fatal internal errors.
Changes
The issue occurred after a table was truncated with drop storage option.
ORA-00600: internal error code, arguments: [5463], [], [], [], [], [], [], []
Current SQL statement for this session:
TRUNCATE TABLE SCOTT.ABC_DEF_1001_RESOURCE_TRANS DROP STORAGE
Cause
To avoid the problem in the future:
Do not truncate the base table using:
truncate
truncate
Use the following instead:
truncate
Solution
The first step is to review the tracefile to find the correct datafile that is having the issue.
Search on the extent map in the trace file - this will show the following. Convert the hex value to datafile and block - ignore the block because that might be the level 1 bitmap. We are looking for level 3 bitmap which has the information about the temp segment. You will need to use the WEBIV support tools DBA conversion to convert the hex value to datafile and block.
Extent Map
-----------------------------------------------------------------
0x1881ae29 length: 16 -> Datafile 98, Block 110121
0x1881c289 length: 16
1. set event 10061 in the pfile to turn off SMON from cleaning temp segments. This will keep the database from crashing after SMON tries 100 times to clean the segment.
event = '10061 trace name context forever, level 10'
2. >select segment_name, segment_type from dba_segments where
header_file=98 and segment_type='TEMPORARY';
SEGMENT_NAME SEGMENT_TYPE
------------------- -----------------------
98.110124 TEMPORARY
98.110124 <---------- datafile and block number
3. Mark the segment as corrupted:
exec dbms_space_admin.segment_corrupt('
4. Drop the segment
exec dbms_space_admin.segment_drop_corrupt('
5. Rebuild all the bitmap indexes
exec dbms_space_admin.tablespace_rebuild_bitmaps('
6. Remove event 10061 from pfile and bounce database
7. Dbms_space_admin.tablespace_verify ('