记录一次raid恢复之后数据库故障处理(ora-01200,ORA-26101,ORA-600)

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:记录一次raid恢复之后数据库故障处理(ora-01200,ORA-26101,ORA-600)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一个客户硬件故障,进行了raid恢复之后,数据库启动异常,尝试启动发现一系列错误
先遇到了ORA-01200错误(文件实际大小小于文件头记录的block大小)

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-01110: 数据文件 2: 'H:\BAIDUNETDISK\ORCL\SYSAUX01.DBF'
ORA-01122: 数据库文件 2 验证失败
ORA-01110: 数据文件 2: 'H:\BAIDUNETDISK\ORCL\SYSAUX01.DBF'
ORA-01200: 2727680 的实际文件大小小于 2728960 块的正确大小

这个错误相对比较简单,通过bbed修改文件大小或者对文件大小进行补全也可以解决,以前有过类似case:
bbed处理ORA-01200故障
记录一次ORA-01200完美恢复
ORA-01122 ORA-01200故障处理
处理好这个故障之后,尝试恢复数据库报ORA-26101

Sat Oct 18 17:15:32 2025
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Sat Oct 18 17:15:32 2025
WARNING! Recovering data file 1 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 12 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 15 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 18 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 19 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 21 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 23 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 26 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 27 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 31 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Read of datafile 'H:\BAIDUNETDISK\ORCL\ZL9INDEXMTL.DBF' (fno 32) header failed with ORA-26101
Rereading datafile 32 header failed with ORA-26101
Media Recovery failed with error 1110
Sat Oct 18 17:15:32 2025
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_m000_16516.trc:
ORA-00314: 日志 1 (用于线程 1) 要求的 sequence# 112942 与 112939 不匹配
ORA-00312: 联机日志 1 线程 1: 'H:\BAIDUNETDISK\ORCL\REDO01.LOG'
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_m000_16516.trc:
ORA-00314: 日志 2 (用于线程 1) 要求的 sequence# 112943 与 112940 不匹配
ORA-00312: 联机日志 2 线程 1: 'H:\BAIDUNETDISK\ORCL\REDO02.LOG'
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_m000_16516.trc:
ORA-00314: 日志 3 (用于线程 1) 要求的 sequence# 112941 与 112938 不匹配
ORA-00312: 联机日志 3 线程 1: 'H:\BAIDUNETDISK\ORCL\REDO03.LOG'
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

ORA-26101错误原因是由于控制文件中的表空间号和数据文件头的不一致

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ oerr ora 26101
26101, 00000, "tablespace # in file header is %s rather than %s for file %s"
// *Cause:  The tablespace number in the file header is inconsistent
//          with that in the control file.
// *Action: Check if the control file has been migrated correctly.  Retry
//          with the correct control file and data file.

通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查结果核对32号文件的表空间信息(分别取自v$datafile和v$datafile_header)
t1
t2
尝试重建控制文件,报ORA-600 2130错误

SQL> alter database backup controlfile to trace as 'd:/ctl.txt';
alter database backup controlfile to trace as 'd:/ctl.txt'
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [2130], [52196], [2298], [5], [], [], [], [],[], [], [], []

至此基本上可以确认控制文件可能异常了,使用dbv进行验证,确认控制文件损坏

C:\Users\XFF>dbv file=H:\BaiduNetdisk\orcl\CONTROL01.CTL blocksize=16384

DBVERIFY: Release 11.2.0.4.0 - Production on 星期日 10月 19 15:30:24 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BaiduNetdisk\orcl\CONTROL01.CTL
页 13 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0000000d (file 0, block 13)
Fractured block found during dbv:
Data in bad block:
 type: 21 format: 2 rdba: 0x0000000d
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00b41501
 check value in block header: 0xd318
 computed block checksum: 0xb4

页 17 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00000011 (file 0, block 17)
Fractured block found during dbv:
Data in bad block:
 type: 21 format: 2 rdba: 0x00000011
 last change scn: 0xffff.00221b1d seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1b791501
 check value in block header: 0x8ee0
 computed block checksum: 0x64

页 21 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00000015 (file 0, block 21)
Fractured block found during dbv:
Data in bad block:
 type: 21 format: 2 rdba: 0x00000015
 last change scn: 0xffff.00221b64 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1bb41501
 check value in block header: 0xdd09
 computed block checksum: 0xd0

页 25 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00000019 (file 0, block 25)
Fractured block found during dbv:
Data in bad block:
 type: 21 format: 2 rdba: 0x00000019
 last change scn: 0xffff.00221ba7 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1ba31501
 check value in block header: 0x47b2
 computed block checksum: 0x4

页 29 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0000001d (file 0, block 29)
Fractured block found during dbv:
Data in bad block:
 type: 0 format: 2 rdba: 0x0000001d
 last change scn: 0x0000.000000d4 seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000001
 check value in block header: 0xc719
 computed block checksum: 0xd4

页 277 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00000115 (file 0, block 277)
Fractured block found during dbv:
Data in bad block:
 type: 0 format: 2 rdba: 0x00000115
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x009b0001
 check value in block header: 0xc615
 computed block checksum: 0x9b

页 281 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00000119 (file 0, block 281)
Fractured block found during dbv:
Data in bad block:
 type: 21 format: 2 rdba: 0x00000119
 last change scn: 0xffff.00221bcd seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1b561501
 check value in block header: 0xea78
 computed block checksum: 0x9b

页 321 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00000141 (file 0, block 321)
Fractured block found during dbv:
Data in bad block:
 type: 21 format: 2 rdba: 0x00000141
 last change scn: 0xffff.00221b7a seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1b9a1501
 check value in block header: 0xc645
 computed block checksum: 0xe0



DBVERIFY - 验证完成

检查的页总数: 624
处理的页总数 (数据): 0
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 74
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 542
标记为损坏的总页数: 8
流入的页总数: 8
加密的总页数        : 0
最高块 SCN            : 2235323 (65535.2235323)

C:\Users\XFF>

这样的情况,只能人工构造创建控制文件语句,然后进行重建ctl,然后尝试recover恢复库,结果报ORA-600 3051

SQL> startup nomount pfile='d:/pfile.txt';
ORACLE 例程已经启动。

Total System Global Area 4275781632 bytes
Fixed Size                  2288080 bytes
Variable Size             939525680 bytes
Database Buffers         3321888768 bytes
Redo Buffers               12079104 bytes
SQL> @H:\BaiduNetdisk\orcl\check_db\rectl.sql

控制文件已创建。

