19C ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln], [49]

created at 10-07-2021 views: 23

environment:

AWS Amazon Cloud oracle 19c

Problem description:

Data is deleted in production (column deletion of large tables). After deletion, the alarm log always reports an error: ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln], [49].

The deleted statement is:

alter table user_order_detail set unused (DEL_IND,DEL_USER_ID,DEL_DTT) online;
alter table user_order_detail drop unused columns checkpoint 1000;

In fact, several aws executed the delete statement and no error was reported; but a few reported errors.

Troubleshooting:

1. Check whether there is a problem with the business
Although we all know that ORA-600 is an error caused by an internal problem in the database, some of the oracle databases have reported errors, and some have not reported errors. Therefore, it was initially suspected that the table structure was changed due to the business, and the business code did not change. The database does not recognize the business code and throws an error;

After the investigation by the business developers, there was no problem, so it was still suspected to be an internal problem in the database.

2. Troubleshoot database problems
View alert log details:

error log

ORA-00600: internal error code, arguments: [insChkBuffering_1], [4], [4], [], [], [], [], [], [], [], [], []

View similar and related trc files:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
Build label: RDBMS_19.8.0.0.0DBRU_LINUX.X64_200702
ORACLE_HOME: /rdsdbbin/oracle
System name: Linux
Node name: 
Release: 4.1.12-124.28.6.el7uek.x86_64
Version: #2 SMP Tue Jun 25 20:08:29 PDT 2019
Machine: x86_64
VM name: Xen Version: 4.11 (HVM)
Instance name: CLDTTFC
Redo thread mounted by this instance: 1
Oracle process number: 1456
Unix process pid: 28607, image: oracle


*** 2021-09-16T20:10:29.643785+08:00
*** SESSION ID:(15042.34599) 2021-09-16T20:10:29.643812+08:00
*** CLIENT ID:() 2021-09-16T20:10:29.643845+08:00
*** SERVICE NAME:(SYS$USERS) 2021-09-16T20:10:29.643853+08:00
*** MODULE NAME:(dotnet.exe) 2021-09-16T20:10:29.643861+08:00
*** ACTION NAME:() 2021-09-16T20:10:29.643868+08:00
*** CLIENT DRIVER:(ODPC.NET : 18.3.0.0.0) 2021-09-16T20:10:29.643914+08:00

2021-09-16T20:10:29.643530+08:00
Incident 2411672 created, dump file: /rdsdbdata/log/diag/rdbms/cldttfc_a/CLDTTFC/incident/incdir_2411672/CLDTTFC_ora_28607_i2411672.trc
ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln], [49], [], [], [], [], [], [], [], [], [], []

2021-09-16T21:27:20.089785+08:00
Incident 2411673 created, dump file: /rdsdbdata/log/diag/rdbms/cldttfc_a/CLDTTFC/incident/incdir_2411673/CLDTTFC_ora_28607_i2411673.trc

*** 2021-09-16T21:27:20.089915+08:00
ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln], [49], [], [], [], [], [], [], [], [], [], []

2021-09-17T07:16:29.448634+08:00
Incident 2411674 created, dump file: /rdsdbdata/log/diag/rdbms/cldttfc_a/CLDTTFC/incident/incdir_2411674/CLDTTFC_ora_28607_i2411674.trc

*** 2021-09-17T07:16:29.448742+08:00
ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln], [49], [], [], [], [], [], [], [], [], [], []

2021-09-17T09:26:38.390214+08:00
Incident 2411675 created, dump file: /rdsdbdata/log/diag/rdbms/cldttfc_a/CLDTTFC/incident/incdir_2411675/CLDTTFC_ora_28607_i2411675.trc

*** 2021-09-17T09:26:38.390317+08:00
ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln], [49], [], [], [], [], [], [], [], [], [], []

Such information is also a headache, aws query does not have a similar file like CLDTTFC_ora_28607_i2411675.trc.

2.1 Support keyword search
Using the keyword ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln], [49] to search for a series of less relevant solutions, you need to

alter system set "_ignore_fg_deps"=ALL;

But the related BUGs are all 12c and previous versions, and 19c is not involved, which is not very reliable.

2.2 Bug 30404639 (support Doc ID 30404639.8)
Later, some statements cannot be executed through related businesses, and the statements that cannot be executed are found to include trigger-related businesses. So it is found that the trigger is abnormal due to the drop unused column.

21.1.0.0.0
19.10.0.0.210119 (Jan 2021) Database Release Update (DB RU)
18.14.0.0.210420 (APR 2021) Database Release Update (DB RU)
The previous major versions of these three versions have this BUG, which has been fixed since then.

Deleting columns in tables with triggers may result in incorrect trigger operations if the columns used by the trigger refer to :NEW/:OLD.

If the trigger stops working normally afterwards, you may see this problem. The table has deleted a column. The trigger uses :OLD or :NEW. Please see the example operation below support (non-sys user)
The following is a slightly modified script because it is 19.3, the support is 18.3, there is no truncate table t2 after deleting the column,
Script from: https://jonathanlewis.wordpress.com/2020/01/14/drop-column-bug/

create table t1 (c0 varchar2(30), c1 varchar2(30), c2 varchar2(30), c3 varchar2(30), c4 varchar2(30));
create table t2 (c_log varchar2(30));

create or replace trigger t1_ariu
after insert or update on t1
for each row
begin
        IF :new.c3 is not null then
                insert into t2 values (:new.c3);
        end if;
end;
/


insert into t1(c3) values ('Inserting c3 - should log');
select * from t2;

insert into t1(c4) values ('Inserting c4 - should not log');
select * from t2;

Now, when the T1.C2 column is deleted, we want the trigger to fail and recompile to work again. However, invalidation occurs, and the trigger works in unexpected ways.

alter table t1 set unused (c1, c2);
alter table t1 drop unused columns;

truncate table t2;

insert into t1(c3) values ('Inserting c3 - should log');
select * from t2;

insert into t1(c4) values ('Inserting c4 - should not log');
select * from t2;

ora-00600

The trigger seems to "lost the count of the column in the table" (presumably it was compiled to refer to something like "column_position = 3" and will not be adjusted on the "drop column"-the link error comment on MOS refers to This problem is related to the project to increase the fine-grained dependencies) so it managed to survive the deletion of one column, because there is still a "column 3" which now happens to be the column that was once the "column 4".

Solution

If after deleting a column of a large table in the production oracle database, the error ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln] is reported, this will only use a two-step method to delete the column, namely: first set unused, and then delete . If you just issue "alter table t1 drop column c1" (with or without "checkpoint NNN"), there will be no problem. Delete the column that appears in the table before the highest position column mentioned in the trigger. Because the order listed in the table declaration is not necessarily the internal column order.

Solution:

1. "alter trigger t1_ariu compile";

2. Or delete and rebuild the trigger.

Specify COMPILE to explicitly compile the trigger, regardless of whether it is valid or invalid. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents related runtime compilation errors and performance overhead.

recompilation

created at:10-07-2021
edited at: 10-07-2021: