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 2025Version 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 - ProductionVersion 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.txtPlease Enter Password:  ===== Starting Datafile Header modification program =====Datafile list file: 1.txtOperation Mode: Only Modify Datafile Header CheckPointBlock Size: 8192Log Path: /tmp/modify_scn---------------------------------------------------------Preparing Datafile list file...Verifying Datafile existence...ERROR: Datafile  does not exist!Datafile verification passedInitializing 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 2025Version 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 - ProductionVersion 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 forcedORA-00604: error occurred at recursive SQL level 2ORA-01578: ORACLE data block corrupted (file # 1, block # 132585)ORA-01110: data file 1: '/data/app/oracle/oradata/ORCL/system01.dbf'Process ID: 1617Session ID: 1 Serial number: 5

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

2025-10-14T21:38:22.889985+08:00MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setstopping change tracking2025-10-14T21:38:22.909799+08:00Starting background process SMCO2025-10-14T21:38:22.930114+08:00SMCO started with pid=57, OS id=1630 2025-10-14T21:38:23.000833+08:00ARC0 (PID:1616): Archived Log entry 3 added for T-1.S-3205 ID 0x6539a8b7 LAD:12025-10-14T21:38:23.524409+08:00Undo 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 beginningTablespace 'TEMP' #3 found in data dictionary,but not in the controlfile. Adding to controlfile.Dictionary check completeVerifying 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  ADD TEMPFILE           Alternatively, if these temporary tablespaces are no longer         needed, then they can be dropped.           Empty temporary tablespace: TEMP*********************************************************************Database Characterset is AL32UTF8No Resource Manager plan active2025-10-14T21:38:24.645423+08:00Hex 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 readData 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:00Errors 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.trc2025-10-14T21:38:24.861113+08:00Corrupt 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 Segment2025-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, whenanalysis of the redo logs will help Oracle Support with thediagnosis.It is recommended that you retain all the redo logs generated (byall the instances) during the past 12 hours, in case additionalredo dumps are required to help with the diagnosis.*****************************************************************2025-10-14T21:38:26.769742+08:00Errors in file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc:ORA-00604: error occurred at recursive SQL level 2ORA-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:00Errors in file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc:ORA-00604: error occurred at recursive SQL level 2ORA-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 databaseErrors in file /u01/app/diag/rdbms/orcl/ORCL/trace/ORCL_ora_26765.trc  (incident=51503):ORA-00603: ORACLE server session terminated by fatal errorORA-01092: ORACLE instance terminated. Disconnection forcedORA-00604: error occurred at recursive SQL level 2ORA-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.trcInvalid temporary block relative dba: 0x004205e9 (file 1, block 132585)Bad header found during validationData 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 dataReread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt dataReread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt dataReread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt dataReread of blocknum=132585, file=/data/app/oracle/oradata/ORCL/system01.dbf. found same corrupt dataChecker run found 1 new persistent data failures2025-10-14T21:38:27.515191+08:00opiodr aborting process unknown ospid (26765) as a result of ORA-6032025-10-14T21:38:27.528063+08:00ORA-603 : opitsk aborting processLicense high water mark = 18USER (ospid: (prelim)): terminating the instance due to ORA error 2025-10-14T21:38:28.576271+08:00Instance 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故障处理
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！