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

pg删除数据恢复—pdu恢复pg delete数据

误操作,在应用端选择每页50条记录然后点击删除,类似这样误操作删除了近10000条记录,现在希望对其进行恢复.对于这种表被误删除数据的,可以使用pdu进行解析wal然后进行恢复.
1. 配置pdu.ini指定wal日志路径

PGDATA=/data/pg/16/data
ARCHIVE_DEST=/data/arch

2. 初始化字典信息

PDU.public=# b;
开始初始化...
 -pg_database:</data/pg/16/data/global/1262>
    【postgres】
      -pg_schema:</data/pg/16/data/base/5/2615>
      -pg_class:</data/pg/16/data/base/5/1259>，共86行
      -pg_attribute:</data/pg/16/data/base/5/1249>，共3273行
      模式:
        -->public，2张表

3. 关于删除恢复相关帮助

**误删数据恢复**
scan t1;                                │ 扫描被误删的表
restore del <Tx Number>;                │ 通过 事务号 恢复被误删的数据
restore del all;                        │ 通过 时间区间 恢复被误删的数据
--------------------------------------------------------------------------------------------------
add <filenode> <tablename> <attibutes>; │ 将特定表信息手动添加到restore库中
例如: <add 12345 t1 varchar,varchar,timestamp,varchar,numeric,varchar,varchar,varchar,numeric;>
--------------------------------------------------------------------------------------------------
restore db <dbname> <DB Path>;          │ 初始化特定的数据库目录 (例: restore db xmandb /home/postgres/data/base/290113;)

**参数设置**
param startwal 000000010000000800000008;│ 设置scan扫描的起始WAL文件，如果未设置则默认是归档目录的第一个文件
param endwal 000000010000000800000009;  │ 设置scan扫描的结束WAL文件，如果未设置则默认是归档目录的最后一个文件
param resmode tx|time;                  │ 设置restore恢复的模式，选择按照【事务号/TX】或【时间区间/TIME】进行恢复
param starttime 2025-01-01_00:00:00;    │ 设置scan扫描的起始时间 
param endtime 2025-01-01_00:00:00;      │ 设置scan扫描的结束时间 
reset <param name>                      │ 重置某个参数 
show;                                   │ 查看当前所有参数

4.可以通过param相关参数设置扫描wal日志范围,时间,恢复模式等.我们先使用事务模式确认开始时间

postgres.public=# scan t_delete;

正在扫描表<t_delete>的删除记录...

开始扫描归档目录
|-起始文件<000000030000052500000050>
|-终点文件<0000000300000525000000EF>

正在扫描中

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
当前为 事务号恢复 模式,扫描结果将以【事务号】为单位进行输出
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━



扫描结束,当前扫描的时间段为
|-开始时间：2025-03-05 18:05:38.006348 CST
|-结束时间：2025-03-05 19:00:19.515825 CST

--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:06:20.420863 CST | 事务号：1360118630 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：1 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:38:06.749572 CST | 事务号：1360430105 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：1 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:48:41.947320 CST | 事务号：1360532435 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：50 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:48:41.948734 CST | 事务号：1360532430 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：50 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:48:41.948754 CST | 事务号：1360532434 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：50 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:48:41.948781 CST | 事务号：1360532432 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：50 
--------------------------------------------------------------------------------------------------------------------------------
……………………
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:48:56.872903 CST | 事务号：1360536441 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：50 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:48:57.125587 CST | 事务号：1360536457 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：36 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:56:24.931723 CST | 事务号：1360596835 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：1 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
 时间戳：2025-03-05 18:59:38.314220 CST | 事务号：1360621790 | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：1 
--------------------------------------------------------------------------------------------------------------------------------
postgres.public=#

5.基于上述数据,事务情况,可以确认第一次发生删除时间为2025-03-05 18:48:41.947320,最后删除时间为:2025-03-05 18:48:57.125587,重新设置基于时间的恢复模式

postgres.public=# param starttime 2025-03-05_18:48:40; 
OK! starttime=2025-03-05_18:48:40
postgres.public=# param endtime 2025-03-05_18:49:00;
OK! endtime=2025-03-05_18:49:00
postgres.public=# param resmode time;
OK! restoreMode=time
postgres.public=# show;
---------------------------------------------------
|       参数       |             当前值           |
---------------------------------------------------
|startwal          |                              |
|endwal            |                              |
|starttime         |2025-03-05 18:48:40.000000 CST|
|endtime           |2025-03-05 18:49:00.000000 CST|
|resmode           |             TIME             |
---------------------------------------------------

6.重新扫描需要恢复表

postgres.public=# scan t_delete;

正在扫描表<t_delete>的删除记录...

开始扫描归档目录
|-起始文件<000000030000052500000050>
|-终点文件<0000000300000525000000EF>

正在扫描中

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
当前为 时间区间恢复 模式,将输出该表在时间区间内的所有删除结果
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━



扫描结束,当前扫描的时间段为
|-开始时间：2025-03-05 18:48:40.000000 CST
|-结束时间：2025-03-05 18:49:00.000000 CST

----------------------------------------------------------------------------------------------------------------------------------------------------
 开始时间：2025-03-05 18:48:40.000000 CST | 结束时间：2025-03-05 18:49:00.000000 CST | 数据文件oid：725686 | toast文件oid：725689 |待恢复条数：9636 
----------------------------------------------------------------------------------------------------------------------------------------------------
postgres.public=#

7.确认删除的记录9636条全部被扫描出来,现在对其进行恢复

postgres.public=# restore del all; 
开始扫描归档目录
|-起始文件<000000030000052500000050>
|-终点文件<0000000300000525000000EF>

正在扫描中
|-已解析出9636条数据 

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
解析结果：成功 9636,【失败 0】(即对应FPW未找到), 共计9636条数据
恢复出的csv文件路径为<restore/public/t_delete_2025-03-05 18:48:40.000000 CST_2025-03-05 18:49:00.000000 CST.csv>
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

扫描结束,当前扫描的时间段为
|-开始时间：2025-03-05 18:48:40.000000 CST
|-结束时间：2025-03-05 18:49:00.000000 CST

restore完成
postgres.public=#

8.恢复数据导入库中

[postgres@localhost pdu]$ psql -f postgres/COPY/public_copy.sql
SET
COPY 9636

QQ20250315-235527

.[OnlyBuy@cyberfear.com].REVRAC勒索mysql恢复

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

标题：.[OnlyBuy@cyberfear.com].REVRAC勒索mysql恢复

有朋友接到一个mariadb库被加密的case,部分文件被加密为:.[D2BB58C7].[OnlyBuy@cyberfear.com].REVRAC扩展名

黑客预留的+README-WARNING+.txt内容类似:

YOUR FILES ARE ENCRYPTED

Your files, documents, photos, databases and other important files are encrypted.

You are not able to decrypt it by yourself! The only method of recovering files is to purchase an unique private key.
Only we can give you this key and only we can recover your files.

To be sure we have the decryptor and it works you can send an 
    email: TechSupport@cyberfear.com  and decrypt one file for free.

Before paying you can send us up to 1 file for free decryption. The total size of files must be less than 1Mb 
(non archived), and files should not contain valuable information. (databases,backups, large excel sheets,sql. etc.) 

Do you really want to restore your files?
Write to email: OnlyBuy@cyberfear.com

Your personal ID is indicated in the names of the files and in the end of this message, before writing a message by email
indicate the name of the ID indicated in the files IN THE SUBJECT OF THE EMAIL

Attention!
 * Do not rename encrypted files.
 * Do not try to decrypt your data using third party software, it may cause permanent data loss.
 * Decryption of your files with the help of third parties may cause increased price (they add their fee to our) 
   or you can become a victim of a scam.

YOUR ID: D2BB58C7

通过分析ibd文件没有被破坏

