ORA-600 ktbair2: illegal inheritance恢复

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

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

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

查看alert日志

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

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

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

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

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

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

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

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

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

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

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

alert日志相关报错信息

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

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

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

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

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

联系：手机/微信(+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文件替换实现数据访问

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

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

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

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