SQL> recover database;
ORA-00279: 更改 1073321258 (在 10/13/2025 10:27:23 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\XFF\PRODUCT\11.2.0.4\DBHOME_1\RDBMS\ARC0000112940_1016795858.0001
ORA-00280: 更改 1073321258 (用于线程 1) 在序列 #112940 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
H:\BAIDUNETDISK\ORCL\REDO02.LOG
ORA-00363: 日志不是归档版本
ORA-00334: 归档日志: 'H:\BAIDUNETDISK\ORCL\REDO02.LOG'


ORA-00600: 内部错误代码, 参数: [3051], [82], [], [], [], [], [], [], [], [],[], []

基于这种情况,数据库无法正常应用日志(数据库非归档部分redo已经覆盖),只能尝试强制打开库

SQL> recover database until cancel;
ORA-00279: 更改 1073321258 (在 10/13/2025 10:27:23 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\XFF\PRODUCT\11.2.0.4\DBHOME_1\RDBMS\ARC0000112940_1016795858.0001
ORA-00280: 更改 1073321258 (用于线程 1) 在序列 #112940 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\ORCL\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾
进程 ID: 18848
会话 ID: 1 序列号: 3

强制打开库失败,前端直接报ORA-03113: 通信通道的文件结尾 错误,alert日志主要报ORA-600 4042和ORA-600 kdBlkCheckError错误

Sat Oct 18 17:28:18 2025
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1073321258
Resetting resetlogs activation ID 1544625487 (0x5c111d4f)
Sat Oct 18 17:28:18 2025
Setting recovery target incarnation to 2
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Sat Oct 18 17:28:18 2025
Assigning activation ID 1742624120 (0x67de5578)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: H:\BAIDUNETDISK\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Oct 18 17:28:18 2025
SMON: enabling cache recovery
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_18848.trc  (incident=20564):
ORA-00600: 内部错误代码, 参数: [4042], [0], [], [], [], [], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\orcl\orcl\incident\incdir_20564\orcl_ora_18848_i20564.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Oct 18 17:31:15 2025
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_13700.trc  (incident=20572):
ORA-00600: 内部错误代码, 参数: [kdBlkCheckError], [1], [675], [6401], [], [], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\orcl\orcl\incident\incdir_20572\orcl_ora_13700_i20572.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Block recovery from logseq 1, block 3 to scn 1074362471
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: H:\BAIDUNETDISK\ORCL\REDO01.LOG
Block recovery stopped at EOT rba 1.4.16
Block recovery completed at rba 1.4.16, scn 0.1074362470
Sat Oct 18 17:31:17 2025
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_13700.trc  (incident=20573):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 675)
ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\ORCL\SYSTEM01.DBF'
Sat Oct 18 17:31:18 2025
Sweep [inc][20572]: completed
Incident details in: C:\APP\XFF\diag\rdbms\orcl\orcl\incident\incdir_20573\orcl_ora_13700_i20573.trc
Sweep [inc][20564]: completed
Sweep [inc2][20572]: completed
Sat Oct 18 17:31:19 2025
Sweep [inc][20573]: completed
Checker run found 1 new persistent data failures
Sat Oct 18 17:32:19 2025
Sweep [inc2][20573]: completed
Sat Oct 18 17:33:26 2025
Shutting down instance (abort)
License high water mark = 22
USER (ospid: 1640): terminating the instance
Instance terminated by USER, pid = 1640

通过报错分析,主要是由于file 1 block 675异常导致,通过对该block进行处理,再次尝试打开库

SQL> recover database;
完成介质恢复。
SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [25027], [0], [3273132828], [], [],
[], [], [], [], [], [], []
进程 ID: 3124

数据库无法正常打开,报ORA-600 25027错误,对应的alert日志为

Sat Oct 18 17:38:18 2025
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 19 processes
Started redo scan
Completed redo scan
 read 19 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 2, block 3
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: H:\BAIDUNETDISK\ORCL\REDO02.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 2, block 42, scn 1074402517
 0 data blocks read, 0 data blocks written, 19 redo k-bytes read
Sat Oct 18 17:38:18 2025
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: H:\BAIDUNETDISK\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Oct 18 17:38:18 2025
SMON: enabling cache recovery
Undo initialization finished serial:0 start:29002046 end:29002046 diff:0 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
           Empty temporary tablespace: ZLTOOLSTMP
*********************************************************************
Database Characterset is ZHS16GBK
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_11844.trc  (incident=24166):
ORA-00600: 内部错误代码, 参数: [25027], [0], [1600133727], [], [], [], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\orcl\orcl\incident\incdir_24166\orcl_ora_11844_i24166.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 C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_11844.trc:
ORA-00600: 内部错误代码, 参数: [25027], [0], [1600133727], [], [], [], [], [], [], [], [], []
Errors in file C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_11844.trc:
ORA-00600: 内部错误代码, 参数: [25027], [0], [1600133727], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 11844): terminating the instance due to error 600
Instance terminated by USER, pid = 11844
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (11844) as a result of ORA-1092

ORA-00600[25027]错误的触发原因是ORACLE检测到一个无效的表空间号TSN Tablespace Number或者相对文件号Relative File Number。
该ORA-00600[25027]的2个变量各代表:
arg[a] Tablespace Number表空间号
arg[b] 十进制的相对数据块号Relative Data Block Address (RDBA)


通过trace文件定位具体报错信息

*** 2025-10-18 17:38:18.320
*** SESSION ID:(1.1) 2025-10-18 17:38:18.320
*** CLIENT ID:() 2025-10-18 17:38:18.320
*** SERVICE NAME:(SYS$USERS) 2025-10-18 17:38:18.320
*** MODULE NAME:(sqlplus.exe) 2025-10-18 17:38:18.320
*** ACTION NAME:() 2025-10-18 17:38:18.320

Dump continued from file: C:\APP\XFF\diag\rdbms\orcl\orcl\trace\orcl_ora_11844.trc
ORA-00600: 内部错误代码, 参数: [25027], [0], [1600133727], [], [], [], [], [], [], [], [], []

========= Dump for incident 24166 (ORA 600 [25027]) ========

*** 2025-10-18 17:38:18.322
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=1gu8t96d0bdmu) -----
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,
t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),
t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),
t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts
 where t.obj#= :1 and t.obj# = ts.obj# (+)

并确认最终报错block为3339
3339


对该坏块进行修复处理,数据库顺利open

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 19 processes
Started redo scan
Completed redo scan
 read 20 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 6, block 2, scn 1074462651
Recovery of Online Redo Log: Thread 1 Group 3 Seq 6 Reading mem 0
  Mem# 0: H:\BAIDUNETDISK\ORCL\REDO03.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 6, block 43, scn 1074482693
 0 data blocks read, 0 data blocks written, 20 redo k-bytes read
Sat Oct 18 17:55:22 2025
Thread 1 advanced to log sequence 7 (thread open)
Thread 1 opened at log sequence 7
  Current log# 1 seq# 7 mem# 0: H:\BAIDUNETDISK\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Oct 18 17:55:22 2025
SMON: enabling cache recovery
Undo initialization finished serial:0 start:30026203 end:30026218 diff:15 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
           Empty temporary tablespace: ZLTOOLSTMP
*********************************************************************
Database Characterset is ZHS16GBK
Stopping background process MMNL
Stopping background process MMON
Sat Oct 18 17:55:24 2025
Starting background process MMON
Sat Oct 18 17:55:24 2025
MMON started with pid=16, OS id=14036 
Starting background process MMNL
Sat Oct 18 17:55:24 2025
MMNL started with pid=17, OS id=9384 
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open 

并查询业务数据正常,实现在损坏raid恢复之后最大限度的数据恢复
2025


nbu备份文件img格式直接rman恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:nbu备份文件img格式直接rman恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有朋友从nbu中直接导出来img等格式文件(是oracle的rman 备份文件在其中)
nbu


但是该文件rman无法正常失败,不是有效的rman备份RMAN-06172

H:\TEMP\nbu_oracle12c_imgs>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期日 10月 19 13:03:01 2025

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

已连接到空闲例程。

SQL> startup nomount pfile='d:/pfile12.txt'
ORA-32006: UTL_FILE_DIR initialization parameter has been deprecated
ORACLE 例程已经启动。

Total System Global Area 4294967296 bytes
Fixed Size                  8755072 bytes
Variable Size            1644169344 bytes
Database Buffers         2634022912 bytes
Redo Buffers                8019968 bytes
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 断开

H:\TEMP\nbu_oracle12c_imgs>rman target /

恢复管理器: Release 12.2.0.1.0 - Production on 星期日 10月 19 13:04:04 2025

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

已连接到目标数据库: ORCL (未装载)

RMAN> restore controlfile from 'H:\TEMP\nbu_oracle12c_imgs\ora12c_1760595637_C1_F1.1760595637.img';

从位于 19-10月-25 的 restore 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=1832 设备类型=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: 位于 10/19/2025 13:04:16 的 restore 命令失败
RMAN-06172: 没有找到 AUTOBACKUP, 或者指定的句柄不是有效副本或片段

通过对文件进行分析,确认该文件是控制文件备份
ctl_bak


通过分析可以通过人工构造出来rman的disk备份格式,中途借用了上次开发的把rman备份集从tape格式修改为disk格式的工具(RMAN SBT_TAPE备份通过小程序修改实现直接DISK通道还原),通过修复之后,生成的备份文件为:
disk

再次尝试还原控制文件

RMAN> restore controlfile from 'H:\TEMP\nbu_oracle12c_imgs\NEW\ora12c_1760595637_C1_F1.1760595637.img.disk';

从位于 19-10月-25 的 restore 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=1832 设备类型=DISK

通道 ORA_DISK_1: 正在还原控制文件
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=H:\TEMP\NBU_ORACLE12C_IMGS\NEW\CONTROL01.CTL
在 19-10月-25 完成了 restore

RMAN> alter database mount;

已处理语句
释放的通道: ORA_DISK_1

看看当前备份集信息

RMAN> list backup;


备份集列表
===================


BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
1       Incr 0  647.00M    SBT_TAPE    00:00:15     01-7月 -25
        BP 关键字: 1   状态: AVAILABLE  已压缩: NO  标记: HOT_DB_BK_INC_LVL0
        句柄: bk_1_1_1205357639   介质: /usr/openv/diskpool1//ora12c_1751376844_C1_F1
  备份集 1 中的数据文件列表
  File LV Type Ckp SCN    Ckp 时间   Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ---------- ----------- ------ ----
  1    0  Incr 1603360    01-7月 -25              NO  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_n67rkzok_.dbf
  6    0  Incr 1603360    01-7月 -25              NO  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_n67rmpx2_.dbf

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
2       Incr 0  474.50M    SBT_TAPE    00:00:29     01-7月 -25
        BP 关键字: 2   状态: AVAILABLE  已压缩: NO  标记: HOT_DB_BK_INC_LVL0
        句柄: bk_2_1_1205357639   介质: /usr/openv/diskpool1//ora12c_1751376863_C1_F1
  备份集 2 中的数据文件列表
  File LV Type Ckp SCN    Ckp 时间   Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ---------- ----------- ------ ----
  3    0  Incr 1603361    01-7月 -25              NO  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_n67rjlgp_.dbf
  4    0  Incr 1603361    01-7月 -25              NO  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_n67rmr26_.dbf

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
3       Incr 0  9.75M      SBT_TAPE    00:00:12     01-7月 -25
        BP 关键字: 3   状态: AVAILABLE  已压缩: NO  标记: HOT_DB_BK_INC_LVL0
        句柄: bk_3_1_1205357665   介质: /usr/openv/diskpool1//ora12c_1751376878_C1_F1
  包括的控制文件: Ckp SCN: 1603370      Ckp 时间: 01-7月 -25

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
4       Incr 0  256.00K    SBT_TAPE    00:00:00     01-7月 -25
        BP 关键字: 4   状态: AVAILABLE  已压缩: NO  标记: HOT_DB_BK_INC_LVL0
        句柄: bk_4_1_1205357675   介质: /usr/openv/diskpool1//ora12c_1751376882_C1_F1
  包含的 SPFILE: 修改时间: 01-7月 -25
  SPFILE db_unique_name: ORCL

BS 关键字  大小       设备类型占用时间 完成时间
------- ---------- ----------- ------------ ----------
5       31.25M     SBT_TAPE    00:00:05     01-7月 -25
        BP 关键字: 5   状态: AVAILABLE  已压缩: NO  标记: TAG20250701T213457
        句柄: al_5_1_1205357697   介质: /usr/openv/diskpool1//ora12c_1751376902_C1_F1

  备份集 5 中的已存档日志列表
  线程序列     低 SCN    时间下限   下一个 SCN   下一次
  ---- ------- ---------- ---------- ---------- ---------
  1    4       1599673    01-7月 -25 1603426    01-7月 -25

BS 关键字  大小       设备类型占用时间 完成时间
------- ---------- ----------- ------------ ----------
6       256.00K    SBT_TAPE    00:00:08     01-7月 -25
        BP 关键字: 6   状态: AVAILABLE  已压缩: NO  标记: TAG20250701T213457
        句柄: al_6_1_1205357697   介质: /usr/openv/diskpool1//ora12c_1751376906_C1_F1

  备份集 6 中的已存档日志列表
  线程序列     低 SCN    时间下限   下一个 SCN   下一次
  ---- ------- ---------- ---------- ---------- ---------
  1    5       1603426    01-7月 -25 1603471    01-7月 -25

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
7       Full    9.75M      SBT_TAPE    00:00:06     01-7月 -25
        BP 关键字: 7   状态: AVAILABLE  已压缩: NO  标记: TAG20250701T213513
        句柄: cntrl_7_1_1205357713   介质: /usr/openv/diskpool1//ora12c_1751376919_C1_F1
  包括的控制文件: Ckp SCN: 1603523      Ckp 时间: 01-7月 -25

通过控制文件中备份集的记录,可以看到备份是SBT_TAPE设备,介质是文件系统中的 /usr/openv/diskpool1/

catalog注册新的备份文件

RMAN> catalog start with 'H:\TEMP\nbu_oracle12c_imgs\*.disk';

搜索与样式 H:\TEMP\nbu_oracle12c_imgs\*.disk 匹配的所有文件

数据库未知文件的列表
=====================================
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595598_C1_F1.1760595598.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595618_C1_F1.1760595618.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595637_C1_F1.1760595637.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595642_C1_F1.1760595642.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595661_C1_F1.1760595661.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595665_C1_F1.1760595665.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595680_C1_F1.1760595680.IMG.DISK

是否确实要将上述文件列入目录 (输入 YES 或 NO)? yes
正在编制文件目录...
目录编制完毕

已列入目录的文件的列表
=======================
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595598_C1_F1.1760595598.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595618_C1_F1.1760595618.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595637_C1_F1.1760595637.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595642_C1_F1.1760595642.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595661_C1_F1.1760595661.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595665_C1_F1.1760595665.IMG.DISK
文件名: H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595680_C1_F1.1760595680.IMG.DISK

使用rman还原新数据文件

RMAN> report schema;

RMAN-06139: 警告: 控制文件对于 REPORT SCHEMA 不是最新
db_unique_name 为 ORCL 的数据库的数据库方案报表

永久数据文件列表
===========================
文件大小 (MB) 表空间           回退段数据文件名称
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_n67rkzok_.dbf
3    580      SYSAUX               ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_n67rjlgp_.dbf
4    60       UNDOTBS1             ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_n67rmr26_.dbf
6    5        USERS                ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_n67rmpx2_.dbf

临时文件列表
=======================
文件大小 (MB) 表空间           最大大小 (MB) 临时文件名称
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_n67roknm_.tmp

RMAN> RUN {
2> set newname for datafile 1 to 'H:\TEMP\nbu_oracle12c_imgs\NEW\system01.dbf';
3> set newname for datafile 3 to 'H:\TEMP\nbu_oracle12c_imgs\NEW\sysaux01.dbf';
4> set newname for datafile 4 to 'H:\TEMP\nbu_oracle12c_imgs\NEW\undotbs01.dbf';
5> set newname for datafile 6 to 'H:\TEMP\nbu_oracle12c_imgs\NEW\users01.dbf';
6> restore database;
7> switch datafile all;
8> }

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

从位于 19-10月-25 的 restore 开始
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00003 还原到 H:\TEMP\nbu_oracle12c_imgs\NEW\sysaux01.dbf
通道 ORA_DISK_1: 将数据文件 00004 还原到 H:\TEMP\nbu_oracle12c_imgs\NEW\undotbs01.dbf
通道 ORA_DISK_1: 正在读取备份片段 H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595598_C1_F1.1760595598.IMG.DISK
通道 ORA_DISK_1: 片段句柄=H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595598_C1_F1.1760595598.IMG.DISK 标记=HOT_DB_BK_INC_LVL0
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00001 还原到 H:\TEMP\nbu_oracle12c_imgs\NEW\system01.dbf
通道 ORA_DISK_1: 将数据文件 00006 还原到 H:\TEMP\nbu_oracle12c_imgs\NEW\users01.dbf
通道 ORA_DISK_1: 正在读取备份片段 H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595618_C1_F1.1760595618.IMG.DISK
通道 ORA_DISK_1: 片段句柄=H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595618_C1_F1.1760595618.IMG.DISK 标记=HOT_DB_BK_INC_LVL0
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
在 19-10月-25 完成了 restore

数据文件 1 已转换成数据文件副本
输入数据文件副本 RECID=5 STAMP=1214913255 文件名 = H:\TEMP\NBU_ORACLE12C_IMGS\NEW\SYSTEM01.DBF
数据文件 3 已转换成数据文件副本
输入数据文件副本 RECID=6 STAMP=1214913255 文件名 = H:\TEMP\NBU_ORACLE12C_IMGS\NEW\SYSAUX01.DBF
数据文件 4 已转换成数据文件副本
输入数据文件副本 RECID=7 STAMP=1214913255 文件名 = H:\TEMP\NBU_ORACLE12C_IMGS\NEW\UNDOTBS01.DBF
数据文件 6 已转换成数据文件副本
输入数据文件副本 RECID=8 STAMP=1214913255 文件名 = H:\TEMP\NBU_ORACLE12C_IMGS\NEW\USERS01.DBF

尝试还原归档日志

RMAN> restore archivelog sequence 7;

从位于 19-10月-25 的 restore 开始
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始将归档日志还原到默认目标
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1 序列 = 7
通道 ORA_DISK_1: 正在读取备份片段 H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595661_C1_F1.1760595661.IMG.DISK
通道 ORA_DISK_1: 片段句柄=H:\TEMP\NBU_ORACLE12C_IMGS\ORA12C_1760595661_C1_F1.1760595661.IMG.DISK 标记=TAG20251016T142056
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
在 19-10月-25 完成了 restore