这种情况恢复相对比较简单,可以直接通过对单独ibd文件会的思路进行处理,类似恢复文章:
frm和ibd文件数据库恢复
MySQL 8.0版本ibd文件恢复
[MySQL异常恢复]mysql ibd文件恢复
InnoDB: Cannot open table db/tab from the internal data dictionary of InnoDB though the .frm file for the table exists
当然前提需要有表创建语句,这个客户有昨天的备份的被的.sql备份,通过技术手段分析,确认只有3个表的创建语句丢失,对于丢失的ddl语句,通过直接对ibdata文件解析获取,基于这些信息结合,实现数据的完美恢复

对于类似这种被加密的勒索的数据文件,我们可以实现比较好的恢复效果,如果此类的数据库(oracle,mysql,sql server)等被加密,需要专业恢复技术支持，请联系我们:
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
系统安全防护措施建议：
1.多台机器，不要使用相同的账号和口令
2.登录口令要有足够的长度和复杂性，并定期更换登录口令
3.重要资料的共享文件夹应设置访问权限控制，并进行定期备份
4.定期检测系统和软件中的安全漏洞，及时打上补丁。
5.定期到服务器检查是否存在异常。
6.安装安全防护软件，并确保其正常运行。
7.从正规渠道下载安装软件。
8.对不熟悉的软件，如果已经被杀毒软件拦截查杀，不要添加信任继续运行。
9.保存良好的备份习惯，尽量做到每日备份，异地备份。

表dml操作权限授权给public,导致只读用户失效

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

标题：表dml操作权限授权给public,导致只读用户失效

最近一个客户和我反馈,他们创建了一个只读用户(之时给了create session和select表权限),但是其中有部分表可以执行dml操作,我登录系统进行确认

SQL> SELECT PRIVILEGE, ADMIN_OPTION
  2    FROM DBA_SYS_PRIVS
  3   WHERE GRANTEE =  'ALL_READONLY'
  4  UNION
  5  SELECT PRIVILEGE, ADMIN_OPTION
  6    FROM ROLE_SYS_PRIVS
  7   WHERE ROLE IN
  8         (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE =  'ALL_READONLY')
  9  UNION
 10  SELECT PRIVILEGE, ADMIN_OPTION
 11    FROM ROLE_SYS_PRIVS
 12   WHERE ROLE IN (SELECT GRANTED_ROLE
 13                    FROM ROLE_ROLE_PRIVS
 14                   WHERE ROLE IN (SELECT GRANTED_ROLE
 15                                    FROM DBA_ROLE_PRIVS
 16                                   WHERE GRANTEE = 'ALL_READONLY'));

PRIVILEGE                                ADM
---------------------------------------- ---
CREATE SESSION                           NO

尝试对一个表做dml操作,确实可以对u1.t1表进行dml操作

SQL> conn all_readonly/PASSWORD
Connected.
SQL> update U1.T1 set SNAME='111_test' where sid='www.xifenfei.com';

1 row updated.

SQL> rollback;

Rollback complete.

查看这个表的相关授权,关于all_readonly(只读用户)的授权,也确实只是授权了查询权限

SQL>  SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME  FROM dba_TAB_PRIVS WHERE TABLE_NAME ='T1' and GRANTEE='ALL_READONLY'

GRANTEE              PRIVILEGE                                OWNER                TABLE_NAME
-------------------- ---------------------------------------- -------------------- --------------------
ALL_READONLY         SELECT                                   U1                    T1

既然t1这个表可以被dml操作,那是这个表是否还有其他授权,进一步查询该表授权(不限于ALL_REAONLY用户)

SQL> SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME  FROM dba_TAB_PRIVS WHERE TABLE_NAME ='T1';

GRANTEE              PRIVILEGE                                OWNER                TABLE_NAME
-------------------- ---------------------------------------- -------------------- --------------------
PUBLIC               ALTER                                    U1                    T1
PUBLIC               DELETE                                   U1                    T1
PUBLIC               INDEX                                    U1                    T1
PUBLIC               INSERT                                   U1                    T1
PUBLIC               SELECT                                   U1                    T1
PUBLIC               UPDATE                                   U1                    T1
PUBLIC               REFERENCES                               U1                    T1
PUBLIC               ON COMMIT REFRESH                        U1                    T1
PUBLIC               QUERY REWRITE                            U1                    T1
PUBLIC               DEBUG                                    U1                    T1
PUBLIC               FLASHBACK                                U1                    T1
ALL_READONLY         SELECT                                   U1                    T1

14 rows selected.

这下明确了,由于授权了u1.t1表的(insert,delete,update等)权限给public,导致其他用户也可以对这些表进行授权给public的所有操作.
不管任何原因,都不建议授权表/对象的操作给public,这样会导致登录该数据库的所有用户都具有这个权限,风险不可控

21c数据库恢复遭遇ora-600 ktugct: corruption detected

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

标题：21c数据库恢复遭遇ora-600 ktugct: corruption detected

接手一个oracle 21c的库恢复请求,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本检测之后,发现undo文件offline之后,做了resetlogs操作,导致该文件目前处于WRONG RESETLOGS状态
21c
wrong-resetlogs

尝试恢复数据库ORA-16433错误

SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

2025-03-11T12:07:43.381296+08:00
ALTER DATABASE RECOVER  datafile 1  
2025-03-11T12:07:43.381296+08:00
Media Recovery Start
2025-03-11T12:07:43.381296+08:00
Media Recovery failed with error 16433
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 1  ...

官方对于该错误的解释

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ oerr ora 16433
16433, 00000, "The database must be opened in read/write mode."
// *Cause:  An attempt was made to open the database in read-only mode after an
//          operation that requires that the database be opened in read/write
//          mode.
// *Action: Open the database in read/write mode. The database can then be
//          opened in read-only mode.

对于该错误,可以通过rectl进行解决,但是由于undo文件的resetlog 信息不对,无法直接重建ctl,这种情况下,通过Oracle Recovery Tools小工具对undo文件的resetlogs信息今星期修改

然后重建ctl成功

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 星期二 3月 11 12:15:00 2025
Version 21.3.0.0.0

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


连接到:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLCDB" NORESETLOGS  NOARCHIVELOG
  2         MAXLOGFILES 50
  3         MAXLOGMEMBERS 5
  4         MAXDATAFILES 5000
  5         MAXINSTANCES 8
  6         MAXLOGHISTORY 2920
  7  LOGFILE
  8         group 1   'H:\TEMP\ORCLCDB\REDO01.LOG' size 200M,
  9         group 3   'H:\TEMP\ORCLCDB\REDO03.LOG' size 200M,
 10         group 2   'H:\TEMP\ORCLCDB\REDO02.LOG' size 200M
 11  DATAFILE
 12          'H:\TEMP\ORCLCDB\SYSTEM01.DBF',
 13          'H:\TEMP\ORCLCDB\SYSAUX01.DBF',
 14          'H:\TEMP\ORCLCDB\UNDOTBS01.DBF',
 15          'H:\TEMP\ORCLCDB\PDBSEED\SYSTEM01.DBF',
 16          'H:\TEMP\ORCLCDB\PDBSEED\SYSAUX01.DBF',
 17          'H:\TEMP\ORCLCDB\USERS01.DBF',
 18          'H:\TEMP\ORCLCDB\PDBSEED\UNDOTBS01.DBF',
 19          'H:\TEMP\ORCLCDB\ORCLPDB1\SYSTEM01.DBF',
 20          'H:\TEMP\ORCLCDB\ORCLPDB1\UNDOTBS01.DBF',
 21          'H:\TEMP\ORCLCDB\ORCLPDB1\USERS01.DBF'
 22  CHARACTER SET  AL32UTF8 ;

控制文件已创建。

尝试open库报ORA-600 ktugct: corruption detected错误

SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktugct: corruption detected], [],
[], [], [], [], [], [], [], [], [], []
进程 ID: 8372
会话 ID: 978 序列号: 21772

通过对启动过程进行分析,确认是在seq$表访问的时候报错

