KGL-heap-size-exceeded报错

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：KGL-heap-size-exceeded报错

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

alert日志报错

2021-04-19T15:06:08.208159+08:00
Memory Notification: Library Cache Object loaded into SGA
Heap size 512000K exceeds notification threshold (51200K)
Details in trace file /mnt/app/diag/rdbms/xff/xff/trace/xff_ora_12300.trc
2021-04-19T15:06:08.208212+08:00
KGL object name :select DISTINCT(BEGIN_TIME) beginTime,
    MILE mile,
    END_TIME endTime,
    LAST_TIME lastTime,
    BEGIN_TIME beginTime2,
    END_TIME endTime2,
    TEMPERATURE1 temperature1,
    TEMPERATURE2 temperature2,
    TEMPERATURE3 temperature3,
    TEMPERATURE4 temperature4,
    LATITUDE latitude,
    LONGITUDE longitude
    from T_XIFENFEI
    where VEHICLE_ID = :1 
    and END_TIME is not null
    and BEGIN_TIME BETWEEN TO_DATE(:2 , 'yyyy-MM-dd hh24:mi:ss') and TO_DATE(:3 , 'yyyy-MM-dd hh24
Errors in file /mnt/app/diag/rdbms/xff/xff/trace/xff_ora_12300.trc  (incident=162089):
ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded], 
[0x1C4782708], [0], [524288008], [], [], [], [], [], [], [], []
Incident details in: /mnt/app/diag/rdbms/xff/xff/incident/incdir_162089/xff_ora_12300_i162089.trc
Use ADRCI or Support Workbench to package the incident.

数据库版本信息

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:    /mnt/app/product/12.2.0/dbhome_1
System name:    Linux
Node name:      izbp1399kdj96ra6bjtwyxz
Release:        3.10.0-693.2.2.el7.x86_64
Version:        #1 SMP Tue Sep 12 22:26:13 UTC 2017
Machine:        x86_64

Call Stack信息

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+119         call     kgdsdst()            7FFD6CB9A258 000000002
                                                   7FFD6CB7BCC0 ? 7FFD6CB7BDD8 ?
                                                   000000000 000000082 ?
dbkedDefDump()+1200  call     ksedst()             000000000 000000002 ?
                                                   7FFD6CB7BCC0 ? 7FFD6CB7BDD8 ?
                                                   000000000 ? 000000082 ?
ksedmp()+259         call     dbkedDefDump()       000000003 000000002
                                                   7FFD6CB7BCC0 ? 7FFD6CB7BDD8 ?
                                                   000000000 ? 000000082 ?
dbgexPhaseII()+2130  call     ksedmp()             0000003EB 000000002 ?
                                                   7FFD6CB7BCC0 ? 7FFD6CB7BDD8 ?
                                                   000000000 ? 000000082 ?
dbgexExplicitEndInc  call     dbgexPhaseII()       7F4853DB46C0 7F4853DB77A0
()+602                                             7FFD6CB9E388 7FFD6CB7BDD8 ?
                                                   000000000 ? 000000082 ?
dbgeEndDDEInvocatio  call     dbgexExplicitEndInc  7F4853DB46C0 7F4853DB77A0
nImpl()+658                   ()                   7FFD6CB9E388 ? 7FFD6CB7BDD8 ?
                                                   000000000 ? 000000082 ?
kglLargeHeapWarning  call     dbgeEndDDEInvocatio  7F4853DB46C0 7F4853DB77A0
()+1544                       nImpl()              7F4853DF49A0 7FFD6CB7BDD8 ?
                                                   7F4853DF49A0 000000082 ?
kglHeapAllocCbk()+2  call     kglLargeHeapWarning  7F4853DF49A0 ? 1C4782708 ?
27                            ()                   000000000 01F400060 ?
                                                   0B6DADD88 000000082 ?
kghalp()+1086        call     kglHeapAllocCbk()    7F4853DF49A0 ? 1C4782708 ?
                                                   2EE171B70 01F400060 ?
                                                   000000058 ? 000000082 ?
kkocsRegBindEqvCtxC  call     kghalp()             7F4853DF49A0 ? 0B6DADD88 ?
ommon()+145                                        2EE171B70 ? 000000001 ?
                                                   000000058 ? 0119E7860 ?
kkocsAddBeElem()+81  call     kkocsRegBindEqvCtxC  7F4853DF49A0 ? 7FFD6CB9F460 ?
1                             ommon()              2EE171B70 ? 000000001 ?
                                                   000000002 ? 0119E7860 ?
kkocsProcBSensPredC  call     kkocsAddBeElem()     7F484E2EF998 ? 7F484E2EFAC8 ?
B()+431                                            0BBED4798 ? 209DFEA60 ?
                                                   000000002 ? 0119E7860 ?
qksopVisitPreds()+5  call     kkocsProcBSensPredC  7F484E2EF998 ? 7FFD6CB9F640 ?
39                            B()                  0BBED4798 ? 209DFEA60 ?
                                                   000000002 ? 0119E7860 ?
kkocsBuildBESelCtx(  call     qksopVisitPreds()    7F484E2EF998 ? 7FFD6CB9F640 ?
)+153                                              0BBED4798 ? 209DFEA60 ?
                                                   000000002 ? 0119E7860 ?
kkocsBindSensCheck(  call     kkocsBuildBESelCtx(  7F484E2EF998 ? 7FFD6CB9F640 ?
)+585                         )                    0BBED4798 ? 209DFEA60 ?
                                                   000000002 ? 0119E7860 ?
kkoipt()+901         call     kkocsBindSensCheck(  7F484E2EFA20 ? 7FFD6CB9F640 ?
                              )                    0BBED4798 ? 209DFEA60 ?
                                                   000000002 ? 0119E7860 ?
kkoqbc()+4677        call     kkoipt()             7F484E31F1E0 ? 7FFD6CB9F640 ?
                                                   0BBED4798 ? 209DFEA60 ?
                                                   000000002 ? 000000000
apakkoqb()+183       call     kkoqbc()             7F484E31F1E0 ? 7F4853CC2958 ?
                                                   0BBED4798 ? 209DFEA60 ?
                                                   000000001 000000000 ?
apaqbdDescendents()  call     apakkoqb()           7FFD6CBA1870 ? 000000EB5 ?
+503                                               0BBED4C18 ? 209DFEA60 ?
                                                   000000001 ? 000000000 ?
apaqbd()+136         call     apaqbdDescendents()  7FFD6CBA1870 ? 7F4853CC2958 ?
                                                   0BBED4C18 ? 209DFEA60 ?
                                                   000000001 ? 000000000 ?
apadrv()+1228        call     apaqbd()             7FFD6CBA1870 ? 7F4853CC2958 ?
                                                   0BBED4C18 ? 209DFEA60 ?
                                                   000000001 ? 000000000 ?
opitca()+2139        call     apadrv()             0BBED4C18 ? 7F4853CC2958 ?
                                                   0BBED4C18 ? 209DFEA60 ?
                                                   000000001 ? 000000000 ?
kksFullTypeCheck()+  call     opitca()             7F484E2F2A08 0BBED4C18
86                                                 7FFD6CBA42F0 209DFEA60 ?
                                                   000000001 ? 000000000 ?
rpiswu2()+627        call     kksFullTypeCheck()   7FFD6CBA2FF8 ? 0BBED4C18 ?
                                                   7FFD6CBA42F0 ? 209DFEA60 ?
                                                   000000001 ? 000000000 ?
kksLoadChild()+8003  call     rpiswu2()            7FFD6CBA2FF8 ? 0BBED4C18 ?
                                                   7FFD6CBA42F0 ? 209DFEA60 ?
                                                   7FFD6CBA24F8 ? 00000006A ?
kxsGetRuntimeLock()  call     kksLoadChild()       7F4853DF49A0 0BBED4C18 ?
+2029                                              7FFD6CBA42F0 ? 209DFEA60 ?
                                                   7FFD6CBA24F8 ? 00000006A ?
kksfbc()+15091       call     kxsGetRuntimeLock()  7F4853DF49A0 7F484E2F2A08
                                                   7FFD6CBA4270 209DFEA60 ?
                                                   7FFD6CBA24F8 ? 1C4782708
opiexe()+2932        call     kksfbc()             7F484E2F2A08 7F484E2F2A08 ?
                                                   7FFD6CBA4270 ? 209DFEA60 ?
                                                   7FFD6CBA24F8 ? 1C4782708 ?
kpoal8()+2679        call     opiexe()             000000049 7F484E2F2A08 ?
                                                   7FFD6CBA58F0 209DFEA60 ?
                                                   7FFD6CBA24F8 ? 1C4782708 ?
opiodr()+1229        call     kpoal8()             00000005E 000000026
                                                   7FFD6CBA9300 209DFEA60 ?
                                                   7FFD6CBA24F8 ? 1C4782708 ?
ttcpip()+1257        call     opiodr()             00000005E 000000026
                                                   7FFD6CBA9300 000000000
                                                   7FFD6CBA24F8 ? 1C4782708 ?
opitsk()+1940        call     ttcpip()             7F4853E11750 ? 000000026 ?
                                                   7FFD6CBA9300 00000017D ?
                                                   7FFD6CBA8D50 7FFD6CBA955C
opiino()+941         call     opitsk()             000000000 000000000
                                                   7FFD6CBA9300 ? 00000017D ?
                                                   7FFD6CBA8D50 ? 7FFD6CBA955C ?
opiodr()+1229        call     opiino()             00000003C 000000004
                                                   7FFD6CBAAA18 00000017D ?
                                                   7FFD6CBA8D50 ? 7FFD6CBA955C ?
opidrv()+1021        call     opiodr()             00000003C 000000004
                                                   7FFD6CBAAA18 000000000
                                                   7FFD6CBA8D50 ? 7FFD6CBA955C ?
sou2o()+145          call     opidrv()             00000003C 000000004
                                                   7FFD6CBAAA18 000000000 ?
                                                   7FFD6CBA8D50 ? 7FFD6CBA955C ?
opimai_real()+455    call     sou2o()              7FFD6CBAA9F0 00000003C
                                                   000000004 7FFD6CBAAA18
                                                   7FFD6CBA8D50 ? 7FFD6CBA955C ?
ssthrdmain()+417     call     opimai_real()        000000000 7FFD6CBAAD00
                                                   000000004 ? 7FFD6CBAAA18 ?
                                                   7FFD6CBA8D50 ? 7FFD6CBA955C ?
main()+262           call     ssthrdmain()         000000000 000000002
                                                   7FFD6CBAAD00 000000001
                                                   000000000 7FFD6CBA955C ?
__libc_start_main()  call     main()               000000000 7FFD6CBAAF38
+245                                               7FFD6CBAAD00 ? 000000001 ?
                                                   000000000 ? 7FFD6CBA955C ?
_start()+41          call     __libc_start_main()  000D04D40 000000002
                                                   7FFD6CBAAF38 7F484F241555 ?
                                                   000000000 ? 7FFD6CBA955C ?
--------------------- Binary Stack Dump ---------------------

根据官方描述,该问题是由于Bug 20917487 – CORRUPT KKOCS STRUCTURES AFTER PARTIAL PURGE OF CURSOR HEAP引起
解决方案：
1. 升级数据库到19.1+版本
2. 应用 Patch 20917487
3. Disable adaptive cursor sharing

alter system set "_optimizer_adaptive_cursor_sharing" = false;
alter system set "_optimizer_extended_cursor_sharing_rel" = "none";

4. 通过隐含参数设置一个足够大的值

alter system set "_kgl_large_heap_warning_threshold"=83886080 scope=spfile; --一个足够大的值
--or
alter system set "_kgl_large_heap_warning_threshold"=0 scope=spfile;
alter system set "_kgl_large_heap_assert_threshold"=0 scope=spfile;

shutdown immediate
startup

参考文档：
ORA-600 [KGL-heap-size-exceeded] Errors in the Alert Log (Doc ID 2628072.1)
Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded] (Doc ID 330239.1)

ORA-21779错误处理

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-21779错误处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户win 10.2.0.4的rac,查看alert日志发现如下错误

Mon May 04 17:25:18 2020
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl2_smon_2548.trc:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1

Mon May 04 17:25:28 2020
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl2_smon_2548.trc:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1

Mon May 04 17:25:38 2020
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl2_smon_2548.trc:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1

Mon May 04 17:25:39 2020
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl2_smon_2548.trc:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1

查看对应trace

Dump file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl2_smon_2548.trc
Sat Aug 31 15:02:39 2019
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Windows Server 2003 Version V5.2 Service Pack 2
CPU                 : 32 - type 8664, 2 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:17543M/32742M, Ph+PgF:19550M/33833M

…………

