C_OBJ#_INTCOL#坏块导致数据库无法open故障---惜分飞

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

标题：C_OBJ#_INTCOL#坏块导致数据库无法open故障处理

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

客户通过硬件恢复出来数据文件之后,尝试启动数据库报错,他们经过多轮尝试依旧无法open数据库,还原到恢复出来文件的初始状态,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本检测,发现file# 3 需要sequence#为3200的日志,其他文件需要sequence#为3205的日志

---

检查发现数据库为非归档模式,而且sequence#为3200的redo已经被覆盖

---

基于上述情况,可以确认除file# 3之外,其他文件可以正常recover

 

[oracle@oracledb check_db]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 15 16:08:33 2025

Version 19.3.0.0.0

 

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

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> recover datafile 1;

Media recovery complete.

SQL> recover datafile 2,4,5,7,8,9,10,11,12,13,14,15,16,17;

Media recovery complete.

SQL> recover datafile 18,19,20,21;

Media recovery complete.

对于缺少日志的数据文件,直接使用自研的m_scn(modify scn)工具进行修改

[oracle@oracledb check_db]$ chmod +x m_scn

[oracle@oracledb check_db]$ ./m_scn 1.txt

Please Enter Password:

 

===== Starting Datafile Header modification program =====

Datafile list file: 1.txt

Operation Mode: Only Modify Datafile Header CheckPoint

Block Size: 8192

Log Path: /tmp/modify_scn

---------------------------------------------------------

Preparing Datafile list file...

Verifying Datafile existence...

ERROR: Datafile  does not exist!

Datafile verification passed

Initializing working directory...

Recovery script created: /tmp/modify_scn/backup/recover_datafile.sh

---------------------------------------------------------

Starting Datafile Header processing (total 3 files)...

[1/3] Processing Datafile Header: /data/20251014HF/oradata/system01.dbf (File number: 1)

  - Skipping file number 1 (control file)

---------------------------------------------------------

[2/3] Processing Datafile Header: /data/20251014HF/oradata/sysaux01.dbf (File number: 3)

  - Backing up Datafile header...

  - Executing Datafile Header modification with block size 8192...

  - Datafile Header processing completed

---------------------------------------------------------

Cleaning up temporary files...

================= All operations completed =================

 

Note: Execute /tmp/modify_scn/backup/recover_datafile.sh operation for rollback

[oracle@oracledb check_db]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 15 16:15:12 2025

Version 19.3.0.0.0

 

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

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> recover datafile 3;

Media recovery complete.

然后尝试打开数据库报ORA-01092 ORA-01578等错误

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 2