通过上述操作证明,通过对nbu的img文件进行二次修复,然后直接实现rman还原数据库.

ORA-600 kokasgi1故障处理(sys被重命名)

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-600 kokasgi1故障处理(sys被重命名)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

接到一个客户数据库无法启动的case请求,查看alert日志,发现错误是经典的ORA-600 kokasgi1

Fri Oct 17 21:56:48 2025
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 30 KB redo, 24 data blocks need recovery
Started redo application at
 Thread 1: logseq 3749, block 2, scn 111907926
Recovery of Online Redo Log: Thread 1 Group 2 Seq 3749 Reading mem 0
  Mem# 0: /u01/oradata/orcl/redo02.log
Completed redo application of 0.02MB
Completed crash recovery at
 Thread 1: logseq 3749, block 62, scn 111927984
 24 data blocks read, 24 data blocks written, 30 redo k-bytes read
Thread 1 advanced to log sequence 3750 (thread open)
Thread 1 opened at log sequence 3750
  Current log# 3 seq# 3750 mem# 0: /u01/oradata/orcl/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[2261] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4294684900 end:4294684970 diff:70 (0 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
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2261.trc  (incident=81755):
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_81755/orcl_ora_2261_i81755.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_2261.trc:
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2261.trc:
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 2261): terminating the instance due to error 600
Instance terminated by USER, pid = 2261

尝试启动数据库确实报该错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [],[], [], [], [], []
Process ID: 164830
Session ID: 688 Serial number: 169

这类错误恢复比较多:
ORA-600 kokasgi1故障恢复
win环境报ora-600 kokasgi1处理
再次遇到ORA-600 kokasgi1故障恢复
2022年恢复第一单ORA-600 kokasgi1
等保修改oracle SYS用户名要求的请注意—ORA-00600 kokasgi1
重命名sys用户引起数据库启动报ORA-01092 ORA-00600 kokasgi1错误
处理方法比较简单,就是在数据库启动的过程中绕过对SYS用户的检查,然后把user#=0的用户update为SYS(在后续数据库版本中,oracle可能发现了该问题,直接禁止用户级别对user$进行update操作update user$报ORA-01031错误),再重启库即可
sys

SQL> update user$ set name='SYS' WHERE USER#=0;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select user#,name from user$ WHERE USER#=0;

     USER# NAME
---------- ------------------------------
         0 SYS

SQL> alter system checkpoint;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.



