今天負(fù)責(zé)報(bào)表的同事反饋在DG庫查詢時(shí)出現(xiàn)如下報(bào)錯(cuò)

ORA-01578:ORACLE數(shù)據(jù)塊損壞(文件號(hào)6,塊號(hào) 2494856)ORA-01110:數(shù)據(jù)文件6: '/oradata/PMSDG/o1 mf users_molczgmn_.dbfORA-26040:數(shù)據(jù)塊是使用 NOLOGGING 選項(xiàng)加載的

可以看到報(bào)錯(cuò)是數(shù)據(jù)文件損壞，提示了file id和block id

在 Oracle Data Guard 環(huán)境中，如果?備庫數(shù)據(jù)文件?出現(xiàn)壞塊，而主庫正常，如何來修復(fù)呢？這里假設(shè)備庫是?Physical Standby?模式。

如果是邏輯備庫則如下的方法1/2不適用。

---

一. 確認(rèn)問題

? 確認(rèn)壞塊信息

在備庫執(zhí)行以下命令查看壞塊信息：

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

? 檢查具體受影響的數(shù)據(jù)文件

查看壞塊對(duì)應(yīng)的數(shù)據(jù)文件編號(hào)和名稱:(主備均查詢一下，確認(rèn)主庫是否也有損壞)

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

---

二. 修復(fù)方法

方法 1：通過主庫恢復(fù)壞塊?

在 Data Guard 環(huán)境中， 如果確認(rèn)主庫沒有損壞 只有備庫出現(xiàn)文件損壞，可以直接通過rman來修復(fù)

1.1 停止備庫的日志應(yīng)用

在備庫停止日志應(yīng)用：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

1.2 恢復(fù)壞塊

通過 RMAN 恢復(fù)受影響的數(shù)據(jù)文件：

登錄到備庫的 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 會(huì)自動(dòng)從主庫提取所需的日志恢復(fù)受影響的塊。

1.3 啟動(dòng)日志應(yīng)用

修復(fù)完成后，重新啟動(dòng)日志應(yīng)用：

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

---

方法 2：從主庫復(fù)制數(shù)據(jù)文件

如果壞塊數(shù)量較多或?RECOVER?無法成功，可以直接從主庫復(fù)制整個(gè)數(shù)據(jù)文件。

2.1 確認(rèn)數(shù)據(jù)文件路徑

在主庫和備庫執(zhí)行以下查詢，確認(rèn)數(shù)據(jù)文件路徑一致：

SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;

2.2 在主庫創(chuàng)建數(shù)據(jù)文件備份

登錄到主庫的 RMAN，備份受影響的數(shù)據(jù)文件：

[oracle@PMS~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:46:31 2025 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 傳輸備份到備庫

將備份文件傳輸?shù)絺鋷煜嗤窂?#xff1a;

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

2.4 在備庫切換數(shù)據(jù)文件

在備庫停止日志應(yīng)用：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

登錄到備庫的 RMAN，切換到新數(shù)據(jù)文件：

啟動(dòng)數(shù)據(jù)庫到mount注冊(cè)數(shù)據(jù)文件副本CATALOG DATAFILECOPY '/home/oracle/o1 mf users_molczgmn_.dbf';RESTORE DATAFILE 6 ;RECOVER DATAFILE 6;開啟數(shù)據(jù)庫alter database open；
[oracle@pmsdg ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:14:57 2025 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 --將備庫啟動(dòng)到mount階段. [oracle@pmsdg]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 22:27:50 2025 Copyright (c) 1982, 2013, Oracle. All rights reserved. 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 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 啟動(dòng)日志應(yīng)用

修復(fù)完成后，重新啟動(dòng)日志應(yīng)用：

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

---

方法 3：重建備庫（簡單粗暴）

如果壞塊影響嚴(yán)重且以上方法無法修復(fù)，可以考慮重新同步備庫，如果是數(shù)據(jù)量比較小的推薦這樣操作

如果是數(shù)據(jù)量很大的話 推薦前面的兩種方法：

3.1 RMAN duplicate方式重建備庫

創(chuàng)建主庫的全量備份（包括數(shù)據(jù)文件、控制文件、歸檔日志）：

rman target sys/xxxx@pms auxiliary sys/xxxx@PMSDGrun { 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 重新開始?xì)w檔應(yīng)用

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

在備庫使用 RMAN 恢復(fù)備份并重新配置 Data Guard 環(huán)境。

---

三. 驗(yàn)證修復(fù)

? 檢查壞塊是否修復(fù)

在備庫執(zhí)行以下命令，確認(rèn)壞塊是否已修復(fù)：

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

? 確認(rèn)日志應(yīng)用正常

查看備庫是否同步正常：

--日志傳輸狀態(tài) 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); -- 最后應(yīng)用的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;

---

四. 注意事項(xiàng)優(yōu)先通過主庫恢復(fù)：

1. • 如果壞塊較少，推薦使用方法 1 恢復(fù)。
   • 如果壞塊較多，直接使用方法 2 替換數(shù)據(jù)文件。
   • 如果數(shù)據(jù)庫比較小 也推薦重建方式? 簡單直接，如果數(shù)據(jù)庫太大 重建耗時(shí)太久 推薦前兩種方式
   • 另外如果是主庫有文件損壞，備庫正常方法2也適用。