PARSING IN CURSOR #2238117495280 len=102 dep=1 uid=0 oct=3 lid=0 tim=821317050254 
 hv=3967354608 ad='7ff9048c41b8' sqlid='axmdf8vq7k1rh'
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
END OF STMT
PARSE #2238117495280:c=6253,e=6253,p=5,cr=90,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=821317050254
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=1
EXEC #2238117156016:c=95,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317050580
FETCH #2238117156016:c=8,e=8,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317050600
CLOSE #2238117156016:c=3,e=3,dep=2,type=3,tim=821317050619
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=2
EXEC #2238117156016:c=87,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317050746
FETCH #2238117156016:c=7,e=7,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317050764
CLOSE #2238117156016:c=3,e=3,dep=2,type=3,tim=821317050781
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=3
EXEC #2238117156016:c=84,e=84,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317050885
FETCH #2238117156016:c=6,e=6,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317050902
CLOSE #2238117156016:c=2,e=2,dep=2,type=3,tim=821317050917
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=4
EXEC #2238117156016:c=83,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317051019
FETCH #2238117156016:c=5,e=5,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317051035
CLOSE #2238117156016:c=3,e=2,dep=2,type=3,tim=821317051050
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=5
EXEC #2238117156016:c=88,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317051158
FETCH #2238117156016:c=5,e=5,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317051174
CLOSE #2238117156016:c=2,e=2,dep=2,type=3,tim=821317051190
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=6
EXEC #2238117156016:c=83,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317051292
FETCH #2238117156016:c=5,e=6,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317051308
CLOSE #2238117156016:c=2,e=2,dep=2,type=3,tim=821317051323
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=7
EXEC #2238117156016:c=83,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317051425
FETCH #2238117156016:c=5,e=5,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317051440
CLOSE #2238117156016:c=3,e=3,dep=2,type=3,tim=821317051456
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=8
EXEC #2238117156016:c=83,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317051558
FETCH #2238117156016:c=5,e=5,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317051573
CLOSE #2238117156016:c=2,e=2,dep=2,type=3,tim=821317051588
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=9
EXEC #2238117156016:c=82,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317051688
FETCH #2238117156016:c=5,e=5,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317051704
CLOSE #2238117156016:c=2,e=2,dep=2,type=3,tim=821317051719
BINDS #2238117156016:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f628  bln=22  avl=02  flg=01
  value=10
EXEC #2238117156016:c=82,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=821317051821
FETCH #2238117156016:c=5,e=5,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=3,plh=2239883476,tim=821317051837
CLOSE #2238117156016:c=2,e=2,dep=2,type=3,tim=821317051852
BINDS #2238117508144:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=1a33f5f8  bln=22  avl=02  flg=05
  value=100
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1a33f610  bln=22  avl=02  flg=01
  value=10
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=1a33f628  bln=22  avl=01  flg=01
  value=0
EXEC #2238117508144:c=144,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=3312420081,tim=821317052013
FETCH #2238117508144:c=164,e=164,p=1,cr=3,cu=0,mis=0,r=3,dep=2,og=3,plh=3312420081,tim=821317052189
CLOSE #2238117508144:c=4,e=4,dep=2,type=3,tim=821317052208
BINDS #2238117495280:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=1a33f628  bln=22  avl=03  flg=05
  value=1749
EXEC #2238117495280:c=2211,e=2211,p=1,cr=33,cu=0,mis=1,r=0,dep=1,og=4,plh=2203911306,tim=821317052521
WAIT #2238117495280: nam='db file sequential read' ela= 82 file#=1 block#=1201 blocks=1 obj#=105 tim=821317052620
WAIT #2238117495280: nam='db file sequential read' ela= 78 file#=1 block#=1202 blocks=1 obj#=105 tim=821317052723
WAIT #2238117495280: nam='db file sequential read' ela= 77 file#=1 block#=1161 blocks=1 obj#=100 tim=821317052824
ktuisc detected lost changes to transaction table:
 xid of interest:  xid: 0x0003.00c.0006e788 xid from block :  xid: 0x0003.00c.0006e785
2025-03-11T12:18:43.210707+08:00
Incident 25673 created, dump file: C:\APP\XFF\diag\rdbms\orclcdb\o21c\incident\incdir_25673\o21c_ora_12976_i25673.trc
ORA-00600: 内部错误代码, 参数: [ktugct: corruption detected], [], [], [], [], [], [], [], [], [], [], []

FETCH #2238117495280:c=1350284,e=1359602,p=3,cr=4,cu=0,mis=0,r=0,dep=1,og=4,plh=2203911306,tim=821318412135
STAT #2238117495280 id=1 cnt=0 pid=0 pos=1 obj=100 
  op='TABLE ACCESS BY INDEX ROWID SEQ$ (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=73 card=1)'
STAT #2238117495280 id=2 cnt=1 pid=1 pos=1 obj=105 
  op='INDEX UNIQUE SCAN I_SEQ1 (cr=2 pr=2 pw=0 str=1 time=209 us cost=1 size=0 card=1)'
<error barrier> at 0x0000003197FF21D8 placed dbsdrv.c@5035
ORA-00600: 内部错误代码, 参数: [ktugct: corruption detected], [], [], [], [], [], [], [], [], [], [], []
<error barrier> at 0x0000003197FF21D8 placed dbsdrv.c@5035
ORA-00600: 内部错误代码, 参数: [ktugct: corruption detected], [], [], [], [], [], [], [], [], [], [], []
2025-03-11T12:18:44.572180+08:00
Incident 25674 created, dump file: C:\APP\XFF\diag\rdbms\orclcdb\o21c\incident\incdir_25674\o21c_ora_12976_i25674.trc
ORA-00603: ORACLE 服务器会话因致命错误而终止
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [ktugct: corruption detected], [], [], [], [], [], [], [], [], [], [], []

进一步分析trace信息

[TOC00001]
ORA-00600: 内部错误代码, 参数: [ktugct: corruption detected], [], [], [], [], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 25673 (ORA 600 [ktugct: corruption detected]) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
Corruption or lost changes detected in transaction table
Cleanout struct - flg: 0x00000000 ne = 1 cu: 0 cv: 1 cec:0 actcnt:0
xids being resolved:
 xid: 0x0003.00c.0006e788 sit: 2
inverse itl idx to cleanout entry mapping:
Dump of memory from 0x000002091036B32A to 0x000002091036B429

Block cleanout record, scn: 0xffffffffffffffff ver: 0x01 opt: 0x01
, entries follow...
-- Start of buffer-cache dump for undo segment header --
Dump of buffer cache at level 10 for pdb=1 tsn=2 rdba=16777376
BH (0x7ffaccf5e9e0) file#: 4 rdba: 0x010000a0 (4/160) class: 21 ba: 0x7ffacc17c000
  set: 57 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f
  hash: [0x7ff91759e230,0x7ff91759e230] lru: [0x7ffaccf5ec30,0x7ffaccf5e960]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x7ff9041e1708,0x7ff9041e1708] objaq: [0x7ff9041e16f8,0x7ff9041e16f8] qhead: 0x7ff9041e16e8
  st: XCURRENT md: NULL fpin: 'ktuwh117: ktugus:kturec' fscn: 0x1a321837 tch: 1
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535] 
  Printing buffer operation history (latest change first): (cnt 10)
  01. sid:14 L353:gcur:set:MEXCL      02. sid:14 L145:zib:mk:EXCL       
  03. sid:14 L212:zib:bic:FSQ         04. sid:14 L122:zgb:set:st        
  05. sid:14 L830:olq1:clr:WRT+CKT    06. sid:14 L951:zgb:lnk:objq      
  07. sid:14 L372:zgb:set:MEXCL       08. sid:14 L123:zgb:no:FEN        
  09. sid:14 L083:zgb:ent:fn          10. sid:00 L203:w_ini_dc:bic:FVB  
  buffer tsn: 2 rdba: 0x010000a0 (4/160)
  scn: 0x192ea609 seq: 0x01 flg: 0x04 tail: 0xa6092601
  frmt: 0x02 chkval: 0x3395 type: 0x26=KTU SMU HEADER BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FFACC17C000 to 0x00007FFACC17E000
