Database SOS – 第10页 – Professional Database Recovery Support@+8617813235971

PostgreSQL恢复系列:pg_filedump批量处理

pg_filedump工具使用起来比较麻烦,主要存在问题：
1. 需要人工一个个枚举各个列类型无法实现批量恢复,参考以前写的PostgreSQL恢复系列:pg_filedump基本使用
2. 特别是在pg库无法正常运行的情况下,如果没有业务提供表创建语句,恢复基本上无法正常进行.
基于这两个问题,在以前的文章中写过PostgreSQL恢复系列:pg_filedump恢复字典构造,为了解决上述的两个,弄了一个pg_filedump_batch脚本实现批量恢复需求

在测试的pg库中创建了一些测试表,并查看部分表数据,便于对比后续恢复效果

postgres=# \d
             List of relations
 Schema |      Name      | Type  |  Owner   
--------+----------------+-------+----------
 public | t_tbs          | table | postgres
 public | t_xff          | table | postgres
 public | t_xff2         | table | postgres
 public | t_xff3         | table | postgres
 public | t_xff4         | table | postgres
 public | t_xifenfei     | table | postgres
 public | tab_attribute  | table | postgres
 public | tab_class      | table | postgres
 public | tab_database   | table | postgres
 public | tab_namespace  | table | postgres
 public | tab_tablespace | table | postgres
 public | tab_type       | table | postgres
(12 rows)

postgres=# select * from tab_database;
  oid  |   datname   | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoi
d | datfrozenxid | datminmxid | dattablespace 
-------+-------------+--------+----------+-------------+-------------+---------------+--------------+--------------+-------------
--+--------------+------------+---------------
 14187 | postgres    |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |          1663
 16403 | db_xff      |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |          1663
     1 | template1   |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         1418
6 |          479 |          1 |          1663
 14186 | template0   |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         1418
6 |          479 |          1 |          1663
 16407 | db_xifenfei |  16405 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |         16406
(5 rows)
postgres=# select count(1) from tab_class;
 count 
-------
   407
(1 row)

postgres=# select *from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions 
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        | 
  1664 | pg_global    |       10 |        | 
 16406 | tbs_xifenfei |    16405 |        | 
(3 rows)

使用pg_filedump_bath脚本来实现批量恢复

[root@xifenfei tmp]# ./pg_filedump_batch recover --database-oid=14187  \
 --output-directory=/data/recovery --pgdata=/var/lib/pgsql/12/data
Recover tables in database with oid: 14187
LOG: starting to process table tab_attribute
LOG: starting to process table tab_class
LOG: starting to process table tab_database
LOG: starting to process table tab_namespace
LOG: starting to process table tab_tablespace
LOG: starting to process table tab_type
LOG: starting to process table t_tbs
LOG: starting to process table t_xff
LOG: starting to process table t_xff2
LOG: starting to process table t_xff3
LOG: starting to process table t_xff4
LOG: starting to process table t_xifenfei
Check dumps in /data/recovery

参考数据恢复

[root@xifenfei tmp]# cd /data/recovery/
[root@xifenfei recovery]# ls -ltr
total 156
-rw-r--r-- 1 root root 82797 Apr 18 20:35 recovered-14187-tab_attribute.csv
-rw-r--r-- 1 root root 31129 Apr 18 20:35 recovered-14187-tab_class.csv
-rw-r--r-- 1 root root   343 Apr 18 20:35 recovered-14187-tab_database.csv
-rw-r--r-- 1 root root   118 Apr 18 20:35 recovered-14187-tab_namespace.csv
-rw-r--r-- 1 root root    50 Apr 18 20:35 recovered-14187-tab_tablespace.csv
-rw-r--r-- 1 root root  7907 Apr 18 20:35 recovered-14187-tab_type.csv
-rw-r--r-- 1 root root     0 Apr 18 20:35 recovered-14187-t_tbs.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff2.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff3.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff4.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xifenfei.csv
[root@xifenfei recovery]# cat recovered-14187-tab_database.csv
14187   postgres        10      6       en_US.UTF-8     en_US.UTF-8     f       t       -1      14186   479     1       1663
16403   db_xff  10      6       en_US.UTF-8     en_US.UTF-8     f       t       -1      14186   479     1       1663
1       template1       10      6       en_US.UTF-8     en_US.UTF-8     t       t       -1      14186   479     1       1663
14186   template0       10      6       en_US.UTF-8     en_US.UTF-8     t       f       -1      14186   479     1       1663
16407   db_xifenfei     16405   6       en_US.UTF-8     en_US.UTF-8     f       t       -1      14186   479     1       16406
[root@xifenfei recovery]# cat recovered-14187-tab_class.csv|wc -l
407
[root@xifenfei recovery]# cat recovered-14187-tab_tablespace.csv
1663    pg_default
1664    pg_global
16406   tbs_xifenfei

把pg_class恢复数据导入库中进行对比,证明恢复的数据完全正确

postgres=# COPY tab_class_new FROM '/data/recovery/recovered-14187-tab_class.csv';
COPY 407

postgres=# select count(1) from tab_class;
 count 
-------
   407
(1 row)

 count 
-------
   407
(1 row)

postgres=# select count(1) from tab_class_new;
 count 
-------
   407
(1 row)

postgres=# select * from tab_class_new 
postgres-# EXCEPT
postgres-# select * from tab_class;
 oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel
allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind 
-----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+----
-----------+---------------+-------------+-------------+----------------+---------
(0 rows)

postgres=# select * from tab_class
postgres-# EXCEPT
postgres-# select * from tab_class_new;
 oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel
allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind 
-----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+----
-----------+---------------+-------------+-------------+----------------+---------
(0 rows)

通过上述操作证明:
1. 在没有人工列出列类型的情况下实现批量pg_filedump恢复功能
2. 在pg库没有启动的情况下直接解析字典实现恢复功能
3. 实现pg数据库的批量恢复
如果有PostgreSQL的数据库故障,自行无法解决,请联系我们提供专业数据库恢复技术支持:
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

