数据库启动报 maximum number of processes () exceeded分析

Posted on 2025 年 12 月 28 日 by 惜分飞

标题：数据库启动报 maximum number of processes () exceeded分析

比较少遇到打开一个数据库报ORA-00020: maximum number of processes () exceeded,导致open数据库失败

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00450: background process 'QMNC' did not start
ORA-00444: background process "QMNC" failed while starting
ORA-00020: maximum number of processes () exceeded
进程 ID: 6340
会话 ID: 109 序列号: 3

看这个报错应该是由于进程数达到了限制，导致在open数据库过程中启动后台进程QMNC失败,导致open过程失败,分析进程数不足的原因,查看启动时候的参数配置

Fri Dec 26 16:48:27 2025
Adjusting the default value of parameter parallel_max_servers
from 2560 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Fri Dec 26 16:48:38 2025
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =28
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
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:82501M/130752M, Ph+PgF:103658M/150208M 
Using parameter settings in client-side pfile D:\PFILE.TXT on machine HISSERVER
System parameters with non-default values:
  processes                = 150
  sga_target               = 39296M
  control_files            = "D:\ORACLE64\ORADATA\ORA817\CONTROL01.CTL"
  control_files            = "D:\ORACLE64\RECOVERY_AREA\ORA817\CONTROL02.CTL"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "D:\ORACLE64\recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ora817XDB)"
  audit_file_dest          = "D:\ORACLE64\ADMIN\ORA817\ADUMP"
  audit_trail              = "DB"
  db_name                  = "ora817"
  open_cursors             = 300
  pga_aggregate_target     = 13075M
  diagnostic_dest          = "D:\ORACLE64"
Fri Dec 26 16:49:10 2025
PMON started with pid=2, OS id=1992 
Fri Dec 26 16:49:10 2025
PSP0 started with pid=3, OS id=6744 
Fri Dec 26 16:49:12 2025
VKTM started with pid=4, OS id=7592 at elevated priority
Fri Dec 26 16:49:12 2025
GEN0 started with pid=5, OS id=7780 
Fri Dec 26 16:49:12 2025
DBRM started with pid=7, OS id=5372 
Fri Dec 26 16:49:12 2025
MMAN started with pid=9, OS id=6700 
Fri Dec 26 16:49:12 2025
DBW0 started with pid=10, OS id=1920 
Fri Dec 26 16:49:12 2025
DBW1 started with pid=11, OS id=4476 
Fri Dec 26 16:49:12 2025
DBW2 started with pid=12, OS id=6788 
Fri Dec 26 16:49:12 2025
DBW3 started with pid=13, OS id=4608 
Fri Dec 26 16:49:12 2025
DBW4 started with pid=14, OS id=6716 
Fri Dec 26 16:49:12 2025
DBW5 started with pid=15, OS id=6252 
Fri Dec 26 16:49:12 2025
DBW6 started with pid=16, OS id=7948 
Fri Dec 26 16:49:12 2025
DBW7 started with pid=17, OS id=5868 
Fri Dec 26 16:49:12 2025
LGWR started with pid=18, OS id=1644 
Fri Dec 26 16:49:12 2025
CKPT started with pid=19, OS id=5704 
Fri Dec 26 16:49:12 2025
SMON started with pid=20, OS id=4532 
Fri Dec 26 16:49:12 2025
RECO started with pid=21, OS id=380 
Fri Dec 26 16:49:12 2025
MMON started with pid=22, OS id=1316 
Fri Dec 26 16:49:12 2025
MMNL started with pid=23, OS id=6108 
Fri Dec 26 16:49:12 2025
DIAG started with pid=6, OS id=7904 
Fri Dec 26 16:49:12 2025
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\ORACLE64

这里可以看出来数据库版本是11.2.0.3,processes配置为默认值150.进一步查看启动报错时候的alert日志

ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Fri Dec 26 17:03:44 2025
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_pr00_344.trc:
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
alter database open
Fri Dec 26 17:03:48 2025
Thread 1 opened at log sequence 37301
  Current log# 2 seq# 37301 mem# 0: D:\ORACLE64\ORADATA\ORA817\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 26 17:03:49 2025