*** 2020-05-04 18:40:35.687
SMON: following errors trapped and ignored:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1
*** 2020-05-04 18:40:45.734
	 Drop transient type:   SYSTPzpEA6olJSImGURLObkiE6w==
*** 2020-05-04 18:40:45.734
SMON: following errors trapped and ignored:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1
*** 2020-05-04 18:40:55.812
	 Drop transient type:   SYSTPzpEA6olJSImGURLObkiE6w==
*** 2020-05-04 18:40:55.812
SMON: following errors trapped and ignored:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1
*** 2020-05-04 18:41:05.875
	 Drop transient type:   SYSTPzpEA6olJSImGURLObkiE6w==
*** 2020-05-04 18:41:05.875
SMON: following errors trapped and ignored:
ORA-21779: 持续时间不活动
ORA-06512: 在 line 1

出现该问题是由于oracle的smon进程无法清理掉 transient types,从而出现该问题,根据官方的说法,这个错误是不会影响数据库正常使用,但是可以通过以下方法暂时规避这种错误:
1)通过设置alter system set events ’22834 trace name context forever, level 1′禁止smon清理transient types,从而来规避该错误,但是可能会引起transient types对象越来越多,当然你可以通过以下sql查询出来

select o.* from obj$ o, type$ t
where o.oid$ = t.tvoid and
bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007;

然后删除掉相关记录DROP TYPE “SYSTPf/r2wN4keX7gQKjA3AFMSw==” FORCE;【这个删除不是必须的】
2) flush shared_pool也可以临时规避这个问题
3) 重启数据库,可以暂时规避

具体参考:
SMON: Following Errors Trapped And Ignored ORA-21779 (Doc ID 988663.1)
Receiving ORA-21780 Continuously in the Alert Log and SMON Trace Reports “Drop transient type”. (Doc ID 1081950.1)

Oracle数据库O/S-Error: (OS 1453)错误

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：Oracle数据库O/S-Error: (OS 1453)错误

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

客户数据库在运行过程中突然报错

Fri Apr 03 15:00:17 2020
Thread 1 cannot allocate new log, sequence 8201
Private strand flush not complete
  Current log# 1 seq# 8200 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Thread 1 advanced to log sequence 8201 (LGWR switch)
  Current log# 2 seq# 8201 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Fri Apr 03 20:25:06 2020
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ckpt_2292.trc:
ORA-00202: control file: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'
ORA-27070: async read/write failed
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1453) 配额不足，无法完成请求的服务。
Fri Apr 03 20:27:32 2020
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_m000_3754676.trc:
ORA-00202: control file: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'
ORA-27070: async read/write failed
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1453) 配额不足，无法完成请求的服务。
Fri Apr 03 20:28:42 2020
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ckpt_2292.trc:
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: 'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 1453) 配额不足，无法完成请求的服务。
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 1453) 配额不足，无法完成请求的服务。
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ckpt_2292.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: 'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 1453) 配额不足，无法完成请求的服务。
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 1453) 配额不足，无法完成请求的服务。
CKPT (ospid: 2292): terminating the instance due to error 221
Fri Apr 03 20:28:42 2020
opiodr aborting process unknown ospid (3753944) as a result of ORA-1092

这个错误提示io error,但是有O/S-Error: (OS 1453)的错误,根据经验判断很可能不是真的io错误,查询mos发现相关记录,发现在How To Resolve (OS 1453) Insufficient Quota To Complete The Requested Service Errors (Doc ID 758595.1)文章中有类似描述.
20200410124105

是由于oracle请求的过程中发现该应用可以使用的物理内存不足导致.该数据库恰好是32位的,最简单的做法就是换64位数据库,可以大大的避免这种错误发生

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

ORA-04020导致adg异常

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-04020导致adg异常

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今日早上有客户反馈adg停止同步了,通过检查alert日志发现

Tue Dec 24 18:17:41 2019
Media Recovery Waiting for thread 1 sequence 56655 (in transit)
Recovery of Online Redo Log: Thread 1 Group 11 Seq 56655 Reading mem 0
  Mem# 0: Y:\ORACLE\ORADATA\ORACLE11\STD_REDO11.LOG