ORA-01578: ORACLE data block corrupted (file # 1, block # 132585)

ORA-01110: data file 1: '/data/app/oracle/oradata/ORCL/system01.dbf'

Process ID: 1617

Session ID: 1 Serial number: 5

报错比较明显是由于坏块导致数据库无法打开,进一步检查alert日志

2025-10-14T21:38:22.889985+08:00

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

stopping change tracking

2025-10-14T21:38:22.909799+08:00

Starting background process SMCO

2025-10-14T21:38:22.930114+08:00

SMCO started with pid=57, OS id=1630

2025-10-14T21:38:23.000833+08:00

ARC0 (PID:1616): Archived Log entry 3 added for T-1.S-3205 ID 0x6539a8b7 LAD:1

2025-10-14T21:38:23.524409+08:00

Undo initialization recovery: err:0 start: 19911054 end: 19911123 diff: 69 ms (0.1 seconds)

2025-10-14T21:38:23.826920+08:00

[26765] Successfully onlined Undo Tablespace 2.

Undo initialization online undo segments: err:0 start: 19911123 end: 19911426 diff: 303 ms (0.3 seconds)

Undo initialization finished serial:0 start:19911054 end:19911454 diff:400 ms (0.4 seconds)

Dictionary check beginning

Tablespace 'TEMP' #3 found in data dictionary,

but not in the controlfile. Adding to controlfile.

Dictionary check complete

Verifying minimum file header compatibility for tablespace encryption..

Verifying file header compatibility for tablespace encryption completed for pdb 0

*********************************************************************

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

*********************************************************************

Database Characterset is AL32UTF8

No Resource Manager plan active

2025-10-14T21:38:24.645423+08:00

Hex dump of (file 1, block 132585) in trace file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc

 

Invalid temporary block relative dba: 0x004205e9 (file 1, block 132585)

Bad header found during buffer read

Data in bad block:

 type: 66 format: 2 rdba: 0x4c444242

 last change scn: 0x4c44.4242.4c444242 seq: 0x44 flg: 0x4c

 spare3: 0x4c44

 consistency value in tail: 0xfcc60601

 check value in block header: 0x4242

 computed block checksum: 0x9e0a

 

Reading datafile '/data/app/oracle/oradata/ORCL/system01.dbf' for corrupt data at rdba:0x004205e9(file 1,block 132585)

Reread (file 1, block 132585) found same corrupt data (no logical check)

2025-10-14T21:38:24.838291+08:00

Errors in file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc  (incident=51502):

ORA-01578: ORACLE data block corrupted (file # 1, block # 132585)

ORA-01110: data file 1: '/data/app/oracle/oradata/ORCL/system01.dbf'

Incident details in: /u01/app/diag/rdbms/orcl/ORCL/incident/incdir_51502/ORCL_ora_26765_i51502.trc

2025-10-14T21:38:24.861113+08:00

Corrupt Block Found

         TIME STAMP (GMT) = 10/14/2025 21:38:23

         CONT = 0, TSN = 0, TSNAME = SYSTEM

         RFN = 1, BLK = 132585, RDBA = 4326889

         OBJN = 66, OBJD = 64, OBJECT = C_OBJ#_INTCOL#, SUBOBJECT =

         SEGMENT OWNER = SYS, SEGMENT TYPE = Cluster Segment

2025-10-14T21:38:26.554220+08:00

*****************************************************************

An internal routine has requested a dump of selected redo.

This usually happens following a specific internal error, when

analysis of the redo logs will help Oracle Support with the

diagnosis.

It is recommended that you retain all the redo logs generated (by

all the instances) during the past 12 hours, in case additional

redo dumps are required to help with the diagnosis.

*****************************************************************

2025-10-14T21:38:26.769742+08:00

Errors in file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc:

ORA-00604: error occurred at recursive SQL level 2

ORA-01578: ORACLE data block corrupted (file # 1, block # 132585)

ORA-01110: data file 1: '/data/app/oracle/oradata/ORCL/system01.dbf'

2025-10-14T21:38:26.769940+08:00

Errors in file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc:

ORA-00604: error occurred at recursive SQL level 2

ORA-01578: ORACLE data block corrupted (file # 1, block # 132585)

ORA-01110: data file 1: '/data/app/oracle/oradata/ORCL/system01.dbf'

Error 604 happened during db open, shutting down database

Errors in file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc  (incident=51503):

ORA-00603: ORACLE server session terminated by fatal error

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 2

ORA-01578: ORACLE data block corrupted (file # 1, block # 132585)

ORA-01110: data file 1: '/data/app/oracle/oradata/ORCL/system01.dbf'

Incident details in: /u01/app/diag/rdbms/orcl/ORCL/incident/incdir_51503/ORCL_ora_26765_i51503.trc

Invalid temporary block relative dba: 0x004205e9 (file 1, block 132585)

Bad header found during validation

Data in bad block:

 type: 66 format: 2 rdba: 0x4c444242

 last change scn: 0x4c44.4242.4c444242 seq: 0x44 flg: 0x4c

 spare3: 0x4c44

 consistency value in tail: 0xfcc60601

 check value in block header: 0x4242

 computed block checksum: 0x9e0a

 

Reread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt data

Reread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt data

Reread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt data

Reread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt data

Reread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt data

Checker run found 1 new persistent data failures

2025-10-14T21:38:27.515191+08:00

opiodr aborting process unknown ospid (26765) as a result of ORA-603

2025-10-14T21:38:27.528063+08:00

ORA-603 : opitsk aborting process

License high water mark = 18

USER (ospid: (prelim)): terminating the instance due to ORA error

2025-10-14T21:38:28.576271+08:00

Instance terminated by USER(prelim), pid = 26765

通过分析alert日志可以确定是由于file 1, block 132585损坏,对应的对象为C_OBJ#_INTCOL#,该对象是histgrm$表的cluster,非数据库必须核心对象,可以在启动的时候跳过该对象,启动数据库,然后设置event对该对象进行处理

SQL> alter database open;

 

Database altered.

 

SQL>  create table good_histgrm$ as select * from histgrm$;

 

Table created.

 

SQL> truncate cluster c_obj#_intcol#;

 

Cluster truncated.

 

SQL> insert into histgrm$ select * from good_histgrm$ ;

 

286071 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select /*+ full(a) */ count(*) from histgrm$ a ;

 

  COUNT(*)

----------

    286071

 

SQL> select /*+ full(a) */ count(*) from histgrm$ a ;

 

  COUNT(*)

----------

    286071

至此整体上完成该库的恢复任务

• 解决一次硬件恢复之后数据文件0kb的故障恢复case
• 在线mv方式迁移数据文件导致数据库无法正常启动
• ORA-600 kcbzpbuf_1故障恢复
• Oracle Recovery Tools 解决ORA-600 3020故障
• 虚拟机故障引起ORA-00310 ORA-00334故障处理
• ora-600 kcratr_scan_lastbwr
• 数据库文件变成32k故障恢复
• ORA-600 3020/ORA-600 2662故障
• 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
• 差点被误操作的ORA-600 kcratr_nab_less_than_odr故障
• 一起ORA-600 3020故障恢复的大体思路
• ORA-600 kcratr_nab_less_than_odr和ORA-600 4194故障处理