ORACLE DATAGUARD遇到GAP增量恢復方式修復RAC環境備機的實踐

ORACLE DATAGUARD技術是一個常用的數據保護機制，在DATAGUARD運行過程中，遇到異常導致備機不同步，而主庫的歸檔日志也被清理，此時出現GAP，無法同步；就需要人工處理；對于小型數據庫重新全量同步數據即可，但是對于大型數據庫，通常建議是增量方式恢復，減少時間精力、網絡、磁盤等資源的消耗，如下為一個RAC環境的備機增量恢復的過程：

1.查詢備庫SCN號及主備庫數據文件信息
備庫操作，備庫取消歸檔應用，查詢最小SCN號
SQL> select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(F.FHSCN)
----------------
75039049863

生產

SQL> select to_char(current_scn) from v$database;

SQL> select min(fhscn) from x$kcvfh;

SQL> select min(f.fhscn) from x$kcvfh f,v$datafile d where f.hxfil=d.file# and d.enabled!='READ ONLY';

備庫----------------------------------------------

SQL> select to_char(current_scn) from v$database;

SQL> ?select min(fhscn) from x$kcvfh;

SQL> select min(f.fhscn) from x$kcvfh f,v$datafile d where f.hxfil=d.file# and d.enabled!='READ ONLY';

查完后查看最小值

查詢主備庫數據文件個數是否一致并記錄備庫數據文件路徑
select file#, name from v$datafile order by file# ;

2. 主庫做RMAN的增量備份，scn號為查詢出來的備庫最小scn號
[oracle@his01 rmanbak]$ cat rman.sh?
#!/bin/bash

# BACKUP_PATH=/backup/rman_backup/

# mkdir $BACKUP_PATH

source /home/oracle/.bash_profile

rman target / << EOF

run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup as compressed backupset datafile 60,61 format '/home/oracle/zlback/datafile_%U.bak' tag 'datafile';------若主庫比備庫多兩個數據文件，執行這條命令。
BACKUP as compressed INCREMENTAL FROM SCN 75039049863 DATABASE FORMAT '/backup/hisbak/rmanbak/ForStandby_%U' tag 'FORSTANDBY';-----as compressed 為壓縮參數
release channel d1;
release channel d2;
}

3.將備份好的備份集scp到備庫
[oracle@his01 rmanbak]$ scp ForStandby_i* 192.168.10.35:/rmanbak/

4.備庫注冊備份集
RMAN> catalog start with '/rmanbak/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /rmanbak/

List of Files Unknown to the Database
=====================================
File Name: /rmanbak/ForStandby_ic3kr2p0_38476_1_1
File Name: /rmanbak/ForStandby_ib3kr1ok_38475_1_1
File Name: /rmanbak/ForStandby_ia3kr1ok_38474_1_1

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

List of Cataloged Files
=======================
File Name: /rmanbak/ForStandby_ic3kr2p0_38476_1_1
File Name: /rmanbak/ForStandby_ib3kr1ok_38475_1_1
File Name: /rmanbak/ForStandby_ia3kr1ok_38474_1_1

5.使用增量備份集恢復備庫
RMAN> recover database noredo;

Starting recover at 20-MAR-2025 20:14:28
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=304 instance=hisdb1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=908 instance=hisdb1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2570 instance=hisdb1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=3174 instance=hisdb1 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATADG/hisdb/datafile/system01.dbf
destination for restore of datafile 00002: +DATADG/hisdb/datafile/sysaux01.dbf
……
destination for restore of datafile 00074: +DATADG/hisdb/datafile/portal_his.366.1186787373
destination for restore of datafile 00077: +DATADG/hisdb/datafile/portal_his.369.1186787431
destination for restore of datafile 00080: +DATADG/hisdb/datafile/undotbs2.396.1195663885
channel ORA_DISK_1: reading from backup piece /rmanbak/ForStandby_ia3kr1ok_38474_1_1
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: +DATADG/hisdb/datafile/sysaux04.dbf
destination for restore of datafile 00009: +DATADG/hisdb/datafile/portal01.dbf
destination for restore of datafile 00013: +DATADG/hisdb/datafile/portal_his04.dbf
destination for restore of datafile 00015: +DATADG/hisdb/datafile/portal_his06.dbf
destination for restore of datafile 00017: +DATADG/hisdb/datafile/portal_his08.dbf
destination for restore of datafile 00019: +DATADG/hisdb/datafile/portal_his11.dbf
destination for restore of datafile 00021: +DATADG/hisdb/datafile/portal_his13.dbf
destination for restore of datafile 00024: +DATADG/hisdb/datafile/portal_his16.dbf
destination for restore of datafile 00027: +DATADG/hisdb/datafile/portal_his10.dbf
……
destination for restore of datafile 00075: +DATADG/hisdb/datafile/portal_his.367.1186787375
destination for restore of datafile 00076: +DATADG/hisdb/datafile/portal_his.368.1186787429
destination for restore of datafile 00078: +DATADG/hisdb/datafile/portal_his_2025_0208.dbf
destination for restore of datafile 00079: +DATADG/hisdb/datafile/undotbs1.395.1195663875
channel ORA_DISK_2: reading from backup piece /rmanbak/ForStandby_ib3kr1ok_38475_1_1
channel ORA_DISK_2: piece handle=/rmanbak/ForStandby_ib3kr1ok_38475_1_1 tag=FORSTANDBY
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:20:46
channel ORA_DISK_1: piece handle=/rmanbak/ForStandby_ia3kr1ok_38474_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:28:16

