Database SOS – Professional Database Recovery Support@+8617813235971

比较少遇到打开一个数据库报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 [ksunfy : too few sessions]

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

标题：ORA-600 [ksunfy : too few sessions]

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设置限制已经达到上限所以出现该错误

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

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

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

以前遇到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-600 ktbair2: illegal inheritance恢复

联系：手机/微信(+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

联系：手机/微信(+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$异常修复小工具修复之后,然后直接启动数据库成功,完成本次恢复任务

PostgreSQL查询一个表相关的所有oid

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

标题：PostgreSQL查询一个表相关的所有oid

在pg中查询一个表相关的所有oid(表OID、TOAST表OID、索引OID、TOAST表的索引OID、约束OID)

-- 替换这里的表名和模式名
\set search_path = public;
\set table_name '\'AO_6384AB_DISCOVERED\''
\set schema_name '\'public\''

WITH table_info AS (
    -- 基础表信息：表OID、TOAST表OID
    SELECT
        c.oid AS table_oid,
        c.relname AS table_name,
        c.reltoastrelid AS toast_oid,
        n.nspname AS schema_name
    FROM
        pg_catalog.pg_class c
    JOIN
        pg_catalog.pg_namespace n ON c.relnamespace = n.oid
    WHERE
        c.relname = :table_name
        AND n.nspname = :schema_name
        AND c.relkind = 'r'
),
index_info AS (
    -- 索引信息：索引OID
    SELECT
        t.table_oid,
        i.oid AS object_oid,
        i.relname AS object_name,
        'index' AS object_type
    FROM
        pg_catalog.pg_class i
    JOIN
        pg_catalog.pg_index idx ON i.oid = idx.indexrelid
    JOIN
        table_info t ON idx.indrelid = t.table_oid
    WHERE
        i.relkind = 'i'
),
toast_info AS (
    -- TOAST表信息：TOAST表OID
    SELECT
        t.table_oid,
        t.toast_oid AS object_oid,
        c.relname AS object_name,
        'toast_table' AS object_type
    FROM
        table_info t
    JOIN
        pg_catalog.pg_class c ON t.toast_oid = c.oid
    WHERE
        t.toast_oid <> 0
),
toast_index_info AS (
    -- TOAST索引信息：TOAST表的索引OID
    SELECT
        t.table_oid,
        i.oid AS object_oid,
        i.relname AS object_name,
        'toast_index' AS object_type
    FROM
        table_info t
    JOIN
        pg_catalog.pg_class c ON t.toast_oid = c.oid
    JOIN
        pg_catalog.pg_index idx ON c.oid = idx.indrelid
    JOIN
        pg_catalog.pg_class i ON idx.indexrelid = i.oid
    WHERE
        t.toast_oid <> 0
        AND i.relkind = 'i'
),
constraint_info AS (
    -- 约束信息：约束OID
    SELECT
        t.table_oid,
        con.oid AS object_oid,
        con.conname AS object_name,
        CASE con.contype
            WHEN 'p' THEN 'primary_key'
            WHEN 'u' THEN 'unique_constraint'
            WHEN 'f' THEN 'foreign_key'
            WHEN 'c' THEN 'check_constraint'
            ELSE 'other_constraint'
        END AS object_type
    FROM
        pg_catalog.pg_constraint con
    JOIN
        table_info t ON con.conrelid = t.table_oid
)
-- 合并所有结果，先输出表本身的信息，再输出其他关联对象
SELECT
    t.table_oid AS object_oid,
    t.table_name AS object_name,
    'table' AS object_type,
    t.schema_name AS schema_name
FROM
    table_info t
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    index_info
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    toast_info
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    toast_index_info
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    constraint_info
ORDER BY
    object_type;

PostgreSQL oid文件替换实现数据访问

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

标题：PostgreSQL oid文件替换实现数据访问

对于postgresql数据库,我们都知道他的数据在独立的oid文件里面,而字典主要在pg_type/1247,pg_class/1259,pg_attribute/1260等对象中,而数据是存储在独立的oid和toast oid文件中.如果通过提供oid文件,可以实现狸猫换太子的恢复数据.这里做一个简单测试,大概思路是这样的库里面有一个表a,然后创建一个空表b,然后把a表相关的oid文件复制成b表的oid文件,然后验证数据是否可以正常查询
创建一个空的b表,结构和a表一致

[postgres@xifenfei 13676]$ psql
psql (12.8)
Type "help" for help.

postgres=# CREATE TABLE "public"."AO_6384AB_DISCOVERED1" (
postgres(#   "DATE" "pg_catalog"."timestamp",
postgres(#   "ID" "pg_catalog"."int4",
postgres(#   "KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default",
postgres(#   "PLUGIN_KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default",
postgres(#   "USER_KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default"
postgres(# )
postgres-# ;
CREATE TABLE

检查a/b的情况

postgres=# select count(1) from "AO_6384AB_DISCOVERED";
 count 
-------
   544
(1 row)

postgres=# select count(1) from "AO_6384AB_DISCOVERED1";
 count 
-------
     0
(1 row)

postgres=# \d "AO_6384AB_DISCOVERED"
                    Table "public.AO_6384AB_DISCOVERED"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 DATE       | timestamp without time zone |           |          | 
 ID         | integer                     |           |          | 
 KEY        | character varying           |           |          | 
 PLUGIN_KEY | character varying           |           |          | 
 USER_KEY   | character varying           |           |          | 

postgres=# \d "AO_6384AB_DISCOVERED1"
                   Table "public.AO_6384AB_DISCOVERED1"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 DATE       | timestamp without time zone |           |          | 
 ID         | integer                     |           |          | 
 KEY        | character varying           |           |          | 
 PLUGIN_KEY | character varying           |           |          | 
 USER_KEY   | character varying           |           |          |

存在数据的a/b表相关oid信息



postgres=# select oid,relname,relfilenode from pg_class where relname like '%AO_6384AB_DISCOVERED%';
  oid  |        relname        | relfilenode 
-------+-----------------------+-------------
 16516 | AO_6384AB_DISCOVERED  |       16516
 17069 | AO_6384AB_DISCOVERED1 |       17069
(2 rows)

postgres=# select oid,relname,relfilenode from pg_class where relname like '%16516%' or  relname like '%17069%'  ;
  oid  |       relname        | relfilenode 
-------+----------------------+-------------
 16519 | pg_toast_16516       |       16519
 16521 | pg_toast_16516_index |       16521
 17072 | pg_toast_17069       |       17072
 17074 | pg_toast_17069_index |       17074
(4 rows)

关闭数据库把a表相关的oid文件拷贝替换b表的oid文件

[postgres@xifenfei 13676]$ pg_ctl stop -D /pgdata
waiting for server to shut down....2025-12-12 20:00:58.804 HKT [21445] LOG:  received fast shutdown request
2025-12-12 20:00:58.805 HKT [21445] LOG:  aborting any active transactions
2025-12-12 20:00:58.805 HKT [21471] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.805 HKT [21473] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.805 HKT [21856] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.806 HKT [21472] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.806 HKT [21445] LOG: background worker"logical replication launcher"(PID 252)exited with exit code 1
2025-12-12 20:00:58.807 HKT [21447] LOG:  shutting down
2025-12-12 20:00:58.811 HKT [21445] LOG:  database system is shut down
 done
server stopped
[postgres@xifenfei 13676]$ cp  16516 17069
[postgres@xifenfei 13676]$ cp  16519 17072
[postgres@xifenfei 13676]$ cp  16521 17074
[postgres@xifenfei 13676]$ pg_ctl start -D /pgdata
waiting for server to start....2025-12-12 20:02:05.985 HKT [22241] LOG:  
starting PostgreSQL 12.8 on x86_64-pc-linux-gnu, 
compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26.0.1), 64-bit
2025-12-12 20:02:05.985 HKT [22241] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-12-12 20:02:05.986 HKT [22241] LOG:  listening on IPv6 address "::", port 5432
2025-12-12 20:02:05.986 HKT [22241] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-12-12 20:02:05.993 HKT [22242] LOG:  database system was shut down at 2025-12-12 20:00:58 HKT
2025-12-12 20:02:05.995 HKT [22241] LOG:  database system is ready to accept connections
 done
server started

查询a/b表数据

[postgres@xifenfei 13676]$ psql
psql (12.8)
Type "help" for help.

postgres=# select count(1) from "AO_6384AB_DISCOVERED1";
 count 
-------
   544
(1 row)

postgres=# select count(1) from "AO_6384AB_DISCOVERED";
 count 
-------
   544
(1 row)

通过上述从测试,证明创建一个新的表结构,完全可以访问老的oid文件中的数据

模拟sql server故障备份完成恢复实现数据0丢失

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

标题：模拟sql server故障备份完成恢复实现数据0丢失

在sql server数据库中,使用备份还原,可以用来恢复的备份文件只能是全备、增量备份、事务日志备份,在很多情况下,sql server生产库异常,最后的事务日志没有来得及备份(但是ldf文件存在),这种情况下如何最大限度恢复数据实现数据0丢失,其实最重要的关键点就是对最后异常库的ldf文件的事务日志进行备份,在sql server里面给这个操作起了一个专业的词叫做:备份结尾日志（Tail of log).我这边通过一个实验来模拟整个操作过程.
1. 创建一个库,恢复模式为完整

2.创建表并插入一条记录,然后进行全备

3. 再插入一条记录,并做事务日志备份

4. 再插入一条记录,并在脱机情况下删除掉数据库的mdf文件（模拟故障）,在实际生产中如果原机器损坏,可以把在新机器上安装同版本sql,然后创建同名库,把ldf文件替换

5. 进行最后的ldf(事务日志的备份),在sql server中这个备份操作叫做“备份结尾日志（Tail of log)”,先尝试直接使用图形化工具进行备份

6. 图形化这种情况无法直接备份,使用命令行形式成功备份

这里备份命令带WITH NORECOVERY、WITH NO_TRUNCATE、WITH CONTINUE_AFTER_ERROR相关内容官方描述:

如果数据库处于联机状态并且您计划对数据库执行还原操作，则从备份日志结尾开始。 
要避免联机数据库出错，必须使用 BACKUP Transact-SQL 语句的 WITH NORECOVERY 选项。

如果数据库处于脱机状态而无法启动，则需要还原数据库，从备份日志结尾开始。 
由于此时不会发生任何事务，因此请使用 WITH NO_TRUNCATE 选项。 
NO_TRUNCATE 实际上与仅复制事务日志备份相同。 由于此时不会发生任何事务，因此可以选择使用 WITH NORECOVERY。

如果数据库损坏，则尝试使用 WITH CONTINUE_AFTER_ERROR 语句的 BACKUP 选项执行结尾日志备份。

在损坏的数据库上，仅当日志文件未受损、数据库处于支持结尾日志备份的状态并且数据库不包含任何大容量日志更改时，
日志尾部备份才会成功。 如果无法创建结尾日志备份，则最新日志备份后提交的任何事务都将丢失。

7. 进行数据库还原操作(这里先删除了故障库和对应ldf文件),也可以还原成其他库名和不同路径,也可以通过命令行进行还原(全备和除最后一个事务日志之前的备份需要使用WITH NORECOVERY)

基于上述测试,完美的恢复故障之前的所有数据

sql server 事务日志备份异常恢复案例

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

标题：sql server 事务日志备份异常恢复案例

有客户的sql server数据库运行在双机环境中,由于心跳网络异常导致双机频繁切换最终数据库损坏DBCC检查报大量错误

DBCC CHECKDB('OLTP') WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 28147935764938752 (type Unknown), page ID (1:33059984) contains an incorrect page ID in its page header. The PageId in the page header = (68:3276868).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 11540680206712832 (type Unknown), page ID (1:33059985) contains an incorrect page ID in its page header. The PageId in the page header = (102:6488116).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 16888988233302016 (type Unknown), page ID (1:33059986) contains an incorrect page ID in its page header. The PageId in the page header = (93:6619252).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 16888988233302016 (type Unknown), page ID (1:33059987) contains an incorrect page ID in its page header. The PageId in the page header = (93:6619252).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 16888988233302016 (type Unknown), page ID (1:33059988) contains an incorrect page ID in its page header. The PageId in the page header = (93:6619252).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 28147836977938432 (type Unknown), page ID (1:33059989) contains an incorrect page ID in its page header. The PageId in the page header = (73:6619248).
Msg 8909, Level 16, State 1, Line 1
……………………
Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (type In-row data): Page (1:36535484) could not be processed.  See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (type In-row data): Page (1:36535485) could not be processed.  See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (type In-row data): Page (1:36535486) could not be processed.  See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (type In-row data): Page (1:36535487) could not be processed.  See other errors for details.
CHECKDB found 0 allocation errors and 24 consistency errors in table 'CIOMessage' (object ID 1961110077).
CHECKDB found 0 allocation errors and 17955 consistency errors in database 'OLTP'.

Completion time: 2025-11-19T17:13:03.2762122+08:00

客户每天做全库备份,每4小时做事务日志备份,备份类似这样的情况

客户尝试使用全备进行恢复,结果发现只有13日的全备是好的,可以还原出来数据库,其他备份还原直接报错,基于这样的情况,可以希望把数据恢复到11月19日.我接手这个故障之后,先尝试还原13日的备份

然后尝试人工应用事务日志备份,类似命令

RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_13_030001_7745248.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_13_060001_3581210.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_13_090001_2856408.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_13_120002_0713663.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_13_150001_7305524.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_13_180000_9123036.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_13_210001_3663138.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_14_000001_1605695.trn' WITH NORECOVERY
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_14_030001_7280782.trn' WITH NORECOVERY
………………
RESTORE LOG OLTP1121 FROM DISK = 'D:\share\OLTP_backup_2025_11_17_180001_1343952.trn' WITH NORECOVERY

结果在OLTP_backup_2025_11_17_180001_1343952文件位置报错

Processed 0 pages for database 'OLTP_1121', file 'OLTP' on file 1.
Processed 10388 pages for database 'OLTP1121', file 'OLTP_log' on file 1.
Msg 9004, Level 16, State 3, Line 1
An error occurred while processing the log for database 'OLTP_1121'.  If possible, restore from backup. 
If a backup is not available, it might be necessary to rebuild the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Completion time: 2025-11-21T13:41:54.2352031+08:00

通过图形化界面进行事务日志恢复也报错

基于这样的情况,数据库层面的正常恢复途径只能恢复到11月17日18时左右数据,因为后面的日志发生了损坏,无法继续正常恢复,对于这种情况,我们这边使用日志解析工具对剩余事务日志备份进行解析,生成.sql文件

然后客户把解析出来的.sql文件依次在会到11月17日18时的库上面去执行,这样顺利吧客户整体数据库恢复到最新状态,完成本次恢复任务(注意后续可能一些类似序列值需要调整)

win平台挂起Oracle数据库启动进程

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

标题：win平台挂起Oracle数据库启动进程

对于oracle的有些故障,如果数据库完全open会报错,导致启动失败,这个时候,我们可以尝试在数据库open到一半的过程中把启动过程挂起,然后登录数据库对字典进行修复,然后就可以正常启动数据库。非win平台可以通过gdb挂起数据库启动过程,因为win系统是进程模式,无法通过gdb挂起来实现,通过自己写程序修改oracle内存来实现这个功能

在Oracle Recovery Tools工具中,点击修改内存—>检索Oracle数据库进程—>选择需要操作的数据库进程—>挂起Oracle进程就可以实现oracle数据库在open的过程中挂住

然后其他会话登录数据库,进行需要的操作,再重新点击取消挂起数据库可以继续open成功

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