某客户数据库查询分区表遭遇ORA-00600 [20424], [1671764], [84], [SYS_XXX_Pxx4], [L_xxx_LOG]

告警日志分析:

opiodr aborting process unknown ospid (8734) as a result of ORA-609
Tue Jun 04 19:22:02 2019
DDE: Problem Key 'ORA 600 [qesmaGetTblSeg1]' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
Tue Jun 04 19:24:57 2019
Errors in file /oracle/xxx/diag/rdbms/xxx/xxx2/trace/xxx2_ora_12156.trc  (incident=221253):
ORA-00600: , : [20424], [1671764], [84], [SYS_XXX_Pxx4], [L_xxx_LOG], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Jun 04 19:24:58 2019
Sweep [inc][221253]: completed
Tue Jun 04 19:25:23 2019
Thread 2 advanced to log sequence 73142 (LGWR switch)
  Current log# 4 seq# 73142 mem# 0: +DATA/xxx/onlinelog/group_4.266.890476731
Tue Jun 04 19:25:55 2019
Errors in file /oracle/xxx/diag/rdbms/xx/xxx2/trace/xxx2_ora_12156.trc  (incident=221339):
ORA-00600: , : [20424], [1671764], [84], [SYS_XXX_Pxx4], [L_xxx_LOG], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Jun 04 19:25:58 2019
Sweep [inc][221339]: completed
Tue Jun 04 19:31:35 2019
Errors in file /oracle/xxx/diag/rdbms/xx/xxx2/trace/xxx2_ora_12156.trc  (incident=221387):
ORA-00600: , : [20424], [1671764], [84], [SYS_XXX_Pxx4], [L_xxx_LOG], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Jun 04 19:31:36 2019
Sweep [inc][221387]: completed
Tue Jun 04 19:32:02 2019
DDE: Problem Key 'ORA 600 [qesmaGetTblSeg1]' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
.......

复制

查询mos文档 ID 1503597.1 需要对分区进行删除或者重命名操作来解决:
检查相关表情况:

select table_owner,table_name,partition_name from DBA_TAB_PARTITIONS where table_name='L_xxx_LOG' and partition_name='SYS_XXX_Pxx4';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
xxx_user                        L_xxx_LOG                      SYS_XXX_Pxx4
复制

经过确认可以对该分区进行删除或者重命名操作:
以下通过删除该分区来解决:
二节点删除报错:

SQL> alter table xxx_user.L_xxx_LOG drop partition SYS_XXX_Pxx4;
alter table xxx_user.L_xxx_LOG drop partition SYS_XXX_Pxx4
*
ERROR at line 1:
ORA-00600: , : [20424], [1671764], [84], [SYS_XXX_Pxx4], [L_xxx_LOG], [], [], [], [], [], [], []

在一节点进行删除(比较奇怪，只能在另一个节点进行删除操作):

SQL> alter table xxx_user.L_xxx_LOG drop partition SYS_XXX_Pxx4;

Table altered.

复制

参考mos ORA-00600: internal error code, arguments: [20424] While Selecting From a Partition (文档 ID 1503597.1)

SYMPTOMS
ORA-00600: internal error code, arguments: [20424], [730755], [69], [Partition_name], [table_name], [], [], [], [], [], [], []

while running SELECT from a partition.

Select count(column_name) from tablename partition(part_name);

CAUSE
There are several bugs that seem similar. However, they are logged against 12.1.
 
Check if the Call stack from trace file is similar to:

skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp
<- ksfdmp <- dbgexPhaseII <- dbgexProcessError <- dbgeExecuteForError <- dbgePostErrorKGE
<- 1100 <- dbkePostKGE_kgsf <- kgeade <- kgeriv_int <- kgeriv
<- kgesiv <- ksesic4 <- kkpapDCBounds0 <- kkpapDCBounds <- kkdlgstd
<- kkmfcblo <- kkmpfcbk <- qcsprfro <- qcsprfro_tree <- qcsprfro_tree
<- qcspafq <- qcspqbDescendents <- qcspqb <- kkmdrv <- opiSem
<- opiDeferredSem <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild
<- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0
<- kpooprx <- kpoal8 <- opiodr <- ttcpip <- opitsk
<- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
<- ssthrdmain <- main <- start

SOLUTION
Possible Workaround 

=================
There has been damage in the particular partition ( Or on all partitions ) of the table, which leads to the ORA-00600.

Hence eliminating either the partition(s) or the table by dropping/renaming partition or the table is expected to resolve the error here.
This will eliminate the invalid entries of metadata by making them obsolete.


复制