Oracle非歸檔模式遇到文件損壞怎么辦？

昨天夜里基地夜班的兄弟，打電話說有個報表庫連不上了，趕緊起來連上VPN查看一下，看到實例宕機了，先趕緊startup起來。

1.查看報錯信息

環境介紹：Redhat 6.9 Oracle 11.2.0.4? ?No Archive Mode

查看alert log 關鍵報錯信息如下

Thread 1 advanced to log sequence 4231012 (LGWR switch)Current log# 2 seq# 4231012 mem# 0: /oradata/rtp/redo02.log
Thu May 08 23:22:56 2025
KCF: read, write or open error, block=0x240ab online=1file=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 08 23:22:57 2025
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 Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE\_HOME = /u01/app/oracle/product/11.2.0/db\_1
System name:    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
Instance name: rtp
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 3300, image: oracle\@rtpdb (DBW0)\*\*\* 2025-05-08 23:22:56.680
\*\*\* SESSION ID:(1521.1) 2025-05-08 23:22:56.680
\*\*\* CLIENT ID:() 2025-05-08 23:22:56.680
\*\*\* SERVICE NAME:(SYS\$BACKGROUND) 2025-05-08 23:22:56.680
\*\*\* MODULE NAME:() 2025-05-08 23:22:56.680
\*\*\* ACTION NAME:() 2025-05-08 23:22:56.680KCF: 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'
Encountered write error
DDE rules only 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) -----
error 63999 detected in background process
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
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-08 23:22:56.750
DBW0 (ospid: 3300): terminating the instance due to error 63999
ksuitm: waiting up to \[5] seconds before 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> VALIDATE DATAFILE 118;Starting validate at 09-MAY-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=647 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00118 name=/oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
118  FAILED 0              85421        268800          74401038924File Name: /oradata2/rtp/RTP/datafile/o1_mf_tbs_ods_n1qx02j0_.dbfBlock Type Blocks Failing Blocks Processed---------- -------------- ----------------Data       0              99872           Index      0              82856           Other      49             651             validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/rtp/rtp/trace/rtp_ora_22883.trc for details
Finished validate at 09-MAY-25RMAN> list backup summary;specification does not match any backup in the repositoryRMAN>

從/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，看到是一個表

SELECT tablespace_name, segment_type, owner, segment_name 
FROM dba_extents 
WHERE file_id = 118 AND (block_id BETWEEN 189903 AND 189918 OR(block_id + blocks - 1) BETWEEN 189903 AND 189918 ORblock_id < 189903 AND (block_id + blocks - 1) > 189918);
TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ---------------------------------------------------------------------------------
TBS_ODS                        TABLE              ODS                            LOT_MATERIAL_MASTER

5.標記壞塊，防止操作失敗

標記這些壞塊并跳過，這個不算是標準處理流程，因為是報表庫，元數據都是從另外一個庫拉取的，這里標記跳過，聯系報表的同事重建這個表

BEGINDBMS_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           4231147

expdp備份部分表的腳本供參考

[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表中