Archived Log entry 56248 added for thread 1 sequence 56654 ID 0x5b6bcf9b dest 1:
Tue Dec 24 18:18:11 2019
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\oracle11dg\oracle11\trace\oracle11_lgwr_3252.trc:
ORA-04020: deadlock detected while trying to lock object SYS.orcl
LGWR (ospid: 3252): terminating the instance due to error 4020
Tue Dec 24 18:18:11 2019
System state dump requested by (instance=1, osid=3252 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file C:\APP\ADMINISTRATOR\diag\rdbms\oracle11dg\trace\oracle11_diag_3236_20191224181811.trc
Dumping diagnostic data in directory=[cdmp_20191224181811],
      requested by (instance=1, osid=3252 (LGWR)), summary=[abnormal instance termination].
Instance terminated by LGWR, pid = 3252

由于lgwr进程遭遇ORA-04020,从而使得lgwr进程异常,进而整个数据库crash.

分析trace文件

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 8 - type 8664, 8 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:8395M/32733M, Ph+PgF:41002M/65464M
Instance name: oracle11
Redo thread mounted by this instance: 1
Oracle process number: 10
Windows thread id: 3252, image: ORACLE.EXE (LGWR)
*** 2019-12-24 18:18:11.072
*** SESSION ID:(384.1) 2019-12-24 18:18:11.072
*** CLIENT ID:() 2019-12-24 18:18:11.072
*** SERVICE NAME:(SYS$BACKGROUND) 2019-12-24 18:18:11.072
*** MODULE NAME:() 2019-12-24 18:18:11.072
*** ACTION NAME:() 2019-12-24 18:18:11.072
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object SYS.orcl
--------------------------------------------------------
  object   waiting  waiting       blocking blocking
  handle   session     lock mode   session     lock mode
--------  -------- -------- ----  -------- -------- ----
0000000676C20F08  000000066D22BE10 00000006738AB970    X  000000066D22BE10 00000006738A04B0    S
0000000677DF2E80  00000006792E2880 0000000673B13AE8    X  000000066D22BE10 00000006738A19B8    S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x00000006738AB970, type: 78, owner: 0x000000065D440498, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 proc=0x000000066CDE7AD0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0
LibraryObjectLock:Address=00000006738AB970 Handle=0000000676C20F08
  RequestMode=X CanBeBrokenCount=2 Incarnation=3 ExecutionCount=0
………………
SO: 0x00000006738A19B8, type: 78, owner: 0x000000065A38D6C0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 proc=0x000000066CDE7AD0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0
LibraryObjectLock:Address=00000006738A19B8 Handle=0000000677DF2E80
   Mode=S CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0
  User=000000066D22BE10 Session=000000066D22BE10 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=1b749
  LibraryHandle:  Address=0000000677DF2E80 Hash=675351da LockMode=S PinMode=0 LoadLockMode=0 Status=0
  ObjectName:  Name=SYS.orcl
    FullHashValue=285b654fe3f440652c403c98675351da Namespace=DBINSTANCE(74) Type=CURSOR(00) Identifier=1 OwnerIdn=0
  Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=1 TotalLockCount=74719 TotalPinCount=0
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0
        BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0000000677DF2F30(0, 0, 0, 0) Mutex=0000000677DF2FC0(0, 149471, 1, 0)
  Flags=RON/PIN/KEP/BSO/[00810003]
  WaitersLists:
    Lock=0000000677DF2F10[0000000673B13B58,000000067382E2F0]
    Pin=0000000677DF2EF0[0000000677DF2EF0,0000000677DF2EF0]
    LoadLock=0000000677DF2F68[0000000677DF2F68,0000000677DF2F68]
  Timestamp:
  HandleReference:  Address=0000000677DF3030 Handle=0000000000000000 Flags=[00] ---------------------------------
This lock request was aborted.
error 4020 detected in background process
ORA-04020: deadlock detected while trying to lock object SYS.orcl
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+585<-kjzdssdmp()+329<-kjzduptcctx()+288<-kjzdicrshnfy()+99<-ksuitm()+1525<-ksbrdp()+4578<-opirip()
+853<-opidrv()+906<-sou2o()+98<-opimai_real()+280<-opimai()+191
    <-BackgroundThreadStart()+646<-0000000076CF59CD<-0000000076E2A561
----- End of Abridged Call Stack Trace -----
*** 2019-12-24 18:18:11.165
LGWR (ospid: 3252): terminating the instance due to error 4020
*** 2019-12-24 18:18:17.483
ksuitm: waiting up to [5] seconds before killing DIAG(3236)

日志显示由于lgwr进程等待LIBRARY OBJECT LOCK超时,从而引起异常,根据经验此类问题一般是由于bug导致,查询mos发现匹配bug信息Bug 18515268 ORA-4020 in ADG Standby Database causing instance crash by LGWR
20191225112622

可以根据需要打上相关Patch 18515268: ACTIVE DATAGUARD STANDBY CRASHES DUE TO AN ORA-4020 ENCOUNTERED BY LGWR

ORA-00600: internal error code, arguments: [2252], [3987]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00600: internal error code, arguments: [2252], [3987]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

客户数据库版本10.2.0.4，启动成功之后立马crash，让我们协助解决
version

Thu Jul  4 13:03:10 2019
Completed: ALTER DATABASE OPEN
Thu Jul  4 13:03:10 2019
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Jul  4 13:04:01 2019
Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_reco_22268.trc:
ORA-00600: internal error code, arguments: [2252], [3987], [3375047096], [], [], [], [], []
Thu Jul  4 13:04:01 2019
Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_reco_22268.trc:
ORA-00600: internal error code, arguments: [2252], [3987], [3375047096], [], [], [], [], []
Thu Jul  4 13:04:02 2019
Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_reco_22268.trc:
ORA-00600: internal error code, arguments: [2252], [3987], [3375047096], [], [], [], [], []
Thu Jul  4 13:04:02 2019
RECO: terminating instance due to error 476
Instance terminated by RECO, pid = 22268

根据以往经验记录一次ORA-00600[2252]故障解决,很可能是scn过大引起.通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查scn相关信息
scn

从ORA-600 2252错误信息看,由于scn可能超过该数据库的天花板理论上而导致该问题,而reco进程主要是由于分布式事务引起,通过和客户确认,该库有通过dblink去访问11204版本oracle,而从2019年6月23日之后scn的算法发生了一些改变(SCN Compatibility问题汇总-2019年6月23日),导致数据库可以支持更大的scn,从而当低版本需要进行分布式事务操作之时,可能导致数据库异常.

处理方案：通过临时屏蔽分布式事务,让数据库临时正常工作;长期解决方案需要把数据库版本升级,避免scn引起相关问题

ORA-07217: sltln: environment variable cannot be evaluated.

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-07217: sltln: environment variable cannot be evaluated.

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

dg备库mount报ORA-07217
在一次搭建dg过程中,备库无法mount,报ORA-07217: sltln: environment variable cannot be evaluated.错误

[oracle@localhost orcl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 13 22:27:31 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-07217: sltln: environment variable cannot be evaluated.

alert日志报错

Set as converted control file due to db_unique_name mismatch
Changing di2dbun from ORCL to ORCLDG
ORA-7217 signalled during: alter database mount...
Wed Feb 13 20:33:41 2019
alter database mount
ORA-7217 signalled during: alter database mount...
Wed Feb 13 20:36:11 2019
alter database mount
ORA-7217 signalled during: alter database mount...

根据mos排查
根据mos ORA-07217: sltln: environment variable cannot be evaluated (Doc ID 2487898.1)描述排查,确认不是该问题

SQL> show parameter diag;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle
[oracle@dg:/opt/app/oracle/arch]$env|grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/opt/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1

检查发现数据文件异常

SQL> select name from v$datafile where name like '%$%';
NAME
------------------------------------------------------
+DATA/orcl/datafile/ts_his$emr01
+DATA/orcl/datafile/ts_his$emr02

通过分析,注释掉db_file_name_convert=’+DATA/orcl/datafile/’,’/u01/app/oradata/orcl/’即可正常mount成功,数据文件命令一定要规范(不要有特殊符号),不然给后人和自己留下巨坑

下调虚拟化资源导致ORA-00494

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：下调虚拟化资源导致ORA-00494

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在虚拟化环境中的Oracle数据库近期报大量ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) 错误,每次客户重启一段时间之后又不行

Mon Nov 26 14:04:39 中国标准时间 2018
Thread 1 advanced to log sequence 97327 (LGWR switch)
  Current log# 1 seq# 97327 mem# 0: D:\ORADATA\xifenfei\REDO01.LOG
Mon Nov 26 14:20:02 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc1_1860.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')
Mon Nov 26 14:20:03 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc4_1872.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')
Mon Nov 26 14:20:03 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc3_1868.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')
Mon Nov 26 14:20:03 中国标准时间 2018
Errors in file c:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_arc0_1856.trc:
ORA-00494: 入队 [CF] 被持有的时间过长 (more than 900 seconds) (由 'inst 1, osid 3264')

因为这个系统有一段历史,以前是客户的核心生产库,虚拟化资源给的比较多128G,后来核心生产迁移到其他系统,该系统跑一些边缘小业务,所以怀疑客户有可能下调了虚拟化的资源(cpu和内存),检查机器硬件资源

C:\Users\Administrator>systeminfo
主机名:           HIS-VM
OS 名称:          Microsoft Windows Server 2008 R2 Enterprise
OS 版本:          6.1.7601 Service Pack 1 Build 7601
OS 制造商:        Microsoft Corporation
OS 配置:          独立服务器
OS 构件类型:      Multiprocessor Free
注册的所有人:     Windows 用户
注册的组织:
产品 ID:          00486-OEM-8400691-20006
初始安装日期:     2013/4/19, 12:22:44
系统启动时间:     2018/11/27, 8:06:00
系统制造商:       VMware, Inc.
系统型号:         VMware Virtual Platform
系统类型:         x64-based PC
处理器:           安装了 2 个处理器。
                  [01]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel
Mhz
                  [02]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel
Mhz
BIOS 版本:        Phoenix Technologies LTD 6.00, 2014/4/14
Windows 目录:     C:\Windows
系统目录:         C:\Windows\system32
启动设备:         \Device\HarddiskVolume1
系统区域设置:     zh-cn;中文(中国)
输入法区域设置:   zh-cn;中文(中国)
时区:             (UTC+08:00) 北京，重庆，香港特别行政区，乌鲁木齐
物理内存总量:     32,767 MB
可用的物理内存:   22,740 MB
虚拟内存: 最大值: 95,047 MB
虚拟内存: 可用:   10,426 MB
虚拟内存: 使用中: 84,621 MB
页面文件位置:     C:\pagefile.sys
域:               WORKGROUP

果然系统内存从128G下调到了32G,但是数据库sga估计没有对应降低

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 80G
sga_target                           big integer 80G

基本上就是由于虚拟机内存下调,但是数据库sga配置过大导致该问题.

ORA-600 2131故障说明

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 2131故障说明

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

oracle 12c数据库启动报ORA-600 2131错误

Mon Nov 26 09:43:57 2018
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\app\Administrator
alter database mount exclusive
Mon Nov 26 09:44:00 2018
Using default pga_aggregate_limit of 2048 MB
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl12c\orcl12c\trace\orcl12c_ora_3040.trc  (incident=375524):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl12c\orcl12c\incident\incdir_375524\orcl12c_ora_3040_i375524.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: alter database mount exclusive...

这个日志比较明显,数据库无法mount,在mount操作的时候报ORA-600 2131错误.
trace文件报错

Error: kccpb_sanity_check_2
Control file sequence number mismatch!
fhcsq: 497844 bhcsq: 497849 cfn 0 rpbn 16
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+92         CALL???  skdstdst()           000000001 000004000 000030000
                                                   016301338
kccpb_sanity_check(  CALL???  ksedst1()            1492761E0 0000798B4 0000798B9
)+834                                              000000000
kccbmp_get()+275     CALL???  kccpb_sanity_check(  000000000 000000000 000000000
                              )                    000004000
kccsed_rbl()+174     CALL???  kccbmp_get()         000017E28 015A67E14 015592200
                                                   000000001
kccocx()+1399        CALL???  kccsed_rbl()         100000010 100000001 0000354D8
                                                   000035508
kccocf()+167         CALL???  kccocx()+528         016303990 000000000
                                                   7FF00000001 000000000
kcfcmb()+1254        CALL???  kccocf()             000000000 000000000 000000000
                                                   000000000
kcfmdb()+69          CALL???  kcfcmb()             000000000 7FF59FFF856
                                                   000000007 7FE00000000
adbdrv_options()+43  CALL???  kcfmdb()             0163083E0 14903FF2C 000000005
724                                                000000000
adbdrv()+149         CALL???  adbdrv_options()     000000000 000000000 0163084A0
                                                   851F2CC90B75
opiexe()+22668       CALL???  adbdrv()             7FF00000023 000000003
                                                   000000000 016309380
opiosq0()+6009       CALL???  opiexe()             000000000 000000000 016309990
                                                   000000000
kpooprx()+410        CALL???  opiosq0()            000000003 000000000 000000000
                                                   0000000A4
kpoal8()+994         CALL???  kpooprx()            0146A57FC 000000001 0146A5820
                                                   000000001
opiodr()+1601        CALL???  kpoal8()             000000000 015523288 015523270
                                                   0159FCDD0
ttcpip()+1223        CALL???  opiodr()             7FE0000005E 00000001F
                                                   01630DA20 7FE00000000
opitsk()+2160        CALL???  ttcpip()             0146C0690 000000000 000000000
                                                   000000000
opiino()+1079        CALL???  opitsk()             000000007 000000000 01630F200
                                                   01630E970
opiodr()+1601        CALL???  opiino()             00000003C 000000000 01630F470
                                                   000000000
opidrv()+842         CALL???  opiodr()             00000003C 000000004 01630F470
                                                   000000000
sou2o()+94           CALL???  opidrv()+156         10000003C 7FE00000004
                                                   01630F470 0154E6A30
opimai_real()+276    CALL???  sou2o()              1D4851F4C467583 00A9D55E0
                                                   8001A000B07E2 1004B0039001E
opimai()+170         CALL???  opimai_real()        000000000 851F2CB1B179
                                                   00A9D55E0 01630F628
OracleThreadStart()  CALL???  opimai()             000000000 149031F90 000000050
+713                                               0000005C8
00000000775259CD     CALL???  OracleThreadStart()  000000000 000000000 000000000
                                                   000000000
000000007765A561     CALL???  00000000775259C0     000000000 000000000 000000000
                                                   000000000
--------------------- Binary Stack Dump ---------------------

这个错误和以往版本中的kccpb_sanity_check_2比较类似,由于数据库异常关闭导致ctl写丢失导致
ORA-600 2131/kccpb_sanity_check_2解释

DESCRIPTION:
  This internal error is raised when the sequence number (seq#) of the
  current block of the controlfile is greater than the seq# in the controlfile header.
  The header value should always be equal to, or greater than the value
  held in the control file block(s).
  This extra check was introduced in Oracle 10gR2 to detect lost writes
  or stale reads to the header.
ARGUMENTS:
  Arg [a] seq# in control block header.
  Arg [b] seq# in the control file header.
  Arg 
FUNCTIONALITY:
  Kernel Cache layer Control file component.

ORA-00600 dbkif_find_next_record_1

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00600 dbkif_find_next_record_1

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库版本信息

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

数据库启动报ORA-00600 dbkif_find_next_record_1错误

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 133 KB redo, 72 data blocks need recovery
Started redo application at
 Thread 1: logseq 49070, block 14720
Recovery of Online Redo Log: Thread 1 Group 2 Seq 49070 Reading mem 0
  Mem# 0: D:\APP\xff\ORADATA\ORCL\REDO02.LOG
Completed redo application of 0.09MB
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc  (incident=477756):
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\xff\diag\rdbms\orcl\orcl\incident\incdir_477756\orcl_ora_2340_i477756.trc
Wed Sep 26 10:52:07 2018
Trace dumping is performing id=[cdmp_20180926105207]
Wed Sep 26 10:52:08 2018
Aborting crash recovery due to error 600
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc:
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
Errors in file d:\app\xff\diag\rdbms\orcl\orcl\trace\orcl_ora_2340.trc:
ORA-00600: ??????, ??: [dbkif_find_next_record_1], [], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...

这里比较明显，数据库是在做实例恢复的时候遭遇到ORA-600 dbkif_find_next_record_1错误,无法正常应用日志导致该错误,具体原因由于:the NAB and finds it is less than the block# from the on-disk-RBA recorded，对于这种问题，通过人工修改next available block# in the Online Redo Log的相关记录,即可正常open数据库,而且理论上数据0丢失

ORA-07445 qcdlgcd

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-07445 qcdlgcd

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

SQL执行报ORA-03113错误

SQL> SELECT *
  FROM USER.TABLE_NAME t
 WHERE t.YEAR = '2019'
   AND t.UPPCODE = '51010000'
   AND  t.MONTH = '01';
  2    3    4    5
SELECT *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 68389
Session ID: 2419 Serial number: 34370

数据库版本等信息

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /oracle/product/db12cr1
System name:	Linux
Node name:	Tkfcsdb
Release:	2.6.32-431.el6.x86_64
Version:	#1 SMP Sun Nov 10 22:19:54 EST 2013
Machine:	x86_64

alert日志报错

Thu Sep 27 17:41:15 2018
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDFB0C6, qcdlgcd()+70] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/tst12uf/tst12uf/trace/tst12uf_ora_234433.trc  (incident=126129) (PDBNAME=PTST12UF):
ORA-07445:exception encountered:core dump[qcdlgcd()+70][SIGSEGV][ADDR:0x4][PC:0xCDFB0C6][Address not mapped to object][]
Incident details in: /oracle/diag/rdbms/tst12uf/tst12uf/incident/incdir_126129/tst12uf_ora_234433_i126129.trc
Use ADRCI or Support Workbench to package the incident.

trace文件

----- Beginning of Customized Incident Dump(s) -----
Dumping swap information
Memory (Avail / Total) = 948.73M / 63736.63M
Swap (Avail / Total) = 30025.52M /  32255.99M
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDFB0C6, qcdlgcd()+70] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000000 %rbx: 0x000000020c3da3f0 %rcx: 0x00007fc492268a40
%rdx: 0x00007fc49239eab0 %rdi: 0x000000022cd5da20 %rsi: 0x00007fc492f2bc80
%rsp: 0x00007fffbd9c9430 %rbp: 0x00007fffbd9c94a0  %r8: 0x00000002372efcb8
 %r9: 0x000000022cd5da20 %r10: 0x00007fc49239eab0 %r11: 0x00007fc492268a40
%r12: 0x000000022cd5da20 %r13: 0x00007fc492f2bc80 %r14: 0x00007fc49239eab0
%r15: 0x00007fc492268a40 %rip: 0x000000000cdfb0c6 %efl: 0x0000000000010206
  qcdlgcd()+53 (0xcdfb0b5) mov %rdi,%r12
  qcdlgcd()+56 (0xcdfb0b8) mov 0x10(%rax),%rbx
  qcdlgcd()+60 (0xcdfb0bc) jz 0xcdfb29e
  qcdlgcd()+66 (0xcdfb0c2) mov 0x60(%r15),%rax
> qcdlgcd()+70 (0xcdfb0c6) movzwl 0x4(%rax),%r8d
  qcdlgcd()+75 (0xcdfb0cb) cmp $30,%r8d
  qcdlgcd()+79 (0xcdfb0cf) jnle 0xcdfb277
  qcdlgcd()+85 (0xcdfb0d5) pxor %xmm0,%xmm0
  qcdlgcd()+89 (0xcdfb0d9) movaps %xmm0,-0x40(%rbp)
*** 2018-09-27 17:41:15.110
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=52chysuyh36t4) -----
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune
no_monitoring optimizer_features_enable(default)
no_parallel result_cache(snapshot=3600) */
SUM(C1) FROM (SELECT /*+ qb_name("innerQuery")
NO_INDEX_FFS( "T")  */ 1 AS C1 FROM "USER"."TABLE_NAME" "T"
WHERE ("T"."MONTH"='01') AND ("T"."UPPCODE"='51010000') AND ("T"."YEAR"='2019')) innerQuery
[TOC00004-END]
[TOC00005]
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+45        call     kgdsdst()            7FC492BAB678 000000003
                                                   7FC492B8D0B0 ? 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
ksedst()+119         call     skdstdst()           7FC492BAB678 000000001
                                                   000000001 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
dbkedDefDump()+1119  call     ksedst()             000000001 000000001 ?
                                                   000000001 ? 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
ksedmp()+261         call     dbkedDefDump()       000000003 000000003
                                                   000000001 ? 7FC492B8D1C8 ?
                                                   7FC492BAAEA8 ? 000000083 ?
ssexhd()+2650        call     ksedmp()             00000044F 000000003 ?
                                                   000000001 ? 000000003
                                                   7FC492BAAEA8 ? 000000083 ?
sslsshandler()+456   call     ssexhd()             202F206C69617641
                                                   3D20296C61746F54
                                                   4D33372E38343920
                                                   3633373336202F20
                                                   6177530A4D33362E 000000083 ?
__sighandler()       call     sslsshandler()       000002000 000000000 000000000
                                                   3633373336202F20 ?
                                                   6177530A4D33362E ?
                                                   000000083 ?
qcdlgcd()+70         signal   __sighandler()       22CD5DA20 7FC492F2BC80
                                                   7FC49239EAB0 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
kkdlgcd()+118        call     qcdlgcd()            22CD5DA20 ? 7FC492F2BC80 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
kkmfbtic()+17        call     kkdlgcd()            7FC49239EAB0 ? 7FC492268A40 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
qcsgcic()+163        call     kkmfbtic()           7FC49239EAB0 ? 7FC492268A40 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   2372EFCB8 ? 22CD5DA20 ?
kkmgkc()+147         call     qcsgcic()            22CD5DA20 ? 7FC492F2BC80 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   000000007 22CD5DA20 ?
kokscit()+65         call     kkmgkc()             22CD5DA20 ? 7FC492F2BC80 ?
                                                   7FC49239EAB0 ? 7FC492268A40 ?
                                                   000000007 ? 22CD5DA20 ?