PostgreSQL部分主要字典信息

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

标题：PostgreSQL部分主要字典信息

一、pg_class:

该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有字段对所有对象类型都有意义。

名字	类型	引用	描述
relname	name	数据类型名字。
relnamespace	oid	pg_namespace.oid	包含这个对象的名字空间(模式)的OI。
reltype	oid	pg_type.oid	对应这个表的行类型的数据类型。
relowner	oid	pg_authid.oid	对象的所有者。
relam	oid	pg_am.oid	对于索引对象，表示该索引的类型(B-tree，hash)。
relfilenode	oid	对象存储在磁盘上的文件名，如果没有则为0。
reltablespace	oid	pg_tablespace.oid	对象所在的表空间。如果为零，则表示使用该数据库的缺省表空间。(如果对象在磁盘上没有文件，这个字段就没有什么意义)
relpages	int4	该数据表或索引所占用的磁盘页面数量，查询规划器会借助该值选择最优路径。
reltuples	float4	表中行的数量，该值只是被规划器使用的一个估计值。
reltoastrelid	oid	pg_class.oid	与此表关联的TOAST表的OID，如果没有为0。TOAST表在一个从属表里”离线”存储大字段。
reltoastidxid	oid	pg_class.oid	如果是TOAST表，该字段为它索引的OID，如果不是TOAST表则为0。
relhasindex	bool	如果这是一个数据表而且至少有(或者最近有过)一个索引，则为真。它是由CREATE INDEX设置的，但DROP INDEX不会立即将它清除。如果VACUUM发现一个表没有索引，那么它清理 relhasindex。
relisshared	bool	如果该表在整个集群中由所有数据库共享，则为真。
relkind	char	r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表
relnatts	int2	数据表中用户字段的数量(除了系统字段以外，如oid)。在pg_attribute里肯定有相同数目的数据行。见pg_attribute.attnum.
relchecks	int2	表中检查约束的数量，参阅pg_constraint表。
reltriggers	int2	表中触发器的数量；参阅pg_trigger表。
relhasoids	bool	如果我们为对象中的每行都生成一个OID，则为真。
relhaspkey	bool	如果该表存在主键，则为真。
relhasrules	bool	如表有规则就为真；参阅pg_rewrite表。
relhassubclass	bool	如果该表有子表，则为真。
relacl	aclitem[]	访问权限。

二、pg_attribute:

该系统表存储所有表(包括系统表，如pg_class)的字段信息。数据库中的每个表的每个字段在pg_attribute表中都有一行记录。

名字	类型	引用	描述
attrelid	oid	pg_class.oid	此字段所属的表。
attname	name	字段名。
atttypid	oid	pg_type.oid	字段的数据类型。
attstattarget	int4	attstattarget控制ANALYZE为这个字段设置的统计细节的级别。零值表示不收集统计信息，负数表示使用系统缺省的统计对象。正数值的确切信息是和数据类型相关的。
attlen	int2	该字段所属类型的长度。(pg_type.typlen的拷贝)
attnum	int2	字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数。
attndims	int4	如果该字段是数组，该值表示数组的维数，否则是0。
attcacheoff	int4	在磁盘上总是-1，但是如果装载入内存中的行描述器中，它可能会被更新为缓冲在行中字段的偏移量。
atttypmod	int4	表示数据表在创建时提供的类型相关的数据(比如，varchar字段的最大长度)。其值对那些不需要atttypmod的类型而言通常为-1。
attbyval	bool	pg_type.typbyval字段值的拷贝。
attstorage	char	pg_type.typstorage字段值的拷贝。
attalign	char	pg_type.typalign字段值的拷贝。
attnotnull	bool	如果该字段带有非空约束，则为真，否则为假。
atthasdef	bool	该字段是否存在缺省值，此时它对应pg_attrdef表里实际定义此值的记录。
attisdropped	bool	该字段是否已经被删除。如果被删除，该字段在物理上仍然存在表中，但会被分析器忽略，因此不能再通过SQL访问。
attislocal	bool	该字段是否局部定义在对象中的。
attinhcount	int4	该字段所拥有的直接祖先的个数。如果一个字段的祖先个数非零，那么它就不能被删除或重命名。

三、pg_attrdef:

该系统表主要存储字段默认值，字段中的主要信息存放在pg_attribute系统表中。注意：只有明确声明了缺省值的字段在该表中才会有记录。

名字	类型	引用	描述
adrelid	oid	pg_class.oid	这个字段所属的表
adnum	int2	pg_attribute.attnum	字段编号，其规则等同于pg_attribute.attnum
adbin	text	字段缺省值的内部表现形式。
adsrc	text	缺省值的人可读的表现形式。

四、pg_constraint:

该系统表存储PostgreSQL中表对象的检查约束、主键、唯一约束和外键约束。

名字	类型	引用	描述
conname	name	约束名字(不一定是唯一的)。
connamespace	oid	pg_namespace.oid	包含这个约束的名字空间(模式)的OID。
contype	char	c = 检查约束， f = 外键约束， p = 主键约束， u = 唯一约束
condeferrable	bool	该约束是否可以推迟。
condeferred	bool	缺省时这个约束是否是推迟的？
conrelid	oid	pg_class.oid	该约束所在的表，如果不是表约束则为0。
contypid	oid	pg_type.oid	该约束所在的域，如果不是域约束则为0。
confrelid	oid	pg_class.oid	如果为外键，则指向参照的表，否则为0。
confupdtype	char	外键更新动作代码。
confdeltype	char	外键删除动作代码。
confmatchtype	char	外键匹配类型。
conkey	int2[]	pg_attribute.attnum	如果是表约束，则是约束控制的字段列表。
confkey	int2[]	pg_attribute.attnum	如果是外键，则是参照字段的列表。
conbin	text	如果是检查约束，则表示表达式的内部形式。
consrc	text	如果是检查约束，则是表达式的人可读的形式。

五、pg_tablespace:

该系统表存储表空间的信息。注意：表可以放在特定的表空间里，以帮助管理磁盘布局和解决IO瓶颈。

