Oracle DG备库数据文件损坏修复方法(ORA-01578/ORA-01110)

今天负责报表的同事反馈在DG库查询时出现如下报错可以看到报错是数据文件损坏，提示了file id和block id在 Oracle Data Guard 环境中，如果出现坏块，而主库正常，如何来修复呢？这里假设备库是模式。如果是逻辑备库则如下的方法1/2不适用。

xiaofan23z

1543人浏览 · 2025-01-03 14:41:31

xiaofan23z · 2025-01-03 14:41:31 发布

今天负责报表的同事反馈在DG库查询时出现如下报错

ORA-01578:ORACLE数据块损坏(文件号6,块号 2494856)ORA-01110:数据文件6: '/oradata/PMSDG/o1 mf users_molczgmn_.dbfORA-26040:数据块是使用 NOLOGGING 选项加载的

可以看到报错是数据文件损坏，提示了file id和block id

在 Oracle Data Guard 环境中，如果 备库数据文件 出现坏块，而主库正常，如何来修复呢？这里假设备库是 Physical Standby 模式。

如果是逻辑备库则如下的方法1/2不适用。

一. 确认问题

确认坏块信息

在备库执行以下命令查看坏块信息：

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

检查具体受影响的数据文件

查看坏块对应的数据文件编号和名称:(主备均查询一下，确认主库是否也有损坏)

SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE# FROM V$DATABASE_BLOCK_CORRUPTION);

二. 修复方法

方法 1：通过主库恢复坏块

在 Data Guard 环境中，如果确认主库没有损坏只有备库出现文件损坏，可以直接通过rman来修复

1.1 停止备库的日志应用

在备库停止日志应用：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

1.2 恢复坏块

通过 RMAN 恢复受影响的数据文件：

登录到备库的 RMAN：

rman target / RMAN> RECOVER DATAFILE 6 BLOCK 2494856; Starting recover at 02-JAN-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=200 device type=DISK

starting media recovery media recovery complete, elapsed time: 00:00:00

Finished recover at 02-JAN-25

RMAN>

RMAN 会自动从主库提取所需的日志恢复受影响的块。

1.3 启动日志应用

修复完成后，重新启动日志应用：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

方法 2：从主库复制数据文件

如果坏块数量较多或 RECOVER 无法成功，可以直接从主库复制整个数据文件。

2.1 确认数据文件路径

在主库和备库执行以下查询，确认数据文件路径一致：

SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;

2.2 在主库创建数据文件备份

登录到主库的 RMAN，备份受影响的数据文件：

[oracle@PMS~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:46:31 2025

connected to target database: PMS (DBID=4262015389)

RMAN> BACKUP AS COPY DATAFILE 6 FORMAT '/home/oracle/o1 mf users_molczgmn_.dbf';

Starting backup at 02-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=764 instance=pms2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/pms/datafile/o1 mf users_molczgmn_.dbf
output file name=/home/oracle/o1 mf users_molczgmn_.dbf tag=TAG20250102T224636 RECID=4 STAMP=1189378160
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:45
Finished backup at 02-JAN-25

Starting Control File and SPFILE Autobackup at 02-JAN-25
piece handle=+ARCH/pms/autobackup/2025_01_02/s_1189378162.29728.1189378163 comment=NONE
Finished Control File and SPFILE Autobackup at 02-JAN-25

2.3 传输备份到备库

将备份文件传输到备库相同路径：

scp -P 11122 o1 mf users_molczgmn_.dbf oracle@10.xx.xx.xx:/home/oracle

2.4 在备库切换数据文件

在备库停止日志应用：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

登录到备库的 RMAN，切换到新数据文件：

启动数据库到mount
注册数据文件副本
CATALOG DATAFILECOPY '/home/oracle/o1 mf users_molczgmn_.dbf';
RESTORE DATAFILE 6 ;
RECOVER DATAFILE 6;
开启数据库
alter database open；

[oracle@pmsdg ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:14:57 2025

connected to target database: PMS(DBID=4262015389)
RMAN> catalog start with '/home/oracle/o1 mf users_molczgmn_.dbf';

searching for all files that match the pattern /home/oracle/o1 mf users_molczgmn_.dbf

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/o1 mf users_molczgmn_.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/o1 mf users_molczgmn_.dbf

--将备库启动到mount阶段.
[oracle@pmsdg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 22:27:50 2025

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl150> startup mount;
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 989859016 bytes
Database Buffers 587202560 bytes
Redo Buffers 7393280 bytes
Database mounted.

[oracle@pmsdg]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:28:24 2025

connected to target database: PMSDG (DBID=4262015389, not open)

RMAN> restore datafile 6;

Starting restore at 02-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /oradata/PMSDG/o1 mf users_molczgmn_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/o1 mf users_molczgmn_.dbf
channel ORA_DISK_1: piece handle=/home/oracle/o1 mf users_molczgmn_.dbf tag=TAG20230203T204533
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:01
Finished restore at 02-JAN-25
RMAN> recover datafile 6;

Starting recover at 02-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:05:01

Finished recover at 02-JAN-25

RMAN> alter database open;

database opened

2.5 启动日志应用

修复完成后，重新启动日志应用：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

方法 3：重建备库（简单粗暴）

如果坏块影响严重且以上方法无法修复，可以考虑重新同步备库，如果是数据量比较小的推荐这样操作

如果是数据量很大的话推荐前面的两种方法：

3.1 RMAN duplicate方式重建备库

创建主库的全量备份（包括数据文件、控制文件、归档日志）：

rman target sys/xxxx@pms auxiliary sys/xxxx@PMSDG
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database for standby from active database nofilenamecheck;
}

3.2 重新开始归档应用

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;

在备库使用 RMAN 恢复备份并重新配置 Data Guard 环境。

三. 验证修复

检查坏块是否修复

在备库执行以下命令，确认坏块是否已修复：

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

确认日志应用正常

查看备库是否同步正常：

--日志传输状态 standby> select 'Last Applied : ' Logs, to_char(next_time, 'DD-MON-YY HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied = 'YES') union select 'Last Received : ' Logs, to_char(next_time, 'DD-MON-YY HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

-- 最后应用的sequence序列 Standby> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" from (select thread# thrd, max(sequence#) almax from v$archived_log where resetlogs_change# = (select resetlogs_change# from v$database) group by thread#) al, (select thread# thrd, max(sequence#) lhmax from v$log_history where first_time = (select max(first_time) from v$log_history) group by thread#) lh where al.thrd = lh.thrd;

四. 注意事项优先通过主库恢复：

- 如果坏块较少，推荐使用方法 1 恢复。
- 如果坏块较多，直接使用方法 2 替换数据文件。
- 如果数据库比较小也推荐重建方式简单直接，如果数据库太大重建耗时太久推荐前两种方式
- 另外如果是主库有文件损坏，备库正常方法2也适用。

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

AI原生应用必看：用户意图理解的7种算法对比

你是否遇到过这样的场景？对智能音箱说“我想吃辣的”，它却推荐了甜品店；给客服机器人描述“快递三天没动了”，它只会重复“请耐心等待”。这些“答非所问”的背后，往往是用户意图理解算法的失败。本文将聚焦“用户意图理解”这一AI原生应用的核心技术，覆盖从传统规则到最新多模态模型的7种主流算法，帮开发者、产品经理快速掌握“如何选对算法”的关键逻辑。