ORA-00600 KGLDELETEALLPINSONOBJ1 a BUG crossing 10g/11g/12c

Posted by Ind007 on Thu, 06 Feb 2020 09:03:03 +0100

A friend on motianlun encountered a rare ORA-600 error. The first error parameter is KGLDELETEALLPINSONOBJ1.

From the naming of KGLDELETEALLPINSONOBJ1, we can quickly guess the basic situation:

KGL - Kernel Generic Library cache, which means that the problem lies in the Library Cache level;

DELETE - means to DELETE information;

ALLPINSONOBJ - represents ALL Pins On OBJ and all library cache pins on an object

So this error means that Oracle encountered an internal error and threw an exception when clearing all library cache pins on an object. Most of this is caused by bugs at the code level.

We extract an error tracking file:

Trace file /u01/app/oracle/diag/taldb1_m001_1107.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /u01/app/oracle/product/12.2.0.1/dbhome_1
System name: Linux
Release: 2.6.39-400.281.1.el6uek.x86_64
Version: #1 SMP Fri Jun 17 20:10:16 PDT 2016
Machine: x86_64
Instance name: portaldb1
Redo thread mounted by this instance: 1
Oracle process number: 646
Unix process pid: 1107, image: oracle@bserver (M001)

*** 2019-12-02T03:37:04.327775+08:00
*** SESSION ID:(2589.45217) 2019-12-02T03:37:04.327800+08:00
*** CLIENT ID:() 2019-12-02T03:37:04.327809+08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2019-12-02T03:37:04.327817+08:00
*** MODULE NAME:(MMON_SLAVE) 2019-12-02T03:37:04.327826+08:00
*** ACTION NAME:(Advisor Task Maintenance) 2019-12-02T03:37:04.327834+08:00
*** CLIENT DRIVER:() 2019-12-02T03:37:04.327842+08:00

2019-12-02T03:37:04.327545+08:00
Incident 1525642 created, dump file: /u01/app/oracle/taldb1_m001_1107_i15.trc
ORA-00600: internal error code, arguments: [kglDeleteAllPinsOnObj1], [0x1D4E29D4A8], [0x18D8D5C8C8], [], [], [], [], [], [], [], [], []

As can be seen from the above trace file, the basic elements of this problem are as follows:

1. MMON performs internal operation;

2. The internal operation is Advisor Task Maintenance;

Further more detailed information can be obtained from the trace file. The CLEANUP / DELETE operation of the system task SQL Access Advisor triggered the ORA-600 error. The trigger statement is a DROP materialized view operation:

----- Current SQL Statement for this session (sql_id=5qcmq7xb5zrhj) -----
DROP MATERIALIZED VIEW "SYSTEM"."MV$$_341480004"
[TOC00005]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x1d1d528fd0 1619 package body SYS.PRVT_ACCESS_ADVISOR.CLEANUP_TASK
0x1d1d528fd0 1265 package body SYS.PRVT_ACCESS_ADVISOR.RESET_TASK
0x1d1d528fd0 1356 package body SYS.PRVT_ACCESS_ADVISOR.DELETE_TASK
0x180eab3b78 38 type body SYS.WRI$_ADV_SQLACCESS_ADV.SUB_DELETE
0x1d5e8873e8 2132 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x1d5e8873e8 7326 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x180e53c568 1 anonymous block

Combined with these factors, we need to use the official support site MOS to find the known BUG.

The first related BUG is from Oracle 10g, and the affected version is 10.2.0.4:

Bug 8262446 Problems with DBMS_ADVISOR.DELETE_TASK

This is the most directly related problem. The same problem still appears in 11g, and now it seems that it has not been completely solved in 12c.

Of course, this problem is triggered by uncommitted transactions. If you can ensure that there is no active transaction before deleting, you can avoid it. In addition, in the frequently triggered environment, you can consider disabling the DBMS advisor timer task (in fact, many users do not use and refer to the conclusion of this feature).

Another BUG in 12.2 was found: