分类目录归档：ORA-xxxxx

ORA-600 [ksunfy : too few sessions]

发表于 2025 年 12 月 27 日由惜分飞

Oracle 修改processes参数之后，数据库启动报ORA-600 [ksunfy : too few sessions]错误

对应的alert日志信息

Fri Dec 26 22:39:18 2025
Starting ORACLE instance (normal)
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_2532.trc  (incident=52848):
ORA-00600: internal error code, arguments: [ksunfy : too few sessions], [9536],[75072],[],[],[],[],[],[],[],[],[]
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_52848\ora817_ora_2532_i52848.trc
Sweep [inc][52848]: completed
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

trace信息

Dump file D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_52848\ora817_ora_2532_i52848.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.2  
CPU                 : 64 - type 8664, 32 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:119070M/130752M, Ph+PgF:138338M/150208M 
Instance name: ora817
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
Windows thread id: 2532, image: ORACLE.EXE (SHAD)


*** 2025-12-26 22:39:20.746
Dump continued from file: D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_2532.trc
ORA-00600: internal error code, arguments: [ksunfy : too few sessions], [9536],[75072],[],[],[],[],[],[],[],[],[]

========= Dump for incident 52848 (ORA 600 [ksunfy : too few sessions]) ========

*** 2025-12-26 22:39:20.770
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- SQL Statement (None) -----
Current SQL information unavailable - no session.

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+129        CALL???  skdstdst()           00190E43A 000000000 000000000
                                                   00000001D
ksedst()+69          CALL???  ksedst1()            003030000 000000000 1477C1B20
                                                   25A00000007
dbkedDefDump()+4541  CALL???  ksedst()             000000000 000000000 000000000
                                                   0464DFF90
ksedmp()+43          CALL???  dbkedDefDump()       000000003 000000002 000000000
                                                   000000000
ksfdmp()+87          CALL???  ksedmp()             000000000 000000000 000000000
                                                   000000000
dbgexPhaseII()+1819  CALL???  ksfdmp()             000000000 000000000 000000000
                                                   000000000
dbgexProcessError()  CALL???  dbgexPhaseII()       0009C0518 0009CD3F0 00000CE70
+3008                                              000000002
dbgeExecuteForError  CALL???  dbgexProcessError()  0009C0518 0009C74E8 000000001
()+65                                              000000000
dbgePostErrorKGE()+  CALL???  dbgeExecuteForError  000000000 000000030 000000201
2255                          ()                   0464D9280
dbkePostKGE_kgsf()+  CALL???  dbgePostErrorKGE()   00ABF9730 000000000 000000258
77                                                 767287A10789
kgeadse()+349        CALL???  dbkePostKGE_kgsf()   000092A00 14008CE12 14777712C
                                                   100000000
kgerinv_internal()+  CALL???  kgeadse()            000000001 000000000 1476C3B6C
76                                                 14008D953
kgerinv()+49         CALL???  kgerinv_internal()   000000004 A0000027318
                                                   100000004 000004A20
kgeasnmierr()+64     CALL???  kgerinv()            000000000 7FFEEB3DAFB0
                                                   1492BD1C0 00000003F
ksunfy()+6598        CALL???  kgeasnmierr()        1477094CC 00ABF9170 0000011F8
                                                   000000002
kscnfy()+1091        CALL???  ksunfy()             000666EF8 00161F45D 000DD7800
                                                   800000000000000
ksmcsg()+7168        CALL???  kscnfy()             100000004 1492BD1C0 0009C0518
                                                   0009C0518
opistr_real()+1225   CALL???  ksmcsg()             000000000 000000000 000000000
                                                   000000000
opistr()+350         CALL???  opistr_real()        000000030 000000002 0464DE080
                                                   000000000
opiodr()+1644        CALL???  opistr()             000000030 000000002 0464DE080
                                                   000A75FC8
ttcpip()+1325        CALL???  opiodr()             000000030 000000002 0464DE080
                                                   000000000
opitsk()+2092        CALL???  ttcpip()             00AC15970 000000000 000000000
                                                   000000000
opiino()+1246        CALL???  opitsk()             00000001E 000000000 100000000
                                                   003EBCD8D
opiodr()+1644        CALL???  opiino()             00000003C 000000004 0464DFAB0
                                                   000000000
opidrv()+862         CALL???  opiodr()             00000003C 000000004 0464DFAB0
                                                   000000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 0464DFAB0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              1DC76756A6A6157 000000000
                                                   000000000 0464DFBA4
opimai()+191         CALL???  opimai_real()        000000000 000000000 000000000
                                                   000000000
OracleThreadStart()  CALL???  opimai()             000000000 000000002 148D22CA0
+724                                               000000000
00007FFFFA008364     CALL???  OracleThreadStart()  0461DFF18 000000000 000000000
                                                   000000000
