ORA-600 [kkpo_rcinfo_defstg:objnotfound] on a 11.2 RAC instance

RECOMMENDATION: Set the deferred_segment_creation parameter to FALSE for 11.2 RAC databases!

Recently our monitoring picked up an ORA-600 from the alertlog of one of our 11.2 RAC instances.

ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound],[87421], [], [], [], [], [], [], [], [], [], []

Oracle Support was telling me that I was hitting bug 14252187 (Bug 14252187 – ORA-600 [qesmaGetTblSeg1] from deferred segment creation in RAC (Doc ID 14252187.8)) and there is no patch available. The ORA-600 is generated on one of the RAC instances (maybe more, but I have only seen this on 2-node RAC databases) while the other RAC instance does not have the problem.

Once the ORA-600 is thrown, the error is very easy to reproduce by looking at the trace file that is generated to find the (table) object that is involved. Just running a simple SELECT count(1) FROM <tablename> will throw you the error on 1 RAC instance and will show a result on the other RAC instance.

Solution

The only way to solve the problem (there is no patch for it, except an Exadata database patch 11.2.0.3.8) is to:

  1. export (datapump) table (object)
  2. drop table (object)
  3. purge recyclebin
  4. import (datapump) table (object)

Where the table (object) is the object that is involved (most of the times it is a very small table because it was a deferred object). You have to make sure you run the datapump export on the RAC instance that is able to access the object (so execute the SELECT count(1) FROM <tablename> to find out the RAC instance you can use!).

Prevention

The only workaround to prevent this problem from happening, is to set the init parameter deferred_segment_creation to FALSE, that disables the creation of deferred objects. The problem with this workaround is that is will only prevent the problem from happening for tables (objects) that are created after you have set this parameter to false.

Unfortunately there is not way to predict which object is going to get into problems, it could be any of the “deferred” objects. I have to say Oracle Support is not very helpfull on this matter, they just say to set the parameter to false and do an export/import when the error occurs. They just don’t want to hear that this workaround will most likely cause downtime for a production application.

Most likely you already have deferred objects in your database, because the default value for the deferred_segment_creation parameter is TRUE. In a RAC database these are all objects that could eventually result into this ORA-600 bug. To get rid of all deferred objects in the application schema’s I have created some PL/SQL code, that uses the DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS procedure to for the creation of a segment for a deferred table or table partition. I have asked Oracle Support if this will prevent this error for sure, but I haven’t received a response yet.

SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 500 HEAD OFF FEEDBACK OFF

-- Will use DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS to force segment creation on tables, table partitions and table subpartitions

DECLARE
  CURSOR c_deftabs IS
    SELECT
      owner, table_name
    FROM
      dba_tables
    WHERE
        segment_created='NO'
      AND
        owner NOT IN ('SYS','SYSTEM','WMSYS','EXFSYS','DBSNMP','CTXSYS','APPQOSSYS','XDB','ANONYMOUS','DIP','OUTLN','XS$NULL','MDSYS')
    ORDER BY
      owner, table_name;

  CURSOR c_deftabparts IS
    SELECT
      table_owner, table_name, partition_name
    FROM
      dba_tab_partitions
    WHERE
        segment_created='NO'
      AND
        table_owner NOT IN ('SYS','SYSTEM','WMSYS','EXFSYS','DBSNMP','CTXSYS','APPQOSSYS','XDB','ANONYMOUS','DIP','OUTLN','XS$NULL','MDSYS')
    ORDER BY
      table_owner, table_name, partition_name;

  CURSOR c_deftabsubparts IS
    SELECT
      table_owner, table_name, partition_name, subpartition_name
    FROM
      dba_tab_subpartitions
    WHERE
        segment_created='NO'
      AND
        table_owner NOT IN ('SYS','SYSTEM','WMSYS','EXFSYS','DBSNMP','CTXSYS','APPQOSSYS','XDB','ANONYMOUS','DIP','OUTLN','XS$NULL','MDSYS')
    ORDER BY
      table_owner, table_name, partition_name, subpartition_name;


  l_stmt    VARCHAR2(255);
BEGIN
  dbms_output.put_line('Force segment creation for deferred tables:');
  dbms_output.put_line('============================================================================================');

  FOR r_deftab IN c_deftabs
  LOOP
    BEGIN
      -- Force segment creation
      DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS(schema_name => r_deftab.owner, table_name => r_deftab.table_name);
      dbms_output.put_line('Segment created for deferred table: '||r_deftab.owner||'.'||r_deftab.table_name);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('[ERROR] Error executing statement:');
        dbms_output.put_line(l_stmt);
        dbms_output.put_line(SUBSTR(SQLERRM, 1, 200));
    END;
  END LOOP;

  -- Force segment creation for table partitions
  FOR r_deftabpart IN c_deftabparts
  LOOP
    BEGIN
      -- Force segment creation for table partition
      DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS(schema_name => r_deftabpart.table_owner, table_name => r_deftabpart.table_name);
      dbms_output.put_line('Segment created for deferred table: '||r_deftabpart.table_owner||'.'||r_deftabpart.table_name);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('[ERROR] Error executing statement:');
        dbms_output.put_line(l_stmt);
        dbms_output.put_line(SUBSTR(SQLERRM, 1, 200));
    END;
  END LOOP;

  -- Force segment creation for table partitions (including subpartitions)
  FOR r_deftabsubpart IN c_deftabsubparts
  LOOP
    BEGIN
      -- Force segment creation for table subpartition
      DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS(schema_name => r_deftabsubpart.table_owner, table_name => r_deftabsubpart.table_name);
      dbms_output.put_line('Segment created for deferred table: '||r_deftabsubpart.table_owner||'.'||r_deftabsubpart.table_name);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('[ERROR] Error executing statement:');
        dbms_output.put_line(l_stmt);
        dbms_output.put_line(SUBSTR(SQLERRM, 1, 200));
    END;
  END LOOP;
END;
/

 

This entry was posted in RAC and tagged , , , , , , , , , . Bookmark the permalink.

One Response to ORA-600 [kkpo_rcinfo_defstg:objnotfound] on a 11.2 RAC instance

  1. zia uddin says:

    “Oracle Exadata targets that can be monitored and managed by OEM 12c such as Oracle Exadata Storage Servers,Database Server or Compute nodes,Infiniband Switches,Cisco Switches,Power Distribution Units (PDU),KVM (Keyboard, Video & Mouse)
    In this article, I will describe how to Discover Exadata Database Machine OEM 12c”

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*