qkebCreateColById()  call     kokscit()            22CD5DA20 ? 7FC492F2BC80 ?
+298                                               7FC49239EAB0 ? 7FC492268A40 ?
                                                   000000007 ? 22CD5DA20 ?
qksvcGetGuardCol()+  call     qkebCreateColById()  000000002 7FC4923A4078
161                                                7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
qksvcProcessVCColum  call     qksvcGetGuardCol()   7FC49239D9D8 ? 7FC4923A4078 ?
ns()+1625                                          7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
qkacol()+405         call     qksvcProcessVCColum  000000000 ? 000000000 ?
                              ns()                 7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
qkadrv()+933         call     qkacol()             7FC49239EAB0 ? 000000000 ?
                                                   7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
opitca()+2417        call     qkadrv()             7FC4923A4078 ? 000000001 ?
                                                   7FC49239EAB0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
kksFullTypeCheck()+  call     opitca()             7FC4922F4BD0 22CD5DC40
79                                                 7FFFBD9CF3B0 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
rpiswu2()+2235       call     kksFullTypeCheck()   7FFFBD9CDF08 ? 22CD5DC40 ?
                                                   7FFFBD9CF3B0 ? 000000007 ?
                                                   7FC492268A40 ? 22CD5DA20 ?
kksLoadChild()+7590  call     rpiswu2()            7FFFBD9CDF08 ? 22CD5DC40 ?
                                                   7FFFBD9CF3B0 ? 000000008 ?
                                                   7FC492F5D260 ? 22CD5DA20 ?
kxsGetRuntimeLock()  call     kksLoadChild()       7FFFBD9CDF08 ? 22CD5DC40 ?
+2155                                              000000000 000000008 ?
                                                   7FC492F5D260 ? 22CD5DA20 ?
kksfbc()+14306       call     kxsGetRuntimeLock()  7FC492F2BC80 7FC4922F4BD0
                                                   7FFFBD9CF330 22D1E7C28 ?
                                                   7FC492F5D260 ? 22D1E7C28
kkspsc0()+3146       call     kksfbc()             7FC4922F4BD0 7FC4922F4BD0 ?
                                                   7FFFBD9CF330 ? 22D1E7C28 ?
                                                   7FC492F5D260 ? 22D1E7C28 ?
kksParseCursor()+11  call     kkspsc0()            7FC4928321A0 7FC49235D198
8                                                  00000015F 000000003 000000006
                                                   000000020
opiosq0()+2210       call     kksParseCursor()     7FFFBD9D0178 ? 7FC49235D198 ?
                                                   00000015F ? 000000003 ?
                                                   000000006 ? 000000020 ?
opiall0()+4530       call     opiosq0()            000000003 7FC49235D198 ?
                                                   7FC492F5D260 ? 000000020
                                                   000000000 000000020 ?
opikpr()+567         call     opiall0()            000000003 ? 000000022
                                                   7FFFBD9D0A50 000000000
                                                   000000000 ? 000000020 ?
opiodr()+1165        call     opikpr()             000000065 ? 000000022 ?
                                                   7FFFBD9D22F0 000000000 ?
                                                   000000000 ? 000000020 ?
rpidrus()+206        call     opiodr()             000000065 00000001F
                                                   7FFFBD9D22F0 ? 000000000 ?
                                                   000000000 ? 100000001
skgmstack()+144      call     rpidrus()            7FFFBD9D1B70 00000001F ?
                                                   7FC492F2BE78 000000000 ?
                                                   000000000 ? 100000001 ?
rpiswu2()+723        call     skgmstack()          7FFFBD9D1B48 ? 7FC492F2B7A0 ?
                                                   00000F618 ? 00CBFE570 ?
                                                   7FFFBD9D1B70 ? 100000001 ?
kprball()+1163       call     rpiswu2()            7FFFBD9D1B48 ? 7FC492F2B7A0 ?
                                                   00000F618 ? 000000002 ?
                                                   7FC492F5D260 ? 100000001 ?
qksdsExeStmt()+2411  call     kprball()            7FFFBD9D22F0 004000180
                                                   00000F618 ? 000000002 ?
                                                   7FC492F5D260 ? 100000001 ?
qksdsExecute()+959   call     qksdsExeStmt()       7FFFBD9D2B98 7FC49235D150
                                                   000000001 000000008
                                                   7FFFBD9D28C8 100000001 ?
kkoatVerifyEst()+30  call     qksdsExecute()       7FFFBD9D2B98 7FC49235D150 ?
85                                                 000000001 ? 000000008 ?
                                                   7FFFBD9D28C8 ? 100000001 ?
kkeAdjSingTabCard()  call     kkoatVerifyEst()     300000001 7FC4923E9DA8
+714                                               1EE909D75846 7FC49235D108
                                                   000000000 100000001 ?
kkecdn()+3803        call     kkeAdjSingTabCard()  7FC4923E9DA8 7FC4923EA328
                                                   7FFFBD9D2E30 ? 7FC49235D108 ?
                                                   000000000 ? 100000001 ?
kkotap()+13019       call     kkecdn()             7FC4923ECF08 000000003 ?
                                                   7FFFBD9D2E30 ? 7FC4923EA328 ?
                                                   000000000 ? 100000001 ?
kkoiqb()+8331        call     kkotap()             7FC492F5D260 ? 000000000 ?
                                                   000000011 000000000 000000000
                                                   100000001 ?
kkooqb()+532         call     kkoiqb()             000000000 ? 000000000
                                                   000000000 000000000 ?
                                                   000000000 ? 100000001 ?
