Oracle非归档模式遇到文件损坏怎么办？
青
潇湘秦约 12 分钟
潇湘秦约 12 分钟昨天夜里基地夜班的兄弟，打电话说有个报表库连不上了，赶紧起来连上VPN查看一下，看到实例宕机了，先赶紧startup起来。
1.查看报错信息
环境介绍：Redhat 6.9 Oracle 11.2.0.4 No Archive Mode
查看alert log 关键报错信息如下
Thread1 advanced to log sequence 4231012 (LGWR switch)
Current log# 2 seq# 4231012 mem# 0: /oradata/rtp/redo02.log
Thu May 0823:22:562025
KCF: read, write or open error, block=0x240ab online=1
file=118 '/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf'
error=27072 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 147627
Additional information: -1'
Errors in file /u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp_dbw0_3300.trc:
Errors in file /u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp_dbw0_3300.trc:
ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 118 (block # 147627)
ORA-01110: data file 118: '/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf'
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 147627
Additional information: -1
DBW0 (ospid: 3300): terminating the instance due to error 63999
Thu May 0823:22:572025
System state dump requested by (instance=1, osid=3300 (DBW0)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp_diag_3292_20250508232257.trc
Instance terminated by DBW0, pid = 3300排查路径 查看报错的trc文件
Trace file /u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp\_dbw0\_3300.trc
Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, DataMiningandReal Application Testing options
ORACLE\_HOME = /u01/app/oracle/product/11.2.0/db\_1
Systemname: Linux
Node name: rtpdb
Release: 2.6.32-696.el6.x86\_64
Version: #1 SMP Tue Feb 21 00:53:17 EST 2017
Machine: x86\_64
VM name: VMWare Version: 6
Instancename: rtp
Redothread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 3300, image: oracle\@rtpdb (DBW0)
\*\*\* 2025-05-0823:22:56.680
\*\*\* SESSIONID:(1521.1) 2025-05-0823:22:56.680
\*\*\* CLIENTID:() 2025-05-0823:22:56.680
\*\*\* SERVICE NAME:(SYS\$BACKGROUND) 2025-05-0823:22:56.680
\*\*\* MODULENAME:() 2025-05-0823:22:56.680
\*\*\* ACTIONNAME:() 2025-05-0823:22:56.680
KCF: read, write oropenerror, block=0x240abonline=1
file=118'/oradata2/rtp/RTP/datafile/o1\_mf\_tbs\_ods\_n1qx02j0\_.dbf'
error=27072 txt: 'Linux-x86\_64 Error: 5: Input/output error
Additional information: 4
Additional information: 147627
Additional information: -1'
Encountered write error
DDE rulesonly execution for: ORA 1110
\----- START Event Driven Actions Dump ----
\---- END Event Driven Actions Dump ----
\----- START DDE Actions Dump -----
Executing SYNC actions
\----- START DDE Action: 'DB\_STRUCTURE\_INTEGRITY\_CHECK' (Async) -----
Successfully dispatched
\----- END DDE Action: 'DB\_STRUCTURE\_INTEGRITY\_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
\----- END DDE Actions Dump (total 0 csec) -----
error63999 detected in background process
ORA-63999: datafile suffered media failure
ORA-01114: IO error writing blocktofile118 (block# 147627)
ORA-01110: datafile118: '/oradata2/rtp/RTP/datafile/o1\_mf\_tbs\_ods\_n1qx02j0\_.dbf'
ORA-27072: File I/O error
Linux-x86\_64 Error: 5: Input/outputerror
Additional information: 4
Additional information: 147627
Additional information: -1
kjzduptcctx: Notifying DIAG for crash event
\----- Abridged Call Stack Trace -----
ksedsts()+465<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+63<-ksuitm()+5570<-ksbrdp()+3507<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai\_real()+250<-ssthrdmain()+265<-main()+201<-\_\_libc\_start\_main()+253
\----- End of Abridged Call Stack Trace -----
\*\*\* 2025-05-0823:22:56.750
DBW0 (ospid: 3300): terminating the instance due toerror63999
ksuitm: waiting up to \[5] secondsbefore killing DIAG(3292)
\[oracle\@rtpdb \~]\$2. OS层面检查IO报错问题
2.1查看/oradata2挂载点是否正常，发现有较多的io错误
[oracle@rtpdb ~]$ dmesg | grep -i error
end_request: I/O error, dev sdc, sector 716711160
Buffer I/O error on device dm-0, logical block 89588639
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588640
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588641
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588642
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588643
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588644
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588645
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588646
lost page write due to I/O error on dm-0
Buffer I/O error on device dm-0, logical block 89588647
lost page write due to I/O error on dm-0
JBD2: Detected IO errors while flushing file data on dm-0-8
[root@rtpdb ~]# tail -f /var/log/messages
May 8 23:22:50 rtpdb kernel: Buffer I/O error on device dm-0, logical block 89588645
May 8 23:22:50 rtpdb kernel: lost page write due to I/O error on dm-0
May 8 23:22:50 rtpdb kernel: Buffer I/O error on device dm-0, logical block 89588646
May 8 23:22:50 rtpdb kernel: lost page write due to I/O error on dm-0
May 8 23:22:50 rtpdb kernel: Buffer I/O error on device dm-0, logical block 89588647
May 8 23:22:50 rtpdb kernel: lost page write due to I/O error on dm-0
May 8 23:22:50 rtpdb kernel: JBD2: Detected IO errors while flushing file data on dm-0-8
May 9 03:40:04 rtpdb rhsmd: In order for Subscription Manager to provide your system with updates, your system must be registered with the Customer Portal. Please enter your Red Hat login to ensure your system is up-to-date.
May 9 12:38:21 rtpdb kernel: NET: Unregistered protocol family 36
May 9 12:38:21 rtpdb kernel: NET: Registered protocol family 36
3.Rman检查报错的文件是否有坏块
RMAN>VALIDATEDATAFILE118;
Startingvalidateat09-MAY-25
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=647devicetype=DISK
channel ORA_DISK_1:startingvalidationofdatafile
channel ORA_DISK_1:specifyingdatafile(s)forvalidation
inputdatafilefilenumber=00118name=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf
channel ORA_DISK_1:validationcomplete,elapsed time:00:00:15
ListofDatafiles
=================
FileStatusMarkedCorruptEmptyBlocksBlocksExaminedHighSCN
-------------------------------------------------------------
118FAILED08542126880074401038924
File Name:/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf
BlockTypeBlocksFailingBlocksProcessed
----------------------------------------
Data099872
Index082856
Other49651
validatefoundoneormorecorruptblocks
Seetracefile/u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp_ora_22883.trcfordetails
Finishedvalidateat09-MAY-25
RMAN>listbackupsummary;
specificationdoesnotmatchanybackupintherepository
RMAN>从/u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp_ora_22883.trc中检查具体的有哪些block损坏， 一下检查到这么多corrupt block
而且还没有物理备份（非归档模式的库）？该如何处理
[oracle@rtpdb ~]$ cat /u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp_ora_22883.trc | grep -i "Corrupt"
Corrupt block relative dba: 0x1d82e5cf (file 118, block 189903)
Reread of blocknum=189903, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189903, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189903, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189903, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189903, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data。。。
Corrupt block relative dba: 0x1d82e5ff (file 118, block 189951)
Reread of blocknum=189951, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189951, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189951, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189951, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
Reread of blocknum=189951, file=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf. found same corrupt data
这里出现连续的block 189903 到 block 189918 至少有 16坏块
4.查看坏块对应的object
检查这些坏块是输入对应的哪个object，看到是一个表
SELECTtablespace_name,segment_type,owner,segment_name
FROMdba_extents
WHEREfile_id=118AND
(block_idBETWEEN189903AND189918OR
(block_id+blocks-1)BETWEEN189903AND189918OR
block_id<189903AND(block_id+blocks-1)>189918);TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ---------------------------------------------------------------------------------
TBS_ODS TABLE ODS LOT_MATERIAL_MASTER
5.标记坏块，防止操作失败
标记这些坏块并跳过，这个不算是标准处理流程，因为是报表库，元数据都是从另外一个库拉取的，这里标记跳过，联系报表的同事重建这个表
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
schema_name => 'ODS',
object_name => 'LOT_MATERIAL_MASTER',
object_type => DBMS_REPAIR.TABLE_OBJECT,
flags => DBMS_REPAIR.SKIP_FLAG);
END;
/PL/SQL procedure successfully completed.
5.1 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS包介绍
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS 用于告诉数据库在访问特定表或索引时跳过已知的坏块（corrupt blocks），从而避免访问错误中断操作。
✅ 主要作用
标记指定对象中的坏块为可跳过，当应用或查询访问这些坏块时，Oracle 会跳过它们，而不是报错。
适用于：
表（
TABLE_OBJECT）索引（
INDEX_OBJECT）常用于数据库文件损坏、硬盘故障、备份文件不完整等情况下临时绕过问题块继续业务运行或数据导出。
🧠 使用场景举例：
表中某些数据块损坏，导致全表扫描失败。
临时需要导出未损坏的数据，用于转移或恢复。
配合
DBMS_REPAIR.CHECK_OBJECT检测坏块后，继续运行业务逻辑。
📌 工作机制
启用后，对象上的查询或操作：
遇到坏块 → Oracle 跳过不访问这些坏块
这样能 最大程度保留/导出/访问完好数据
不影响数据块的实际内容（不会修复坏块，仅跳过）
常用调用格式
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
schema_name => 'SCOTT',
object_name => 'EMP',
object_type => DBMS_REPAIR.TABLE_OBJECT,
flags => DBMS_REPAIR.SKIP_FLAG -- 开启跳过
);
END;
关闭跳过功能：
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
schema_name => 'SCOTT',
object_name => 'EMP',
object_type => DBMS_REPAIR.TABLE_OBJECT,
flags => DBMS_REPAIR.NOSKIP_FLAG -- 关闭跳过
);
END;
⚠️ 注意事项
此操作不会修复坏块，只是忽略它们。
配合
DBMS_REPAIR.CHECK_OBJECT使用，先识别出坏块。通常用于应急，不应长期依赖。
处理后建议尽快进行数据恢复或表重建。
总结
因为这个是库是非归档模式的，所以没有物理备份，这样遭遇了block corrupt确实非常麻烦，建议重要的库还是一定要启用归档并使用RMAN备份。
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 4231143
Current log sequence 4231147expdp备份部分表的脚本 供参考
[oracle@rtpdb ~]$ cat $HOME/jobs/expback.sh
#!/bin/bash
#backup table on noarchive db
#create by norton.fan 20220729
PATH=$PATH:$HOME/bin
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=rtp
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export PATH
NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG
#export DELTIME=`date -d "15 days ago" +%Y%m%d`
export BACKUPTIME=`date +%Y%m%d%H%M%S`
expdp ods/ods dumpfile=ods$BACKUPTIME.dmp logfile=ods$BACKUPTIME.log parfile=/home/oracle/jobs/exp.par
#echo "Delete backup cycle before 15 days"
find /oradata/backup/ -mtime +1 -name *.dmp -exec rm -f {} ';'
find /oradata/backup/ -mtime +7 -name *.log -exec rm -f {} ';'
[oracle@rtpdb ~]$
[oracle@rtpdb ~]$ cat /home/oracle/jobs/exp.par
DIRECTORY = dmpdir
SCHEMAS = ods
INCLUDE = TABLE:"IN (select table_name from exptab)" ##将需要备份的表名放入到exptab表中如何优化一个看似正常的数据库
查询rownum伪列引起的sql性能问题分析
一个很小的系统为什么负载那么高？
IBM大瓜：总经理举报董事长
靴子落地！IBM裁掉中国研发中心！
IBM中国研究院即将大裁员？
网易云音乐事故复盘
P0事故，网易门户，网易云音乐全挂了！！
DBA是打破35岁魔咒的IT职业吗？
MOS（My Oracle Support）怎么用？Oracle DBA必备技能！
分享一个比较实用的MySQL8一键巡检脚本
Oracle自动处理表空间不足脚本
分享两个ADG监控脚本
Oracle 19C一键巡检脚本分享
oracle常用监控脚本（纯干货，没有EMCC,ZABBIX也不怕）
大量enq: SQ - contention 等待导致系统卡顿
如何优化一个看似正常的数据库
小白也能学会的Oracle优化教程-主打零基础
最好的数据库监控平台-ORACLE EMCC13.5安装配置使用文档
NBU备份oracle详细配置文档(含常见报错处理方法)
Oracle rac如何替换OCR和VOTE磁盘组
谁是DBA圈里最大的背锅侠？