7FFACC17C000 0000A226 010000A0 192EA609 04010000  [&...............]
7FFACC17C010 00003395 00000000 00000000 00000000  [.3..............]
7FFACC17C020 00000000 00000004 0000010F 00000FF0  [................]
7FFACC17C030 00000000 00000000 00000007 010000A1  [................]
7FFACC17C040 00000000 00000000 00000000 00000000  [................]
7FFACC17C050 00000000 00000000 00000000 00000004  [................]
7FFACC17C060 00000000 00000000 40000000 010000A1  [...........@....]
7FFACC17C070 00000007 010000B8 00000008 01000780  [................]
7FFACC17C080 00000080 01000180 00000080 00000000  [................]
7FFACC17C090 00000000 00000000 00000000 00000000  [................]
        Repeat 246 times
7FFACC17D000 00000000 67770DDD 6776D1BB 6776EEC1  [......wg..vg..vg]
7FFACC17D010 67770DDD 6775F337 6775F337 6775F34B  [..wg7.ug7.ugK.ug]
7FFACC17D020 67734C0B 676F7532 676CB861 6734082B  [.Lsg2uoga.lg+.4g]
7FFACC17D030 6734082B 671AD100 671AD100 671AD100  [+.4g...g...g...g]
7FFACC17D040 671AD100 671AA6DC 67014F31 67014F31  [...g...g1O.g1O.g]
7FFACC17D050 67014F31 67014F31 67014F31 62BB38EE  [1O.g1O.g1O.g.8.b]
7FFACC17D060 62BB38EE 62BB38EE 62BB38EE 62BB38EE  [.8.b.8.b.8.b.8.b]
        Repeat 1 times
7FFACC17D080 62BB38EE 62BB38EE 62BB0EE3 62BB0EE3  [.8.b.8.b...b...b]
7FFACC17D090 62BB0EE3 62BB0EE3 62BB0EE3 62BB0EE3  [...b...b...b...b]
        Repeat 14 times
7FFACC17D180 62BB0EE3 00000000 00000000 00000000  [...b............]
7FFACC17D190 00000000 00000000 00000000 00000000  [................]
        Repeat 99 times
7FFACC17D7D0 192EA31E 00000000 010000A1 001E3150  [............P1..]
7FFACC17D7E0 31500001 00000001 00000000 00080004  [..P1............]
7FFACC17D7F0 0001B000 00000068 7FFFFFFE 010000A1  [....h...........]
7FFACC17D800 001E3150 055E0000 00000000 0007314E  [P1....^.....N1..]
7FFACC17D810 0CEC0002 00000000 000B3146 1A180002  [........F1......]
7FFACC17D820 00000000 00173103 12B40002 00000000  [.....1..........]
7FFACC17D830 00000000 00000000 0006E7A0 010000A1  [................]
7FFACC17D840 192EA53E 00000000 00100009 00000000  [>...............]
7FFACC17D850 00000000 00000000 00000001 67770DEE  [..............wg]
7FFACC17D860 0006E7AF 010000A1 192EA4E4 00000000  [................]
7FFACC17D870 000F0009 00000000 00000000 00000000  [................]
7FFACC17D880 00000001 67770DE8 0006E790 010000A1  [......wg........]
7FFACC17D890 192EA58D 00000000 001F0009 00000000  [................]
7FFACC17D8A0 00000000 00000000 00000001 67770DF3  [..............wg]
7FFACC17D8B0 0006E797 010001FF 192EA431 00000000  [........1.......]
7FFACC17D8C0 000B0009 00000000 00000000 00000000  [................]
7FFACC17D8D0 00000001 67770DDC 0006E795 010001FF  [......wg........]
7FFACC17D8E0 192EA33D 00000000 000D0009 00000000  [=...............]
7FFACC17D8F0 00000000 00000000 00000001 67770DCC  [..............wg]
7FFACC17D900 0006E79A 010000A1 192EA4D1 00000000  [................]
7FFACC17D910 00160009 00000000 00000000 00000000  [................]
7FFACC17D920 00000001 67770DE6 0006E792 010000A1  [......wg........]
7FFACC17D930 192EA472 00000000 000A0009 00000000  [r...............]
7FFACC17D940 00000000 00000000 00000001 67770DE0  [..............wg]
7FFACC17D950 0006E7A6 010001FF 192EA3FB 00000000  [................]
7FFACC17D960 00090009 00000000 00000000 00000000  [................]
7FFACC17D970 00000001 67770DD8 0006E7A7 010000A1  [......wg........]
7FFACC17D980 192EA609 00000000 FFFF0009 00000000  [................]
7FFACC17D990 00000000 00000000 00000001 67770DFB  [..............wg]
7FFACC17D9A0 0006E79C 010001FF 192EA41A 00000000  [................]
7FFACC17D9B0 00030009 00000000 00000000 00000000  [................]
7FFACC17D9C0 00000001 67770DDA 0006E79E 010000A1  [......wg........]
7FFACC17D9D0 192EA481 00000000 00150009 00000000  [................]
7FFACC17D9E0 00000000 00000000 00000001 67770DE1  [..............wg]
7FFACC17D9F0 0006E7A3 010000A1 192EA44C 00000000  [........L.......]
7FFACC17DA00 00190009 00000000 00000000 00000000  [................]
7FFACC17DA10 00000002 67770DDD 0006E785 010001FF  [......wg........]
7FFACC17DA20 192EA391 00000000 00180009 00000000  [................]
7FFACC17DA30 00000000 00000000 00000001 67770DD1  [..............wg]
7FFACC17DA40 0006E7AB 010001FF 192EA355 00000000  [........U.......]
7FFACC17DA50 001A0009 00000000 00000000 00000000  [................]
7FFACC17DA60 00000001 67770DCD 0006E793 010000A1  [......wg........]
7FFACC17DA70 192EA51F 00000000 00000009 00000000  [................]
7FFACC17DA80 00000000 00000000 00000001 67770DEC  [..............wg]
7FFACC17DA90 0006E7AD 010000A1 192EA4FA 00000000  [................]
7FFACC17DAA0 000E0009 00000000 00000000 00000000  [................]
7FFACC17DAB0 00000001 67770DE9 0006E794 010000A1  [......wg........]
7FFACC17DAC0 192EA550 00000000 00130009 00000000  [P...............]
7FFACC17DAD0 00000000 00000000 00000001 67770DEF  [..............wg]
7FFACC17DAE0 0006E792 010000A1 192EA5DF 00000000  [................]
7FFACC17DAF0 00210009 00000000 00000000 00000000  [..!.............]
7FFACC17DB00 00000001 67770DF9 0006E766 010000A1  [......wgf.......]
7FFACC17DB10 192EA5C2 00000000 001E0009 00000000  [................]
7FFACC17DB20 00000000 00000000 00000001 67770DF7  [..............wg]
7FFACC17DB30 0006E787 010000A1 192EA561 00000000  [........a.......]
7FFACC17DB40 001C0009 00000000 00000000 00000000  [................]
7FFACC17DB50 00000001 67770DF0 0006E7A3 010001FF  [......wg........]
7FFACC17DB60 192EA3D2 00000000 00070009 00000000  [................]
7FFACC17DB70 00000000 00000000 00000001 67770DD6  [..............wg]
7FFACC17DB80 0006E7A5 010000A1 192EA498 00000000  [................]
7FFACC17DB90 00200009 00000000 00000000 00000000  [.. .............]
7FFACC17DBA0 00000001 67770DE2 0006E791 010000A1  [......wg........]
7FFACC17DBB0 192EA4DD 00000000 00010009 00000000  [................]
7FFACC17DBC0 00000000 00000000 00000001 67770DE7  [..............wg]
7FFACC17DBD0 0006E79F 010000A1 192EA5F6 00000000  [................]
7FFACC17DBE0 00080009 00000000 00000000 00000000  [................]
7FFACC17DBF0 00000001 67770DFA 0006E78A 010001FF  [......wg........]
7FFACC17DC00 192EA3A7 00000000 001D0009 00000000  [................]
7FFACC17DC10 00000000 00000000 00000001 67770DD3  [..............wg]
7FFACC17DC20 0006E797 010000A1 192EA45D 00000000  [........].......]
7FFACC17DC30 00060009 00000000 00000000 00000000  [................]
7FFACC17DC40 00000001 67770DDF 0006E796 010001FF  [......wg........]
7FFACC17DC50 192EA368 00000000 001B0009 00000000  [h...............]
7FFACC17DC60 00000000 00000000 00000001 67770DCF  [..............wg]
7FFACC17DC70 0006E78E 010001FF 192EA37D 00000000  [........}.......]
7FFACC17DC80 000C0009 00000000 00000000 00000000  [................]
7FFACC17DC90 00000001 67770DD0 0006E798 010000A1  [......wg........]
7FFACC17DCA0 192EA56F 00000000 00020009 00000000  [o...............]
7FFACC17DCB0 00000000 00000000 00000001 67770DF1  [..............wg]
7FFACC17DCC0 0006E7A1 010001FF 192EA3B5 00000000  [................]
7FFACC17DCD0 00140009 00000000 00000000 00000000  [................]
7FFACC17DCE0 00000001 67770DD4 0006E7A2 010000A1  [......wg........]
7FFACC17DCF0 192EA5CE 00000000 00110009 00000000  [................]
7FFACC17DD00 00000000 00000000 00000001 67770DF7  [..............wg]
7FFACC17DD10 0006E794 010000A1 192EA5A0 00000000  [................]
7FFACC17DD20 00120009 00000000 00000000 00000000  [................]
7FFACC17DD30 00000001 67770DF4 0006E7A2 010000A1  [......wg........]
7FFACC17DD40 192EA4A5 00000000 00050009 00000000  [................]
7FFACC17DD50 00000000 00000000 00000001 67770DE3  [..............wg]
7FFACC17DD60 0006E7A5 010000A1 192EA5E9 00000000  [................]
7FFACC17DD70 00170009 00000000 00000000 00000000  [................]
7FFACC17DD80 00000001 67770DF9 00000003 00000000  [......wg........]
7FFACC17DD90 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7FFACC17DDB0 00000001 00000000 00000000 00000000  [................]
        Repeat 33 times