[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 18 00:36:37 2025

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2252904 bytes
Variable Size            1157631896 bytes
Database Buffers          117440512 bytes
Redo Buffers                8740864 bytes
Database mounted.
SQL>
SQL>
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

完美处理ORA-600 kokasgi1故障,实现数据0丢失,业务快速恢复

Patch_SCN for Linux 功能完善

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:Patch_SCN for Linux 功能完善

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

Patch_SCN工具目前有For win和for linux版本,主要用于修改Oracle SCN(特别是在oracle数据库非常规恢复中实现一键修改SCN功能,实现快速open数据库.主要解决典型问题:
ORA-600 2662
ORA-600 kclchkblk_4
ORA-600 kcbzib_kcrsds_1
最近有点空对Patch_SCN软件的for linux版本进行了完善,主要完善功能:
1. 整个代码全部通过C代码实现
2. 完善了注册机制
3. 无需输入内存地址,程序一般情况下可以直接获取地址并修改
上传软件到linux环境并授权
确保执行用户有x权限

[root@iZbp11c0qyuuo1gr7j98upZ tmp]# ls -ltr /tmp/Patch_SCN 
-rw-r--r-- 1 root root 13984 Oct 13  2025 /tmp/Patch_SCN
[root@iZbp11c0qyuuo1gr7j98upZ tmp]# chmod +x /tmp/Patch_SCN 
[root@iZbp11c0qyuuo1gr7j98upZ tmp]# ls -ltr /tmp/Patch_SCN 
-rwxr-xr-x 1 root root 13984 Oct 13  2025 /tmp/Patch_SCN

使用软件之前需要进行注册
第一次使用要求输入注册码

[root@iZbp11c0qyuuo1gr7j98upZ tmp]# ./Patch_SCN 
This software is not registered.

========================================
           Software Registration        
========================================

Your Hardware ID: XXXXXXX

Please send your Hardware ID to XiFenFei to register.
Website: https://www.xifenfei.com 
Tel/WX: +86-17813235971 

Enter Registration Code: 6F2EEF38-693AEA80
Registration successful!
Usage:
  Automatic address mode: ./Patch_SCN <pid> <new_value>
  Manual address mode:    ./Patch_SCN <pid> <address> <new_value>
  Where:
    <pid> - Oracle process ID (must be entered manually)
    <address> - Memory address (entered in manual mode, hexadecimal)
    <new_value> - SCN value to modify (must be entered manually, supports decimal or hexadecimal)

授权成功之后,后续使用软件无需注册

[root@iZbp11c0qyuuo1gr7j98upZ tmp]# ./Patch_SCN 
Usage:
  Automatic address mode: ./Patch_SCN <pid> <new_value>
  Manual address mode:    ./Patch_SCN <pid> <address> <new_value>
  Where:
    <pid> - Oracle process ID (must be entered manually)
    <address> - Memory address (entered in manual mode, hexadecimal)
    <new_value> - SCN value to modify (must be entered manually, supports decimal or hexadecimal)

Patch_SCN使用演示
软件支持自动发现内存地址和手工输入内存地址两种模式(当某些情况无法软件自动发现地址时,可以考虑人工输入地址方式进行)


###############自动识别内存地址方式修改#############################
--修改scn之前
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
 5691132517

---进行scn修改
[oracle@iZbp11c0qyuuo1gr7j98upZ tmp]$ ./Patch_SCN 18884 5691232517
Successfully obtained address automatically: 0x6001ae70
Original Oracle SCN at Address 0x6001ae70: 0x15337ca8a
Are you sure you want to modify Oracle SCN? (yes/no): yes
New SCN at Address 0x6001ae70: 0x153395105
Oracle SCN successfully modified.

--修改scn之后
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
 5691232520   --->由于scn会自动增加,所以比修改值稍大一点


###############手工输入内存地址方式修改########################
---当程序提示无法获取到内存地址时,采用人工输入内存地址方式进行修改
[root@iZbp11c0qyuuo1gr7j98upZ tmp]# ./Patch_SCN 18884 5691232517
Could not find valid  SCN ddress
Failed to get SCN address automatically
Please use manual address mode instead

---使用get_addr程序获取内存地址,如果无法获取联系:xifenfei
[oracle@iZbp11c0qyuuo1gr7j98upZ tmp]$ ./get_addr 
Retrieving Oracle SCN address...
Successfully retrieved SCN address: 0x6001ae70

---修改scn值
[root@iZbp11c0qyuuo1gr7j98upZ tmp]# ./Patch_SCN 18884 0x6001ae70 6691232517
Using manually provided address: 0x6001ae70
Original Oracle SCN at Address 0x6001ae70: 0x15339517f
Are you sure you want to modify Oracle SCN? (yes/no): yes
New SCN at Address 0x6001ae70: 0x18ed41b05
Oracle SCN successfully modified.

---验证修改之后scn值
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
 6691232521  --->由于scn会自动增加,所以比修改值稍大一点

Patch_SCN下载:Patch_SCN下载
Patch_SCN使用说明:Patch_SCN使用说明

ORA-600 2662错误处理-202510

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-600 2662错误处理-202510

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一个朋友一个历史库,由于某种原因无法正常启动,尝试强制打开库报ORA-600 2662错误

[oracle@db01 check_db]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 10 22:08:31 2025

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

Connected to an idle instance.

SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 2.0176E+10 bytes
Fixed Size                  2261928 bytes
Variable Size            2818575448 bytes
Database Buffers         1.7314E+10 bytes
Redo Buffers               41463808 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [4293], [1546030629],
[4293], [1646742297], [4194545], [], [], [], [], [], []
Process ID: 12499
Session ID: 66 Serial number: 3

对应的alert日志报错如下

Fri Oct 10 22:08:41 2025
ALTER DATABASE   MOUNT
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from  to xff
Successful mount of redo thread 1, with mount id 92634729
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Oct 10 22:08:58 2025
alter database open resetlogs
RESETLOGS after complete recovery through change 18439840632350
Clearing online redo logfile 1 /u01/oradata/xff/redo01.log
Clearing online log 1 of thread 1 sequence number 0
Clearing online redo logfile 1 complete
Resetting resetlogs activation ID 90952602 (0x56bd39a)
Online log /u01/oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared
Online log /u01/oradata/xff/redo04.log: Thread 1 Group 4 was previously cleared
Online log /u01/oradata/xff/redo05.log: Thread 1 Group 5 was previously cleared
Online log /u01/oradata/xff/redo06.log: Thread 1 Group 6 was previously cleared
Online log /u01/oradata/xff/redo07.log: Thread 1 Group 7 was previously cleared
Online log /u01/oradata/xff/redo08.log: Thread 1 Group 8 was previously cleared
Fri Oct 10 22:08:59 2025
Setting recovery target incarnation to 2
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 18439840632353, threshold SCN value is 0
If you have not previously reported this warning on this database, 
   please notify Oracle Support so that additional diagnosis can be performed.
Fri Oct 10 22:08:59 2025
Assigning activation ID 92634729 (0x5857e69)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/oradata/xff/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Oct 10 22:08:59 2025
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_12499.trc  (incident=4961):
ORA-00600: internal error code, arguments: [2662], [4293], [1546030629], [4293], [1646742297],[4194545]
Incident details in: /u01/app/oracle/diag/rdbms/xff/xff/incident/incdir_4961/xff_ora_12499_i4961.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/xff/xff/trace/xff_ora_12499.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [4293], [1546030629], [4293],[1646742297],[4194545]
Errors in file /u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_12499.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [4293], [1546030629], [4293],[1646742297],[4194545]
Error 704 happened during db open, shutting down database
USER (ospid: 12499): terminating the instance due to error 704
Instance terminated by USER, pid = 12499
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (12499) as a result of ORA-1092

这个错误算是在oracle 12c之前比较经典的一个错误,一般是由于文件头的SCN小于数据库在启动/运行过程中block的SCN,从而出现该问题,解决该问题的方法一般就是把文件头的SCN修改为更大值即可,对于这种情况,可以通过Patch_SCN小工具进行修改(Patch SCN一键解决ORA-600 2662故障),也可以反向找出来报错的block,把其SCN修改为更小的值,以前写过类似的文章:https://www.xifenfei.com/2011/12/%e4%bd%bf%e7%94%a8bbed%e8%a7%a3%e5%86%b3ora-006002662.html
通过调整之后,数据库正常打开

SQL> recover database;
Media recovery complete.
SQL> alter database open ;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 ONLINE

11 rows selected.

检测字典没有问题

SQL> @hcheck
HCheck Version 07MAY18 on 10-OCT-2025 22:31:36
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: XFF

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj                 ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- MissingOIDOnObjCol          ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- SourceNotInObj              ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- OversizedFiles              ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- PoorDefaultStorage          ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- PoorStorage                 ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- TabPartCountMismatch        ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- OrphanedTabComPart          ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- MissingSum$                 ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- MissingDir$                 ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- DuplicateDataobj            ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- ObjSynMissing               ... 1102000400 <=  *All Rel* 10/10 22:31:36 PASS
.- ObjSeqMissing               ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedUndo                ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedIndex               ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedIndexPartition      ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedIndexSubPartition   ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedTable               ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedTablePartition      ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedTableSubPartition   ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- MissingPartCol              ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedSeg$                ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedIndPartObj#         ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- DuplicateBlockUse           ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- FetUet                      ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- Uet0Check                   ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- SeglessUET                  ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadInd$                     ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadTab$                     ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadIcolDepCnt               ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- ObjIndDobj                  ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- TrgAfterUpgrade             ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- ObjType0                    ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadOwner                    ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- StmtAuditOnCommit           ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadPublicObjects            ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadSegFreelist              ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadDepends                  ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- CheckDual                   ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- ObjectNames                 ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadCboHiLo                  ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- ChkIotTs                    ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- NoSegmentIndex              ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- BadNextObject               ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- DroppedROTS                 ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- FilBlkZero                  ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- DbmsSchemaCopy              ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanedObjError            ... 1102000400 >  1102000000 10/10 22:31:37 PASS
.- ObjNotLob                   ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- MaxControlfSeq              ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- SegNotInDeferredStg         ... 1102000400 >  1102000000 10/10 22:31:37 PASS
.- SystemNotRfile1             ... 1102000400 >   902000000 10/10 22:31:37 PASS
.- DictOwnNonDefaultSYSTEM     ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- OrphanTrigger               ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
.- ObjNotTrigger               ... 1102000400 <=  *All Rel* 10/10 22:31:37 PASS
---------------------------------------
10-OCT-2025 22:31:37  Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_8794_HCHECK.trc

SQL>

system表空间丢失部分文件恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:system表空间丢失部分文件恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户因为system表空间有一个数据文件放在其他位置,当时没有正常拷贝出来(备份了oradata路径下面文件,遗漏了一个system文件),尝试启动库报ORA-01157 ORA-01147等错误

[oracle@xifenfei check_db]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 5 21:13:28 2025

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 datafile 1;
Media recovery complete. 
SQL> recover datafile 2,3,4,5,6,7,8,9,10;   
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11:
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'

SQL> alter database datafile 11 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 11 is offline
ORA-01110: data file 11:
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'

alert日志报错信息

Sun Oct 05 22:35:01 2025
alter database open
Sun Oct 05 22:35:01 2025
Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_dbw0_5946.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_ora_11264.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'
ORA-1157 signalled during: alter database open...
Sun Oct 05 22:35:25 2025
alter database datafile 11 offline 
ORA-1145 signalled during: alter database datafile 11 offline ...
alter database datafile 11 offline drop
Completed: alter database datafile 11 offline drop
alter database open
Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_ora_11264.trc:
ORA-01147: SYSTEM tablespace file 11 is offline
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'
ORA-1147 signalled during: alter database open...

由于11号文件是system表空间的一个数据文件,对于这种数据文件丢失无法offline该数据文件,然后open库(也就是说在open库的时候,system表空间的数据文件必须全部online,如果有部分文件offline就会报ORA-01147).对于这样的情况,以前有过类似恢复经历:bbed打开丢失部分system数据文件库,这次的编写了一个m_scn程序实现快速处理

[oracle@xifenfei  tmp]$ cat 1.txt
1@/data/app/oracle/oradata/mtxdb1/system01.dbf
11@/tmp/11.dbf
[oracle@xifenfei  tmp]$ ./m_scn 1.txt

-------------Is processing datafile:/tmp/11.dbf-------------
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.000835728 s, 1.3 GB/s

[oracle@xifenfei tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 8 11:27:32 2025

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> set numw 16
SQL> col CHECKPOINT_TIME for a40
SQL> set lines 150
SQL> set pages 1000
SQL> SELECT status,
  2  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
  3  count(*) ROW_NUM
  4  FROM v$datafile_header
  5  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
  6  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
OFFLINE 2025-10-02 06:50:06                      NO      17328662858685                1
ONLINE  2025-10-02 06:50:06                      NO      17328662858685               10


SQL> alter database datafile 11 online;

Database altered.

然后重建ctl,并尝试打开库
ctl_re


然后查询11号文件中涉及的对象情况

SQL> select distinct owner,segment_name,segment_type from dba_extents where file_id=11;

OWNER                          SEGMENT_NAME                           SEGMENT_TYPE
------------------------------ -------------------------------------- ------------------
SYS                            SYSTEM                                 ROLLBACK
SYS                            I_COL1                                 INDEX
SYS                            AUD$                                   TABLE

SQL> select owner,segment_name from dba_segments where HEADER_FILE=11;

no rows selected

证明丢失的11号文件(system表空间文件),涉及的对象较少,而且不涉及核心字典,比如tab$,obj$,col$等非常核心对象,评估理论上应该不涉业务数据丢失,尝试直接expdp导出数据,但是很不幸,报ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018]错误

. . exported "XFF020"."OTHERBILLDETAIL_DEL"              6.405 MB  126048 rows
. . exported "XFF020"."POSSOLDOUT"                       7.784 MB  281413 rows
ORA-31693: Table data object "XFF020"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=159:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020"."MATERIELTRAN"] 
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
    WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=159:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xef2fc508     21979  package body SYS.KUPW$WORKER
0xef2fc508      9742  package body SYS.KUPW$WORKER
0xef2fc508      3437  package body SYS.KUPW$WORKER
0xef2fc508     10436  package body SYS.KUPW$WORKER
0xef2fc508      1824  package body SYS.KUPW$WORKER
0xef2feb20         2  anonymous block

ORA-39097: Data Pump job encountered unexpected error -607
ORA-39065: unexpected master process exception in DISPATCH
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []

ORA-31693: Table data object "XFF020"."ANALYSEREPORT" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=161:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020"."ANALYSEREPORT"] 
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
   WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020"."ANALYSEREPORT" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=161:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xef2fc508     21979  package body SYS.KUPW$WORKER
0xef2fc508      9742  package body SYS.KUPW$WORKER
0xef2fc508      3437  package body SYS.KUPW$WORKER
0xef2fc508     10436  package body SYS.KUPW$WORKER
0xef2fc508      1824  package body SYS.KUPW$WORKER
0xef2feb20         2  anonymous block

ORA-31693: Table data object "XFF020CW"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=160:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020CW"."MATERIELTRAN"] 
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
   WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020CW"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=160:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xef2fc508     21979  package body SYS.KUPW$WORKER
0xef2fc508      9742  package body SYS.KUPW$WORKER
0xef2fc508      3437  package body SYS.KUPW$WORKER
0xef2fc508     10436  package body SYS.KUPW$WORKER
0xef2fc508      1824  package body SYS.KUPW$WORKER
0xef2feb20         2  anonymous block

Job "SYS"."SYS_EXPORT_FULL_01" stopped due to fatal error at Wed Oct 8 11:59:29 2025 elapsed 0 00:18:48

对ORA-600 kdBlkCheckError进行分析分析(11表示文件号,3表示block),是由于导出生成的master表写入在system表空间,而system表空间中的file# 11是人工构造出来的,block 3 是位图分配信息(该信息和实际字典中存储信息不匹配),所以导致出现该错误,对于这个问题解决方法为expdp写master表不在system表空间即可,通过该操作,顺利导出数据,完成本次恢复任务
expdp_ok


arm环境vg损坏mysql数据库恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:arm环境vg损坏mysql数据库恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

国庆节期间接到朋友咨询,原先在vg中的磁盘被重新pvcreate了,想恢复原磁盘中的mysql数据库
pvcreate


通过分析系统的history日志,发现操作不是简单的pvcreate,我简单梳理下操作步骤
故障之前磁盘情况

[root@0002 ~]# lsblk
NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sr0                    11:0    1 1024M  0 rom  
vda                   253:0    0  200G  0 disk 
├─vda1                253:1    0  600M  0 part /boot/efi
├─vda2                253:2    0    1G  0 part /boot
└─vda3                253:3    0 38.4G  0 part 
  ├─klas-root         252:0    0 34.4G  0 lvm  /
  └─klas-swap         252:1    0    4G  0 lvm  [SWAP]
vdb                   253:16   0 1000G  0 disk 
└─vdb1                253:17   0  500G  0 part 
  └─mysql-mysql--mycg 252:2    0  500G  0 lvm  /mysql

这里可以看到出来vdb磁盘一共1000G,分区vdb1 为500G,然后这500G加入到vg中并分配了lv.

vdb磁盘现状

[root@0002 mysql]# lsblk /dev/vdb
NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
vdb                   253:16   0 1000G  0 disk 
└─vdb1                253:17   0 1000G  0 part 

Disk /dev/vdb: 1000 GiB, 1073741824000 bytes, 2097152000 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x5a6aaeee

Device     Boot Start        End    Sectors  Size Id Type
/dev/vdb1        2048 2097151999 2097149952 1000G 8e Linux LVM

这里基本上可以确定,vdb1磁盘分区从以前的500G变成了1000G(也就是说被重新分区了,后续和现场沟通确认进行了重新分区操作)


通过history日志追述大概的操作过程

  898  [2025-09-28 11:55:13][root]fdisk -l
  899  [2025-09-28 11:55:21][root]df -h
  900  [2025-09-28 11:56:41][root]lsblk
  901  [2025-09-28 11:59:44][root]fdisk /dev/vdb
  902  [2025-09-28 12:00:46][root]partprobe /dev/vdb
  903  [2025-09-28 12:00:50][root]pvresize /dev/vdb1
  904  [2025-09-28 12:00:56][root]df -h
  905  [2025-09-28 12:01:25][root]vgdisplay mysql
  906  [2025-09-28 12:01:40][root]lsblk
  907  [2025-09-28 12:02:05][root]sudo partprobe /dev/vdb
  908  [2025-09-28 12:02:10][root]pvresize /dev/vdb1
  909  [2025-09-28 12:02:27][root]sudo pvresize /dev/vdb1
  910  [2025-09-28 12:03:07][root]sudo pvcreate /dev/vdb1
  911  [2025-09-28 12:03:22][root]sudo pvscan
  912  [2025-09-28 12:03:30][root]sudo pvdisplay
  913  [2025-09-28 12:05:37][root]parted /dev/vdb
  914  [2025-09-28 12:06:11][root]pvresize /dev/vdb1
  915  [2025-09-28 12:06:15][root]lsblk
  916  [2025-09-28 12:09:48][root]lvextend -l +100%FREE /dev/mysql/mysql--mycg
  917  [2025-09-28 12:10:00][root]cd /dev/mysql/
  918  [2025-09-28 12:10:01][root]ll
  919  [2025-09-28 12:10:20][root]pwd
  920  [2025-09-28 12:10:32][root]lvextend -l +100%FREE /dev/mysql/mysql-mycg
  921  [2025-09-28 12:10:55][root]lsblk /dev/vdb

基本上可以确定9月28日先进行了fdisk分区操作,然后尝试pvresize 操作[应该不会成功,因为重新分区导致pv信息丢失],然后进行了pvcreate之后再次进行parted分区操作,再pvresize,lvextend操作[同理pv信息丢失应该不会成功],然后10月5日继续进行的部分操作

  956  [2025-10-05 08:29:27][root]umount /mysql
  957  [2025-10-05 08:29:38][root]lsof /mysql
  958  [2025-10-05 08:29:58][root]service mysqld stop
  959  [2025-10-05 08:30:02][root]umount /mysql
  960  [2025-10-05 08:30:05][root]lsof /mysql
  961  [2025-10-05 08:30:23][root]cd /
  962  [2025-10-05 08:30:25][root]umount /mysql
  963  [2025-10-05 08:30:34][root]pvcreate --force /dev/vdb1
  964  [2025-10-05 08:30:47][root]vgextend mysql /dev/vdb1
  965  [2025-10-05 08:31:02][root]df -h
  966  [2025-10-05 08:31:33][root]pvdisplay /dev/vdb1
  967  [2025-10-05 08:31:41][root]pvcreate --force /dev/vdb1
  968  [2025-10-05 08:32:11][root]lvs | grep mysql-mysql--mycg
  969  [2025-10-05 08:32:19][root]dmsetup ls | grep mysql
  970  [2025-10-05 08:32:38][root]fuser /dev/vdb1
  971  [2025-10-05 08:32:41][root]lsof /dev/vdb1
  972  [2025-10-05 08:32:50][root]pvcreate --force /dev/vdb1
  973  [2025-10-05 08:33:14][root]reboot
  974  [2025-10-05 08:36:23][root]pvcreate --force /dev/vdb1
  975  [2025-10-05 08:36:47][root]lvdisplay /dev/mapper/mysql-mysql--mycg
  976  [2025-10-05 08:36:53][root]vgextend mysql /dev/vdb1
  977  [2025-10-05 08:37:10][root]lvextend -l +100%FREE /dev/mysql/mysql--mycg

初步看,应该是先尝试umount /dev/vdb1,但是没有成功,然后直接reboot重启了主机,起来之后,进行了pvcreate[操作成功],vgextend,lvextend等操作[失败,因为vg里面的之前的pv信息已经丢失],而且之前lv无法mount成功,数据库文件/备份均在这个lv里面,而且从库很久之前没有正常同步.基于这样的情况,就一定要对vdb磁盘中数据进行恢复.查看操作系统信息,确认是arm系统
arm


由于arm系统一般工具均无法正常解析,只能让客户把磁盘挂载到x86环境进行处理,通过专业恢复工具解析,运气不错可以直接读取数据
m1

传输数据到客户服务器中,并成功启动mysql,客户测试业务没有任何问题,数据完整恢复
2

redhat系列7/8进入单用户模式

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:redhat系列7/8进入单用户模式

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

以前写过一篇文章在linux老版本中,进入单用户模式的方法:linux 4/5/6版本进入单用户模式,今天测试主流的redhat系列(测试使用OEL,没有本质区别)7和8版本中进入单用户.
主要操作步骤:
1)选择linux启动项,输入e
2)根据你的RHEL/CentOS/OEL版本,找到 linux16/linux/linuxefi等类似启动语句语句,按下键盘上的 End 键,跳到行末,添加关键词 rd.break,按下 Ctrl+x 或 F10 来进入单用户模式
3)mount 根文件系统为读写模式:mount -o remount,rw /sysroot
4)指定/sysroot为/挂载点:chroot /sysroot
5)进行需要的系统操作,比如重设root密码,修改不合适的系统配置(fstab,sysctl.conf等),然后sync同步数据
6)重启系统:reboot -f(也可以两次exit实现重启)
linux 7系列进入单用户演示
s1
s2
s3


