分类目录归档：数据库

ORA-600 kcratr_nab_less_than_odr和ORA-600 4194故障处理

发表于 2025 年 8 月 8 日 由 惜分飞

客户一个11.2.0.1的库由于断电导致启动的时候报ORA-600 kcratr_nab_less_than_odr错误
ORA-600-kcratr_nab_less_than_odr

Wed Aug 06 15:55:07 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 27 processes
Started redo scan
Completed redo scan
 read 11787 KB redo, 4129 data blocks need recovery
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_3520.trc  (incident=2624321):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [111988], [42537], [43285], [], [], [], [], [], [], []
Incident details in: d:\app\xff\diag\rdbms\primary\orcl\incident\incdir_2624321\orcl_ora_3520_i2624321.trc
Aborting crash recovery due to error 600
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_3520.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [111988], [42537], [43285], [], [], [], [], [], [], []
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_3520.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [111988], [42537], [43285], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...

类似的ORA-600 kcratr_nab_less_than_odr故障处理不少,但是应用redo完成之后,再open报错的不多:
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故障
11.2.0.4库中遇到ORA-600 kcratr_nab_less_than_odr报错
但是这个客户比较不幸,经过一系列尝试打开数据库,但是报ORA-600 4194错误

Wed Aug 06 17:09:33 2025
ALTER DATABASE RECOVER  database until cancel using backup controlfile  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 28 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel using backup controlfile  ...
Wed Aug 06 17:10:17 2025
ALTER DATABASE RECOVER    LOGFILE 'D:\app\xff\oradata\orcl\REDO05.LOG'  
Media Recovery Log D:\app\xff\oradata\orcl\REDO05.LOG
Wed Aug 06 17:10:17 2025
Incomplete recovery applied all redo ever generated.
Recovery completed through change 2118813100 time 08/06/2025 15:02:15
Completed: ALTER DATABASE RECOVER    LOGFILE 'D:\app\xff\oradata\orcl\REDO05.LOG'  
Wed Aug 06 17:14:05 2025
alter database open resetlogs
Archived Log entry 188143 added for thread 1 sequence 111985 ID 0x634438e6 dest 1:
Archived Log entry 188144 added for thread 1 sequence 111986 ID 0x634438e6 dest 1:
Archived Log entry 188145 added for thread 1 sequence 111987 ID 0x634438e6 dest 1:
Archived Log entry 188146 added for thread 1 sequence 111984 ID 0x634438e6 dest 1:
Archived Log entry 188147 added for thread 1 sequence 111988 ID 0x634438e6 dest 1:
RESETLOGS after complete recovery through change 2118813100
Resetting resetlogs activation ID 1665415398 (0x634438e6)
Wed Aug 06 17:15:03 2025
Archived Log entry 188148 added for thread 1 sequence 1 ID 0x677cfac1 dest 1:
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
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
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_smon_2428.trc  (incident=3264274):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\xff\diag\rdbms\primary\orcl\incident\incdir_3264274\orcl_smon_2428_i3264274.trc
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Aug 06 17:15:06 2025
QMNC started with pid=34, OS id=3052 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs

后续由于该undo异常报ORA-01595和ORA-600 4194等错误导致数据库crash

Wed Aug 06 17:15:49 2025
Doing block recovery for file 3 block 888
Resuming block recovery (PMON) for file 3 block 888
Block recovery from logseq 2, block 316 to scn 2118813732
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\APP\xff\ORADATA\ORCL\REDO02.LOG
Block recovery stopped at EOT rba 2.447.16
Block recovery completed at rba 2.447.16, scn 0.2118813732
Doing block recovery for file 3 block 1960
Resuming block recovery (PMON) for file 3 block 1960
Block recovery from logseq 2, block 316 to scn 2118813622
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\APP\xff\ORADATA\ORCL\REDO02.LOG
Block recovery completed at rba 2.317.16, scn 0.2118813626
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_smon_2428.trc:
ORA-01595: error freeing extent (2) of rollback segment (27))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

接手这个库之后,处理工作相对比较简单,直接对异常undo进行重建即可

Thu Aug 07 09:35:08 2025
create undo tablespace undotbs2 datafile 'D:\APP\xff\ORADATA\ORCL\undotbs02.dbf' size 128M autoextend on
Completed: create undo tablespace undotbs2 datafile 'D:\APP\xff\ORADATA\ORCL\undotbs02.dbf' size 128M autoextend on
Thu Aug 07 09:37:53 2025
drop tablespace UNDOTBS1 including contents and datafiles
Deleted file D:\APP\xff\ORADATA\ORCL\UNDOTBS01.DBF
Completed: drop tablespace UNDOTBS1 including contents and datafiles

然后数据库报大量的ORA-600 kdsgrp1错误

Thu Aug 07 10:03:53 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_6156.trc  (incident=5121829):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:00 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_4564.trc  (incident=5121501):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:06 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_9160.trc  (incident=5121837):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:16 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_5868.trc  (incident=5121821):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Thu Aug 07 10:04:28 2025
Errors in file d:\app\xff\diag\rdbms\primary\orcl\trace\orcl_ora_4448.trc  (incident=5122077):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []

* kdsgrp1-1: *************************************************
            row 0x013a9ae9.0 continuation at
            0x013a9ae9.0 file# 4 block# 3840745 slot 0 not found
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 0 ..... nrows: 2
kdsgrp - dump CR block dba=0x013a9ae9
Block header dump:  0x013a9ae9
 Object id on Block? Y
 seg/obj: 0x31dd9  csc: 0x00.7dc0f246  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x13a9082 ver: 0x01 opc: 0
     inc: 2  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

通过分析是由于index和表记录不匹配导致,对index进行重新rebuild完成本次修复任务.

发表在 Oracle备份恢复 | 标签为 , , , , | 评论关闭

pg_wal中文件的名称中的logseq和实际文件中的logseq不匹配

发表于 2025 年 8 月 6 日 由 惜分飞

一个朋友由于某种原因给我发了一个pg_wal里面的wal文件,让我帮忙分析下故障原因,我打开文件之后发现文件编号小的修改时间比编号大的文件修改时间要新
pg_wal

wal日志文件命名规则：
我们看到的wal日志是这样的：0000000100004D6E000000CF
其中前8位:00000001表示timeline
中间8位：00004D6E表示logid
最后8位：000000CF表示logseg
在上述截图中,可以看到logseq为CF的文件比D0的要新很多,这个初步给人感觉不太正常.使用pg_waldump分别对其进行dump操作
pg_waldump1
[postgres@xifenfei bin]$ pg_waldump /data/wal/0000000100004D6E000000D0|head -10
pg_waldump: fatal: could not find a valid record after 4D6E/D0000000

这样可以看出来CF的wal文件可以正常dump出来,但是D0的文件dump报pg_waldump: fatal: could not find a valid record after类似异常.通过od命令分别对两个文件进行分析

[postgres@xifenfei bin]$ od -x /data/wal/0000000100004D6E000000CF|head -1
0000000 d101 0006 0001 0000 0000 cf00 4d6e 0000
[postgres@xifenfei bin]$ od -x /data/wal/0000000100004D6E000000D0|head -1
0000000 d101 0007 0001 0000 0000 9400 4d6e 0000

从第8个字节到第12个字节结束为logseq的值,这里明显可以看出来D0文件的logseq值和实际文件中的不一致.尝试把D0修改为94之后即可正常的pg_waldump进行分析
pg_waldump2

通过这里可以的出来一个结论,pg_wal中文件的名称中的logseq和实际文件中的logseq不匹配.出现这种问题的本质是由于pg_wal中的wal日志是相当oracle的redo,是通过类似重命名机制(看到有文档介绍是说硬链接指向旧文件然后删除旧文件)引起的问题.