名字	类型	引用	描述
spcname	name	表空间名称。
spcowner	oid	pg_authid.oid	表空间的所有者，通常是创建它的角色。
spclocation	text	表空间的位置(目录路径)。
spcacl	aclitem[]	访问权限。

六、pg_namespace:

该系统表存储名字空间(模式)。

名字	类型	引用	描述
nspname	name	名字空间(模式)的名称。
nspowner	oid	pg_authid.oid	名字空间(模式)的所有者
nspacl	aclitem[]	访问权限。

七、pg_database:

该系统表存储数据库的信息。和大多数系统表不同的是，在一个集群里该表是所有数据库共享的，即每个集群只有一份pg_database拷贝，而不是每个数据库一份。

名字	类型	引用	描述
datname	name	数据库名称。
datdba	oid	pg_authid.oid	数据库所有者，通常为创建该数据库的角色。
encoding	int4	数据库的字符编码方式。
datistemplate	bool	如果为真，此数据库可以用于CREATE DATABASE TEMPLATE子句，把新数据库创建为此数据库的克隆。
datallowconn	bool	如果为假，则没有人可以联接到这个数据库。
datlastsysoid	oid	数据库里最后一个系统OID，此值对pg_dump特别有用。
datvacuumxid	xid
datfrozenxid	xid
dattablespace	text	pg_tablespace.oid	该数据库的缺省表空间。在这个数据库里，所有pg_class.reltablespace为零的表都将保存在这个表空间里，特别要指出的是，所有非共享的系统表也都存放在这里。
datconfig	text[]	运行时配置变量的会话缺省值。
datacl	aclitem[]	访问权限。

八、pg_index:

该系统表存储关于索引的一部分信息。其它的信息大多数存储在pg_class。

名字	类型	引用	描述
indexrelid	oid	pg_class.oid	该索引在pg_class里的记录的OID。
indrelid	oid	pg_class.oid	索引所在表在pg_class里的记录的OID。
indnatts	int2	索引中的字段数量(拷贝的pg_class.relnatts)。
indisunique	bool	如果为真，该索引是唯一索引。
indisprimary	bool	如果为真，该索引为该表的主键。
indisclustered	bool	如果为真，那么该表在这个索引上建了簇。
indkey	int2vector	pg_attribute.attnum	该数组的元素数量为indnatts，数组元素值表示建立这个索引时所依赖的字段编号，如1 3，表示第一个字段和第三个字段构成这个索引的键值。如果为0，则表示是表达式索引，而不是基于简单字段的索引。
indclass	oidvector	pg_opclass.oid	对于构成索引键值的每个字段，这个字段都包含一个指向所使用的操作符表的OID。
indexprs	text	表达式树用于那些非简单字段引用的索引属性。它是一个列表，在indkey里面的每个零条目一个元素。如果所有索引属性都是简单的引用，则为空。
indpred	text	部分索引断言的表达式树。如果不是部分索引，则是空字串。

PostgreSQL恢复系列:pg_filedump恢复字典构造

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

标题：PostgreSQL恢复系列:pg_filedump恢复字典构造

pg_filedump是在pg数据库极端情况下直接解析数据库文件的利器,但是由于是开源软件,本身难以实现批量处理,通过对底层基表分析,可以实现批量处理功能
分析PostgreSQL库中数据库信息

--数据库查询结果
postgres=# select oid,datname,datdba,dattablespace from pg_database;
  oid  |   datname   | datdba | dattablespace 
-------+-------------+--------+---------------
 14187 | postgres    |     10 |          1663
 16403 | db_xff      |     10 |          1663
     1 | template1   |     10 |          1663
 14186 | template0   |     10 |          1663
 16407 | db_xifenfei |  16405 |         16406
(5 rows)

--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    5 (0x0005)  Flags: REDIRECT
 Item   2 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   3 -- Length:  260  Offset: 7320 (0x1c98)  Flags: NORMAL
COPY: 14187     postgres
 Item   4 -- Length:  260  Offset: 7056 (0x1b90)  Flags: NORMAL
COPY: 16403     db_xff
 Item   5 -- Length:  297  Offset: 7888 (0x1ed0)  Flags: NORMAL
COPY: 1 template1
 Item   6 -- Length:  297  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 14186     template0
 Item   7 -- Length:  260  Offset: 6792 (0x1a88)  Flags: NORMAL
COPY: 16407     db_xifenfei

分析PostgreSQL 表空间信息

--sql查询表空间信息
postgres=# select * from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions 
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        | 
  1664 | pg_global    |       10 |        | 
 16406 | tbs_xifenfei |    16405 |        | 
(3 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 1663      pg_default
 Item   2 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 1664      pg_global
 Item   3 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 16406     tbs_xifenfei

分析PostgreSQL 对象id、name、path对应关系

--对象信息查询
postgres=# select oid ,relname,relnamespace,reltype,reloftype,relowner,relam,relfilenode,
           reltablespace from pg_class where relname like 't_t%' or relname like 't_x%';
  oid  |  relname   | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace 
-------+------------+--------------+---------+-----------+----------+-------+-------------+---------------
 16387 | t_xifenfei |         2200 |   16389 |         0 |       10 |     2 |       16390 |             0
 16391 | t_xff      |         2200 |   16393 |         0 |       10 |     2 |       16391 |             0
 16394 | t_xff2     |         2200 |   16396 |         0 |       10 |     2 |       16394 |             0
 16397 | t_xff3     |         2200 |   16399 |         0 |       10 |     2 |       16397 |             0
 16400 | t_xff4     |         2200 |   16402 |         0 |       10 |     2 |       16400 |             0
 16408 | t_tbs      |         2200 |   16410 |         0 |       10 |     2 |       16408 |         16406
(6 rows)

--通过dump 该文件解析数据（显示部分）
COPY: 16394     t_xff2  2200    16396   0       10      2       16394   0       0       0.000000000000  0       0       f       f       p       r
 Item  29 -- Length:    0  Offset:   31 (0x001f)  Flags: REDIRECT
 Item  30 -- Length:  172  Offset: 2592 (0x0a20)  Flags: NORMAL
COPY: 16397     t_xff3  2200    16399   0       10      2       16397   0       0       0.000000000000  0       0       f       f       p       r
 Item  31 -- Length:  205  Offset: 3376 (0x0d30)  Flags: NORMAL
COPY: 12093     pg_shadow       11      12094   0       10      0       0       0       0       0.000000000000  0       0       f       f       p       v
 Item  32 -- Length:  172  Offset: 2416 (0x0970)  Flags: NORMAL
COPY: 16400     t_xff4  2200    16402   0       10      2       16400   0       0       0.000000000000  0       0       f       f       p       r

这个里面获取到pg_class.reltablespace是表空间的id值,根据自定义表空间的规则:在pgdata/pg_tblspc创建link指向创建表空间时候的文件夹路径

-bash-4.2$ pwd
/var/lib/pgsql/12/data/pg_tblspc
-bash-4.2$ ls -ltr
total 0
lrwxrwxrwx 1 postgres postgres 30 Apr 15 20:13 16406 -> /var/lib/pgsql/12/data/tbs_xff

结合上述的pg_database,pg_tablespace,pg_class信息,可以获取到每个表对应实际的存储路径
分析PostgreSQL 模式信息

--sql查询模式信息
postgres=# select * from pg_namespace;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13887 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 16404 | u_xifenfei         |       10 | 
(7 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   2 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 99        pg_toast
 Item   3 -- Length:    0  Offset:    7 (0x0007)  Flags: REDIRECT
 Item   4 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 12314     pg_temp_1
 Item   5 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 12315     pg_toast_temp_1
 Item   6 -- Length:  141  Offset: 7760 (0x1e50)  Flags: NORMAL
COPY: 11        pg_catalog
 Item   7 -- Length:  141  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 2200      public
 Item   8 -- Length:    0  Offset:    9 (0x0009)  Flags: REDIRECT
 Item   9 -- Length:  141  Offset: 7472 (0x1d30)  Flags: NORMAL
COPY: 13887     information_schema
 Item  10 -- Length:   96  Offset: 7376 (0x1cd0)  Flags: NORMAL
COPY: 16404     u_xifenfei

通过pg_namespace，pg_class信息，可以获取到对象所属的模式关系，基于上述汇总,可以获取到某个模式下面,所有表id和实际存储路径,现在使用pg_filedump进行恢复,还缺少表的列类型信息,通过pg_type和pg_attribute来获取。

获取PostgreSQL表的列名称和类型[编号]信息

--sql查询列信息
postgres=# \d t_tbs
               Table "public.t_tbs"
  Column  | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 oid      | oid  |           |          | 
 spcname  | name |           |          | 
 spcowner | oid  |           |          | 
Tablespace: "tbs_xifenfei"

postgres=# select attrelid,attname,atttypid,attstattarget,attlen,attnum from pg_attribute where attrelid=16408;
 attrelid | attname  | atttypid | attstattarget | attlen | attnum 
----------+----------+----------+---------------+--------+--------
    16408 | tableoid |       26 |             0 |      4 |     -6
    16408 | cmax     |       29 |             0 |      4 |     -5
    16408 | xmax     |       28 |             0 |      4 |     -4
    16408 | cmin     |       29 |             0 |      4 |     -3
    16408 | xmin     |       28 |             0 |      4 |     -2
    16408 | ctid     |       27 |             0 |      6 |     -1
    16408 | oid      |       26 |            -1 |      4 |      1
    16408 | spcname  |       19 |            -1 |     64 |      2
    16408 | spcowner |       26 |            -1 |      4 |      3
(9 rows)

--dump 内容（截取部分）
 Item  11 -- Length:  144  Offset: 1424 (0x0590)  Flags: NORMAL
COPY: 16408     oid     26      -1      4       1
 Item  12 -- Length:  144  Offset: 1280 (0x0500)  Flags: NORMAL
COPY: 16408     spcname 19      -1      64      2
 Item  13 -- Length:  144  Offset: 1136 (0x0470)  Flags: NORMAL
COPY: 16408     spcowner        26      -1      4       3
 Item  14 -- Length:  144  Offset:  992 (0x03e0)  Flags: NORMAL
COPY: 16408     ctid    27      0       6       -1
 Item  15 -- Length:  144  Offset:  848 (0x0350)  Flags: NORMAL
COPY: 16408     xmin    28      0       4       -2
 Item  16 -- Length:  144  Offset:  704 (0x02c0)  Flags: NORMAL
COPY: 16408     cmin    29      0       4       -3
 Item  17 -- Length:  144  Offset:  560 (0x0230)  Flags: NORMAL
COPY: 16408     xmax    28      0       4       -4
 Item  18 -- Length:  144  Offset:  416 (0x01a0)  Flags: NORMAL
COPY: 16408     cmax    29      0       4       -5

PostgreSQL获取类型编号和实际类型名称对应关系

--查询类型编号和实际类型关系
postgres=# select oid,typname from pg_type;
  oid  |                typname                
-------+---------------------------------------
    16 | bool
    17 | bytea
    18 | char
    19 | name
    20 | int8
    21 | int2
    22 | int2vector
    23 | int4
    24 | regproc
    25 | text
    26 | oid
    27 | tid
    28 | xid
    29 | cid
……

--dump 内容（截取部分）
 Item   1 -- Length:  176  Offset: 8016 (0x1f50)  Flags: NORMAL
COPY: 16        bool
 Item   2 -- Length:  176  Offset: 7840 (0x1ea0)  Flags: NORMAL
COPY: 17        bytea
 Item   3 -- Length:  176  Offset: 7664 (0x1df0)  Flags: NORMAL
COPY: 18        char
 Item   4 -- Length:  176  Offset: 7488 (0x1d40)  Flags: NORMAL
COPY: 19        name
 Item   5 -- Length:  176  Offset: 7312 (0x1c90)  Flags: NORMAL
COPY: 20        int8
 Item   6 -- Length:  176  Offset: 7136 (0x1be0)  Flags: NORMAL
COPY: 21        int2
 Item   7 -- Length:  176  Offset: 6960 (0x1b30)  Flags: NORMAL
COPY: 22        int2vector
 Item   8 -- Length:  176  Offset: 6784 (0x1a80)  Flags: NORMAL
COPY: 23        int4
 Item   9 -- Length:  176  Offset: 6608 (0x19d0)  Flags: NORMAL
COPY: 24        regproc
 Item  10 -- Length:  176  Offset: 6432 (0x1920)  Flags: NORMAL
COPY: 25        text
 Item  11 -- Length:  176  Offset: 6256 (0x1870)  Flags: NORMAL
COPY: 26        oid
 Item  12 -- Length:  176  Offset: 6080 (0x17c0)  Flags: NORMAL
COPY: 27        tid
 Item  13 -- Length:  176  Offset: 5904 (0x1710)  Flags: NORMAL
COPY: 28        xid
 Item  14 -- Length:  176  Offset: 5728 (0x1660)  Flags: NORMAL
COPY: 29        cid
 Item  15 -- Length:  176  Offset: 5552 (0x15b0)  Flags: NORMAL
COPY: 30        oidvector

通过pg_class、pg_type和pg_attribute可以获取对象的表的列名称，数据类型等信息。通过以上几个对象，即可获取到pg_filedmp处理所需要的所有信息,然后可以实现批量处理

PostgreSQL 16 源码安装

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

标题：PostgreSQL 16 源码安装

空闲中尝试在linux 8的版本上,源码安装PostgreSQL 16，参考文章:PostgreSQL16.2源码部署教程

[root@xifenfei tmp]# cat /etc/redhat-release 
Red Hat Enterprise Linux release 8.7 (Ootpa)
[root@xifenfei tmp]#  uname -osr
Linux 5.15.0-3.60.5.1.el8uek.x86_64 GNU/Linux

下载pg源码程序:PostgreSQL 16源码下载
linux配置

--关闭selinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
setenforce 0

--安装相应的包
yum -y install readline readline-devel zlib zlib-devel gettext \
 gettext-devel openssl openssl-devel pam pam-devel libxml2 \
libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel \
libuuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*  libicu  libicu-devel

--修改内核参数
cat>>/etc/sysctl.conf<<EOF
fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=75
net.ipv4.ip_local_port_range = 10000 65535
EOF
sysctl -p

--创建组和用户
groupadd postgres -g 3000
useradd postgres -g 3000 -u 3000

--用户 limits 配置
cat>>/etc/security/limits.conf<<EOF
postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456
EOF

--创建相关目录和权限
mkdir -p /pg/database/server
mkdir -p /pg/database/data
mkdir -p /pg/database/wal
mkdir -p /pg/database/archive
chown -R postgres:postgres /pg -R
chmod 0775 /pg
chmod 0700 /pg/database/data

--设置环境变量
vi /home/postgres/.bash_profile
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/pg/database/server
export PGDATA=/pg/database/data
export PATH=$PGHOME/bin:$PATH

--编译pg软件
su - postgres
tar xzvf postgresql-16.2.tar.gz
cd /tmp/postgresql-16.2
./configure --prefix=/pg/database/server --with-pgport=5432
make
make install

--确认软件安装成功（检查编译日志和测试如下命令）
[postgres@xifenfei ~]$ postgres --version
postgres (PostgreSQL) 16.2

创建数据库

[postgres@xifenfei ~]$  /pg/database/server/bin/initdb -D/pg/database/data\
 -X/pg/database/wal -EUTF8 -Upostgres -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /pg/database/data ... ok
fixing permissions on existing directory /pg/database/wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A,
 or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /pg/database/server/bin/pg_ctl -D /pg/database/data -l logfile start

启动数据库

[postgres@xifenfei ~]$ /pg/database/server/bin/pg_ctl -D /pg/database/data -l logfile start
waiting for server to start.... done
server started
[postgres@xifenfei ~]$ psql
psql (16.2)
Type "help" for help.

postgres=# \d pg_class
                     Table "pg_catalog.pg_class"
       Column        |     Type     | Collation | Nullable | Default 
---------------------+--------------+-----------+----------+---------
 oid                 | oid          |           | not null | 
 relname             | name         |           | not null | 
 relnamespace        | oid          |           | not null | 
 reltype             | oid          |           | not null | 
 reloftype           | oid          |           | not null | 
 relowner            | oid          |           | not null | 
 relam               | oid          |           | not null | 
 relfilenode         | oid          |           | not null | 
 reltablespace       | oid          |           | not null | 
 relpages            | integer      |           | not null | 
 reltuples           | real         |           | not null | 
 relallvisible       | integer      |           | not null | 
 reltoastrelid       | oid          |           | not null | 
 relhasindex         | boolean      |           | not null | 
 relisshared         | boolean      |           | not null | 
 relpersistence      | "char"       |           | not null | 
 relkind             | "char"       |           | not null | 
 relnatts            | smallint     |           | not null | 
 relchecks           | smallint     |           | not null | 
 relhasrules         | boolean      |           | not null | 
 relhastriggers      | boolean      |           | not null | 
 relhassubclass      | boolean      |           | not null | 
 relrowsecurity      | boolean      |           | not null | 
 relforcerowsecurity | boolean      |           | not null | 
 relispopulated      | boolean      |           | not null | 
 relreplident        | "char"       |           | not null | 
 relispartition      | boolean      |           | not null | 
 relrewrite          | oid          |           | not null | 
 relfrozenxid        | xid          |           | not null | 
 relminmxid          | xid          |           | not null | 
 relacl              | aclitem[]    |           |          | 
 reloptions          | text[]       | C         |          | 
 relpartbound        | pg_node_tree | C         |          | 
Indexes:
    "pg_class_oid_index" PRIMARY KEY, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

配置开机自动启动

[root@xifenfei tmp]# cp /tmp/postgresql-16.2/contrib/start-scripts/linux /etc/init.d/PostgreSQL
[root@xifenfei tmp]# vi /etc/init.d/PostgreSQL 
--修改如下值
prefix=/pg/database/server
PGDATA="/pg/database/data"
[root@xifenfei tmp]# chkconfig --add PostgreSQL
[root@xifenfei tmp]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

PostgreSQL      0:off   1:off   2:on    3:on    4:on    5:on    6:off

ORA-00742 ORA-00312 恢复

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

标题：ORA-00742 ORA-00312 恢复

有客户反馈,断电之后数据库启动报ORA-00742和ORA-00312,无法正常open

我们远程上去尝试open库结果也报同样错误

[oracle@oldhis oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 10 09:40:03 2024

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


Connected to:
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> recover database;
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 3 thread 1: '/oradata/shrdh/redo03.log'


SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         3 CURRENT
         2 INACTIVE

因为recover已经成功,但是依旧报ORA-742错误,尝试查询scn相关信息

SQL> set pages 10000
set numw 16
SELECT status,
checkpoint_change#,
checkpoint_time,last_change#,
count(*) ROW_NUM
FROM v$datafile
GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
ORDER BY status, checkpoint_change#, checkpoint_time;


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

SQL> SQL>   2    3    4    5    6    7  
STATUS  CHECKPOINT_CHANGE# CHECKPOIN     LAST_CHANGE#          ROW_NUM
------- ------------------ --------- ---------------- ----------------
ONLINE          1279351848 26-MAR-24       1279351848               19
SYSTEM          1279351848 26-MAR-24       1279351848                1

SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6  
STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
ONLINE  2024-03-26 00:05:45                      NO          1279351848               20

基于这样的情况,我们判断数据库直接open成功

SQL> recover database using backup controlfile;
ORA-00279: change 1279351848 generated at 03/26/2024 00:05:45 needed for thread 1
ORA-00289: suggestion : /oradata/arch/shrdh/shrdh_1_12984_974767526.arc
ORA-00280: change 1279351848 for thread 1 is in sequence #12984


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/shrdh/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

后面比较不幸，数据库报ORA-600 4194错误导致数据库异常

Wed Apr 10 09:43:08 2024
ALTER DATABASE RECOVER  database using backup controlfile  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ...
Wed Apr 10 09:43:24 2024
ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  
Media Recovery Log /oradata/shrdh/redo03.log
Media Recovery Complete (shrdh)
Completed: ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  
alter database open resetlogs
RESETLOGS after complete recovery through change 1279351849
Clearing online redo logfile 1 /oradata/shrdh/redo01.log
Clearing online log 1 of thread 1 sequence number 12982
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradata/shrdh/redo02.log
Clearing online log 2 of thread 1 sequence number 12983
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradata/shrdh/redo03.log
Clearing online log 3 of thread 1 sequence number 12984
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 1820377766 (0x6c80c2a6)
Online log /oradata/shrdh/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/shrdh/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/shrdh/redo03.log: Thread 1 Group 3 was previously cleared
Wed Apr 10 09:43:34 2024
Setting recovery target incarnation to 2
Wed Apr 10 09:43:34 2024
Assigning activation ID 2011515185 (0x77e54931)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oradata/shrdh/redo01.log
Successful open of redo thread 1
Wed Apr 10 09:43:34 2024
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Apr 10 09:43:34 2024
SMON: enabling cache recovery
[25089] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1273646224 end:1273646494 diff:270 (2 seconds)
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
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc  (incident=84296):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84296/shrdh_smon_21704_i84296.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Starting background process QMNC
Wed Apr 10 09:43:35 2024
QMNC started with pid=24, OS id=25340 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Block recovery from logseq 1, block 61 to scn 1279351933
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery stopped at EOT rba 1.99.16
Block recovery completed at rba 1.99.16, scn 0.1279351933
Block recovery from logseq 1, block 61 to scn 1279351919
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery completed at rba 1.87.16, scn 0.1279351922
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc:
ORA-01595: error freeing extent (2) of rollback segment (7))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Completed: alter database open resetlogs
Wed Apr 10 09:43:37 2024
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_m000_25343.trc  (incident=84392):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84392/shrdh_m000_25343_i84392.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Apr 10 09:43:37 2024
Starting background process CJQ0
Wed Apr 10 09:43:37 2024
CJQ0 started with pid=29, OS id=25357 
Starting background process SMCO
Wed Apr 10 09:43:37 2024
SMCO started with pid=30, OS id=25360 
Wed Apr 10 09:43:38 2024
Flush retried for xcb 0x115b42d28, pmd 0x1148dea70
Block recovery from logseq 1, block 61 to scn 1279351933
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery completed at rba 1.99.16, scn 0.1279351934
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_pmon_21679.trc  (incident=84208):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84208/shrdh_pmon_21679_i84208.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_pmon_21679.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 21679): terminating the instance due to error 472
Wed Apr 10 09:43:47 2024
Instance terminated by PMON, pid = 21679

报错比较明显,对undo进行处理即可.

数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)

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

