分类目录归档：数据库

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

发表于 2025 年 12 月 15 日由惜分飞

今天又遇到一个客户遭遇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$异常修复小工具修复之后,然后直接启动数据库成功,完成本次恢复任务

发表在 Oracle备份恢复 | 标签为 bootstrap$异常恢复工具, ORA-00702 | 评论关闭

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

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

在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
    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 | 标签为 pg表相关oid, 表所有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文件中的数据

发表在 PostgreSQL, PostgreSQL恢复 | 标签为 oid恢复, pg恢复, replace oid recovery, 替换oid恢复 | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

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

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

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

文章分类

最近发表

分类目录归档：数据库

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

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

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

标签云

文章分类

最近发表