00007FFFFAE37091     CALL???  00007FFFFA008350     000000000 000000000 000000000
                                                   000000000
 

--------------------- Binary Stack Dump ---------------------

查询mos发现出现ORA-600 ksunfy : too few sessions错误官方给出来的解释是由于session超过了oracle限制的65536

但是在win系统中由于系统级别对资源的限制，一般不会超过16k个进程,所以还没有到oracle的本身的session设置限制已经达到上限所以出现该错误

发表在 ORA-xxxxx | 标签为 ksunfy : too few sessions, 进程过大数据库启动报ORA-600错误 | 评论关闭

由于数据块scn大于数据库scn导致ORA-600 kcbzib_kcrsds_1错误

发表于 2025 年 12 月 23 日由惜分飞

以前遇到ORA-600 kcbzib_kcrsds_1一般都是数据库强制打开的时候,这次在一个open的库中遇到该问题,做一个记录
在expdp过程中有一个表报ORA-01555错误

. . 导出了 "SRM_DEV"."AAAAAA"             3.256 MB    4636 行
ORA-31693: 表数据对象 "SRM_DEV"."XXXXXXX" 无法加载/卸载并且被跳过, 错误如下:
ORA-02354: 导出/导入数据时出错
ORA-01555: 快照过旧: 回退段号  (名称为 "") 过小

这种错误,根据以往经验,大部分情况是由于lob字段异常引起
查看表结构

SQL> desc "SRM_DEV"."XXXXXXX"
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 DELIVERY_ID                               NOT NULL NUMBER(19)
 ARRIVAL_CUSTOMER_ID                                NUMBER(19)
 ARRIVAL_LOCATION_TYPE                              VARCHAR2(255 CHAR)
 ARRIVAL_SUPPLIER_ID                                NUMBER(19)
 ARRIVAL_TIME                                       TIMESTAMP(6)
 ARRIVAL_USER                                       NUMBER(19)
 CREATE_BY                                          VARCHAR2(32 CHAR)
 CREATE_TIME                                        TIMESTAMP(6)
 DELIVERY_DATE                                      VARCHAR2(32 CHAR)
 DELIVERY_NO                                        VARCHAR2(32 CHAR)
 DELIVERY_TYPE                                      VARCHAR2(32 CHAR)
 INVOICE_TYPE                                       VARCHAR2(2)
 IS_CONFIRM                                         VARCHAR2(2)
 IS_ONEKEY_IO                                       VARCHAR2(2)
 IS_SUPPORTING                                      VARCHAR2(32 CHAR)
 MODI_BY                                            VARCHAR2(32 CHAR)
 MODI_TIME                                          TIMESTAMP(6)
 OP_ATTRIBUTE                                       VARCHAR2(1 CHAR)
 PRINT_TIMES                                        NUMBER(10)
 RECEIVE_COMPANY                                    VARCHAR2(64 CHAR)
 RECEIVE_LOCATION                                   VARCHAR2(64 CHAR)
 RECEIVE_SUPPLIER_ID                                NUMBER(19)
 REMARK                                             VARCHAR2(256 CHAR)
 STATUS                                             VARCHAR2(5 CHAR)
 SUPPLIER_CODE                                      VARCHAR2(64 CHAR)
 SUPPLIER_ID                                        NUMBER(19)
 OVERDUE_STATUS                                     VARCHAR2(255 CHAR)

确认没有lob字段而引起了该问题,进一步分析
对表进行全扫描看看报什么错误

SQL> select /*+full(t)*/ count(1) from "SRM_DEV"."XXXXXXX" t;
select /*+full(t)*/ count(1) from "SRM_DEV"."XXXXXXX" t
                    *
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [],
[], [], [], [], []

根据以往经验,怀疑是由于这个表中的某个block的scn大于数据库scn导致
对全表扫描过程进行跟踪

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 12月 21 22:18:46 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> oradebug setmypid
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;

会话已更改。

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> oradebug TRACEFILE_NAME
F:\APP\diag\rdbms\srmdev\srmdev\trace\srmdev_ora_14768.trc
SQL>
SQL>
SQL> select /*+full(t)*/ count(1) from "SRM_DEV"."XXXXXXX" t;
select /*+full(t)*/ count(1) from "SRM_DEV"."XXXXXXX" t
                    *
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [],
[], [], [], [], []

分析trace文件确认对应的block为20548