标题：数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)

服务器异常断电之后，开机启动数据库启动成功，但是报ORA-00353 ORA-00354以及ORA-600 kdsgrp1错误

Sun Mar 31 01:19:51 2024
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 31 01:19:51 2024
SMON: enabling cache recovery
[4528] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:64250 end:64859 diff:609 (6 seconds)
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
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Mar 31 01:19:57 2024
QMNC started with pid=40, OS id=4912 
Sun Mar 31 01:20:03 2024
Completed: alter database open
Sun Mar 31 01:20:04 2024
Starting background process CJQ0
Sun Mar 31 01:20:04 2024
CJQ0 started with pid=20, OS id=5104 
Setting Resource Manager plan SCHEDULER[0x32DE]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Mar 31 01:20:07 2024
Starting background process VKRM
Sun Mar 31 01:20:07 2024
VKRM started with pid=48, OS id=4160 
Sun Mar 31 01:20:32 2024
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc(incident=132393):
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc(incident=132394):
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\incident\incdir_132393\xifenfei_j000_4180_i132393.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Sun Mar 31 01:20:35 2024
Sweep [inc][132394]: completed
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 13792 更改 33686946 时间 03/31/2024 00:17:54
ORA-00334: 归档日志: 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_j000_4180.trc(incident=132395):
ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Incomplete read from log member 'E:\APP\ARCHIVELOG\ARC0000005281_1157968160.0001'. Trying next member.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