SMON: enabling cache recovery
[6340] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5143437 end:5143718 diff:281 (2 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Fri Dec 26 17:03:49 2025
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process P123 submission failed with error = 20
Starting background process QMNC
Process QMNC submission failed with error = 20
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_6340.trc:
ORA-00444: 后台进程 "QMNC" 启动失败
ORA-00020: 超出最大进程数 ()
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_6340.trc:
ORA-00450: 后台进程 'QMNC' 未启动
ORA-00444: 后台进程 "QMNC" 启动失败
ORA-00020: 超出最大进程数 ()
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_6340.trc:
ORA-00450: 后台进程 'QMNC' 未启动
ORA-00444: 后台进程 "QMNC" 启动失败
ORA-00020: 超出最大进程数 ()
Error 450 happened during db open, shutting down database
USER (ospid: 6340): terminating the instance due to error 450
Fri Dec 26 17:03:50 2025
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p036_8044.trc  (incident=4105):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_4105\ora817_p036_8044_i4105.trc
Fri Dec 26 17:03:50 2025
Process m000 submission failed with error = 20
Fri Dec 26 17:04:07 2025
Instance terminated by USER, pid = 6340
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (6340) as a result of ORA-1092
Fri Dec 26 17:04:09 2025
ORA-1092 : opitsk aborting process

看alert日志比较明显有Process P123 submission failed with error = 20提示,这里表示启动P123进程时候也遭遇到了进程数不足的限制导致该进程无法启动,而Pxxx进程是oracle的并行进程(而且在后面还报了由于坏块导致P036进程报错的信息),也就是说这个数据库在启动过程中启动了大量的并行进程(至少122个已经启动),这也就是说明了为什么150个进程不足的原因.在数据库完成实例恢复,完全open之前启动并行进程进行恢复的,很可能就是并行回滚操作.为了进一步验证猜想,把processes调整到1500,然后尝试启动库

Fri Dec 26 17:06:44 2025
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 20 KB redo, 5 data blocks need recovery
Started redo application at
 Thread 1: logseq 37301, block 18209
Recovery of Online Redo Log: Thread 1 Group 2 Seq 37301 Reading mem 0
  Mem# 0: D:\ORACLE64\ORADATA\ORA817\REDO02.LOG
Completed redo application of 0.02MB
Completed crash recovery at
 Thread 1: logseq 37301, block 18249, scn 327081807
 5 data blocks read, 5 data blocks written, 20 redo k-bytes read
Fri Dec 26 17:06:46 2025
Thread 1 advanced to log sequence 37302 (thread open)
Thread 1 opened at log sequence 37302
  Current log# 3 seq# 37302 mem# 0: D:\ORACLE64\ORADATA\ORA817\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 26 17:06:47 2025
SMON: enabling cache recovery
[7104] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5321812 end:5322031 diff:219 (2 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Dec 26 17:06:49 2025
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p091_5924.trc  (incident=5745):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5745\ora817_p091_5924_i5745.trc
Starting background process QMNC
Fri Dec 26 17:06:49 2025
QMNC started with pid=156, OS id=8012 
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p091_5924.trc  (incident=5746):
ORA-01578: ORACLE data block corrupted (file # , block # )
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5746\ora817_p091_5924_i5746.trc
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_smon_7424.trc  (incident=4961):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_4961\ora817_smon_7424_i4961.trc
Completed: alter database open
SMON: Restarting fast_start parallel rollback
Fri Dec 26 17:06:51 2025
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p000_5472.trc  (incident=5009):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5009\ora817_p000_5472_i5009.trc
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p000_5472.trc  (incident=5010):
ORA-01578: ORACLE data block corrupted (file # , block # )
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_smon_7424.trc  (incident=4962):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5010\ora817_p000_5472_i5010.trc

这次就比较清晰了,数据库在open过程中由于smon启动了并行回滚并遇到了坏块,导致P091进程和smon进程错误。因为数据库已经open,这个坏块导致无法回滚,直接对这个对象进行处理即可解决掉这个回滚报错问题.

ORA-600 ktbair2: illegal inheritance恢复

Posted on 2025 年 12 月 22 日 by 惜分飞

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

标题：ORA-600 ktbair2: illegal inheritance恢复

接到客户一个恢复case咨询,数据库open过程报ORA-00600: 内部错误代码, 参数: [ktbair2: illegal inheritance]错误

查看alert日志

2025-12-21T10:38:49.346922+08:00
alter database open
2025-12-21T10:38:50.286621+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2025-12-21T10:38:50.300632+08:00
Beginning crash recovery of 1 threads
2025-12-21T10:38:50.378690+08:00
 parallel recovery started with 32 processes
 Thread 1: Recovery starting at checkpoint rba (logseq 47910 block 201391), scn 0
2025-12-21T10:38:50.446742+08:00
Started redo scan
2025-12-21T10:38:50.597853+08:00
Completed redo scan
 read 57808 KB redo, 409 data blocks need recovery
2025-12-21T10:38:50.827024+08:00
Started redo application at
 Thread 1: logseq 47910, block 201391, offset 0
2025-12-21T10:38:50.845043+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 47910 Reading mem 0
  Mem# 0: F:\APP\ORADATA\SRMDEV\REDO03.LOG
2025-12-21T10:38:50.929100+08:00
Completed redo application of 3.15MB
2025-12-21T10:38:50.975134+08:00
Errors in file F:\APP\diag\rdbms\srmdev\srmdev\trace\srmdev_p00e_11192.trc  (incident=1382836):
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
Incident details in: F:\APP\diag\rdbms\srmdev\srmdev\incident\incdir_1382836\srmdev_p00e_11192_i1382836.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-12-21T10:38:52.033922+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
…………
2025-12-21T10:39:02.224702+08:00
Errors in file F:\APP\diag\rdbms\srmdev\srmdev\trace\srmdev_ora_9960.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 7, 块号 611)
ORA-01110: 数据文件 7: 'F:\APP\ORADATA\SRMDEV\USERS01.DBF'
ORA-10564: tablespace USERS
ORA-01110: 数据文件 7: 'F:\APP\ORADATA\SRMDEV\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 74736
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
ORA-1578 signalled during: alter database open...

这个错误以前有过类似处理,主要是由于redo无法正确应用,数据库在open过程无法正常完成实例恢复所以出现该问题.以前处理过类似案例:
Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障
这次的故障相对简单一些,通过一些简单尝试之后数据库正常应用成功

2025-12-21T21:43:00.689653+08:00
ALTER DATABASE RECOVER  datafile 1  
2025-12-21T21:43:00.691654+08:00
Media Recovery Start
2025-12-21T21:43:00.693656+08:00
Serial Media Recovery started
2025-12-21T21:43:00.721676+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 47910 Reading mem 0
  Mem# 0: F:\APP\ORADATA\SRMDEV\REDO03.LOG
Completed: ALTER DATABASE RECOVER  datafile 1  
2025-12-21T21:43:14.109099+08:00
ALTER DATABASE RECOVER  datafile 3  
2025-12-21T21:43:14.111100+08:00
Media Recovery Start
2025-12-21T21:43:14.113102+08:00
Serial Media Recovery started
2025-12-21T21:43:14.139121+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 47910 Reading mem 0
  Mem# 0: F:\APP\ORADATA\SRMDEV\REDO03.LOG
Completed: ALTER DATABASE RECOVER  datafile 3  
2025-12-21T21:43:21.361498+08:00
ALTER DATABASE RECOVER  datafile 4  
2025-12-21T21:43:21.363499+08:00
Media Recovery Start
2025-12-21T21:43:21.365500+08:00
Serial Media Recovery started
2025-12-21T21:43:21.397524+08:00
Media Recovery failed with error 264
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 4  ...
2025-12-21T21:43:31.909350+08:00
ALTER DATABASE RECOVER  datafile 7  
2025-12-21T21:43:31.911351+08:00
Media Recovery Start
2025-12-21T21:43:31.913353+08:00
Serial Media Recovery started
2025-12-21T21:43:31.940373+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 47910 Reading mem 0
  Mem# 0: F:\APP\ORADATA\SRMDEV\REDO03.LOG
Completed: ALTER DATABASE RECOVER  datafile 7

然后比较幸运直接open数据库成功,但是报ORA-600 4194错误

2025-12-21T21:45:34.003260+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2025-12-21T21:45:34.262453+08:00
Undo initialization recovery: err:0 start: 45858156 end: 45858296 diff: 140 ms (0.1 seconds)
[13724] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 45858296 end: 45858359 diff: 63 ms (0.1 seconds)
Undo initialization finished serial:0 start:45858156 end:45858359 diff:203 ms (0.2 seconds)
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
Database Characterset is AL32UTF8
2025-12-21T21:45:34.584694+08:00
Errors in file F:\APP\diag\rdbms\srmdev\srmdev\trace\srmdev_smon_13616.trc  (incident=1510624):
ORA-00600: 内部错误代码, 参数: [4194], [58], [49], [], [], [], [], [], [], [], [], []
Incident details in:F:\APP\diag\rdbms\srmdev\srmdev\incident\incdir_1510624\srmdev_smon_13616_i1510624.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-12-21T21:45:34.598703+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2025-12-21T21:45:35.634474+08:00
Autotune of undo retention is turned off. 
2025-12-21T21:45:35.636476+08:00
Resource Manager disabled during database migration: plan '''' not set
Resource Manager disabled during database migration
2025-12-21T21:45:36.009753+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
Doing block recovery for file 4 block 27127
Resuming block recovery (PMON) for file 4 block 27127
Block recovery from logseq 47911, block 78 to scn 0x0000000000000000
2025-12-21T21:45:36.026766+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 47911 Reading mem 0
  Mem# 0: F:\APP\ORADATA\SRMDEV\RED004.LOG
Block recovery completed at rba 0.0.0, scn 0x00000006a378f1bc
Doing block recovery for file 4 block 176
Resuming block recovery (PMON) for file 4 block 176
Block recovery from logseq 47911, block 78 to scn 0x00000006a378f23d
2025-12-21T21:45:36.045781+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 47911 Reading mem 0
  Mem# 0: F:\APP\ORADATA\SRMDEV\RED004.LOG
Block recovery completed at rba 47911.78.16, scn 0x00000006a378f23e
Non-fatal internal error happened while SMON was doing shrinking of rollback segments.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
replication_dependency_tracking turned off (no async multimaster replication found)
2025-12-21T21:45:36.493113+08:00
AQ Processes can not start in restrict mode
Starting background process CJQ0
2025-12-21T21:45:36.545152+08:00
CJQ0 started with pid=73, OS id=14120 
2025-12-21T21:45:37.546898+08:00
Completed: alter database open

这个处理起来比较简单,对于异常的undo进行处理之后,数据库正常导出dmp,完成本次恢复任务

一键恢复ORA-00704 ORA-00702故障—202512

Posted on 2025 年 12 月 15 日 by 惜分飞

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

标题：一键恢复ORA-00704 ORA-00702故障—202512

今天又遇到一个客户遭遇ORA-00702错误

alert日志相关报错信息

Mon Dec 15 14:10:56 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 5 processes
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 654, block 2, scn 16937055
Recovery of Online Redo Log: Thread 1 Group 3 Seq 654 Reading mem 0
  Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 654, block 3, scn 16957057
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thread 1 advanced to log sequence 655 (thread open)
Thread 1 opened at log sequence 655
  Current log# 1 seq# 655 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Dec 15 14:10:57 2025
SMON: enabling cache recovery
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_7952.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_7952.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Error 704 happened during db open, shutting down database
USER (ospid: 7952): terminating the instance due to error 704
Instance terminated by USER, pid = 7952
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (7952) as a result of ORA-1092
Mon Dec 15 14:11:00 2025
ORA-1092 : opitsk aborting process
Mon Dec 15 14:39:18 2025

这种故障处理比较多，主要是bootstrap$基表损坏,后来为了解决这种问题方便,专门写了一个小工具（bootstrap$异常修复小工具）来一键修复该问题

通过bootstrap$异常修复小工具修复之后,然后直接启动数据库成功,完成本次恢复任务

open数据库报ora-600 kdsgrp1故障处理

Posted on 2025 年 12 月 06 日 by 惜分飞

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

标题：open数据库报ora-600 kdsgrp1故障处理

客户服务器异常关机之后,数据库无法正常启动,查看alert日志发现如下报ORA-600 kdsgrp1报错信息导致数据库无法正常open

2025-12-05T16:17:23.315325+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
Undo initialization recovery: err:0 start: 7620046 end: 7620062 diff: 16 ms (0.0 seconds)
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_9672.trc  (incident=1243378):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_1243378\his_ora_9672_i1243378.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-12-05T16:17:25.341421+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2025-12-05T16:17:25.533576+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_9672.trc  (incident=1243379):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_1243379\his_ora_9672_i1243379.trc
2025-12-05T16:17:27.007101+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-12-05T16:17:27.008097+08:00
Undo initialization online undo segments: err:600 start: 7620062 end: 7623343 diff: 3281 ms (3.3 seconds)
2025-12-05T16:17:27.008097+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_9672.trc:
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
2025-12-05T16:17:27.008097+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_9672.trc:
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_9672.trc  (incident=1243380):
ORA-00603: ORACLE 服务器会话因致命错误而终止
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_1243380\his_ora_9672_i1243380.trc
2025-12-05T16:17:28.540363+08:00
opiodr aborting process unknown ospid (9672) as a result of ORA-603

尝试人工启动数据库,报错比较明显也是ORA-600 kdsgrp1错误

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 5 16:45:48 2025
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> recover datafile 1;
Media recovery complete.
SQL> recover database;
Media recovery complete.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []
Process ID: 10800
Session ID: 5025 Serial number: 34108

查看报错trace文件

*** CLIENT DRIVER:(SQL*PLUS) 2025-12-05T16:17:23.924647+08:00
 
[TOC00000]
Jump to table of contents
Dump continued from file: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_9672.trc
[TOC00001]
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 1243378 (ORA 600 [kdsgrp1]) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
kdsDumpState: cdb: 0 dspdb: 0 type: 1 info: 0x5 flag: 0x0
kdsDumpState: sample type: 0 layer: 0
* kdsgrp1-1: ***********************************************
            row 0x00c02754.24 continuation at: 0x00c02754.24 file# 3 block# 10068 slot 36 not found (dscnt: 0)
 state kdscurrid points to 0x00c28206.17
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 36 ..... nrows: 60
Dumping kcb descriptor:
kcbds 0x1e08fe71510: pdb 0, tsn 1, rdba 0x00c02754, afn 3, objd 11331, cls 1, tidflg 0x8 0x80 0x0

在这里基本上可以确认报ORA-600 错误的是file 3 block 10068，dataobj 为11331,根据经验初步怀疑是sysaux中的wrh$_某个对象异常.对启动过程做10046进一步确认

EXEC #2063975471112:c=17341,e=35163,p=54,cr=283,cu=0,mis=1,r=0,dep=1,og=4,plh=794648223,tim=9717774784
WAIT #2063975471112: nam='db file sequential read' ela= 115 file#=3 block#=11434 blocks=1 obj#=11579 tim=9717774961
WAIT #2063975471112: nam='db file sequential read' ela= 93 file#=3 block#=11435 blocks=1 obj#=11579 tim=9717775098
WAIT #2063975471112: nam='db file sequential read' ela= 89 file#=3 block#=11436 blocks=1 obj#=11579 tim=9717775217
WAIT #2063975471112: nam='db file sequential read' ela= 98 file#=3 block#=11437 blocks=1 obj#=11579 tim=9717775361
WAIT #2063975471112: nam='db file sequential read' ela= 23087 file#=3 block#=11438 blocks=1 obj#=11579 tim=9717798471
WAIT #2063975471112: nam='db file sequential read' ela= 83 file#=3 block#=11439 blocks=1 obj#=11579 tim=9717798681
WAIT #2063975471112: nam='db file sequential read' ela= 183 file#=3 block#=10075 blocks=1 obj#=11332 tim=9717799100
WAIT #2063975471112: nam='db file sequential read' ela= 124 file#=3 block#=10078 blocks=1 obj#=11332 tim=9717799291
WAIT #2063975471112: nam='db file sequential read' ela= 115 file#=3 block#=165702 blocks=1 obj#=11332 tim=9717799460
WAIT #2063975471112: nam='db file sequential read' ela= 109 file#=3 block#=83355 blocks=1 obj#=11332 tim=9717799621
WAIT #2063975471112: nam='db file sequential read' ela= 139 file#=3 block#=165703 blocks=1 obj#=11332 tim=9717799816
WAIT #2063975471112: nam='db file sequential read' ela= 139 file#=3 block#=10079 blocks=1 obj#=11332 tim=9717800074
WAIT #2063975471112: nam='db file sequential read' ela= 112 file#=3 block#=165696 blocks=1 obj#=11332 tim=9717800248
WAIT #2063975471112: nam='db file sequential read' ela= 110 file#=3 block#=165701 blocks=1 obj#=11332 tim=9717800502
WAIT #2063975471112: nam='db file sequential read' ela= 107 file#=3 block#=83354 blocks=1 obj#=11332 tim=9717800665
WAIT #2063975471112: nam='db file sequential read' ela= 109 file#=3 block#=83356 blocks=1 obj#=11332 tim=9717800823
WAIT #2063975471112: nam='db file sequential read' ela= 107 file#=3 block#=83358 blocks=1 obj#=11332 tim=9717800978
WAIT #2063975471112: nam='db file sequential read' ela= 106 file#=3 block#=165698 blocks=1 obj#=11332 tim=9717801133
WAIT #2063975471112: nam='db file sequential read' ela= 106 file#=3 block#=164358 blocks=1 obj#=11331 tim=9717801298
WAIT #2063975471112: nam='db file sequential read' ela= 108 file#=3 block#=10068 blocks=1 obj#=11331 tim=9717801509
2025-12-05T16:52:21.382064+08:00
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []

FETCH #2063975471112:c=1599462,e=1655487,p=20,cr=26,cu=0,mis=0,r=0,dep=1,og=4,plh=794648223,tim=9719430286
STAT #2063975471112 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 str=1 time=8 us)'
STAT #2063975471112 id=2 cnt=0 pid=1 pos=1 obj=0 op='HASH JOIN  (cr=0 pr=0 pw=0 str=1 time=5 us cost=9 size=34 card=1)'
STAT #2063975471112 id=3 cnt=1 pid=2 pos=1 obj=11579 op='TABLE ACCESS FULL WRM$_SNAPSHOT
 (cr=12 pr=6 pw=0 str=1 time=23966 us cost=4 size=17 card=1)'
STAT #2063975471112 id=4 cnt=1 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=6 pr=3 pw=0 str=1 time=23513 us)'
STAT #2063975471112 id=5 cnt=1 pid=4 pos=1 obj=11579 op='TABLE ACCESS FULL WRM$_SNAPSHOT
(cr=6 pr=3 pw=0 str=1 time=23497 us cost=4 size=13 card=1)'
STAT #2063975471112 id=6 cnt=6 pid=2 pos=2 obj=11331 op='TABLE ACCESS BY INDEX ROWID BATCHED WRH$_UNDOSTAT
(cr=13 pr=13 pw=0 str=1 time=2455 us cost=4 size=102 card=6)'
STAT #2063975471112 id=7 cnt=7 pid=6 pos=1 obj=11332 op='INDEX SKIP SCAN WRH$_UNDOSTAT_PK
(cr=12 pr=12 pw=0 str=1 time=2304 us cost=2 size=0 card=6)'
2025-12-05T16:52:23.005928+08:00
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []

<error barrier> at 0x000000275BED14D0 placed dbsdrv.c@4959
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
<error barrier> at 0x000000275BED14D0 placed dbsdrv.c@4959
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []

到这里基本上可以确认是WRH$_UNDOSTAT_PK去找WRH$_UNDOSTAT中午对应记录,从而出现该问题,对应的具体sql为

PARSING IN CURSOR #2063975471112 len=233 dep=1 uid=0 oct=3 lid=0 tim=9717739563
  hv=517686153 ad='1e1efda1f20' sqlid='d4m7ss0gdqhw9'
select max(maxconcurrency) from sys.wrh$_undostat where instance_number = :1 and dbid = :2 and snap_id in  
(select snap_id from dba_hist_snapshot where end_interval_time >(select max(end_interval_time)-7 from dba_hist_snapshot))

BINDS #2063975471112:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=8e8646e8  bln=22  avl=02  flg=05
  value=1
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=8e864700  bln=22  avl=06  flg=01
  value=971429521

对于这种问题,处理起来相对比较简单,绕过oracle open过程对该sql的执行,然后open库对该表的index进行rebuild

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 3
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 3
Enter value for block_id: 10068
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 10068 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME
------------------ ------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
SYS
WRH$_UNDOSTAT
TABLE              SYSAUX



SQL> select index_name from dba_indexes where table_name='WRH$_UNDOSTAT';

INDEX_NAME
--------------------------------------------------------------------------------
WRH$_UNDOSTAT_PK

SQL> alter index WRH$_UNDOSTAT_PK REBUILD ONLINE;

Index altered.

至此该数据库完成恢复任务,重启之后一切正常.

expdp dmp 导出不完整导入ORA-39059 ORA-39246 故障抢救数据

Posted on 2025 年 12 月 03 日 by 惜分飞

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

标题：expdp dmp 导出不完整导入ORA-39059 ORA-39246 故障抢救数据

客户一套nc系统,由于安装时候把库建在了比较小的分区上,运行一些时间之后,出现空间不足,现场技术人员对oracle不太熟悉,经过一系列操作(删除业务表空间,复制pdb,创建表空间等等操作),无法恢复数据库,准备使用备份的dmp进行还原,结果分析发现仅保留的最后一份dmp,是一份导出不完全的dmp文件,无法正常导入(以前处理过一个类似case:ORA-39773: parse of metadata stream failed故障处理,尝试导入报ORA-39246错:

C:\Users\XFF>impdp system/oracle@127.0.0.1/orapdb directory=expdp_dir dumpfile=xxxxx_2025-12-01_0230.dmp logfile=1.log

Import: Release 19.0.0.0.0 - Production on 星期三 12月 3 21:00:19 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: 操作无效
ORA-39059: 转储文件集不完整
ORA-39246: 无法在提供的转储文件中定位主表

分析当时当初的dmp日志,由于expdp的job表所在表空间不足导致expdp导出失败

TABLE:"XIFENFEI"."EOM_MEASURE_POINT"
ORA-30032: 挂起的 (可恢复) 语句已超时
ORA-01691: Lob 段 XIFENFEI.SYS_LOB0000161267C00111$$ 无法通过 32 (在表空间 NNC_DATA01 中) 扩展
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: 在 "SYS.KUPW$WORKER", line 12620
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: 在 "SYS.KUPW$WORKER", line 11414
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xda5dae50     33476  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
0xda5dae50     12641  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
0xda5dae50     11602  package body SYS.KUPW$WORKER.CREATE_OBJECT_ROWS
0xda5dae50     15268  package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS
0xda5dae50      3907  package body SYS.KUPW$WORKER.UNLOAD_METADATA
0xda5dae50     13736  package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
0xda5dae50      2429  package body SYS.KUPW$WORKER.MAIN
0x6524a4f0         2  anonymous block
KUPW: Object row index into parse items is: 1
KUPW: Parse item count is: 19
KUPW: In function CHECK_FOR_REMAP_NETWORK
KUPW: Nothing to remap
KUPW: In procedure BUILD_OBJECT_STRINGS - non-base info
KUPW: In procedure BUILD_SUBNAME_LIST with TABLE:XIFENFEI.EOM_MEASURE_POINT
KUPW: In function NEXT_PO_NUMBER
KUPW: PO number assigned: 34198
FORALL
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-30032: 挂起的 (可恢复) 语句已超时
ORA-01691: Lob 段 XIFENFEI.SYS_LOB0000161267C00111$$ 无法通过 32 (在表空间 NNC_DATA01 中) 扩展
作业 "XIFENFEI"."SYS_EXPORT_SCHEMA_01" 因致命错误于 星期一 12月 1 06:33:21 2025 elapsed 0 04:03:18 停止

从导出日志看,在导出大量”0 KB 0 行”记录之后提示表空间不足,expdp的job表无法扩展导致导出挂起然后超时导出终止(这个导出操作没有完全完成),从而在导入的时候出现了ORA-39059: 转储文件集不完整 ORA-39246: 无法在提供的转储文件中定位主表的错误.对于这种故障,分析导出日志,发现运气不错,所有有数据的表都导出完成,基于这个心中就有了第一层底气,所有表数据不会丢失(因为都导出到了这个dmp中),但是非表的字典数据不完整,要想业务完整跑起来,需要找到一个完整的业务字典信息.对于大量的备份dmp被删除,然后对应分区还写入了很多数据,只能尝试看运气,通过对磁盘文件镜像,然后进行反删除恢复,找出来一个11月26日的dmp的压缩文件是完整的