kkoqbc()+2385        call     kkooqb()             7FC4927F8BC0 ? 000000006
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 100000001 ?
apakkoqb()+182       call     kkoqbc()             7FC4927F8BC0 ? 7FC4927F8BC0 ?
                                                   000000001 000000000 ?
                                                   000000000 ? 100000001 ?
apaqbdDescendents()  call     apakkoqb()           000000000 ? 7FC4927F8BC0 ?
+488                                               21EE306B8 ? 000000000 ?
                                                   000000000 ? 100000001 ?
apadrv()+5383        call     apaqbdDescendents()  7FFFBD9DC2B0 ? 7FC4927F8BC0 ?
                                                   21EE306B8 ? 000000000 ?
                                                   000000000 ? 100000001 ?
opitca()+2106        call     apadrv()             21EE306B8 ? 7FC4927F8BC0 ?
                                                   21EE306B8 ? 000000000 ?
                                                   000000000 ? 100000001 ?
kksLoadChild()+7318  call     opitca()             7FC492845648 21EE306B8
                                                   7FFFBD9DE6F0 000000000 ?
                                                   000000000 ? 100000001 ?
kxsGetRuntimeLock()  call     kksLoadChild()       7FC492845648 ? 21EE306B8 ?
+2155                                              000000000 000000000 ?
                                                   000000000 ? 100000001 ?
kksfbc()+14306       call     kxsGetRuntimeLock()  7FC492F2BC80 7FC492845648
                                                   7FFFBD9DE670 232AE3E98 ?
                                                   000000000 ? 232AE3E98
kkspsc0()+3146       call     kksfbc()             7FC492845648 7FC492845648 ?
                                                   7FFFBD9DE670 ? 232AE3E98 ?
                                                   000000000 ? 232AE3E98 ?
kksParseCursor()+11  call     kkspsc0()            7FC492832108 7FFFBD9E0A28
8                                                  000000081 000000003 000000006
                                                   0000000A4
opiosq0()+2210       call     kksParseCursor()     7FFFBD9DF4B8 ? 7FFFBD9E0A28 ?
                                                   000000081 ? 000000003 ?
                                                   000000006 ? 0000000A4 ?
kpoal8()+1223        call     opiosq0()            000000003 7FFFBD9E0A28 ?
                                                   7FC492F5D260 ? 0000000A4
                                                   000000000 0000000A4 ?
opiodr()+1165        call     kpoal8()             00000005E 00000001F
                                                   7FFFBD9E3278 0000000A4 ?
                                                   000000000 ? 0000000A4 ?
ttcpip()+2699        call     opiodr()             00000005E 00000001F
                                                   7FFFBD9E3278 ? 0000000A4 ?
                                                   000000000 ? 100000000
opitsk()+1734        call     ttcpip()             7FC492F41070 ? 00000005E ?
                                                   7FFFBD9E3278 000000000 ?
                                                   7FFFBD9E2CD8 7FFFBD9E3484
opiino()+945         call     opitsk()             000000400 000000000
                                                   7FFFBD9E3278 ? 000000000 ?
                                                   7FFFBD9E2CD8 ? 7FFFBD9E3484 ?
opiodr()+1165        call     opiino()             00000003C 000000004
                                                   7FFFBD9E4918 000000000 ?
                                                   7FFFBD9E2CD8 ? 7FFFBD9E3484 ?
opidrv()+587         call     opiodr()             00000003C 000000004
                                                   7FFFBD9E4918 ? 000000000 ?
                                                   7FFFBD9E2CD8 ? 000000000
sou2o()+145          call     opidrv()             00000003C 000000004
                                                   7FFFBD9E4918 000000000 ?
                                                   7FFFBD9E2CD8 ? 000000000 ?
opimai_real()+154    call     sou2o()              7FFFBD9E48F0 00000003C
                                                   000000004 7FFFBD9E4918
                                                   7FFFBD9E2CD8 ? 000000000 ?
ssthrdmain()+412     call     opimai_real()        000000000 7FFFBD9E4C00
                                                   000000004 ? 7FFFBD9E4918 ?
                                                   7FFFBD9E2CD8 ? 000000000 ?
main()+236           call     ssthrdmain()         000000000 000000002
                                                   7FFFBD9E4C00 000000001
                                                   000000000 000000000 ?
__libc_start_main()  call     main()               7FFFBD9E559F 7FFFBD9E55AD
+253                                               7FFFBD9E4C00 ? 000000001 ?
                                                   000000000 ? 000000000 ?
_start()+41          call     __libc_start_main()  000BBD640 000000002
                                                   7FFFBD9E4E48 000000000 ?
                                                   000000000 ? 000000000 ?
[TOC00005-END]
[TOC00006]
--------------------- Binary Stack Dump ---------------------

解决方法
这里比较明显,我们可以发现这个sql执行报错，其实本质是由于递归执行动态采样报错而引起异常的.通过设置_fix_control来规避这个问题

SQL> alter session set "_fix_control"='14191778:0';
Session altered.
SQL> SELECT *
  FROM USER.TABLE_NAME t
 WHERE t.YEAR = '2019'
   AND t.UPPCODE = '51010000'
   AND  t.MONTH = '01';
  2    3    4    5
	ID EMPCODE		COMCODE 	     YEAR	QUARTER
---------- -------------------- -------------------- ---------- ----------
PUSHRATIO  D A CREATECODE	    UPDATECODE		 CREATEDATE
---------- - - -------------------- -------------------- -------------------
UPDATEDATE	    UPPCODE		 CLAIM		      MONTH
------------------- -------------------- -------------------- ----------
     77813 5101000166		51010702	     2019	1
50	   0 0 1777					 2018-07-18 17:44:19
		    51010000		 50		      01
     77909 8000545245		51010702	     2019	1
0	   0 0 1777		    1777		 2018-07-18 17:35:12
2018-07-18 17:41:20 51010000		 100		      01
	ID EMPCODE		COMCODE 	     YEAR	QUARTER
---------- -------------------- -------------------- ---------- ----------
PUSHRATIO  D A CREATECODE	    UPDATECODE		 CREATEDATE
---------- - - -------------------- -------------------- -------------------
UPDATEDATE	    UPPCODE		 CLAIM		      MONTH
------------------- -------------------- -------------------- ----------
     77912 5101000047		51010702	     2019	1
0	   0 0 1777		    1777		 2018-07-18 17:41:03
2018-07-18 17:44:28 51010000		 100		      01
SQL> SQL>