再次重启数据库报ORA-600 2131错误,数据库无法正常mount

Sun Mar 31 08:59:20 2024
alter database mount exclusive
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_3232.trc  (incident=144175):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: alter database mount exclusive...

因为这个库有ctl备份,通过rman还原ctl备份,然后尝试recover库,结果报ORA-00310 ORA-00334（由于需要的redo无法正常应用导致）

对于这类情况,只能通过屏蔽一致性强制打开库

数据报ORA-600 2662错误,此类错误比较简单,使用patch scn工具一键搞定(ORA-600 2662快速恢复之Patch scn工具),数据库open成功,导出数据完整恢复
对于正常open的库,出现此类问题属于反常现象,通过分析系统事件确定是由于ntfs文件系统本身有问题导致

ORA-600 ktsiseginfo1故障

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

标题：ORA-600 ktsiseginfo1故障

oracle 9i的库在运行途中突然报ORA-600 kcbnew_3错误

Sun Mar 31 14:25:11 2024
Undo Segment 69 Onlined
Sun Mar 31 14:25:11 2024
Created Undo Segment _SYSSMU69$
Sun Mar 31 14:25:11 2024
Created Undo Segment _SYSSMU70$
Undo Segment 70 Onlined
Sun Mar 31 14:28:41 2024
Starting control autobackup
Control autobackup written to SBT_TAPE device
	comment 'API Version 2.0,MMS Version 1.2.0.0',
	media 'A90063L4'
	handle 'c-1757743655-20240331-01'
