ORA-600 ktbsdp2 处理

发表于 2022 年 4 月 5 日 由 惜分飞

客户反馈数据库异常:两个节点rac,两个节点都启动,其中一个节点无法正常open,另外一个节点一段时间后也会挂。以下是无法正常open节点报错信息(正常open节点最终挂掉报错信息也是类似)

2022-03-30T19:19:12.870813+08:00
[84321] Successfully onlined Undo Tablespace 4.
Undo initialization finished serial:0 start:1728252021 end:1728256302 diff:4281 ms (4.3 seconds)
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
2022-03-30T19:19:13.953252+08:00
Database Characterset is ZHS16GBK
2022-03-30T19:19:14.538155+08:00
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_p02o_85718.trc  (incident=1093927):
ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/xff/xff2/incident/incdir_1093927/xff2_p02o_85718_i1093927.trc
2022-03-30T19:19:15.536582+08:00
ORACLE Instance xff2 (pid = 57) - Error 607 encountered while recovering transaction (73, 12) on object 112841.
2022-03-30T19:19:33.699944+08:00
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
2022-03-30T19:19:34.673840+08:00
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
2022-03-30T19:19:34.673954+08:00
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc  (incident=1092704):
ORA-607 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: /oracle/app/oracle/diag/rdbms/xff/xff2/incident/incdir_1092704/xff2_smon_84007_i1092704.trc
2022-03-30T19:19:35.422779+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.
*****************************************************************
2022-03-30T19:19:36.154689+08:00
Starting background process GTX0
2022-03-30T19:19:36.169007+08:00
GTX0 started with pid=370, OS id=87409 
2022-03-30T19:19:36.645876+08:00
USER (ospid: 84007): terminating the instance due to error 607
2022-03-30T19:19:36.680109+08:00
opiodr aborting process unknown ospid (87439) as a result of ORA-1092
2022-03-30T19:19:36.681091+08:00
ORA-1092 : opitsk aborting process
2022-03-30T19:19:36.740357+08:00
System state dump requested by (instance=2, osid=84007 (SMON)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_diag_83895_20220330191936.trc
2022-03-30T19:19:40.135579+08:00
Instance terminated by USER, pid = 84007

对于上述报错信息分析,初步判断是由于事务异常导致,查询mos发现类似报错Bug 32208691 – After upgrade from 12.1 to 19.3 drop columns fails ORA-600[ktbsdp2] ORA-600[4512] (Doc ID 32208691.8),通过咨询客户,确认他们这边是通过plsql dev工具对id为112841表进行增加列的时候网络中断导致增加失败,后续我尝试对该表进行查询发现也报该错误,基本上可以确认由于该表事务异常导致,通过dul把该表数据恢复,然后drop 该表,数据库启动正常,未见其他报错,通过hcheck检查,数据库字典基本一致(除一些统计信息异常,原则上不影响数据库运行)

[oracle@xifenfei2 ~]$ sqlplus / as sysdba @hcheck.sql

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 31 00:38:32 2022

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

HCheck Version 07MAY18 on 31-MAR-2022 00:38:34
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name: xff
Is CDB?: NO

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj                 ... 1202000100 <=  *All Rel* 03/31 00:38:34 PASS
.- MissingOIDOnObjCol          ... 1202000100 <=  *All Rel* 03/31 00:38:34 PASS
.- SourceNotInObj              ... 1202000100 <=  *All Rel* 03/31 00:38:34 PASS
.- OversizedFiles              ... 1202000100 <=  *All Rel* 03/31 00:38:38 PASS
.- PoorDefaultStorage          ... 1202000100 <=  *All Rel* 03/31 00:38:38 PASS
.- PoorStorage                 ... 1202000100 <=  *All Rel* 03/31 00:38:38 PASS
.- TabPartCountMismatch        ... 1202000100 <=  *All Rel* 03/31 00:38:38 PASS
.- OrphanedTabComPart          ... 1202000100 <=  *All Rel* 03/31 00:38:38 PASS
.- MissingSum$                 ... 1202000100 <=  *All Rel* 03/31 00:38:38 PASS
.- MissingDir$                 ... 1202000100 <=  *All Rel* 03/31 00:38:38 PASS
.- DuplicateDataobj            ... 1202000100 <=  *All Rel* 03/31 00:38:40 PASS
.- ObjSynMissing               ... 1202000100 <=  *All Rel* 03/31 00:38:42 PASS
.- ObjSeqMissing               ... 1202000100 <=  *All Rel* 03/31 00:38:42 PASS
.- OrphanedUndo                ... 1202000100 <=  *All Rel* 03/31 00:38:44 PASS
.- OrphanedIndex               ... 1202000100 <=  *All Rel* 03/31 00:38:44 PASS
.- OrphanedIndexPartition      ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- OrphanedIndexSubPartition   ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- OrphanedTable               ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- OrphanedTablePartition      ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- OrphanedTableSubPartition   ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- MissingPartCol              ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- OrphanedSeg$                ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- OrphanedIndPartObj#         ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- DuplicateBlockUse           ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- FetUet                      ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- Uet0Check                   ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- SeglessUET                  ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- BadInd$                     ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- BadTab$                     ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- BadIcolDepCnt               ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- ObjIndDobj                  ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- TrgAfterUpgrade             ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- ObjType0                    ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- BadOwner                    ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- StmtAuditOnCommit           ... 1202000100 <=  *All Rel* 03/31 00:38:45 PASS
.- BadPublicObjects            ... 1202000100 <=  *All Rel* 03/31 00:38:46 PASS
.- BadSegFreelist              ... 1202000100 <=  *All Rel* 03/31 00:38:50 PASS
.- BadDepends                  ... 1202000100 <=  *All Rel* 03/31 00:38:50 PASS
.- CheckDual                   ... 1202000100 <=  *All Rel* 03/31 00:38:57 PASS
.- ObjectNames                 ... 1202000100 <=  *All Rel* 03/31 00:38:57 WARN

HCKW-0018: OBJECT name clashes with SCHEMA name (Doc ID 2363142.1)
Schema=MHWZ PACKAGE=MHWZ.MHWZ
Schema=MHWZ PACKAGE BODY=MHWZ.MHWZ

.- BadCboHiLo                  ... 1202000100 <=  *All Rel* 03/31 00:39:01 WARN

HCKW-0019: HIST_HEAD$.LOWVAL > HIVAL (Doc ID 1361047.1)
OBJ# 324163 INTCOL#=22
OBJ# 482668 INTCOL#=4
OBJ# 442865 INTCOL#=31
OBJ# 436924 INTCOL#=31
OBJ# 580529 INTCOL#=8
OBJ# 459432 INTCOL#=31
OBJ# 451260 INTCOL#=31
OBJ# 530980 INTCOL#=21
OBJ# 498442 INTCOL#=5
OBJ# 652114 INTCOL#=8
OBJ# 701695 INTCOL#=21
OBJ# 831961 INTCOL#=31
OBJ# 831962 INTCOL#=31
OBJ# 831963 INTCOL#=31

.- ChkIotTs                    ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- NoSegmentIndex              ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- BadNextObject               ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- DroppedROTS                 ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- FilBlkZero                  ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- DbmsSchemaCopy              ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- OrphanedIdnseqObj           ... 1202000100 >  1201000000 03/31 00:39:09 PASS
.- OrphanedIdnseqSeq           ... 1202000100 >  1201000000 03/31 00:39:09 PASS
.- OrphanedObjError            ... 1202000100 >  1102000000 03/31 00:39:09 PASS
.- ObjNotLob                   ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- MaxControlfSeq              ... 1202000100 <=  *All Rel* 03/31 00:39:09 PASS
.- SegNotInDeferredStg         ... 1202000100 >  1102000000 03/31 00:39:13 PASS
.- SystemNotRfile1             ... 1202000100 >   902000000 03/31 00:39:13 PASS
.- DictOwnNonDefaultSYSTEM     ... 1202000100 <=  *All Rel* 03/31 00:39:13 PASS
.- OrphanTrigger               ... 1202000100 <=  *All Rel* 03/31 00:39:13 PASS
.- ObjNotTrigger               ... 1202000100 <=  *All Rel* 03/31 00:39:13 PASS
---------------------------------------
31-MAR-2022 00:39:13  Elapsed: 39 secs
---------------------------------------
Found 0 potential problem(s) and 16 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_26887_HCHECK.trc
发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 , | 评论关闭

win文件系统损坏oracle恢复

发表于 2022 年 4 月 4 日 由 惜分飞

有客户反馈数据文件无法拷贝也无法正常操作,通过查看系统日志发现ntfs文件系统异常
20220326210833

在文件系统中看数据文件状态正常,但是拷贝文件报错
20220404201143
通过恢复工具查看文件发现文件大小为0kb
20220326194440
通过文件系统层面扫描,然后进行相关数据恢复,依旧报错(恢复文件大小错误)
20220404201848
经过进一步人工修复文件系统目录,实现数据完整恢复,实现数据0丢失
20220404202616
基于这种情况如果通过文件系统层面无法恢复,对于此类oracle block级别进行处理,类似以前恢复case:
dbca删除库和rm删库恢复
文件系统损坏导致数据文件异常恢复
Oracle 数据文件大小为0kb或者文件丢失恢复

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

asm disk 磁盘部分被清空恢复

发表于 2022 年 3 月 31 日 由 惜分飞

由于未知原因导致磁盘组的一个磁盘前面1G被置空(data磁盘组一共5个1T磁盘,第一个盘未知原因置空了1G数据)

[root@oradb1 ~]#kfed read /dev/mapper/asm-disk1
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F539088C400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
…………
[root@oradb1 ~]#kfed read /dev/mapper/asm-disk1 aun=999
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F8FEB795400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

通过分析disk 1和disk 2的前面1001MB数据,以及asm的数据分布特性,可以确认丢失的1000MB数据为39号文件的数据(和客户当时这个库是通过rman还原过来的操作有关系)
20220330224604
20220330225211

对于这样的情况,通过底层扫描,可以很好的恢复数据,参考以前类似文章
asm disk被加入vg恢复
asm disk header 彻底损坏恢复
asm磁盘dd破坏恢复
通过底层恢复,恢复结果如下
20220331133634
dbv检查数据文件
20220331134249
然后把数据库恢复出来数据文件中的数据恢复到新库中,至此完成该case恢复

发表在 非常规恢复 | 标签为 , , , | 评论关闭