Search This Blog

Thursday, November 18, 2010

ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3]

Symptoms matched description of Bug:4430244  described on Note 4430244.8
Segment advisor code can load blocks into the cache for dropped objects.
This can lead to internal buffer cache like ORA-600 [kcbz_check_objd_typ_3].

Known workarounds for this behaviour were used without success:
1. Disable automatic segment advisor
exec sys.dbms_scheduler.disable( '"SYS"."AUTO_SPACE_ADVISOR_JOB"' );
2.Use "alter system flush buffer cache" to clear bad blocks from the cache


This issue is fixed in

Segment Advisor
The segment advisor performs analysis on the fragmentation of specified tablespaces, segments or objects and makes recommendations on how space can be reclaimed. The advisor is accessible from Enterprise Manager (Home > Advisor Central > Segment Advisor) or from PL/SQL by using the  
    • DBMS_ADVISOR package:
    DECLARE
      l_object_id  NUMBER;
    BEGIN
      -- Create a segment advisor task for the SCOTT.EMP table.
      DBMS_ADVISOR.create_task (
        advisor_name      => 'Segment Advisor',
        task_name         => 'EMP_SEGMENT_ADVISOR',
        task_desc         => 'Segment Advisor For EMP');
    
      DBMS_ADVISOR.create_object (
        task_name   => 'EMP_SEGMENT_ADVISOR',
        object_type => 'TABLE',
        attr1       => 'SCOTT', 
        attr2       => 'EMP', 
        attr3       => NULL, 
        attr4       => 'null',
        attr5       => NULL,
        object_id   => l_object_id);
    
      DBMS_ADVISOR.set_task_parameter (
        task_name => 'EMP_SEGMENT_ADVISOR',
        parameter => 'RECOMMEND_ALL',
        value     => 'TRUE');
    
      DBMS_ADVISOR.execute_task(task_name => 'EMP_SEGMENT_ADVISOR');
    
      -- Create a segment advisor task for the USERS tablespace.
      DBMS_ADVISOR.create_task (
        advisor_name      => 'Segment Advisor',
        task_name         => 'USERS_SEGMENT_ADVISOR',
        task_desc         => 'Segment Advisor For USERS');
    
      DBMS_ADVISOR.create_object (
        task_name   => 'USERS_SEGMENT_ADVISOR',
        object_type => 'TABLESPACE',
        attr1       => 'USERS', 
        attr2       => NULL, 
        attr3       => NULL, 
        attr4       => 'null',
        attr5       => NULL,
        object_id   => l_object_id);
    
      DBMS_ADVISOR.set_task_parameter (
        task_name => 'USERS_SEGMENT_ADVISOR',
        parameter => 'RECOMMEND_ALL',
        value     => 'TRUE');
    
      DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
    END;
    / 
    
    -- Display the findings.
    SET LINESIZE 250
    COLUMN task_name FORMAT A20
    COLUMN object_type FORMAT A20
    COLUMN schema FORMAT A20
    COLUMN object_name FORMAT A30
    COLUMN object_name FORMAT A30
    COLUMN message FORMAT A40
    COLUMN more_info FORMAT A40
    
    SELECT f.task_name,
           f.impact,
           o.type AS object_type,
           o.attr1 AS schema,
           o.attr2 AS object_name,
           f.message,
           f.more_info
    FROM   dba_advisor_findings f
           JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
    WHERE  f.task_name IN ('EMP_SEGMENT_ADVISOR', 'USERS_SEGMENT_ADVISOR')
    ORDER BY f.task_name, f.impact DESC;
     

No comments:

Post a Comment

Transportable tablespace refresh

  1.check tablespace for the user which need to refresh -------------------------------------------------------------------  SQL> select ...