Sun Mar 31 14:31:07 2024
Starting control autobackup
Control autobackup written to SBT_TAPE device
	comment 'API Version 2.0,MMS Version 1.2.0.0',
	media 'A90063L4'
	handle 'c-1757743655-20240331-02'
Sun Mar 31 14:34:31 2024
SMON offlining US=70
Sun Mar 31 14:34:31 2024
Created Undo Segment _SYSSMU73$
Undo Segment 73 Onlined
Sun Mar 31 14:34:31 2024
Created Undo Segment _SYSSMU74$
Undo Segment 74 Onlined
Sun Mar 31 14:34:31 2024
Created Undo Segment _SYSSMU81$
Undo Segment 81 Onlined
Sun Mar 31 14:37:36 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_3741.trc:
ORA-00600: 内部错误代码，参数: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:39:22 2024
Errors in file /u2/oradb/admin/xifenfei/bdump/xifenfei_smon_6890.trc:
ORA-01595: error freeing extent (6) of rollback segment (70))
ORA-00600: internal error code, arguments: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:45:05 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4046.trc:
ORA-00600: 内部错误代码，参数: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:47:47 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:47:47 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:48:03 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4074.trc:
ORA-00600: 内部错误代码，参数: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:48:35 2024
Errors in file /u2/oradb/admin/xifenfei/bdump/xifenfei_qmn0_4044.trc:
ORA-00600: internal error code, arguments: [kcbnew_3], [156], [], [], [], [], [], []
Sun Mar 31 14:50:01 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:50:02 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:50:41 2024
Restarting dead background process QMN0
QMN0 started with pid=53
Sun Mar 31 14:51:09 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:51:09 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:54:36 2024
Failure to extend rollback segment 73 because of 3113 condition
FULL status of rollback segment 73 set.
Sun Mar 31 14:54:36 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 14:54:56 2024
Errors in file /u2/oradb/admin/xifenfei/bdump/xifenfei_smon_6890.trc:
ORA-01595: error freeing extent (6) of rollback segment (70))
ORA-00600: internal error code, arguments: [kcbnew_3], [156], [], [], [], [], [], []

从上述信息看,由于ora-600 kcbnew_3错误导致70/73号回滚段异常,smon无法正常对其进行扩展,关于ora-600 kcbnew_3报错描述

客户尝试关闭数据库重启,数据库报ora-600 ktsiseginfo1错误，无法正常启动

