ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0]

问题背景：

客户数据库宕机，需要排查数据库宕机原因

1> 查看alert日志

 1 Wed Nov 13 22:00:11 2019
 2 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc (incident=73515):
 3 ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], []
 4 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73515/orcl_j004_26935_i73515.trc
 5 Wed Nov 13 22:00:14 2019
 6 Trace dumping is performing id=[cdmp_20191113220014]
 7 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc (incident=73516):
 8 ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], []
 9 ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], []
10 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73516/orcl_j004_26935_i73516.trc
11 Wed Nov 13 22:00:15 2019
12 Sweep [inc][73516]: completed
13 Sweep [inc][73515]: completed
14 Sweep [inc2][73515]: completed
15 Trace dumping is performing id=[cdmp_20191113220018]
16 Wed Nov 13 22:00:21 2019
17 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_2510.trc (incident=72019):
18 ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], []
19 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72019/orcl_pmon_2510_i72019.trc
20 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_2510.trc:
21 ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], []
22 PMON (ospid: 2510): terminating the instance due to error 472
23 Instance terminated by PMON, pid = 2510
24 Thu Nov 14 08:04:36 2019
25 Starting ORACLE instance (normal)
26 LICENSE_MAX_SESSION = 0
27 LICENSE_SESSIONS_WARNING = 0
28 Picked latch-free SCN scheme 3
29 Autotune of undo retention is turned on.

2> 数据库遭遇了ORA-00600的错误，

继续查看

 1 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73516/orcl_j004_26935_i73516.trc
 2 
 3 Dump file /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73515/orcl_j004_26935_i73515.trc
 4 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 5 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 6 ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1
 7 System name:    Linux
 8 Node name:    OA-DBSVR
 9 Release:    3.10.0-123.el7.x86_64
10 Version:    #1 SMP Mon May 5 11:16:57 EDT 2014
11 Machine:    x86_64
12 Instance name: orcl
13 Redo thread mounted by this instance: 1
14 Oracle process number: 189
15 Unix process pid: 26935, image: oracle@OA-DBSVR (J004)
16 
17 
18 *** 2019-11-13 22:00:11.667
19 *** SESSION ID:(437.12455) 2019-11-13 22:00:11.667
20 *** CLIENT ID:() 2019-11-13 22:00:11.667
21 *** SERVICE NAME:(SYS$USERS) 2019-11-13 22:00:11.667
22 *** MODULE NAME:(DBMS_SCHEDULER) 2019-11-13 22:00:11.667
23 *** ACTION NAME:(ORA$AT_SA_SPC_SY_8162) 2019-11-13 22:00:11.667
24 
25 Dump continued from file: /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc
26 ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], []
27 
28 ========= Dump for incident 73515 (ORA 600 [kcbo_unlink_q_3]) ========
29 
30 *** 2019-11-13 22:00:11.669
31 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
32 ----- Current SQL Statement for this session (sql_id=8szmwam7fysa3) -----
33 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))
34 ----- PL/SQL Stack -----
35 ----- PL/SQL Call Stack -----
36 object line object
37 handle number name
38 
39 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))
40 这条sql引发了数据库ORA-00600错误
41 
42 tkprof orcl_j004_26935_i73516.trc orcl_j004_26935_i73516.txt
43 内容如下
44 [oracle@OA-DBSVR incdir_73515]$ cat orcl_j004_26935_i73515.txt
45 
46 TKPROF: Release 11.2.0.1.0 - Development on Thu Nov 14 15:28:37 2019
47 
48 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
49 
50 Trace file: orcl_j004_26935_i73515.trc
51 Sort options: default
52 
53 ********************************************************************************
54 count = number of times OCI procedure was executed
55 cpu = cpu time in seconds executing 
56 elapsed = elapsed time in seconds executing
57 disk = number of physical reads of buffers from disk
58 query = number of buffers gotten for consistent read
59 current = number of buffers gotten in current mode (usually for update)
60 rows = number of rows processed by the fetch or execute call
61 ********************************************************************************
62 Trace file: orcl_j004_26935_i73515.trc
63 Trace file compatibility: 11.1.0.7
64 Sort options: default
65 
66 1 session in tracefile.
67 0 user SQL statements in trace file.
68 0 internal SQL statements in trace file.
69 0 SQL statements in trace file.
70 0 unique SQL statements in trace file.
71 160288 lines in trace file.
72 0 elapsed seconds in trace file.

3> 查询一下这条sql是做什么用的wri$_adv_objspace_trend_data

1 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))

自动SQL调整顾问：自动标识并尝试调整高负载的SQL，任务名是“sqltuning advisor”。
也花费了大量时间这显然是不正常的。

在Metalink上存在如下一个Bug：
Bug 5376783: DBMS_SPACE.OBJECT_GROWTH_TREND CALL TAKES A LOT OF DISK READS

这个Bug在DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发，根本原因在于内部算法在执行空间检查时，耗费了大量的评估IO成本，导致了大量的IO资源使用：

在后台跟踪里可以看到这个步骤的资源消耗：

1 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, 
2 space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, 
3 :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))

临时的处理办法是，暂时关闭这个自动任务：

1 execute dbms_scheduler.disable('sys.auto_space_advisor_job');

这个Bug在10.2.0.2之后的版本中被修正。记录一下供参考！

-The End-