Oracle standby的ORA-01578 ORA-01110 ORA-26040 坑爹的NOLOGGING

来源：这里教程网时间：2026-03-03 13:39:10 作者：

异常： DB: Oracle 11.2.0.1 --版本够low的五一假期时给用户DB做了switch over主备切换后，用了发现切换后新的主库DB中报错如下： Wed May 08 09:44:14 2019 Errors in file /u01/product/diag/rdbms/new/orcl/trace/orcl_ora_100843.trc (incident=50865): ORA-01578: ORACLE 資料區塊損毀 (檔案編號 126, 區塊編號 4969) ORA-01110: 資料檔 126: '/data/orcl/smt_idx01.dbf' ORA-26040: 已使用 NOLOGGING 選項載入資料區塊 Incident details in: /u01/product/diag/rdbms/new/orcl/incident/incdir_50865/orcl_ora_100843_i50865.trc

========= Dump for incident 50865 (ORA 1578) ========

*** 2019-05-08 09:44:14.254 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=52s3v0xvc21j8) ----- SELECT ROWID, STATION_NUMBER, MACHINE_CODE, PRODUCT_NO, VER, EMP_NO, FEEDER_NO, KEY_PART_NO, WORK_TIME, SN, LINE_NAME, MO_NO, SIDE, LOT_NO, VENDOR, DATE_CODE, FEEDER_ID, KEY_PART_QTY, HH_PN, PACKED_QTY, MFG_PN, PKG_ID, CPL_ID, END_TIME, BOM_NO, CUST_PN, DIFFERENCE_QTY, USED_QTY FROM SFISM4.R_SMT_LOG Where PKG_ID = 'VCI3011808R05ZI' 分析： ORA-01578， ORA-01110 第一反应是有数据坏块 使用DBV检查坏块

$dbv file=/data/orcl/smt_idx01.dbf BLOCKSIZE=16384

DBVERIFY: Release 11.2.0.1.0 - Production on Wed May 8 16:15:12 2019

DBVERIFY - Verification starting : FILE = /data/orcl/smt_idx01.dbf

DBV-00201: Block, DBA 528482373, marked corrupt for invalid redo application

DBV-00201: Block, DBA 528482374, marked corrupt for invalid redo application

DBV-00201: Block, DBA 528482375, marked corrupt for invalid redo application

.... DBVERIFY - Verification complete

Total Pages Examined : 294400 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 259171 Total Pages Failing (Index): 0 Total Pages Processed (Other): 19965 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 15264 Total Pages Marked Corrupt : 3 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 2390574971 (2791.2390574971)

DBV-00201 意味着主库到备库中部分redo没有应用到datafile，检查切换之前的主库(现在的备库) 果然datafile '/data/orcl/smt_idx01.dbf' 没有应用 SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE where UNRECOVERABLE_CHANGE# >0 此类问题通常是因为主库中一些nologging的操作导致redo 没能到备库应用，结合之前alert.log 的报错“ ORA-26040: 已使用 NOLOGGING 選項載入資料區塊” ，基本确认了这个问题。难道data guard 没用开到force logging模式导致类似append 操作没用同步？ select force_logging from v$database; 查询 force_logging 为NO还真没用启用 force logging... 解决：检查 NOLOGGING影响没用同步datafile对应的segment： select * from dba_extents where file_id=126 and 4969 between block_id AND block_id + blocks - 1; 还好segment全部是index，rebuild index即可解决。注：如果是table 或其它文件需要对原主库(现备库)的datafile backup再至现主库(原备库)中还原恢复了。最后，老生常谈建立standby，一定记得开启强制归档避免问题发生： alter database force logging;