7FFACC17DFD0 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7FFACC17DFF0 00000000 00000000 00000000 A6092601  [.............&..]
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 4      #blocks: 271   
                  last map  0x00000000  #maps: 0      offset: 4080  
      Highwater::  0x010000a1  ext#: 0      blk#: 0      ext size: 7     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 0     
                   Unlocked
     Map Header:: next  0x00000000  #extents: 4    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x010000a1  length: 7     
   0x010000b8  length: 8     
   0x01000780  length: 128   
   0x01000180  length: 128   
  
 Retention Table 
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1735855581
 Extent Number:1  Commit Time: 1735840187
 Extent Number:2  Commit Time: 1735847617
 Extent Number:3  Commit Time: 1735855581
  
  TRN CTL:: seq: 0x3150 chd: 0x0004 ctl: 0x0008 inc: 0x00000000 nfb: 0x0001
            mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x010000a1.3150.1e scn: 0x00000000192ea31e
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x010000a1.3150.1e ext: 0x0  spc: 0x55e   
    uba: 0x00000000.314e.07 ext: 0x2  spc: 0xcec   
    uba: 0x00000000.3146.0b ext: 0x2  spc: 0x1a18  
    uba: 0x00000000.3103.17 ext: 0x2  spc: 0x12b4  
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0     
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  -----------------------------------------------------------------------------------------
   0x00  9 0x00  0x6e7a0  0x0010  0x00000000192ea53e  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855598
   0x01  9 0x00  0x6e7af  0x000f  0x00000000192ea4e4  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855592
   0x02  9 0x00  0x6e790  0x001f  0x00000000192ea58d  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855603
   0x03  9 0x00  0x6e797  0x000b  0x00000000192ea431  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855580
   0x04  9 0x00  0x6e795  0x000d  0x00000000192ea33d  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855564
   0x05  9 0x00  0x6e79a  0x0016  0x00000000192ea4d1  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855590
   0x06  9 0x00  0x6e792  0x000a  0x00000000192ea472  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855584
   0x07  9 0x00  0x6e7a6  0x0009  0x00000000192ea3fb  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855576
   0x08  9 0x00  0x6e7a7  0xffff  0x00000000192ea609  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855611
   0x09  9 0x00  0x6e79c  0x0003  0x00000000192ea41a  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855578
   0x0a  9 0x00  0x6e79e  0x0015  0x00000000192ea481  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855585
   0x0b  9 0x00  0x6e7a3  0x0019  0x00000000192ea44c  0x010000a1 0x0000.000.00000000  0x00000002 0x00000000 1735855581
   0x0c  9 0x00  0x6e785  0x0018  0x00000000192ea391  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855569
   0x0d  9 0x00  0x6e7ab  0x001a  0x00000000192ea355  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855565
   0x0e  9 0x00  0x6e793  0x0000  0x00000000192ea51f  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855596
   0x0f  9 0x00  0x6e7ad  0x000e  0x00000000192ea4fa  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855593
   0x10  9 0x00  0x6e794  0x0013  0x00000000192ea550  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855599
   0x11  9 0x00  0x6e792  0x0021  0x00000000192ea5df  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855609
   0x12  9 0x00  0x6e766  0x001e  0x00000000192ea5c2  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855607
   0x13  9 0x00  0x6e787  0x001c  0x00000000192ea561  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855600
   0x14  9 0x00  0x6e7a3  0x0007  0x00000000192ea3d2  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855574
   0x15  9 0x00  0x6e7a5  0x0020  0x00000000192ea498  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855586
   0x16  9 0x00  0x6e791  0x0001  0x00000000192ea4dd  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855591
   0x17  9 0x00  0x6e79f  0x0008  0x00000000192ea5f6  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855610
   0x18  9 0x00  0x6e78a  0x001d  0x00000000192ea3a7  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855571
   0x19  9 0x00  0x6e797  0x0006  0x00000000192ea45d  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855583
   0x1a  9 0x00  0x6e796  0x001b  0x00000000192ea368  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855567
   0x1b  9 0x00  0x6e78e  0x000c  0x00000000192ea37d  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855568
   0x1c  9 0x00  0x6e798  0x0002  0x00000000192ea56f  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855601
   0x1d  9 0x00  0x6e7a1  0x0014  0x00000000192ea3b5  0x010001ff 0x0000.000.00000000  0x00000001 0x00000000 1735855572
   0x1e  9 0x00  0x6e7a2  0x0011  0x00000000192ea5ce  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855607
   0x1f  9 0x00  0x6e794  0x0012  0x00000000192ea5a0  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855604
   0x20  9 0x00  0x6e7a2  0x0005  0x00000000192ea4a5  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855587
   0x21  9 0x00  0x6e7a5  0x0017  0x00000000192ea5e9  0x010000a1 0x0000.000.00000000  0x00000001 0x00000000 1735855609
  EXT TRN CTL::
  usn: 3
  sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
  sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
  EXT TRN TBL::
  index      extflag    extHash    extPdbid/Spare1 extSpare2
  -----------------------------------------------------------
   0x00    0x00000000   0x00000000   0x00000001    0x00000000
   0x01    0x00000000   0x00000000   0x00000001    0x00000000
   0x02    0x00000000   0x00000000   0x00000001    0x00000000
   0x03    0x00000000   0x00000000   0x00000001    0x00000000
   0x04    0x00000000   0x00000000   0x00000001    0x00000000
   0x05    0x00000000   0x00000000   0x00000001    0x00000000
   0x06    0x00000000   0x00000000   0x00000001    0x00000000
   0x07    0x00000000   0x00000000   0x00000001    0x00000000
   0x08    0x00000000   0x00000000   0x00000001    0x00000000
   0x09    0x00000000   0x00000000   0x00000001    0x00000000
   0x0a    0x00000000   0x00000000   0x00000001    0x00000000
   0x0b    0x00000000   0x00000000   0x00000001    0x00000000
   0x0c    0x00000000   0x00000000   0x00000001    0x00000000
   0x0d    0x00000000   0x00000000   0x00000001    0x00000000
   0x0e    0x00000000   0x00000000   0x00000001    0x00000000
   0x0f    0x00000000   0x00000000   0x00000001    0x00000000
   0x10    0x00000000   0x00000000   0x00000001    0x00000000
   0x11    0x00000000   0x00000000   0x00000001    0x00000000
   0x12    0x00000000   0x00000000   0x00000001    0x00000000
   0x13    0x00000000   0x00000000   0x00000001    0x00000000
   0x14    0x00000000   0x00000000   0x00000001    0x00000000
   0x15    0x00000000   0x00000000   0x00000001    0x00000000
   0x16    0x00000000   0x00000000   0x00000001    0x00000000
   0x17    0x00000000   0x00000000   0x00000001    0x00000000
   0x18    0x00000000   0x00000000   0x00000001    0x00000000
   0x19    0x00000000   0x00000000   0x00000001    0x00000000
   0x1a    0x00000000   0x00000000   0x00000001    0x00000000
   0x1b    0x00000000   0x00000000   0x00000001    0x00000000
   0x1c    0x00000000   0x00000000   0x00000001    0x00000000
   0x1d    0x00000000   0x00000000   0x00000001    0x00000000
   0x1e    0x00000000   0x00000000   0x00000001    0x00000000
   0x1f    0x00000000   0x00000000   0x00000001    0x00000000
   0x20    0x00000000   0x00000000   0x00000001    0x00000000
   0x21    0x00000000   0x00000000   0x00000001    0x00000000
