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:
- export (datapump) table (object)
- drop table (object)
- purge recyclebin
- 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;
/