linux 8系列进入单用户演示
s4
s5
s6

Failed to open \EFI\redhat\grubx64.efi – Not Found 故障处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:Failed to open EFIredhatgrubx64.efi – Not Found 故障处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

由于某种原因导致linux的grubx64.efi文件丢失,系统重启报错(Failed to open \EFI\redhat\grubx64.efi – Not Found)
grubx64-not-found


grubx64.efi 是 64 位 UEFI 系统 下的 GRUB 引导程序核心文件,作用是启动操作系统.现在系统启动报该文件丢失,导致系统无法正常引导启动.对于这样的情况,比较简单的方法就是使用相同版本的操作系统iso文件进入救援模式,然后把grubx64.efi文件拷贝进去,具体操作步骤:
1.使用光盘启动,并进入Troubleshooting –> 并回车
troubleshooting

2. 选择:Rescue a Oracle Linux system 并回车
rescue

3. 选择1) Continue ,然后回车继续执行
QQ20251004-090450

4. 可以看到原系统分区被挂载在/mnt/sysimage下面的相关挂载点
QQ20251004-090550

5. 输入chroot /mnt/sysimage按照原系统方式进行挂载
QQ20251004-090617

6. 确认grubx64.efi文件丢失
QQ20251004-090645

7. 挂载系统盘到/media,并确认grubx64.efi文件在系统盘中存在
QQ20251004-090804