发表在 PostgreSQL | 标签为 , , | 评论关闭

由于空间满导致PostgreSQL数据库异常处理

发表于 2025 年 7 月 29 日 由 惜分飞

朋友和我反馈pg库异常,通过查看日志确认最初故障是由于磁盘空间满，导出出现类似:无法扩展文件 “pg_tblspc/16394/PG_13_202007201/16395/5055912.143″: No space left on device错误

2025-07-28 09:06:02.703 HKT [15352] 错误:  无法扩展文件 "pg_tblspc/16394/PG_13_202007201/16395/5055912.143": No space left on device
2025-07-28 09:06:02.703 HKT [15352] 提示:  检查空闲磁盘控件.
2025-07-28 09:06:02.703 HKT [15352] 语句:  insert into 语句
2025-07-28 09:06:02.703 HKT [576] 错误:  无法扩展文件 "pg_tblspc/16394/PG_13_202007201/16395/4477723.73": No space left on device
2025-07-28 09:06:02.703 HKT [576] 提示:  检查空闲磁盘控件.
2025-07-28 09:06:02.703 HKT [576] 语句:  update 语句
2025-07-28 09:06:02.706 HKT [11940] 错误:  无法扩展文件 "pg_tblspc/16394/PG_13_202007201/16395/5055912.143": No space left on device
2025-07-28 09:06:02.706 HKT [11940] 提示:  检查空闲磁盘控件.
2025-07-28 09:06:02.706 HKT [11940] 语句:  insert into 语句

pg-nofree

后续对d盘空间进行了清理,pg报无法打开文件”pg_tblspc/16394/PG_13_202007201/16395/4477706.16″(目标数据块5544906): Permission denied

2025-07-28 10:53:10.435 HKT [11920] 语句:  insert into语句
2025-07-28 10:53:10.435 HKT [18300] 错误:  无法打开文件"pg_tblspc/16394/PG_13_202007201/16395/4477706.16"(目标数据块5544906): Permission denied

和报:警告: 无法写入pg_tblspc/16394/PG_13_202007201/16395/4477706的块5545387错误

2025-07-28 11:05:11.008 HKT [5380] 警告:  无法写入pg_tblspc/16394/PG_13_202007201/16395/4477706的块5545387
2025-07-28 11:05:11.008 HKT [5380] 详细信息:  多次失败 --- 写错误可能是永久性的
2025-07-28 11:05:11.015 HKT [12072] 错误:  无法打开文件"pg_tblspc/16394/PG_13_202007201/16395/4477706.17"（目标块5545398）：上一段只有952个块
2025-07-28 11:05:11.015 HKT [12072] 上下文:  写入关系pg_tblspc/16394/PG_13_202007201/16395/4477706的块5545398
2025-07-28 11:05:11.015 HKT [12072] 语句:  select 语句
2025-07-28 11:05:11.016 HKT [12072] 警告:  无法写入pg_tblspc/16394/PG_13_202007201/16395/4477706的块5545398
2025-07-28 11:05:11.016 HKT [12072] 详细信息:  多次失败 --- 写错误可能是永久性的

查看4477706相关文件情况,初步看部分文件大小不对,结合上述报错,可以断定该表异常
pgsize

通过查询pg字典确认该表具体名称

xifenfei=# select oid,relname,relnamespace from pg_class where  relfilenode=4477706;
  oid  |     relname      | relnamespace
-------+------------------+--------------
 16880 | xifenfei01_log   |         2200
(1 行记录)

尝试pg_dump导入数据报错：多次失败 — 写错误可能是永久性的

这次运气比较好,损坏的对象是一个日志表,可以直接清理掉,对该日志表进行清理,然后正常导出数据,如果遇到的损坏表是业务需要的表,可以使用pdu(PostgreSQL表文件损坏恢复—pdu恢复损坏的表文件)进行恢复

发表在 PostgreSQL恢复 | 标签为 , , | 评论关闭