Dump of buffer cache for pdb 1 tsn 2 rdba 0x10000a0 at level 10 done.

基于上述信息基本上可以确认是由于我们修复的undo文件和seq$中的file#=1 block#=1161不匹配导致该问题,对其进行处理之后,数据库open成功

2025-03-11T13:12:52.837511+08:00
alter database open
2025-03-11T13:12:52.860336+08:00
Smart fusion block transfer is disabled:
  instance mounted in exclusive mode.
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED 
2025-03-11T13:12:52.864333+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2025-03-11T13:12:52.864333+08:00
Crash Recovery excluding pdb 3 which was cleanly closed.
Endian type of dictionary set to little
2025-03-11T13:12:52.886321+08:00
Redo log for group 1, sequence 7 is not located on DAX storage
Thread 1 opened at log sequence 7
  Current log# 1 seq# 7 mem# 0: H:\TEMP\ORCLCDB\REDO01.LOG
Successful open of redo thread 1
2025-03-11T13:12:52.894310+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Undo initialization recovery: Parallel FPTR complete: start:824531531 end:824531531 diff:0 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 824531531 end: 824531531 diff: 0 ms (0.0 seconds)
[13696] Successfully onlined Undo Tablespace 5.
Undo initialization online undo segments: err:0 start: 824531531 end: 824531609 diff: 78 ms (0.1 seconds)
Undo initialization finished serial:0 start:824531531 end:824531609 diff:78 ms (0.1 seconds)
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process RCBG
2025-03-11T13:12:53.502478+08:00
RCBG started with pid=48, OS id=17560_12292 
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
2025-03-11T13:12:53.724661+08:00
AQPC started with pid=49, OS id=17560_12948 
PDB$SEED(2):Pluggable database PDB$SEED opening in read only
PDB$SEED(2):Autotune of undo retention is turned on. 
PDB$SEED(2):Endian type of dictionary set to little
PDB$SEED(2):Undo initialization finished serial:0 start:824532375 end:824532375 diff:0 ms (0.0 seconds)
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
PDB$SEED(2):*********************************************************************
PDB$SEED(2):WARNING: The following temporary tablespaces in container(PDB$SEED)
PDB$SEED(2):         contain no files.
PDB$SEED(2):         This condition can occur when a backup controlfile has
PDB$SEED(2):         been restored.  It may be necessary to add files to these
PDB$SEED(2):         tablespaces.  That can be done using the SQL statement:
PDB$SEED(2): 
PDB$SEED(2):         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
PDB$SEED(2): 
PDB$SEED(2):         Alternatively, if these temporary tablespaces are no longer
PDB$SEED(2):         needed, then they can be dropped.
PDB$SEED(2):           Empty temporary tablespace: TEMP
PDB$SEED(2):*********************************************************************
2025-03-11T13:12:53.936415+08:00
PDB$SEED(2):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x0, new 0x0 (no suplog)
PDB$SEED(2):Opening pdb with no Resource Manager plan active
Starting background process CJQ0
2025-03-11T13:12:54.249096+08:00
CJQ0 started with pid=77, OS id=17560_17968 
Completed: alter database open

所幸客户需要的数据直接在cdb中,直接使用expdp导出客户需要的数据,完成本次恢复

pg_control丢失/损坏处理

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

标题：pg_control丢失/损坏处理

-l ：设置下一个wal日志名
可以通过 $PGDATA/pg_wal 目录中查找数值最新的WAL段的文件名+1

[postgres@localhost pg_wal]$ ls
00000001000000000000008E  archive_status

-l 00000001000000000000008F

-m：设置下一个和最旧的事务ID
mxid1：下一个多事务ID的安全值可以通过在 $PGDATA/pg_multixact/offsets目录中查找数值最大的文件名+1，然后乘以65536（0×10000）来确定。
mxid2：最旧的事务ID的安全值可以通过$PGDATA/pg_multixact/offsets目录中数字最小的文件名+1，乘以65536（0×10000）来确定。文件名是十六进制

[postgres@localhost pg_wal]$ cd ../pg_multixact
[postgres@localhost pg_multixact]$ ls
members  offsets
[postgres@localhost pg_multixact]$ cd offsets/
[postgres@localhost offsets]$ ls
0000

-m 0x10000,0x10000

-O：设置下一个多事务处理偏移量
安全值可以通过在 $PGDATA/pg_multixact/members 目录中查找数值最大的文件名，+1，然后乘以52352（0xCC80）来确定。文件名为十六进制

[postgres@localhost members]$ ls
0000

-O 0xCC80

-x：设置下一个事务ID
安全值可以通过在$PGDATA/pg_xact目录中查找数值最大的文件名，+1，然后乘以1048576（0×100000）来确定。请注意，文件名是十六进制

[postgres@localhost pg_xact]$ ls -ltr
total 504
-rw------- 1 postgres postgres 262144 Mar  8 01:53 0000
-rw------- 1 postgres postgres 253952 Mar  9 10:54 0001

-x 0x200000

删除postmaster.pid文件
如果正常关闭库,该文件会被自动删除,异常关闭的才需要处理

[postgres@localhost pg_wal]$ pg_resetwal -l 00000001000000000000008F -m 0x10000,0x10000 -O 0xCC80 -x 0x200000 -f $PGDATA
pg_resetwal: error: lock file "postmaster.pid" exists
pg_resetwal: hint: Is a server running?  If not, delete the lock file and try again.
[postgres@localhost pg_wal]$ cd ../
[postgres@localhost data]$ ls -l postmaster.pid
-rw------- 1 postgres postgres 75 Mar  9 11:02 postmaster.pid
[postgres@localhost data]$ rm -rf postmaster.pid

touch pg_control文件

[postgres@localhost data]$ pg_resetwal -l 00000001000000000000008F -m 0x10000,0x10000 -O 0xCC80 -x 0x200000 -f $PGDATA
pg_resetwal: error: could not open file "global/pg_control" for reading: No such file or directory
pg_resetwal: hint: If you are sure the data directory path is correct, execute
  touch global/pg_control
and try again.
[postgres@localhost data]$ touch global/pg_control

重建pg_control