8. 拷贝系统盘中的grubx64.efi到/boot/efi/EFI/redhat/中
QQ20251004-091128

9. 退出救援模式,系统启动正常,完成grubx64.efi文件丢失修复故障

11.2.0.4升级到19c详细操作过程

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:11.2.0.4升级到19c详细操作过程

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

随着硬件生命周期,数据库等保要求等,不少客户需要把数据库从11.2.0.4版本升级到19c.对于这样的客户的核心生产库升级,一般考虑到回退情况,大部分会选择异机进行升级,提供一个完整版测试
在源库(11.2.0.4)中做升级之前检查

--触发器检查(禁用和启用脚本生成)
SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' DISABLE;' AS disable_commands
FROM dba_triggers 
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT') 
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';


SELECT 'ALTER TRIGGER ' || owner || '.' || trigger_name || ' ENABLE;' AS disable_commands
FROM dba_triggers 
WHERE trigger_type IN ('BEFORE EVENT', 'AFTER EVENT') 
AND triggering_event LIKE '%DDL%'
AND status = 'ENABLED';

--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

--检查时区信息
select * from  v$timezone_file;
select  distinct owner from  dba_tab_columns where 
DATA_TYPE='TIMESTAMP(6) WITH TIME ZONE';

--物化视图刷新检查
SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 

--无效index检查
set linesize 400
select owner,index_name from dba_indexes where status in ('INVALID','UNUSABLE') ;
select index_owner,index_name,partition_name,status from dba_ind_partitions where status in ('INVALID','UNUSABLE') ;

--清空审计表和所属表空间检查
truncate table aud$;
SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';

--清理19c中无法升级组件
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@olspreupgrade.sql
@emremove.sql
@catnoamd.sql
@catnoexf.sql
@$ORACLE_HOME/apex/apxremov.sql
@?/rdbms/admin/utlprp.sql 32
select owner,object_type from dba_objects where object_name =upper('htmldb_system');
drop package htmldb_system;
drop public synonym htmldb_system;

--检查数据文件不处于备份状态
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; 

--清空回收站
PURGE DBA_RECYCLEBIN;

--检查 SYS 及 SYSTEM默认表空间
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

--检查组件状态
set pagesize 500 
set linesize 400
col comp_name for a40 
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;

--记录无效对象
create table system.invalid_obj_10g_beforeup tablespace sysaux as select substr(object_name,1,40) object_name,
substr(owner,1,15) owner, object_type from  dba_objects where status='INVALID' order by owner,object_type;

--禁用Block Change Tracking
SELECT filename, status, bytes FROM v$block_change_tracking;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

--执行 preupgrade 脚本
$ORACLE_HOME/jdk/bin/java -jar /u01/xff/preupgrade.jar FILE TEXT DIR /tmp
@/tmp/preupgrade_fixups.sql

--执行dbupgdiag.sql脚本
alter session set nls_language='American';
SET ECHO ON;
SET SERVEROUTPUT ON;
@dbupgdiag.sql

rman备份还原数据库

--在11.2.0.4库备份操作
backup   filesperset = 5 as compressed backupset  database format
  '/u01/rmanback/full_%T_%U.rman';
sql 'alter system archive log current';
sql 'alter system archive log current';
Backup filesperset = 10 as compressed backupset archivelog all format 
  '/u01/rmanback/arch_%T_%U.rman' not backed up delete input;
backup  format '/u01/rmanback/ctl_%T_%U.rman' current controlfile;

--在19c库还原操作
SQL>startup nomount pfile='/tmp/pfile'
RMAN> restore controlfile from '/u01/rmanback/ctl_20251003_0a459rsp_1_1.rman';
RMAN> alter database mount;
RMAN>  catalog start with '/u01/orabak/';
RMAN> restore database;
RMAN> recover database;   --可以增量追加归档
SQL> alter database open resetlogs upgrade;

正式升级操作(19c环境)

startup pfile='/u01/xff/pfile.upgrade'  mount;
alter database open upgrade;
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
cd $ORACLE_HOME/bin
./dbupgrade

sqlplus / as sysdba
startup
@?/rdbms/admin/utlusts.sql TEXT
@?/rdbms/admin/utlrp.sql 32
@?/rdbms/admin/utlusts.sql TEXT

set pagesize500 
set linesize 400
col comp_name for a40 
select substr(comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;

升级完成后操作

--执行postupgrade_fixups
@/tmp/postupgrade_fixups.sql

--收集字典统计信息
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;


--升级时区
cd $ORACLE_HOME/rdbms/admin
@utltz_countstats.sql
@utltz_countstar.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql

主要日志

--升级操作日志
[oracle@oracledb:/u01/app/oracle/product/19c/db/bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19c/db/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]


/u01/app/oracle/product/19c/db/rdbms/admin/orahome = [/u01/app/oracle/product/19c/db]
/u01/app/oracle/product/19c/db/bin/orabasehome = [/u01/app/oracle/product/19c/db]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c/db]

Analyzing file /u01/app/oracle/product/19c/db/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20251003103800]

catcon::set_log_file_base_path: ALL catcon-related output will be written to
  [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_catcon_15770.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd*.log] 
  files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20251003103800/catupgrd_*.lst]
   files for spool files, if any


Number of Cpus        = 4
Database Name         = orcl
DataBase Version      = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to 
  [/u01/xxx/upgrade20251003103801/catupgrd_catcon_15770.lst]

catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd*.log]
   files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/xxx/upgrade20251003103801/catupgrd_*.lst] 
   files for spool files, if any


Log file directory = [/u01/xxx/upgrade20251003103801]

Parallel SQL Process Count            = 4
Components in [orcl]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX DV EM MGW ODM OLS RAC WK]
DataBase Version      = 11.2.0.4.0

