Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障

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

标题：Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障

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

接到客户反馈,一套运行在虚拟化平台中的Oracle数据库,由于机房断电,导致数据库无法启动,最初启动报错

2025-04-22T16:59:48.922227+08:00
Completed: alter database mount exclusive
alter database open
2025-04-22T16:59:52.609726+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2025-04-22T16:59:52.937852+08:00
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
 Thread 1: Recovery starting at checkpoint rba (logseq 11031 block 139863), scn 0
2025-04-22T16:59:53.094081+08:00
Started redo scan
2025-04-22T16:59:53.203464+08:00
Completed redo scan
 read 15533 KB redo, 1140 data blocks need recovery
2025-04-22T16:59:53.515972+08:00
Hex dump of (file 3,block 82642) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p001_6612.trc

Corrupt block relative dba: 0x00c142d2 (file 3,block 82642)
Fractured block found during crash/instance recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x00c142d2
 last change scn: 0x0000.0001.7a0219e2 seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x0fcb0601
 check value in block header: 0x82eb
 computed block checksum: 0x172a

2025-04-22T16:59:53.563004+08:00
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF' for corrupt data at rdba: 0x00c142d2 (file 3,block 82642)
Reread (file 3,block 82642) found same corrupt data (no logical check)
2025-04-22T16:59:54.578467+08:00
Hex dump of (file 3,block 207498) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p000_6064.trc
2025-04-22T16:59:54.578467+08:00

Corrupt block relative dba: 0x00c32a8a (file 3,block 207498)
Fractured block found during crash/instance recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x00c32a8a
 last change scn: 0x0000.0001.731ec74f seq: 0x2 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x10520601
 check value in block header: 0x61c3
 computed block checksum: 0xe27a

Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF' for corrupt data at rdba: 0x00c32a8a (file 3,block 207498)
Reread (file 3,block 207498) found same corrupt data (no logical check)
Hex dump of (file 4, block 3959) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p000_6064.trc

Corrupt block relative dba: 0x01000f77 (file 4, block 3959)
Fractured block found during crash/instance recovery
Data in bad block:
 type: 2 format: 2 rdba: 0x01000f77
 last change scn: 0x0000.0001.79fa3621 seq: 0x4 flg: 0x04
 spare3: 0x0
 consistency value in tail: 0xcf6c0201
 check value in block header: 0x99d2
 computed block checksum: 0x79e0

Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\HIS\UNDOTBS01.DBF' for corrupt data at rdba: 0x01000f77 (file 4,block 3959)
Reread (file 4, block 3959) found same corrupt data (no logical check)
2025-04-22T16:59:54.640991+08:00
Started redo application at
 Thread 1: logseq 11031, block 139863, offset 0
2025-04-22T16:59:54.672246+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 11031 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
2025-04-22T16:59:54.703494+08:00
Hex dump of (file 3,block 89738) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p006_5236.trc

Corrupt block relative dba: 0x00c15e8a (file 3,block 89738)
Fractured block found during crash/instance recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x00c15e8a
 last change scn: 0x0000.0001.7a09108a seq: 0x2 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x278e0602
 check value in block header: 0xd721
 computed block checksum: 0x2705

Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF' for corrupt data at rdba: 0x00c15e8a (file 3,block 89738)
Reread (file 3,block 89738) found same corrupt data (no logical check)
2025-04-22T16:59:54.734756+08:00
Hex dump of (file 3,block 9504) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p005_3660.trc

Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF' for corrupt data at rdba: 0x00c02520 (file 3,block 9504)
Reread (file 3,block 9504) found same corrupt data (logically corrupt)
*****************************************************************
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-04-22T16:59:54.750364+08:00
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x7FF64221F2F8, kdxlin()+4824]
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p008_3964.trc  (incident=521460):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4824][ACCESS_VIOLATION][ADDR:0xC][PC:0x7FF64221F2F8][UNABLE_TO_READ][]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_521460\his_p008_3964_i521460.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-04-22T16:59:55.156605+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p005_3660.trc:
ORA-00742: 日志读取在线程 1 序列 11029 块 51218 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG'
2025-04-22T16:59:55.828516+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p00a_3428.trc  (incident=521476):
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_521476\his_p00a_3428_i521476.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-04-22T16:59:56.844113+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p006_5236.trc  (incident=521444):
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_521444\his_p006_5236_i521444.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-04-22T16:59:59.062944+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p005_3660.trc:
ORA-00742: 日志读取在线程 1 序列 11029 块 51218 中检测到写入丢失情况
ORA-00334: 归档日志: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG'
2025-04-22T16:59:59.156645+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p00a_3428.trc:
ORA-00742: 日志读取在线程 1 序列 11029 块 51218 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG'
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_521477\his_p00a_3428_i521477.trc
2025-04-22T17:00:04.250414+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p006_5236.trc:
ORA-00742: 日志读取在线程 1 序列 11029 块 51218 中检测到写入丢失情况
ORA-00334: 归档日志: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG'
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2025-04-22T17:00:04.344200+08:00
Slave encountered ORA-10388 exception during crash recovery
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p006_5236.trc  (incident=521445):
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 299732)
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 11351
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_521445\his_p006_5236_i521445.trc
2025-04-22T17:00:05.312967+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p00a_3428.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 216445)
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 11171
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2025-04-22T17:00:05.359805+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-04-22T17:00:06.172286+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p006_5236.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 299732)
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 11351
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2025-04-22T17:00:06.172286+08:00
Slave encountered ORA-1578 exception during crash recovery
Slave exiting with ORA-1578 exception
2025-04-22T17:00:06.172286+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_p006_5236.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 299732)
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 11351
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
ORA-1578 signalled during: alter database open...

这里面主要报错有几大类:
1.在需要实例恢复的block中有坏块,而且主要集中在3号文件(sysaux)上
2. ORA-00742: 日志读取在线程 1 序列 11029 块 51218 中检测到写入丢失情况
3. ORA-600 ktbair2: illegal inheritance错误
对于这些问题的本质都是数据库无法正常实例恢复,尝试按照数据文件级别进行恢复操作,结果只有datafile 3 不成功

2025-04-22T20:11:36.724880+08:00
ALTER DATABASE RECOVER  datafile 3  
2025-04-22T20:11:36.740523+08:00
Media Recovery Start
2025-04-22T20:11:36.771771+08:00
Serial Media Recovery started
2025-04-22T20:11:36.896781+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 11031 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xC] [PC:0x7FF64221F2F8, kdxlin()+4824]
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_2512.trc  (incident=721216):
ORA-07445: ??????: ???? [kdxlin()+4824] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x7FF64221F2F8] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_721216\his_ora_2512_i721216.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-04-22T20:12:56.585813+08:00
ALTER DATABASE RECOVER  datafile 3  
2025-04-22T20:12:56.601434+08:00
Media Recovery Start
2025-04-22T20:12:56.601434+08:00
Serial Media Recovery started
2025-04-22T20:12:57.351435+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 11031 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
2025-04-22T20:12:58.492071+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_5332.trc  (incident=721217):
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\his\his\incident\incdir_721217\his_ora_5332_i721217.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-04-22T20:13:00.710878+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-04-22T20:13:01.398399+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_5332.trc:
ORA-00742: 日志读取在线程 1 序列 11029 块 51218 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG'
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2025-04-22T20:13:03.820304+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_mz00_2200.trc:
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
2025-04-22T20:13:06.070340+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_5332.trc:
ORA-00742: 日志读取在线程 1 序列 11029 块 51218 中检测到写入丢失情况
ORA-00334: 归档日志: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG'
ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], [], [], [], [], [], [], [], [], [], [], []
2025-04-22T20:13:06.085990+08:00
Media Recovery failed with error 10562
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 3  ...

对于这样的故障,前段时间通过Oracle Recovery Tools工具处理过类似异常:Oracle Recovery Tools修复ORA-600 6101/kdxlin:psno out of range故障
QQ20250422-201804

然后尝试recover datafile 3,并打开数据库成功
QQ20250422-201916
至此数据库open成功,由于该库有不少坏块(包括system的一些字典),后续使用逻辑方式迁移数据到新库中(其中由于C_OBJ#簇中有坏块,导致迁移过程中大量index丢失,通过一些方法使用obj$,inde$等基表人工生成index.sql对于丢失的index进行弥补),最终客户业务正常运行

可能是 tempdb 空间用尽或某个系统表不一致故障处理

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

标题：可能是 tempdb 空间用尽或某个系统表不一致故障处理

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

有客户sql server数据库由于异常断电,导致数据库访问异常
QQ20250422-132721

对其做dbcc检查发现报：检查已终止。收集事实数据时检测到错误。可能是 tempdb 空间用尽或某个系统表不一致。请检查前面的错误消息。错误
QQ20250422-121636
查看sql server 日志,发现报错日志内容为:
04/22/2025 10:50:26,spid58,未知,DBCC CHECKDB (SHINVA) WITH no_infomsgs executed by sa terminated abnormally due to error state 5. Elapsed time: 0 hours 0 minutes 1 seconds.
04/22/2025 10:50:26,spid58,未知,The Database ID 7 Page (1:147440) slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
04/22/2025 10:50:26,spid58,未知,错误: 7105，严重性: 22，状态: 9。
对于这种情况,尝试重建LDF,和REPAIR_ALLOW_DATA_LOSS方案都失败,最终确认通过逻辑迁移的方式完成恢复,然后再次尝试dbcc一切正常,完成本次恢复任务
20250423210001

11.2.0.4库中遇到ORA-600 kcratr_nab_less_than_odr报错

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

标题：11.2.0.4库中遇到ORA-600 kcratr_nab_less_than_odr报错

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

今天有一个客户11.2.0.4的库由于断电报ORA-600 kcratr_nab_less_than_odr的故障

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期二 4月 22 10:56:36 2025

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

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

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

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 1.9174E+10 bytes
Fixed Size                  2289320 bytes
Variable Size            3355443544 bytes
Database Buffers         1.5771E+10 bytes
Redo Buffers               45932544 bytes
数据库装载完毕。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4653],
[50575], [50922], [], [], [], [], [], [], []

alert日志报错信息

Tue Apr 22 05:08:12 2025
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 3880792636
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Tue Apr 22 05:08:17 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 2451 KB redo, 1442 data blocks need recovery
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\ydhl\ydhl\trace\ydhl_ora_2164.trc  (incident=240429):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4653], [50575], [50922],[],[],[],[],[],[],[]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\ydhl\ydhl\incident\incdir_240429\ydhl_ora_2164_i240429.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Aborting crash recovery due to error 600
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\ydhl\ydhl\trace\ydhl_ora_2164.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4653], [50575], [50922],[],[],[],[],[],[],[]
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\ydhl\ydhl\trace\ydhl_ora_2164.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4653], [50575], [50922],[],[],[],[],[],[],[]
ORA-600 signalled during: ALTER DATABASE OPEN...

这个故障在一般在11.2.0.1版本中最为常见,处理起来比较简单,一般数据也会0丢失
12c启动报kcratr_nab_less_than_odr
又一例ORA-600 kcratr_nab_less_than_odr
ORA-600 kcratr_nab_less_than_odr故障解决
差点被误操作的ORA-600 kcratr_nab_less_than_odr故障
这里处理比较简单,重建控制文件就完成了恢复工作
102328

[MY-013183] [InnoDB] Assertion failure故障处理

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

标题：[MY-013183] [InnoDB] Assertion failure故障处理

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

在一个存储故障的环境中,通过做硬件恢复,恢复出来一个mysql数据库,但是直接启动报错

[mysql@localhost bin]$ ./mysqld
2025-04-17T03:34:50.352302Z 0 [System] [MY-010116] [Server] /data/mysql/mysql/bin/mysqld (mysqld 8.0.34) starting as process 58239
2025-04-17T03:34:50.356910Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-17T03:34:51.031054Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=160] log sequence number 1728577790947 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.031090Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.031118Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=131] log sequence number 1728577833027 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.031124Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.031138Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=3621] log sequence number 1728577635513 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.031142Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.031193Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=167] log sequence number 1728577760219 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.042480Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_001′ Page [page id: space=4294967279, page number=184] log sequence number 1728577792529 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.042486Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.042359Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_001′ Page [page id: space=4294967279, page number=1975] log sequence number 1728577800027 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.042681Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.059937Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-17T03:34:51.159245Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘xff/t_xifenfei’ Page [page id: space=153, page number=4] log sequence number 1728577926919 is in the future! Current system log sequence number 1728577498088.
2025-04-17T03:34:51.159280Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.163187Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: fut0lst.ic:81:addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA thread 140491735693056
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2025-04-17T03:34:51Z UTC – mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=f183cd3ecfc35a4aa5da997063d5e8c97ffca986
Thread pointer: 0x7fc6bc000b60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7fc6c7ffeaf0 thread_stack 0×100000
/data/mysql/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0×41) [0x21323b1]
/data/mysql/mysql/bin/mysqld(print_fatal_signal(int)+0x2a2) [0xfef932]
/data/mysql/mysql/bin/mysqld(my_server_abort()+0×75) [0xfefb75]
/data/mysql/mysql/bin/mysqld(my_abort()+0xe) [0x212c24e]
/data/mysql/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0×309) [0x237cde9]
/data/mysql/mysql/bin/mysqld() [0x2349cf0]
/data/mysql/mysql/bin/mysqld() [0x234aa54]
/data/mysql/mysql/bin/mysqld(trx_purge(unsigned long, unsigned long, bool)+0xeb) [0x234d56b]
/data/mysql/mysql/bin/mysqld(srv_purge_coordinator_thread()+0×450) [0x23224b0]
/data/mysql/mysql/bin/mysqld(void Detached_thread::operator()<void (*)()>(void (*&&)())+0xca) [0x224bcaa]
/lib64/libstdc++.so.6(+0xc2ba3) [0x7fc731c11ba3]
/lib64/libpthread.so.0(+0x814a) [0x7fc732fe614a]
/lib64/libc.so.6(clone+0×43) [0x7fc7312eef23]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

这个报错主要含义是：

  1. 多个表空间（特别是innodb_undo_*）的日志序列号(LSN)比当前系统LSN要大，这表明可能存在数据损坏或不一致
  2. 系统最终因为断言失败而崩溃

对于这样的情况,可以通过mysql强制拉库的方式启动mysql,如果可以启动成功直接使用mysqldump导出数据,然后重建新库,如果无法启动mysql成功,那就考虑通过对单个的ibd基表进行discard+import方式进行恢复参考:MySQL 8.0版本ibd文件恢复，如果这个方法不能成功考虑直接通过工具读取ibd文件参考:frm和ibd文件数据库恢复

Oracle 19c 202504补丁（RUs+OJVM）-19.27

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

标题：Oracle 19c 202504补丁（RUs+OJVM）-19.27

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

 

19.0.0.0
 Description  Database Update  GI Update  Windows Bundle Patch
 APR2025 (19.27.0.0.0) 37642901  37641958  37532350
 JAN2025 (19.26.0.0.0) 37260974  37257886  37486199
 OCT2024 (19.25.0.0.0) 36912597  36916690  36878821
 JUL2024 (19.24.0.0.0) 36582781  36582629  36521936
 APR2024 (19.23.0.0.0) 36233263  36233126  36219938
 JAN2024 (19.22.0.0.0) 35943157  35940989  35962832
 OCT2023 (19.21.0.0.0) 35643107  35642822  35681552
 JUL2023 (19.20.0.0.0) 35320081  35319490  35348034
 APR2023 (19.19.0.0.0) 35042068  35037840  35046439
 JAN2023 (19.18.0.0.0) 34765931  34762026  34750795
 Oct2022 (19.17.0.0.0) 34419443  34416665  34468114
 JUL2022 (19.16.0.0.0) 34133642  34130714  34110685
 APR2022 (19.15.0.0.0) 33806152  33803476  33829175
 JAN2022 (19.14.0.0.0) 33515361  33509923  33575656
 OCT2021(19.13.0.0.0) 33192793  33182768  33155330
 JUL2021 (19.12.0.0.0) 32904851  32895426  32832237
 APR2021 (19.11.0.0.0) 32545013  32545008  32409154
 JAN2021 (19.10.0.0.0) 32218454  32226239  32062765
 OCT2020 (19.9.0.0.0) 31771877  31750108  31719903
 JUL2020  (19.8.0.0.0) 31281355  31305339  31247621
 APR2020 (19.7.0.0.0) 30869156  30899722  30901317
 JAN2020 (19.6.0.0.0) 30557433  30501910  30445947
 OCT2019 (19.5.0.0.0) 30125133  30116789  30151705
 JUL2019 (19.4.0.0.0) 29834717  29708769   NA
 APR2019 (19.3.0.0.0) 29517242  29517302   NA

 

 

 

19.0.0.0
 Description  OJVM Update  OJVM + DB Update  OJVM + GI Update
 APR2025 (19.27.0.0.250415)  37499406  37591483  37591516
 JAN2025 (19.26.0.0.250121)  37102264  37262172  37262208
 OCT2024 (19.25.0.0.241015)  36878697  36866623  36866740
 JUL2024 (19.24.0.0.240716)  36414915  36522340  36522439
 APR2024 (19.23.0.0.240416)  36199232  36209492  36209493
 JAN2024 (19.22.0.0.240116)  35926646  36031426  36031453
 OCT2023 (19.21.0.0.231017)  35648110  35742413  35742441
 JUL2023 (19.20.0.0.230718)  35354406  35370174  35370167
 APR2023 (19.19.0.0.230418)  35050341  35058163  35058172
 JAN2023 (19.18.0.0.230117)  34786990  34773489  34773504
 OCT2022 (19.17.0.0.221018)  34411846  34449114  34449117
 JUL2022 (19.16.0.0.220719)  34086870  34160831  34160854
 APR2022 (19.15.0.0.220419)  33808367  33859194  33859214
 JAN2022 (19.14.0.0.220118)  33561310  33567270  33567274
 OCT2021 (19.13.0.0.211019)  33192694  33248420  33248471
 JUL2021 (19.12.0.0.210720)  32876380  32900021  32900083
 APR2021 (19.11.0.0.210420)  32399816  32578972  32578973
 JAN2021 (19.10.0.0.210119)  32067171  32126828  32126842
 OCT2020 (19.9.0.0.201020)  31668882  31720396  31720429
 JUL2020 (19.8.0.0.200714)  31219897  31326362  31326369
 APR2020 (19.7.0.0.200414)  30805684  30783543  30783556
 JAN2020 (19.6.0.0.200114)  30484981  30463595  30463609
 OCT2019 (19.5.0.0.191015)  30128191  30133124  30133178
 JUL2019 (19.4.0.0.190716)  29774421  29699079  29699097
 APR2019 (19.3.0.0.190416)  29548437  29621253  29621299

参考:Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)

Oracle Recovery Tools修复ORA-600 6101/kdxlin:psno out of range故障

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

标题：Oracle Recovery Tools修复ORA-600 6101/kdxlin:psno out of range故障

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

数据库异常断电,然后启动异常,我接手该库,尝试recover恢复

SQL> recover database;
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
63710)
ORA-10564: tablespace SYSAUX
ORA-01110: ???????? 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 179030
ORA-00600: ????????????, ????: [6101], [0], [700], [32], [], [], [], [], [],
[], [], []

SQL> recover datafile 1;
完成介质恢复。
SQL> recover datafile 3;
完成介质恢复。
SQL> recover datafile 4;
完成介质恢复。
SQL> recover datafile 5;
完成介质恢复。
SQL> recover datafile 6;
完成介质恢复。
SQL> recover datafile 2;
ORA-00283: ??????????
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
63744)
ORA-10564: tablespace SYSAUX
ORA-01110: ???? 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 67482
ORA-00600: ??????, ??: [kdxlin:psno out of range], [], [], [], [], [], [], [],
[], [], [], []

alert日志信息