Finished recover at 20-MAR-2025 20:42:46

RMAN> exit

恢復多出來的數據文件
RMAN> catalog backuppiece '/datafile_%U.bak';

RMAN> restore datafile 60,61;

6.恢復控制文件
SYS@hisdb1>shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS@hisdb1>startup nomount
ORACLE instance started.

Total System Global Area 8.1068E+10 bytes
Fixed Size ? ? ? ? ? ? ? ? 37218536 bytes
Variable Size ? ? ? ? ? ?1.3422E+10 bytes
Database Buffers ? ? ? ? 6.7377E+10 bytes
Redo Buffers ? ? ? ? ? ? ?231215104 bytes

RMAN> restore standby controlfile from '/rmanbak/ForStandby_ic3kr2p0_38476_1_1';

Starting restore at 20-MAR-2025 21:38:01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=908 instance=hisdb1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATADG/HISDB/CONTROLFILE/current.267.1196261129
Finished restore at 20-MAR-2025 21:38:03

7.catalog datafilecopy
hisdg01:/home/oracle$rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 20 21:50:41 2025
Version 19.16.0.0.0

connected to target database: HISDB (DBID=1936455435, not open)

RMAN> catalog datafilecopy '+DATADG/hisdb/datafile/LOGMINER_TBS.261.1145745771';
catalog datafilecopy '+DATADG/hisdb/datafile/LOGMINER_TBS.397.1196189189';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL.278.1145744523';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL.297.1145740519';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL.401.1196189295';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.256.1152206447';

catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.257.1145737777';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.260.1145737775';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.262.1145737777';
……
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.347.1184327183';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.348.1184327185';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.349.1184327263';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.350.1184327263';
catalog datafilecopy '+DATADG/hisdb/datafile/PORTAL_HIS.351.1184327263';
catalog datafilecusing target database control file instead of recovery catalog
cataloged datafile copy
datafile copy file name=+DATADG/hisdb/datafile/logminer_tbs.261.1145745771 RECID=11 STAMP=1196286653
……
catalog datafilecopy '+DATADG/hisdb/datafile/portal_his09.dbf'
cataloged datafile copy
datafile copy file name=+DATADG/hisdb/datafile/portal_his.394.1192550351 RECID=88 STAMP=1196286659

RMAN>
8.SWITCH DATABASE TO COPY;
RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATADG/hisdb/datafile/system01.dbf"
datafile 2 switched to datafile copy "+DATADG/hisdb/datafile/sysaux01.dbf"
datafile 3 switched to datafile copy "+DATADG/hisdb/datafile/undotbs01.dbf"
……………………
datafile 79 switched to datafile copy "+DATADG/hisdb/datafile/undotbs1.395.1195663875"
datafile 80 switched to datafile copy "+DATADG/hisdb/datafile/undotbs2.396.1195663885"

RMAN>

9.開啟MRP進程
SYS@hisdb1>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

Database altered.

SYS@hisdb1>SYS@hisdb1>

PROCESS ? STATUS ? ? ? ? ?THREAD# ?SEQUENCE# ? ? BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH ? ? ?CLOSING ? ? ? ? ? ? ? 2 ? ? ?52552 ? ? ?36864
ARCH ? ? ?CLOSING ? ? ? ? ? ? ? 1 ? ? ?59424 ? ? ?51200
RFS ? ? ? IDLE ? ? ? ? ? ? ? ? ?1 ? ? ?59425 ? ? ?24784
RFS ? ? ? IDLE ? ? ? ? ? ? ? ? ?2 ? ? ?52553 ? ? ?44835
RFS ? ? ? RECEIVING ? ? ? ? ? ? 2 ? ? ?52265 ? ? 126977
RFS ? ? ? RECEIVING ? ? ? ? ? ? 2 ? ? ?52264 ? ? ?57345
RFS ? ? ? RECEIVING ? ? ? ? ? ? 2 ? ? ?52263 ? ? 436225
RFS ? ? ? RECEIVING ? ? ? ? ? ? 2 ? ? ?52335 ? ? ?14337
RFS ? ? ? RECEIVING ? ? ? ? ? ? 2 ? ? ?52334 ? ? 221185
RFS ? ? ? RECEIVING ? ? ? ? ? ? 2 ? ? ?52333 ? ? 348161
MRP0 ? ? ?APPLYING_LOG ? ? ? ? ?2 ? ? ?52262 ? ? 191805

11 rows selected.

10.第一次增量備份未備份至最新（可通過查找數據文件scn號找出最小scn號）
SYS@hisdb1>select file#,TO_CHAR(checkpoint_change#, 'FM999999999999999999999999') ?from v$datafile_header;

? ? ?FILE# TO_CHAR(CHECKPOINT_CHANGE
---------- -------------------------
? ? ? ? ?1 75578048600
? ? ? ? ?2 75578048600
? ? ? ? ?3 75578048600
? ? ? ? ?4 75578048600
? ? ? ? ?5 75578048600
? ? ? ? ?6 75578048600
? ? ? ? ?7 75578048600
? ? ? ? ?8 75578048600
? ? ? ? ?9 75578048600
? ? ? ? 10 75578048600
? ? ? ? 11 75578048600
?