AWS Amazon Cloud oracle 19c
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], .
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.
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:
ORA-00600: internal error code, arguments: [insChkBuffering_1], , , , , , , , , , , 
View similar and related trc files:
Oracle Database 19c Enterprise Edition Release 188.8.131.52.0 - Production Version 184.108.40.206.0 Build label: RDBMS_220.127.116.11.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.104.22.168.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], , , , , , , , , , ,  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], , , , , , , , , , ,  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], , , , , , , , , , ,  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], , , , , , , , , , , 
Such information is also a headache, aws query does not have a similar file like
2.1 Support keyword search
Using the keyword
ORA-00600: internal error code, arguments: [kkmmctbf:bad intcoln],  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.
22.214.171.124.210119 (Jan 2021) Database Release Update (DB RU)
126.96.36.199.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;
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".
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.
1. "alter trigger t1_ariu compile";
2. Or delete and rebuild the trigger.
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.