Mon Apr 14 12:31:55 2025
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 20 slaves
Mon Apr 14 12:31:56 2025
Recovery of Online Redo Log: Thread 1 Group 2 Seq 6845 Reading mem 0
  Mem# 0: H:\TEMP\GDLISNET\REDO02.LOG
Mon Apr 14 12:31:56 2025
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr0e_9840.trc  (incident=1489):
ORA-00600: 内部错误代码, 参数: [6101], [0], [700], [32], [], [], [], [], [], [], [], []
Incident details in: c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\incident\incdir_1489\gdlisnet_pr0e_9840_i1489.trc
Slave exiting with ORA-10562 exception
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr0e_9840.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 63710)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 179030
ORA-00600: 内部错误代码, 参数: [6101], [0], [700], [32], [], [], [], [], [], [], [], []
Mon Apr 14 12:31:57 2025
Checker run found 6 new persistent data failures
Recovery Slave PR0E previously exited with exception 10562
Media Recovery failed with error 448
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr00_14232.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00448: 后台进程正常结束
Slave exiting with ORA-283 exception
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr00_14232.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00448: 后台进程正常结束
ORA-10562 signalled during: ALTER DATABASE RECOVER  database  ...

ALTER DATABASE RECOVER  datafile 2  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 6845 Reading mem 0
  Mem# 0: H:\TEMP\GDLISNET\REDO02.LOG
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_ora_22400.trc  (incident=1369):
ORA-00600: ??????, ??: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\incident\incdir_1369\gdlisnet_ora_22400_i1369.trc
Media Recovery failed with error 10562
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 2  ...

发现2号数据文件异常,报ORA-600 6101和ORA-600 kdxlin:psno out of range错误,出现该错误的原因主要是由于数据文件的bock和redo中信息不匹配导致,对于这种情况,我这边选择使用Oracle Recovery Tools工具进行快速恢复
QQ20250414-123421
再次recover并且直接打开库

SQL> set numw 16
SQL> col CHECKPOINT_TIME for a40
SQL> set lines 150
SQL> set pages 1000
SQL> SELECT status,
  2  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
  3  count(*) ROW_NUM
  4  FROM v$datafile_header
  5  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
  6  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
ONLINE  2025-03-28 22:00:09                      NO           118575875                6

SQL>
SQL>
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 2 需要介质恢复
ORA-01110: 数据文件 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'


SQL> recover datafile 2;
完成介质恢复。
SQL> alter database open;

数据库已更改。

SQL>

逻辑方法导出该库,完成该库的恢复工作

pdu完美支持金仓数据库恢复(KingbaseES)

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

标题：pdu完美支持金仓数据库恢复(KingbaseES)

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

最近朋友对pdu进行了一些完善,让其可以比较好的支持电科金仓数据库（KingbaseES）的v8/v9版本
使用pdu直接unload数据

[kingbase@xifenfei ~]$ isql -Usystem test
Password for user system: 
isql (V9.0)
Licesen Type: SALES-企业版.
Type "help" for help.

test=# select version();
         version         
-------------------------
 KingbaseES V009R002C010
(1 row)

test=# \dt
        List of relations
 Schema | Name  | Type  | Owner  
--------+-------+-------+--------
 public | t_xff | table | system
(1 row)

test=# drop table walminer_contents;
DROP TABLE
test=# select * from t_xff;
 id |       name       
----+------------------
  1 | 汉字
  2 | xff测试
  3 | 惜分飞测试
  4 | www.xifenfei.com
(4 rows)

test=# insert into t_xff values(5,'www.xifenfie.com
test'# www.orasos.com');
INSERT 0 1
test=# select * from t_xff;
 id |       name       
----+------------------
  1 | 汉字
  2 | xff测试
  3 | 惜分飞测试
  4 | www.xifenfei.com
  5 | www.xifenfie.com+
    | www.orasos.com
(5 rows)

test=# checkpoint;
CHECKPOINT

pdu直接unload恢复

test.public=# b; 
开始初始化...
 -pg_database:</data/kingbase/v9/data/global/1262>

数据库:test 
      -pg_schema:</data/kingbase/v9/data/base/13856/2615>
      -pg_class:</data/kingbase/v9/data/base/13856/1259> 共132行
      -pg_attribute:</data/kingbase/v9/data/base/13856/1249> 共8863行
      模式:
        -->public 1张表

数据库:kingbase 
      -pg_schema:</data/kingbase/v9/data/base/13857/2615>
      -pg_class:</data/kingbase/v9/data/base/13857/1259> 共131行
      -pg_attribute:</data/kingbase/v9/data/base/13857/1249> 共8855行
      模式:
        -->public 0张表
PDU.public=# use test;    
|----------------------------------------|
|          模式             |  表数量    |
|----------------------------------------|
|    public                 |  1         |
|----------------------------------------|
test.public=# set public;
|--------------------------------------------------|
|               表名                  |  表大小    |
|--------------------------------------------------|
|    t_xff                            |  8.00 KB   |
|--------------------------------------------------|

        仅显示表大小排名前 1 的表名
