ORA-600 [5463] While SMON is Doing Temporary Segment Drop

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.3
This 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 drop storage; 

Use the following instead: 
truncate reuse storage;

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

3. Mark the segment as corrupted:
exec dbms_space_admin.segment_corrupt('',98,110124)

4. Drop the segment
exec dbms_space_admin.segment_drop_corrupt('',98,110124)

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 ('');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-438620/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-438620/