分类目录归档：数据库

一个客户硬件故障,进行了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)


尝试重建控制文件,报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)

*** 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

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恢复之后最大限度的数据恢复