[postgres@localhost data]$ pg_resetwal -l 00000001000000000000008F -m 0x10000,0x10000 -O 0xCC80 -x 0x200000 -f $PGDATA
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Write-ahead log reset
[postgres@localhost data]$ pg_ctl start
waiting for server to start....
 done
server started

当前主流数据库版本服务支持周期-202503

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

标题：当前主流数据库版本服务支持周期-202503

在最新的”当前数据库版本的发行时间表 (Doc ID 1626244.1)”文档中,oracle官方更新的数据库产品的支持周期,最重要的一点就是oracle 19c标准服务延期到2029年12月31日
DBROADMAP12-2-2024

版本

补丁结束日期

注意和例外

23ai

Long Term Release

Premier Support – 2032年12月31日

Extended Support - 时间待定

由于此次发布中突破性人工智能技术的重要性，我们将其从 Oracle 数据库 23c 重新命名为 Oracle 数据库 23ai
现在可在云上、Oracle Exadata 和 Oracle Database Appliance 中使用。请参阅下方的 Oracle 数据库日程表中的详细信息
Premier Support将于 2031 年 12 月 31 日结束
通过扩展支持或者ULA进行错误修正/补丁的截止日期待定

21c

Innovation Release

2027年7月31日

错误更正/补丁有效期至2027年7月31日
21c没有资格申请 Extended Support(ES)
21c只提供 Release Updates (RUs) 补丁
21c不适用于Exadata Database Service

19c

Long Term Release

2029年12月31日，没有ES/ULA

2032年12月31日，有ES/ULA

Premier Support（PS）将于2029年12月31日结束。扩展支持（ES）将从2030年1月1日持续到2032年12月31日。
错误更正/补丁，付费的ES可到2032年12月31日；没有付费的ES，有效期到2029年12月31日
从 2022 年 10 月的补丁周期开始，19.17.0 及更高版本将不再提供 19c RUR。在 2023 年 1 月交付 Oracle Database 19c RUR 19.16.2 之后，不会在任何平台上交付额外的 RUR。更多细节请参考文档 Sunsetting of 19c RURs and FAQ (Doc ID 2898381.1))
为了让客户更频繁地访问推荐和经过良好测试的补丁集合，Oracle 从 2022 年 11 月开始推出 Monthly Recommended Patches (MRP)。 MRP 仅支持 Linux x86-64 平台。(更多细节请参考文档 Introducing Monthly Recommended Patches (MRPs) and FAQ (Doc ID 2898740.1))

18c

Innovation Release

2021年6月30日

错误更正/补丁有效期至2021年6月30日，18c已进入 Sustaining Support 阶段。
18c没有资格申请 Extended Support(ES)
18c 在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上不受支持。

12.2.0.1

2022年3月31日

Upgrade Support (Restricted Availability) Jan 1, 2024- Dec 31, 2025 - 具体请联系 CSS

这个版本的错误更正/补丁已经结束
12.2.0.1 没有资格申请 Extended Support(ES)
在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上运行 12.2.0.1 需要购买 Upgrade Support (Restricted Availability)(旧称MDS) 服务

12.1.0.2

最终版本

2022年7月31日，有付费的ES, ULA, 或者减免费用的 EBS

Dec 31, 2025 (Upgrade Support (Restricted Availability)- 具体请联系 CSS)

这个版本的错误更正/补丁已经结束
Premier Support(PS)截止至2018年7月31日，为期一年的免费 Extended Support(ES)有效期至2019年7月31日
从 2019年8月1日至 2022年7月31日，需要ES费用或ULA. 没有付费的 ES or ULA, 补丁截止于 2019年7月31日
我们为电子商务客户提供全球ES uplift 费用减免，详情和到期日期见: Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite (Doc ID 2522948.1) 或技术支持政策文件

Apple Macintosh 平台补丁结束日期为2021年7月31日
微软Windows平台: 对于 12.1.0.2 Database, Oracle 在 Microsoft Windows 2008 上运行 12.1.0.2 Database。这个平台的 end-of-life support 是 January 14, 2020。 甲骨文做出了合理的努力，在2022年7月之前为Windows上的数据库12.1.0.2提供补丁，但这种支持已经过期。
在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上运行 12.1.0.2 需要购买 Upgrade Support (Restricted Availability)(旧称MDS) 服务

12.1.0.1

2016年8月31日

这个版本的错误更正/补丁已经结束
12.1.0.1 没有资格申请 Extended Support (ES)
12.1.0.1 是 Standard Edition (SE) 和 Standard Edition One (SE1) 的最后一个版本

11.2.0.4

最终版本 for 11.2

2020年12月31日（有偿扩展支持或 ULA扩展支持或减免费用的EBS）
2025年12月31日（Upgrade Support (Restricted Availability) - 具体请联系 CSS)
2021年12月31日适用于OpenVMS平台

Premier Support (PS)截止到2015年1月31日，而为期一年的免费Extended Support(ES)持续到2018年12月31日。
从2019年1月1日开始到2020年12月31日，将需要ES费用或ULA。
Oracle为电子商务客户提供全球ES uplift 费用减免，详情和到期日期见: Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite (Doc ID 2522948.1) 或技术支持政策文件。
第1代 ExaCC, OCC DBCS, and ODA 将拥有额外3个月的支持周期. 这些平台上的数据库的支持周期截止到: 2021年3月31日。可以创建新实例，直到扩展支持终止为止。但是，Oracle不承诺在支持终止后任何11.2.0.4 DBCS实例将继续运行。
市场驱动支持（Market Driven Support）提供以下数据库云服务：第1代和第2代ExaCC，OCC DBCS，OCI DBCS，OCI ExaCS。直到市场驱动支持终止（2021年12月31日），可以创建新实例。 Oracle不承诺在Upgrade Support(旧称MDS)支持终止后任何11.2.0.4 DBCS实例将继续运行。市场驱动支持不适用于PSM-based OCI DBCS,OCI-C DBCS和OCI-C ExaCS。
在 Exadata Database Service、Base Database Service 或 Exadata Cloud@Customer 上运行 11.2.0.4 需要购买 Upgrade Support (Restricted Availability)(旧称MDS) 服务
11.2.0.4是OpenVMS上的最终版本。在2021日历年中，除了标准的专业支持费用之外无需其他费用，客户能够收到重要度1的修复程序和安全更新。涵盖范围不包括新认证，第三方产品或任何Java/JDK功能（包括数据库中嵌入的Java组件）。涵盖范围还不包括与加密和网络加密有关的任何更新。此供应不包括标准的安全补丁更新（SPU）。

pg启动报invalid checkpoint record处理

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

标题：pg启动报invalid checkpoint record处理

pg库启动报PANIC: could not locate a valid checkpoint record错误

2025-03-09 10:59:10.365 EDT [73013] LOG:  starting PostgreSQL 16.8 on x86_64-pc-linux-gnu, 
                                          compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
2025-03-09 10:59:10.365 EDT [73013] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-03-09 10:59:10.365 EDT [73013] LOG:  listening on IPv6 address "::", port 5432
2025-03-09 10:59:10.367 EDT [73013] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-03-09 10:59:10.401 EDT [73018] LOG:  database system was interrupted; last known up at 2025-03-09 10:54:43 EDT
2025-03-09 10:59:11.506 EDT [73018] LOG:  invalid checkpoint record
2025-03-09 10:59:11.508 EDT [73018] PANIC:  could not locate a valid checkpoint record
2025-03-09 10:59:12.004 EDT [73013] LOG:  startup process (PID 73018) was terminated by signal 6: Aborted
2025-03-09 10:59:12.004 EDT [73013] LOG:  aborting startup due to startup process failure
2025-03-09 10:59:12.006 EDT [73013] LOG:  database system is shut down

从报错信息中看,是有无法读取到有效的checkpoint记录导致,初步怀疑是wal异常,检查pg_wal目录,发现wal日志为空

[root@localhost pg_wal]# ls -ltr
total 16388
drwx------  2 postgres postgres        6 Mar  6 08:10 archive_status
[root@localhost pg_wal]#