------------------------------------------------------
Phases [0-107]         Start Time:[2025_10_03 10:38:01]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [orcl] Files:1    Time: 30s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl] Files:5    Time: 18s
Restart  Phase #:2    [orcl] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [orcl] Files:19   Time: 9s
Restart  Phase #:4    [orcl] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [orcl] Files:7    Time: 7s
*****************   Catproc Start   ****************
Serial   Phase #:6    [orcl] Files:1    Time: 5s
*****************   Catproc Types   ****************
Serial   Phase #:7    [orcl] Files:2    Time: 4s
Restart  Phase #:8    [orcl] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [orcl] Files:70   Time: 9s
Restart  Phase #:10   [orcl] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [orcl] Files:1    Time: 25s
Restart  Phase #:12   [orcl] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [orcl] Files:95   Time: 2s
Restart  Phase #:14   [orcl] Files:1    Time: 0s
Parallel Phase #:15   [orcl] Files:122  Time: 4s
Restart  Phase #:16   [orcl] Files:1    Time: 0s
Serial   Phase #:17   [orcl] Files:25   Time: 1s
Restart  Phase #:18   [orcl] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [orcl] Files:32   Time: 6s
Restart  Phase #:20   [orcl] Files:1    Time: 0s
Serial   Phase #:21   [orcl] Files:3    Time: 4s
Restart  Phase #:22   [orcl] Files:1    Time: 1s
Parallel Phase #:23   [orcl] Files:25   Time: 79s
Restart  Phase #:24   [orcl] Files:1    Time: 0s
Parallel Phase #:25   [orcl] Files:12   Time: 49s
Restart  Phase #:26   [orcl] Files:1    Time: 0s
Serial   Phase #:27   [orcl] Files:1    Time: 0s
Serial   Phase #:28   [orcl] Files:4    Time: 1s
Serial   Phase #:29   [orcl] Files:1    Time: 0s
Restart  Phase #:30   [orcl] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [orcl] Files:1    Time: 0s
Restart  Phase #:32   [orcl] Files:1    Time: 0s
Serial   Phase #:34   [orcl] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [orcl] Files:297  Time: 9s
Serial   Phase #:36   [orcl] Files:1    Time: 0s
Restart  Phase #:37   [orcl] Files:1    Time: 0s
Serial   Phase #:38   [orcl] Files:10   Time: 2s
Restart  Phase #:39   [orcl] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [orcl] Files:3    Time: 21s
Restart  Phase #:41   [orcl] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [orcl] Files:13   Time: 50s
Restart  Phase #:43   [orcl] Files:1    Time: 1s
Parallel Phase #:44   [orcl] Files:11   Time: 3s
Restart  Phase #:45   [orcl] Files:1    Time: 0s
Parallel Phase #:46   [orcl] Files:3    Time: 0s
Restart  Phase #:47   [orcl] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [orcl] Files:1    Time: 5s
Restart  Phase #:49   [orcl] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [orcl] Files:1    Time: 8s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [orcl] Files:1    Time: 0s
Restart  Phase #:52   [orcl] Files:1    Time: 0s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [orcl] Files:2    Time: 161s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [orcl] Files:1    Time: 0s
Serial   Phase #:56   [orcl] Files:3    Time: 9s
Serial   Phase #:57   [orcl] Files:3    Time: 1s
Parallel Phase #:58   [orcl] Files:10   Time: 1s
Parallel Phase #:59   [orcl] Files:25   Time: 2s
Serial   Phase #:60   [orcl] Files:4    Time: 3s
Serial   Phase #:61   [orcl] Files:1    Time: 0s
Serial   Phase #:62   [orcl] Files:32   Time: 1s
Serial   Phase #:63   [orcl] Files:1    Time: 0s
Parallel Phase #:64   [orcl] Files:6    Time: 4s
Serial   Phase #:65   [orcl] Files:2    Time: 8s
Serial   Phase #:66   [orcl] Files:3    Time: 29s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [orcl] Files:1    Time: 0s
Serial   Phase #:69   [orcl] Files:1    Time: 0s
Parallel Phase #:70   [orcl] Files:2    Time: 14s
Restart  Phase #:71   [orcl] Files:1    Time: 0s
Parallel Phase #:72   [orcl] Files:2    Time: 0s
Serial   Phase #:73   [orcl] Files:2    Time: 0s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [orcl] Files:1    Time: 0s
Serial   Phase #:76   [orcl] Files:1    Time: 16s
Serial   Phase #:77   [orcl] Files:2    Time: 0s
Restart  Phase #:78   [orcl] Files:1    Time: 0s
Serial   Phase #:79   [orcl] Files:1    Time: 8s
Restart  Phase #:80   [orcl] Files:1    Time: 0s
Parallel Phase #:81   [orcl] Files:3    Time: 10s
Restart  Phase #:82   [orcl] Files:1    Time: 0s
Serial   Phase #:83   [orcl] Files:1    Time: 2s
Restart  Phase #:84   [orcl] Files:1    Time: 0s
Serial   Phase #:85   [orcl] Files:1    Time: 4s
Restart  Phase #:86   [orcl] Files:1    Time: 0s
Parallel Phase #:87   [orcl] Files:4    Time: 28s
Restart  Phase #:88   [orcl] Files:1    Time: 0s
Serial   Phase #:89   [orcl] Files:1    Time: 0s
Restart  Phase #:90   [orcl] Files:1    Time: 0s
Serial   Phase #:91   [orcl] Files:2    Time: 4s
Restart  Phase #:92   [orcl] Files:1    Time: 0s
Serial   Phase #:93   [orcl] Files:1    Time: 0s
Restart  Phase #:94   [orcl] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [orcl] Files:1    Time: 6s
Restart  Phase #:96   [orcl] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:97   [orcl] Files:1    Time: 1s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [orcl] Files:1    Time: 22s
*******************   Migration   ******************
Serial   Phase #:99   [orcl] Files:1    Time: 15s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [orcl] Files:1    Time: 0s
Serial   Phase #:101  [orcl] Files:1    Time: 0s
Serial   Phase #:102  [orcl] Files:1    Time: 35s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [orcl] Files:1    Time: 14s
****************   Summary report   ****************
Serial   Phase #:104  [orcl] Files:1    Time: 0s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [orcl] Files:1    Time: 0s
Serial   Phase #:106  [orcl] Files:1    Time: 0s
Serial   Phase #:107  [orcl] Files:1     Time: 50s

------------------------------------------------------
Phases [0-107]         End Time:[2025_10_03 10:51:23]
------------------------------------------------------

Grand Total Time: 802s 

 LOG FILES: (/u01/xxx/upgrade20251003103801/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/xxx/upgrade20251003103801/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:13m:22s]


--组件状态
SQL>@?/rdbms/admin/utlusts.sql TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2025 10:53:4
Database Name: ORCL

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED     19.16.0.0.0  00:05:52
JServer JAVA Virtual Machine           UPGRADED     19.16.0.0.0  00:01:28
Oracle XDK                             UPGRADED     19.16.0.0.0  00:00:16
Oracle Database Java Packages          UPGRADED     19.16.0.0.0  00:00:05
OLAP Analytic Workspace                UPGRADED     19.16.0.0.0  00:00:04
Oracle Text                            UPGRADED     19.16.0.0.0  00:00:18
Oracle Workspace Manager               UPGRADED     19.16.0.0.0  00:00:26
Oracle Real Application Clusters     OPTION OFF     19.16.0.0.0  00:00:00
Oracle XML Database                    UPGRADED     19.16.0.0.0  00:00:58
Oracle Multimedia                      UPGRADED     19.16.0.0.0  00:00:13
Spatial                                UPGRADED     19.16.0.0.0  00:01:11
Oracle OLAP API                        UPGRADED     19.16.0.0.0  00:00:05
Datapatch                                                        00:00:19
Final Actions                                                    00:00:37
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:11:54

Database time zone version is 14. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

--编译无效对象
SQL>@?/rdbms/admin/utlrp.sql 32

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2025-10-03 10:53:48
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2025-10-03 10:55:35
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

--编译之后组件状态正常
SQL>@?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool    10-03-2025 10:55:3
Database Name: ORCL

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                             VALID     19.16.0.0.0  00:05:52
JServer JAVA Virtual Machine              VALID     19.16.0.0.0  00:01:28
Oracle XDK                                VALID     19.16.0.0.0  00:00:16
Oracle Database Java Packages             VALID     19.16.0.0.0  00:00:05
OLAP Analytic Workspace                   VALID     19.16.0.0.0  00:00:04
Oracle Text                               VALID     19.16.0.0.0  00:00:18
Oracle Workspace Manager                  VALID     19.16.0.0.0  00:00:26
Oracle Real Application Clusters     OPTION OFF     19.16.0.0.0  00:00:00
Oracle XML Database                       VALID     19.16.0.0.0  00:00:58
Oracle Multimedia                         VALID     19.16.0.0.0  00:00:13
Spatial                                   VALID     19.16.0.0.0  00:01:11
Oracle OLAP API                           VALID     19.16.0.0.0  00:00:05
Datapatch                                                        00:00:19
Final Actions                                                    00:00:37
Post Upgrade                                                     00:00:12
Post Compile                                                     00:01:47

Total Upgrade Time: 00:13:41

Database time zone version is 14. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

--时区升级之后结果
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
32

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


3 rows selected.

具体参考:Oracle 19c – 手动升级到 Non-CDB Oracle Database 19c 的完整核对清单 (Doc ID 2577572.1)