ORA-600 [kcbgcur_1] After Creating an Index on a Global Temporary Table (GTT) 19c

Problem:

On a 19.5 RAC DB, after the creation of an index on Global Temporary Table (GGT) started to notice that the following errors started to come intensively in the alertlog:

ORA-00600: internal error code, arguments: [kcbgcur_1], [1], [], [], [], [], [], [], [], [], [], []

At the same time, some application users started to report errors in their applications.

Analysis:

When you create an index on an actively used Global Temporary Table, and at the same time some sessions are doing DML transactions on that table, most probably you will hit Bug 27019512 which impacts DB versions from 12.1 and above.

Solution:

You have three options:

1- If you are on 19.11 then you are a lucky guy, just download the Interim Patch 27019512 which you can apply in a rolling fashion with a minimal downtime (if it's a clustered environment).

2- If you are (not on a 19.11) like me! Then open an SR with Oracle support to file a patch request that matches your Oracle version on behalf of you, and they will inform you once the patch is available.

3- If you are not on 19.11 and cannot wait for the patch to be available for your Oracle version, then you can apply the latest RU patch (19.12 or higher) where patch 27019512 is included.

Workarounds:

1- Avoid creating indexes on Global Temporary Tables if it's being used by application sessions. Shutdown or kill the application sessions first before doing a DDL on the GGT to avoid such bug.

2- In case the index is already created, and you started getting that silly ORA-00600 in the alertlog, this means some applications sessions are getting impacted, and you need to take an immediate action to stop their suffer; either by killing those sessions (mercy killing 😯) to force them to reconnect and get fresh locks on the GGT, or even by restarting the application services:

To identify the sessions to be killed that holding locks on GGT, do the following:

First, if you don't know which Global Temporary Table is impacted, you can get it from the trace file which appears in the alertlog the line after the ORA-00600 error:

 - Open the trace and Search for the keyword "objn:" the number in front of it represents the GGT  object_id:

$ grep 'objn:' /u01/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_1921.trc | tail -1
  dbwrid: 0 obj: 36036352 objn: 103359 tsn: [0/3] afn: 508 hint: f

- Use the object_id above to find the object_name:

SQL> select owner,object_name,object_type from dba_objects where object_id='103359';

OWNER                  OBJECT_NAME               OBJECT_TYPE
------------------------- ------------------------------- -----------------------
GGT_OWNER      GGT_TABLE_NAME         TABLE

Second, generate and execute the kill session commands that will Kill the sessions that are holding the locks on the GGT: [Execute it On each RAC node if it's a cluster env.]

SQL> select 'alter system disconnect session ' || ''''|| sid || ',' || serial# || ''' immediate;' "KILL_SESSIONS" from v$session where sid in (select sid from  v$lock where id1 = (select  object_id from  dba_objects where owner = 'GGT_OWNER'  and object_name = 'GGT_TABLE_NAME'));

Forgot to mention to not attempt to drop the newly created index on GTT because unless you get rid of those sessions holding locks on the GTT; most probably you will end up having this error:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

References:

ORA-600 [kcbgcur_1] When Executing DML on a Global Temporary Table (GTT) After Creating an Index on the GTT in Another Session (Doc ID 27019512.8)