WAIT #758663789368: nam='db file sequential read' ela= 40 file#=7 block#=20545 blocks=1 obj#=73756 tim=47881962193
WAIT #758663789368: nam='db file sequential read' ela= 41 file#=7 block#=20546 blocks=1 obj#=73756 tim=47881962259
WAIT #758663789368: nam='db file sequential read' ela= 40 file#=7 block#=20547 blocks=1 obj#=73756 tim=47881962325
WAIT #758663789368: nam='db file sequential read' ela= 41 file#=7 block#=20548 blocks=1 obj#=73756 tim=47881962389
Encountered exception while getting args for function:0x00007FF6F0BC39A0
2025-12-21T22:19:17.768815+08:00
Incident 1562659 created, dump file: F:\APP\diag\rdbms\srmdev\srmdev\incdir_1562659\srmdev_ora_14768_i1562659.trc
ORA-00600: 内部错误代码, 参数: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []

对异常block进行dump分析

PARSE #758663744160:c=0,e=97,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=48020763236
Start dump data blocks tsn: 4 file#:7 minblk 20548 maxblk 20548
Block dump from cache:
Dump of buffer cache at level 3 for pdb=0 tsn=4 rdba=29380676
WAIT #758663744160: nam='db file sequential read' ela= 88 file#=7 block#=20548 blocks=1 obj#=73756 tim=48020763396
Block dump from disk:
buffer tsn: 4 rdba: 0x01c05044 (7/20548)
scn: 0x6a37b2cb8 seq: 0x02 flg: 0x06 tail: 0x2cb80602
frmt: 0x02 chkval: 0xe203 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000B0A274D000 to 0x000000B0A274F000
B0A274D000 0000A206 01C05044 A37B2CB8 06020006  [....DP...,{.....]
B0A274D010 0000E203 001B0001 0001201C A37B2CB4  [......... ...,{.]
B0A274D020 00068000 00321F02 01C05001 00160010  [......2..P......]
B0A274D030 0409F0A2 010001A8 000C0776 00002001  [........v.... ..]

通过这里可以确认当前block的scn为0x6a37b2cb8==>28512562360
查询当前数据库scn

SQL> select CURRENT_SCN  from v$database;

  CURRENT_SCN  
--------------
  28512556338

通过分析确认当前数据库的scn小于该block的scn,所以报ORA-600 kcbzib_kcrsds_1错误.解决该问题的方法有两种
1. 调整数据库是scn,让数据库的scn 大于该block scn即可
2. 通过把该block的scn修改小一些（目的就是数据库scn大于block scn）
通过处理之后实现表正常查询

SQL> alter system checkpoint;

系统已更改。

SQL> select checkpoint_change# from v$database;

  CHECKPOINT_CHANGE#
--------------------
         28512563830

SQL>  select /*+full(t)*/ count(1) from "SRM_DEV"."XXXXXXX" t;

            COUNT(1)
--------------------
               19560

对于这种情况除了ora-600 kcbzib_kcrsds_1的报错之外,还可能会遇到ora-600 kcbzibmlt_kcrsds_1的错误

发表在 ORA-xxxxx | 标签为 kcbzibmlt_kcrsds_1, kcbzib_kcrsds_1, ora-600 kcbzibmlt_kcrsds_1, ORA-600 kcbzib_kcrsds_1 | 评论关闭

ORA-01171: datafile N going offline due to error advancing checkpoint

发表于 2025 年 5 月 30 日由惜分飞

最近接到一个客户有一个数据文件offline的恢复咨询,通过分析日志,当时是由于在启动的时候数据文件被占用导致后续数据库open之后,该文件被强制offline掉

Fri May 16 20:01:05 2025
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Fri May 16 20:01:05 2025
ALTER DATABASE OPEN
Fri May 16 20:01:06 2025
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=70, OS id=4628
Fri May 16 20:01:06 2025
ARC0: Archival started
ARC1 started with pid=74, OS id=4840
Fri May 16 20:01:06 2025
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Fri May 16 20:01:06 2025
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_4080.trc:
ORA-01110: data file 14: 'D:\ORADATA\XIFENFEI105_DAT_1.DBF'
ORA-01114: IO error writing block to file 14 (block # 1)
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 32) 另一个程序正在使用此文件，进程无法访问。

Thread 1 opened at log sequence 172421
  Current log# 1 seq# 172421 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Fri May 16 20:01:06 2025
ARC1: STARTING ARCH PROCESSES
Fri May 16 20:01:06 2025
Successful open of redo thread 1
Fri May 16 20:01:06 2025
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri May 16 20:01:06 2025
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC2 started with pid=78, OS id=4056
Fri May 16 20:01:06 2025
ARC1: Becoming the heartbeat ARCH
Fri May 16 20:01:06 2025
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 16 20:01:06 2025
SMON: enabling cache recovery
Fri May 16 20:01:07 2025
Successfully onlined Undo Tablespace 1.
Fri May 16 20:01:07 2025
SMON: enabling tx recovery
Fri May 16 20:01:08 2025
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=86, OS id=4492
Fri May 16 20:01:12 2025
db_recovery_file_dest_size of 51200 MB is 1.97% 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.
Fri May 16 20:01:13 2025
Completed: ALTER DATABASE OPEN
Fri May 16 20:06:44 2025
Restarting dead background process MMON
MMON started with pid=98, OS id=4232
Fri May 16 20:07:06 2025
Shutting down archive processes
Fri May 16 20:07:11 2025
ARCH shutting down
ARC2: Archival stopped
Fri May 16 20:10:32 2025
Thread 1 advanced to log sequence 172422
  Current log# 2 seq# 172422 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Fri May 16 20:15:33 2025
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_2496.trc:
ORA-01171: datafile 14 going offline due to error advancing checkpoint
ORA-01122: database file 14 failed verification check
ORA-01110: data file 14: 'D:\ORADATA\XIFENFEI105_DAT_1.DBF'
ORA-01208: data file is an old version - not accessing current version

Fri May 16 20:23:09 2025
Starting background process EMN0
EMN0 started with pid=82, OS id=2660

通过dbv检查报错文件,确认被offline文件本身正常

本身这个故障相对比较简单,只要归档存在直接recover datafile,然后online即可,但是由于备份软件定时工作,导致对应的归档被备份走

Fri May 16 21:55:10 2025
Control autobackup written to SBT_TAPE device
	comment 'API Version 2.0,MMS Version 10.0.0.116',
	media 'V_6746190_6959024'
	handle 'c-1300253653-20250516-00'
Fri May 16 21:56:03 2025
Thread 1 cannot allocate new log, sequence 172423
Private strand flush not complete
  Current log# 2 seq# 172422 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG

而且被异常的数据文件不是核心业务文件,导致客户没有及时发现,等到发现之时尝试recover datafile,提示缺少归档

Wed May 28 17:26:01 2025
alter database recover datafile list clear
Wed May 28 17:26:01 2025
Completed: alter database recover datafile list clear
Wed May 28 17:26:01 2025
alter database recover if needed
 datafile 14

Media Recovery Start
 parallel recovery started with 16 processes
ORA-279 signalled during: alter database recover if needed
 datafile 14
...
Wed May 28 17:26:11 2025
alter database recover cancel
Wed May 28 17:26:13 2025
Media Recovery Canceled
Completed: alter database recover cancel
Wed May 28 17:38:58 2025
ALTER DATABASE RECOVER  datafile 'D:\ORADATA\XIFENFEI105_DAT_1.DBF'  
Wed May 28 17:38:58 2025
Media Recovery Start
 parallel recovery started with 16 processes
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 'D:\ORADATA\XIFENFEI105_DAT_1.DBF'  ...
Wed May 28 18:26:37 2025
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Wed May 28 18:26:38 2025
Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_05_28\O1_MF_1_172421_%U_.ARC
Errors with log D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_05_28\O1_MF_1_172421_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed May 28 18:26:38 2025
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Wed May 28 18:26:38 2025
Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_05_28\O1_MF_1_172421_%U_.ARC
Errors with log D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_05_28\O1_MF_1_172421_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed May 28 18:26:38 2025
ALTER DATABASE RECOVER CANCEL 
Wed May 28 18:26:40 2025
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL

这个客户运气还不错,带库中的需要恢复的归档日志都还在,通过指定带库通道,直接recover datafile成功

RUN {
  ALLOCATE CHANNEL ch1 DEVICE TYPE 'sbt_tape' 
  PARMS="BLKSIZE=262144,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1)";
  ALLOCATE CHANNEL ch2 DEVICE TYPE 'sbt_tape' 
  PARMS="BLKSIZE=262144,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch2)";
 recover datafile 14;
}

至此完美解决该问题,通过这个case,的出来的经验有:
1. 数据库重启之后,要检查数据库日志和查询数据库数据文件状态(主要防止一些不太常用的文件异常,不能及时发现)
2. 需要需要数据库的基本情况,比如备份,容灾,asm磁盘组冗余,存储冗余,网络冗余等情况,这样出现问题好排查解决

发表在 ORA-xxxxx | 标签为 O/S-Error: (OS 32), ORA-01114, ORA-01171 | 评论关闭

17813235971

QQ 咨询

分类目录归档：ORA-xxxxx

ORA-600 [ksunfy : too few sessions]

由于数据块scn大于数据库scn导致ORA-600 kcbzib_kcrsds_1错误

ORA-01171: datafile N going offline due to error advancing checkpoint

文章分类

最近发表

分类目录归档：ORA-xxxxx

ORA-600 [ksunfy : too few sessions]

由于数据块scn大于数据库scn导致ORA-600 kcbzib_kcrsds_1错误

ORA-01171: datafile N going offline due to error advancing checkpoint

标签云

文章分类

最近发表