Sun Mar 31 15:13:03 2024
Shutting down instance: further logons disabled
Sun Mar 31 15:15:59 2024
Shutting down instance (immediate)
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 set.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
FULL status of rollback segment 73 set.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 set.
Sun Mar 31 15:15:59 2024
Failure to extend rollback segment 73 because of 1089 condition
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 15:15:59 2024
FULL status of rollback segment 73 cleared.
Sun Mar 31 15:15:59 2024
License high water mark = 161
Sun Mar 31 15:21:04 2024
Active call for process 2878 user 'oracle' program 'oracle@ttperp (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.
Sun Mar 31 15:21:07 2024
Failure to extend rollback segment 73 because of 1089 condition
FULL status of rollback segment 73 set.
Sun Mar 31 15:21:07 2024
SMON: FULL status of rollback segment 73 cleared.
Sun Mar 31 15:21:08 2024
ALTER DATABASE CLOSE NORMAL
SMON: disabling tx recovery
SMON: disabling cache recovery
Sun Mar 31 15:21:16 2024
Shutting down archive processes
Archiving is disabled
Sun Mar 31 15:21:16 2024
ARCH shutting down
Sun Mar 31 15:21:16 2024
ARCH shutting down
Sun Mar 31 15:21:16 2024
ARC1: Archival stopped
Sun Mar 31 15:21:16 2024
ARC0: Archival stopped
Sun Mar 31 15:21:17 2024
Thread 1 closed at log sequence 313828
Successful close of redo thread 1.
Sun Mar 31 15:21:18 2024
Completed: ALTER DATABASE CLOSE NORMAL
Sun Mar 31 15:21:18 2024
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sun Mar 31 15:23:10 2024
Starting ORACLE instance (normal)
Sun Mar 31 15:23:10 2024
Running with 1 strand for Non-Enterprise Edition
Sun Mar 31 15:23:10 2024
WARNING: EINVAL creating segment of size 0x0000000065400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Running with 1 strand for Non-Enterprise Edition
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  processes                = 1000
  timed_statistics         = TRUE
  shared_pool_size         = 218103808
  sga_max_size             = 1679366944
  large_pool_size          = 67108864
  java_pool_size           = 33554432
  control_files            = /u2/oradb/oradata/xifenfei/control01.ctl
  db_block_size            = 8192
  db_cache_size            = 838860800
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  log_archive_dest         = /u6/archive_u6
  log_archive_format       = %s_%t.dbf
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 0
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS7
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  instance_name            = xifenfei
  utl_file_dir             = /u1/usr/oracle/utl
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /u2/oradb/admin/xifenfei/bdump
  user_dump_dest           = /u2/oradb/admin/xifenfei/udump
  core_dump_dest           = /u2/oradb/admin/xifenfei/cdump
  sort_area_size           = 104857600
  db_name                  = xifenfei
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 209715200
  aq_tm_processes          = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Sun Mar 31 15:23:11 2024
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=10
ARC0: Archival started
ARC1 started with pid=11
Sun Mar 31 15:23:11 2024
ARCH: STARTING ARCH PROCESSES COMPLETE
Oracle Data Guard is not available in this edition of Oracle.
Sun Mar 31 15:23:11 2024
ARC1: Archival started
Sun Mar 31 15:23:11 2024
ARC1: Thread not mounted
Sun Mar 31 15:23:11 2024
ARC0: Thread not mounted
Sun Mar 31 15:23:11 2024
ALTER DATABASE   MOUNT
Sun Mar 31 15:23:15 2024
Successful mount of redo thread 1, with mount id 2392685535.
Sun Mar 31 15:23:15 2024
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Sun Mar 31 15:23:15 2024
ALTER DATABASE OPEN
Sun Mar 31 15:23:15 2024
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 313828
  Current log# 5 seq# 313828 mem# 0: /u3/oracle/xifenfei/redolog/redo05a.log
  Current log# 5 seq# 313828 mem# 1: /u4/oracle/xifenfei/redolog/redo05b.log
Successful open of redo thread 1.
Sun Mar 31 15:23:17 2024
SMON: enabling cache recovery
Sun Mar 31 15:23:17 2024
Undo Segment 69 Onlined
Undo Segment 70 Onlined
Undo Segment 71 Onlined
Undo Segment 72 Onlined
Sun Mar 31 15:23:21 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4384.trc:
ORA-00600: 内部错误代码，参数: [ktsiseginfo1], [20], [65], [73], [], [], [], []
Sun Mar 31 15:23:22 2024
Errors in file /u2/oradb/admin/xifenfei/udump/xifenfei_ora_4384.trc:
ORA-00600: 内部错误代码，参数: [ktsiseginfo1], [20], [65], [73], [], [], [], []
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 4384
ORA-1092 signalled during: ALTER DATABASE OPEN...

启动的时候由于回滚段异常导致该问题,临时解决问题可以通过规避掉异常回滚段open库,但是这个问题的本质是由于seg$和undo$中记录不匹配导致,建议进一步通过hcheck进行检查分析,并进行后续处理,参考mos相关描述:

ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因

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

标题：ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因

最近接触过几个ORA-600 16703的恢复case,报错日志类似为：

SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2071.trc  (incident=7357):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_7357/orcl_ora_2071_i7357.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2071.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2071.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 2071): terminating the instance due to error 704
Instance terminated by USER, pid = 2071
ORA-1092 signalled during: ALTER DATABASE OPEN...

查看客户的日志,发现客户的库被人使用bbed按照网上的思路进行过恢复（使用block替换的方式尝试open库）,结果报出来ORA-00600: internal error code, arguments: [16703], [1403], [4]之类的错误

Wed Mar 20 22:00:39 2024
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1840752.trc  (incident=62557):
ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_62557/orcl_ora_1840752_i62557.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1840752.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1840752.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [4], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 1840752): terminating the instance due to error 704
Instance terminated by USER, pid = 1840752
ORA-1092 signalled during: alter database open...

其实ORA-600 16703 1403 N的错误,表示数据库在检索tab$表的时候,N为发现异常的obj#的值,这里的报错表示数据库在启动过程中无法检索到tab$中的tab$对象,应该是恢复过程中缺少该记录导致,解决问题的思路就是把该记录回写到tab$表中即可
以前写过类似文章:ORA-00600: internal error code, arguments: [16703], [1403], [4] 故障处理

最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视

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

标题：最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视

最近由于安装介质问题,导致tab$被清空的case又多了起来,最近遇到几个客户咨询此类问题

以前写过一些类似的文档,参考:
tab$恢复错误汇总
ORA-600 kzrini:!uprofile处理
10g数据库遭遇ORA-600 16703
12C数据库遭遇ORA-600 16703
ORA-00600: internal error code, arguments: [16703], [1403], [32]
ORA-600 16703直接把orachk备份表插入到tab$恢复
ORA-600 16703故障解析—tab$表被清空
tab$异常被处理之后报ORA-600 13304故障处理

aix平台tab$被删除可能出现ORA-600 [16703], [1403], [28]错误
 ORA-00600: internal error code, arguments: [16703], [1403], [4] 故障处理
 ORA-600 16703故障,客户找人恢复数据库,数据库被进一步恶意破坏—ORA-00704 ORA-00922
警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703

ORA-600 2662快速恢复之Patch scn工具

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

标题：ORA-600 2662快速恢复之Patch scn工具

有客户数据库启动报ORA-600 2662错误

SQL> recover database;
完成介质恢复。
SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [272845978], [0], [272853062], [12583040]
ORA-00600: internal error code, arguments: [2662], [0], [272845977], [0], [272853062], [12583040]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [272845973], [0], [272853062], [12583040]
进程 ID: 3528
会话 ID: 33 序列号: 1

通过自研开发的patch scn工具,修改数据库scn值

然后open数据库成功

对于这类故障,patch scn工具是最快速的解决方案