test.public=# param exmode sql;
-------------------------------------------------------------------
|              参数                |             当前值           |
-------------------------------------------------------------------
|    startwal                      |                              |
|    endwal                        |                              |
|    starttime                     |                              |
|    endtime                       |                              |
|    resmode(Data Restore Mode)    |              TX              |
|    exmode(Data Export Mode)      |              SQL             |
------------------------------------------------------------------
test.public=# unload tab t_xff;
正在解析表 <t_xff>. 已解析数据页: 1, 已解析数据: 5 条
表名<t_xff>-</data/kingbase/v9/data/base/13856/16384> 解析完成, 1 个数据页 ,共计 5 条数据. 成功 5 条; 失败 0 条 
COPY文件路径为:<test/public/t_xff.sql>

test.public=# unload ddl;

DDL导出完成. 文件路径: test/DDL/public_ddl.sql, 共计 1 张表

查看恢复结果,证明可以完美恢复金仓中表的数据

[root@xifenfei pdu]# cat test/public/t_xff.sql
INSERT INTO t_xff VALUES(1,'汉字');
INSERT INTO t_xff VALUES(2,'xff测试');
INSERT INTO t_xff VALUES(3,'惜分飞测试');
INSERT INTO t_xff VALUES(4,'www.xifenfei.com');
INSERT INTO t_xff VALUES(5,'www.xifenfie.com
www.orasos.com');
[root@xifenfei pdu]# cat test/DDL/public_ddl.sql
CREATE SCHEMA public;
set search_path to public;
CREATE TABLE t_xff(
        id int,
        name varchar
);

pdu恢复金仓数据库delete数据
删除数据尝试恢复

test=# delete from t_xff where id in(4,2);
DELETE 2
test=# checkpoint;
CHECKPOINT
test=# select * from t_xff;
 id |       name       
----+------------------
  1 | 汉字
  3 | 惜分飞测试
  5 | www.xifenfie.com+
    | www.orasos.com
(3 rows)

使用pdu恢复被删除数据

PDU.public=# use test;
|----------------------------------------|
|          模式             |  表数量    |
|----------------------------------------|
|    public                 |  1         |
|----------------------------------------|
test.public=# set public;
|--------------------------------------------------|
|               表名                  |  表大小    |
|--------------------------------------------------|
|    t_xff                            |  8.00 KB   |
|--------------------------------------------------|

        仅显示表大小排名前 1 的表名
test.public=# scan t_xff;

正在扫描表<t_xff>的删除记录...

开始扫描归档目录
|-起始文件<000000010000000000000005>
|-终点文件<000000010000000000000009>

正在扫描中

-------------------------------------------------------------------
当前为 事务号恢复 模式,扫描结果将以 事务号 为单位进行输出
---------------------------------------------------------------------

读取到最后的日志段： 0/504EC68: 


扫描结束,当前扫描的时间段为
|-开始时间：2025-04-06 19:13:15.280234 CST
|-结束时间：2025-04-08 01:45:59.493354 CST

---------------------------------------------------------------------------------------------------------
 时间戳：2025-04-08 01:45:59.493354 CST | 事务号：1120 | 数据文件oid：16384 | toast文件oid：0 |待恢复条数：2 
---------------------------------------------------------------------------------------------------------
test.public=# restore del 1120;
开始扫描归档目录
|-起始文件<000000010000000000000005>
|-终点文件<000000010000000000000009>

正在扫描中
读取到最后的日志段： 0/504EC68: 
|-已解析出2条数据读取到最后的日志段： 0/504EC68: 


━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
解析结果：成功 2, 失败 0 (即对应FPW未找到), 共计2条数据
恢复出的csv文件路径为<restore/public/t_xff_1120.csv>
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

扫描结束,当前扫描的时间段为
|-开始时间：2025-04-06 19:13:15.280234 CST
|-结束时间：2025-04-08 01:45:59.493354 CST

restore完成
test.public=# exit;
[root@xifenfei pdu]# cat restore/public/t_xff_1120.csv
2       xff测试
4       www.xifenfei.com

通过上述测试,证明pdu工具可以完美的恢复金仓中刚刚被删除的delete数据.

虚拟机故障引起ORA-00310 ORA-00334故障处理

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

标题：虚拟机故障引起ORA-00310 ORA-00334故障处理

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

有客户由于硬件底层问题,导致运行在虚拟机环境中的oracle数据库突然爆大量错误

Reread (file 5, block 2371528) found same corrupt data (no logical check)
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_j000_10927.trc  (incident=397049):
ORA-01578: ORACLE data block corrupted (file # 5, block # 2371528)
ORA-01110: data file 5: '/home/oracle/app/oradata/users01.dbf'

Wed Apr 02 23:10:24 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_j000_10927.trc  (incident=397050):
ORA-00600: internal error code, arguments: [5400], [], [], [], [], [], [], [], [], [], [], []

Wed Apr 02 23:15:29 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11605.trc  (incident=397075):
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []

Wed Apr 02 23:20:32 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11530.trc  (incident=397034):
ORA-00600: internal error code, arguments: [25027], [6], [196610], [], [], [], [], [], [], [], [], []

Wed Apr 02 23:20:52 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11528.trc  (incident=397027):
ORA-00600: internal error code, arguments: [ktspfpblk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []

Wed Apr 02 23:22:53 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11609.trc  (incident=397082):
ORA-00600: internal error code, arguments: [6002], [6], [189], [1], [0], [], [], [], [], [], [], []

Wed Apr 02 23:26:41 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_m000_11966.trc  (incident=397035):
ORA-00600: internal error code, arguments: [dbgrmblur_update_range_1], [11], [6], [], [], [], [], [], [], [], [], []

Wed Apr 02 23:31:47 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_j000_10927.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_49685"
ORA-08102: index key not found, obj# 39, file 1, block 55190 (2)

Thu Apr 03 00:15:18 2025
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8] [PC:0xB9EC41, ksuloget()+421] [flags: 0x0, count: 1]
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_m000_12633.trc  (incident=400879):
ORA-07445: exception encountered:core dump [ksuloget()+421][SIGSEGV][ADDR:0x8][PC:0xB9EC41][Address not mapped to object]

Thu Apr 03 00:15:23 2025
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_pmon_4097.trc  (incident=396817):
ORA-00600: internal error code, arguments: [1100], [0x2E3947E78], [0x2E3947E78], [], [], [], [], [], [], [], [], []

数据库crash掉之后,处理好硬件环境和虚拟机启动之后,数据库直接启动失败,报ORA-01172 ORA-01151

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 29239 KB redo, 4020 data blocks need recovery
Started redo application at
 Thread 1: logseq 211603, block 9107
Recovery of Online Redo Log: Thread 1 Group 4 Seq 211603 Reading mem 0
  Mem# 0: /home/oracle/app/oradata/orcl/redo04.log
  Mem# 1: /home/oracle/app/oradata/orcl/redo041.log
Hex dump of (file 2, block 4835) in trace file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_19174.trc
Reading datafile '/home/oracle/app/oradata/orcl/sysaux01.dbf' for corruption at rdba: 0x008012e3 (file 2, block 4835)
Reread (file 2, block 4835) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 4835 OF FILE 2
Aborting crash recovery due to error 1172
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_19174.trc:
ORA-01172: recovery of thread 1 stuck at block 4835 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_19174.trc:
ORA-01172: recovery of thread 1 stuck at block 4835 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...

然后再次尝试重启提示ORA-01113 ORA-01110

Fri Apr 04 09:34:36 2025
ALTER DATABASE OPEN
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4076.trc:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/home/oracle/app/oradata/users01.dbf'
ORA-1113 signalled during: ALTER DATABASE OPEN...

可以自行尝试了各种恢复,比如using backup controlfile,until cancel,rectl等操作,数据库均为open成功,基本上都是卡在类似如下报ORA-00310 ORA-00334错

Sat Apr 05 10:17:34 2025
ALTER DATABASE RECOVER  database using backup controlfile   
Media Recovery Start
 started logmerger process
Sat Apr 05 10:17:34 2025
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 9 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 10 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 11 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 12 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 13 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 14 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 15 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
Parallel Media Recovery started with 28 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile   ...
Sat Apr 05 10:17:59 2025
ALTER DATABASE RECOVER    LOGFILE '/home/oradata/redo02.log'  
Media Recovery Log /home/oradata/redo02.log
Sat Apr 05 10:17:59 2025
Errors with log /home/oradata/redo02.log
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_pr00_12141.trc:
ORA-00310: archived log contains sequence 211550; sequence 211603 required
ORA-00334: archived log: '/home/oradata/redo02.log'
ORA-310 signalled during: ALTER DATABASE RECOVER    LOGFILE '/home/oradata/redo02.log'  ...
ALTER DATABASE RECOVER CANCEL 
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL

基于上述情况,数据库由于底层异常,导致所需要的redo和实际存在的redo文件内容不匹配,只能屏蔽一致性强制打开库

SQL> alter database open resetlogs ;
alter database open resetlogs 
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [1685409503], [0], [1685415469], [12583040], []
ORA-00600: internal error code, arguments: [2662], [0], [1685409502], [0], [1685415469], [12583040], []
ORA-01092: ORACLE instance terminated. Disconnection force
ORA-00600: internal error code, arguments: [2662], [0], [1685409498], [0], [1685415469], [12583040], []
Process ID: 10637
Session ID: 645 Serial number: 7

ORA-600 2662这个错误比较常见,通过修改数据库scn,进行规避然后尝试打开库

Sat Apr 05 10:31:45 2025
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1685409495
Resetting resetlogs activation ID 1725417463 (0x66d7c7f7)
Sat Apr 05 10:31:46 2025
Setting recovery target incarnation to 2
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 1685409498, threshold SCN value is 0
Sat Apr 05 10:31:46 2025
Assigning activation ID 1725412798 (0x66d7b5be)
Thread 1 opened at log sequence 1
  Current log# 2 seq# 1 mem# 0: /home/oradata/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Apr 05 10:31:46 2025
SMON: enabling cache recovery
Undo initialization finished serial:0 start:61632504 end:61632514 diff:10 (0 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
SMON: enabling tx recovery
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc  (incident=8145):
ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8145/oorcl_smon_22927_i8145.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Stopping background process MMNL
ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (9, 1).
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc:
ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], []
Sat Apr 05 10:31:46 2025
Sweep [inc][8145]: completed
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc  (incident=8146):
ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8146/oorcl_smon_22927_i8146.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Apr 05 10:31:46 2025
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_p054_2643.trc  (incident=8625):
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8625/oorcl_p054_2643_i8625.trc
Sat Apr 05 10:31:46 2025
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_p034_2603.trc  (incident=8465):
ORA-00600: internal error code, arguments: [kturbleurec1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8465/oorcl_p034_2603_i8465.trc
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Sat Apr 05 10:31:48 2025
Starting background process CJQ0
Sat Apr 05 10:31:48 2025
CJQ0 started with pid=80, OS id=2852 
SMON: Restarting fast_start parallel rollback
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc  (incident=8147):
ORA-00600: internal error code, arguments: [4137], [9.1.436887], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8147/oorcl_smon_22927_i8147.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Apr 05 10:31:50 2025
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_p000_2535.trc  (incident=8169):
ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8169/oorcl_p000_2535_i8169.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (9, 1).
Block recovery from logseq 1, block 19 to scn 2147483682
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem 0
  Mem# 0: /home/oradata/redo02.log
Block recovery completed at rba 1.734.16, scn 0.2147483683
Block recovery from logseq 1, block 404 to scn 2147483682
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem 0
  Mem# 0: /home/oradata/redo02.log
Block recovery completed at rba 1.734.16, scn 0.2147483683
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc  (incident=8148):
ORA-00600: internal error code, arguments: [4198], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8148/oorcl_smon_22927_i8148.trc
Sat Apr 05 10:31:50 2025
Sweep [inc][8147]: completed
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc  (incident=8149):
ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8149/oorcl_smon_22927_i8149.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (10, 28).
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc:
ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], []
Sat Apr 05 10:31:50 2025
Sweep [inc][8149]: completed
Checker run found 1 new persistent data failures
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc  (incident=8150):
ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oorcl/oorcl/incident/incdir_8150/oorcl_smon_22927_i8150.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (10, 28).
Errors in file /u01/app/oracle/diag/rdbms/oorcl/oorcl/trace/oorcl_smon_22927.trc  (incident=8151):
ORA-00600: internal error code, arguments: [4137], [10.28.1201778], [0], [0], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Apr 05 10:31:51 2025
Sweep [inc][8150]: completed
ORACLE Instance oorcl (pid = 17) - Error 600 encountered while recovering transaction (10, 28).

虽然数据库open成功，但是有ORA-600 4137/ORA-600 kturbleurec1/ORA-600 4198等错误,但是这里比较明显的undo有问题,对于异常undo进行处理,然后逻辑导出数据,导入新库完成本次恢复任务

pg创建gbk字符集库

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

标题：pg创建gbk字符集库

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

记录下，Postgres库创建中文字符集

postgres=# CREATE DATABASE mydb_gbk
postgres-#     ENCODING 'EUC_CN'
postgres-#     LC_COLLATE 'zh_CN' 
postgres-#     LC_CTYPE 'zh_CN'
postgres-#     TEMPLATE template0;
CREATE DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 mydb_gbk  | postgres | EUC_CN   | zh_CN       | zh_CN       |            | libc            | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(4 rows)

前提系统必须支持zh_CN语言包,检查命令为:

[root@xifenfei yum.repos.d]# locale -a |grep zh_CN
zh_CN
zh_CN.gb18030
zh_CN.gbk
zh_CN.utf8

如果没有使用yum以下命令安装

yum groupinstall "fonts"
yum install glibc-langpack-zh.x86_64

PostgreSQL运行日志管理

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

标题：PostgreSQL运行日志管理

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

PostgreSQL目前配置中,不直接记录日志文件,这样的情况给数据库后期出现问题(特别是无法正常启动的情况)分析带来很大麻烦,不知道具体问题所在,建议在PG安装完成之后,启用日志功能,便于数据库运行状态检查和错误跟踪,主要日志参数涉及以下配置

log_destination = 'stderr'              # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)
//是否将日志重定向至文件中，默认是off。

# These are only used if logging_collector is on:
log_directory = 'pg_log'                   # directory where log files are written,
                                        # can be absolute or relative to PGDATA
//日志文件目录，默认是PGDATA的相对路径，即PGDATA的相对路径，即{PGDATA}/pg_log，也可以改为绝对路径。
//日志文件可能会非常多，建议将日志重定向到其他目录或分区。
//将此配置修改其他目录时，必须先创建此目录，并修改权限，使得postgres用户对该目录有写权限。

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600                    # creation mode for log files,
                                        # begin with 0 to use octal notation
log_rotation_age = 1d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 10MB                # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
//当日志文件已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志。

上述配置得到的结果如下

[postgres@xifenfei pg_log]$ pwd
/data/pg/15/data/pg_log
[postgres@xifenfei pg_log]$ ls -l
total 4
-rw------- 1 postgres postgres 1263 Apr  3 22:37 postgresql-2025-04-03_223117.log

pglog