进一步检查系统操作命令rm删除wal日志命令

cd pg_wal
rm -rf 0000000*

初步确认是由于wal日志被意外删除导致pg库无法启动,尝试重置wal,由于库不是干净关闭,无法直接重置成功

[postgres@localhost log]$ pg_resetwal $PGDATA
The database server was not shut down cleanly.
Resetting the write-ahead log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.

使用pg_resetwal -f强制重置

[postgres@localhost log]$ pg_resetwal -f $PGDATA
Write-ahead log reset

尝试启动pg库成功

[postgres@localhost log]$ pg_ctl start 
waiting for server to start....2025-03-09 11:02:02.609 EDT [73088] LOG:  
redirecting log output to logging collector process
2025-03-09 11:02:02.609 EDT [73088] HINT:  Future log output will appear in directory "log".
 done
server started

2025-03-09 11:02:02.609 EDT [73088] LOG:  starting PostgreSQL 16.8 on x86_64-pc-linux-gnu, 
                                          compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
2025-03-09 11:02:02.609 EDT [73088] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-03-09 11:02:02.609 EDT [73088] LOG:  listening on IPv6 address "::", port 5432
2025-03-09 11:02:02.610 EDT [73088] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-03-09 11:02:02.645 EDT [73092] LOG:  database system was shut down at 2025-03-09 11:01:53 EDT
2025-03-09 11:02:02.650 EDT [73088] LOG:  database system is ready to accept connections

删除redo导致ORA-00313 ORA-00312故障处理

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

标题：删除redo导致ORA-00313 ORA-00312故障处理

有客户由于误操作直接rm 删除了redo文件,导致数据库启动报ORA-00313 ORA-00312错

2025-03-07T14:49:16.325723+08:00
ALTER DATABASE OPEN
2025-03-07T14:50:00.124620+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2025-03-07T14:50:00.198907+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2025-03-07T14:50:00.238450+08:00
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
 Thread 1: Recovery starting at checkpoint rba (logseq 2966 block 74686), scn 0
2025-03-07T14:50:00.325246+08:00
Started redo scan
2025-03-07T14:50:00.341193+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2681.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2025-03-07T14:50:00.372632+08:00
Slave encountered ORA-10388 exception during crash recovery
…………
2025-03-07T14:50:00.385698+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-03-07T14:50:00.388594+08:00
Aborting crash recovery due to error 313
2025-03-07T14:50:00.388739+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2681.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2025-03-07T14:50:00.389243+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2681.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-313 signalled during: ALTER DATABASE OPEN...

然后客户把历史的redo文件拷贝过来,尝试恢复数据库,报ORA-00314 ORA-00312错误

2025-03-07T15:07:30.784759+08:00
ALTER DATABASE OPEN
Ping without log force is disabled:
  instance mounted in exclusive mode.
2025-03-07T15:07:30.808497+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2025-03-07T15:07:30.838664+08:00
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
 Thread 1: Recovery starting at checkpoint rba (logseq 2966 block 74686), scn 0
2025-03-07T15:07:30.897547+08:00
Started redo scan
2025-03-07T15:07:30.898222+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4106.trc:
ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
2025-03-07T15:07:30.930089+08:00
Slave encountered ORA-10388 exception during crash recovery
…………
2025-03-07T15:07:30.940051+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-03-07T15:07:30.942274+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_4138.trc:
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
2025-03-07T15:07:30.945509+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-03-07T15:07:30.945512+08:00
Slave encountered ORA-10388 exception during crash recovery
2025-03-07T15:07:30.948369+08:00
Aborting crash recovery due to error 314
2025-03-07T15:07:30.948488+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4106.trc:
ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
2025-03-07T15:07:30.949390+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4106.trc:
ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-314 signalled during: ALTER DATABASE OPEN...

使用Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本收集信息之后数据文件头状态和所需要redo信息
df_header

数据库需要sequence#为2966的redo日志,但是当前已经被删除,基于当前情况,只能进行强制非一致性恢复,尝试强制打开库

SQL> recover database;                 
ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

QL> select group#,status,sequence# from v$log;

	  GROUP# STATUS 		 SEQUENCE#
---------------- ---------------- ----------------
	       1 UNUSED 			 0
	       3 CURRENT		      2967
	       2 ACTIVE 		      2966

SQL> 
SQL> 
SQL> recover database until cancel;
ORA-00279: change 163033183 generated at 03/07/2025 14:04:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/recovery_area/orcl/archivelog/2025_03_08/o1_mf_1_2966_%u_.arc
ORA-00280: change 163033183 for thread 1 is in sequence #2966


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;

Database altered.

运气不错,直接打开数据库成功,然后逻辑导出数据,完成此处恢复.这个让我想起来了一些类似案例:
Oracle 23ai rm redo*.log恢复
 清空redo,导致ORA-27048: skgfifi: file header information is invalid
由于默认情况下oracle的redo文件扩展名是.log,然后被当做是不重要文件从而被清理导致数据库故障,在oracle服务器上清理数据之前建议查询v$datafile,v$logfile,v$tempfile,v$controlfile来确认是否是数据库文件

Navicat连接postgresql时出现column “datlastsysoid” does not exist错误解决

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

标题：Navicat连接postgresql时出现column “datlastsysoid” does not exist错误解决

使用Navicat Premium 15访问PostgreSQL 16版本的库,报如下错误

ERROR:  column "datlastsysoid" does not exist
LINE 1: SELECT DISTINCT datlastsysoid FROM pg_database;

QQ20250308-105201
QQ20250308-105222

登录pg库查看

postgres=# SELECT DISTINCT datlastsysoid FROM pg_database;
2025-03-07 22:09:03.569 EST [62553] ERROR:  column "datlastsysoid" does not exist at character 17
2025-03-07 22:09:03.569 EST [62553] STATEMENT:  SELECT DISTINCT datlastsysoid FROM pg_database;
ERROR:  column "datlastsysoid" does not exist
LINE 1: SELECT DISTINCT datlastsysoid FROM pg_database;
                        ^
postgres=# \d pg_database;
               Table "pg_catalog.pg_database"
     Column     |   Type    | Collation | Nullable | Default 
----------------+-----------+-----------+----------+---------
 oid            | oid       |           | not null | 
 datname        | name      |           | not null | 
 datdba         | oid       |           | not null | 
 encoding       | integer   |           | not null | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 daticulocale   | text      | C         |          | 
 daticurules    | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

确认没有了datlastsysoid列,通过查看文档确认从Postgres 15版本开始 pg_database表中删除了 datlastsysoid,但是可以通过查询dattablespace替代,使用二进制工具修改libcc.dll文件中的SELECT DISTINCT datlastsysoid部分语句为:SELECT DISTINCT dattablespace
QQ20250308-105427
QQ20250308-105455

然后重新启动Navicat，访问pg库正常
QQ20250308-111555

当然这个问题如果使用高版本的Navicat或者访问低版本的PostgreSQL库不会出现

aix磁盘损坏oracle数据库恢复

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

标题：aix磁盘损坏oracle数据库恢复

客户aix环境硬盘异常导致系统无法启动,初步判断是数据文件存放在本地磁盘的空间中(本地两个盘都异常,系统无法启动),通过硬件恢复厂商镜像出来,但是通过aix文件系统直接挂载提示需要fsck,但是做fsck之后,提示大量文件丢失(最关键的数据文件和备份文件都被自动删除)
dmp-remove
fsck-remove

基于这种情况,采用镜像主机挂载的方式肯定不行,考虑直接采用软件直接解析,能够看到软件,可惜由于大量的文件系统元数据损坏,解析出来的数据文件和dmp也不可用(大量损坏和空块)
QQ20250307-122939

基于上述情况,只能采用碎片级别恢复出来数据文件
QQ20250307-123123

然后使用dul工具把数据恢复到表中,实现最大限度抢救客户数据
QQ20250307-123653

对于数据库级别恢复,这个是理论上的终极恢复方法