Oracle11g dataguard增量恢复备库

mahan

检查主备库信息并模拟主备异常环境
主库130：

SQL> select open_mode,protection_mode,protection_level,database_role role,switchover_status from gv$database;

OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE              SWITCHOVER_STATUS
-------------------- -------------------- -------------------- ---------------- --------------------
READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          TO STANDBY

--主库上停掉传输进程
SQL> alter system set log_archive_dest_state_2='defer';

System altered.
备库129：


SQL> select open_mode,protection_mode,protection_level,database_role role,switchover_status from gv$database;

OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE             SWITCHOVER_STATUS
-------------------- -------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED
--从库上停掉应用进程
alter database recover managed standby database cancel;
System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> create table testg(id number);

Table created.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch
Oldest online log sequence     120
Next log sequence to archive   122
Current log sequence           122
主库130：

--挪走archive文件，这些archive无法传输到备库，主备库产生gap
[oracle@11rac01 arch]$ mkdir bak
[oracle@11rac01 arch]$ mv 1_118_1049556128.dbf 1_119_1049556128.dbf  1_120_1049556128.dbf 1_121_1049556128.dbf bak/

--开启传输进程
SQL> alter system set log_archive_dest_state_2='enable';

System altered.
SQL> select open_mode,protection_mode,protection_level,database_role role,switchover_status from gv$database;

OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE             SWITCHOVER_STATUS
-------------------- -------------------- -------------------- ---------------- --------------------
READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          UNRESOLVABLE GAP  <----这里发生变化

备库129：

--开启应用进程
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.
SQL> select open_mode,protection_mode,protection_level,database_role role,switchover_status from gv$database;

OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE             SWITCHOVER_STATUS
-------------------- -------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED
--查看备库延迟
col value format a30
col delay_time format a30
set lines 1000
select inst_id,name,value,datum_time from gv$dataguard_stats;
   INST_ID NAME                             VALUE                          DATUM_TIME
---------- -------------------------------- ------------------------------ ------------------------------
         1 transport lag                    +00 00:09:25                   05/19/2025 21:15:56
         1 apply lag                        +00 02:44:20                   05/19/2025 21:15:56
         1 apply finish time                +00 00:00:00.001
         1 estimated startup time           6
处理过程
1、备库129检查延迟情况、gap情况：

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1           119            121    <----这里提示缺失119-121之间的归档日志

SQL>
SQL> SELECT PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH            118 CLOSING
ARCH      ARCH            122 CLOSING
ARCH      ARCH              0 CONNECTED
ARCH      ARCH            101 CLOSING
RFS       ARCH              0 IDLE
RFS       UNKNOWN           0 IDLE
RFS       LGWR            123 IDLE
MRP0      N/A             119 WAIT_FOR_GAP  <----这里提示mrp进程在等待gap

8 rows selected.

--查看备库延迟
col value format a30
col delay_time format a30
set lines 1000
SQL>  select inst_id,name,value,datum_time from gv$dataguard_stats;

   INST_ID NAME                             VALUE                          DATUM_TIME
---------- -------------------------------- ------------------------------ ------------------------------
         1 transport lag                    +00 00:41:35                   05/19/2025 21:48:06
         1 apply lag                        +00 03:16:30                   05/19/2025 21:48:06
         1 apply finish time                +00 00:00:00.001
         1 estimated startup time           6

SQL>
2、备库确认当前最小SCN号
备库129执行：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.
SQL> col current_scn for 999999999999999
SQL>  SELECT CURRENT_SCN FROM V$DATABASE;

     CURRENT_SCN
----------------
         2585611

SQL> select min(f.checkpoint_change#) from v$datafile_header f, v$datafile d
  2   where f.file# =d.file#   and d.enabled != 'READ ONLY'  ;

MIN(F.CHECKPOINT_CHANGE#)
-------------------------


分别查出了备库的当前SCN和文件头的最小SCN，强烈建议使用文件头最小SCN在主库进行增量备份。由于这个实验中文件头最小SCN没有，因此使用当前SCN在主库进行增量备份！
SQL>  ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
主库130：

SQL> select FILE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME,STATUS,LAST_TIME,name from v$datafile where CREATION_CHANGE#> 2585611;(备库当前SCN号)

no rows selected
说明：在备库当前SCN号之后主库没有新增加数据文件。
SQL>
备库129

--查询备库数据文件信息：
SQL> select file#, name from v$datafile;

3、主库通过基于SCN方式RMAN备份数据库

#!/bin/bash
$ORACLE_HOME/bin/rman target /  log=/u01/backup/backup_20250519.log <<EOF
run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
BACKUP as compressed backupset INCREMENTAL FROM SCN 2585611（取备库最小SCN号） DATABASE FORMAT '/u01/backup/dg_%U' tag 'FORSTANDBY';
release channel t1;
release channel t2;
}
EOF
主数据库备份完毕后，备份一下主库的控制文件：

SQL> ALTER DATABASE CREATE standby controlfile AS '/u01/backup/standby_20250519.ctl';  

Database altered.

SQL>
把备份集远程复制到备库：

[oracle@11rac01 backup]$ scp  /u01/backup/*  oracle@192.168.100.129:/u01/backup
backup_20250519.log                                           100% 1814     1.8KB/s   00:00   
dg_0d3pski3_1_1                                               100%  168KB 168.0KB/s   00:00   
dg_0e3pski3_1_1                                               100%   80KB  80.0KB/s   00:00   
dg_0f3pskij_1_1                                               100% 1088KB   1.1MB/s   00:00   
standby_20250519.ctl                                          100% 9744KB   9.5MB/s   00:00   
4、备库恢复数据库

备库启动到mount状态：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size                  2255832 bytes
Variable Size             620758056 bytes
Database Buffers         1879048192 bytes
Redo Buffers                3276800 bytes
Database mounted.
备份备库当前的控制文件：

rman target /
backup current controlfile format '/u01/backup/standbycurrent_20250519.ctl';
备库主机上查看文件：

[root@11rac02 ~]# ll /u01/backup/
total 20940
-rw-r--r-- 1 oracle oinstall     1814 May 19 22:15 backup_20250519.log
-rw-r----- 1 oracle oinstall   172032 May 19 22:15 dg_0d3pski3_1_1
-rw-r----- 1 oracle oinstall    81920 May 19 22:15 dg_0e3pski3_1_1
-rw-r----- 1 oracle oinstall  1114112 May 19 22:15 dg_0f3pskij_1_1
-rw-r----- 1 oracle oinstall 10092544 May 19 23:11 standbycurrent_20250519.ctl <----备库当前的控制文件备份
-rw-r----- 1 oracle oinstall  9977856 May 19 22:15 standby_20250519.ctl
在备库恢复之前，把主库的备份集信息注册到备库中：

rman target /
RMAN> CATALOG START WITH '/u01/backup';

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

List of Files Unknown to the Database
=====================================
File Name: /u01/backup/dg_0f3pskij_1_1
File Name: /u01/backup/backup_20250519.log
File Name: /u01/backup/dg_0e3pski3_1_1
File Name: /u01/backup/standby_20250519.ctl
File Name: /u01/backup/dg_0d3pski3_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: /u01/backup/dg_0f3pskij_1_1
File Name: /u01/backup/dg_0e3pski3_1_1
File Name: /u01/backup/standby_20250519.ctl
File Name: /u01/backup/dg_0d3pski3_1_1

List of Files Which Where Not Cataloged
=======================================
File Name: /u01/backup/backup_20250519.log
  RMAN-07517: Reason: The file header is corrupted

在备库执行恢复数据库：

#!/bin/bash
$ORACLE_HOME/bin/rman target /  log=/u01/backup/recover_20250519.log <<EOF
shutdown immediate;
startup mount;
run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
recover database noredo;
release channel t1;
release channel t2;
}
EOF
查看日志：

[root@11rac02 backup]# cat recover_20250519.log

Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 19 23:23:10 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1577191741, not open)

RMAN>
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN>
connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2505338880 bytes

Fixed Size                     2255832 bytes
Variable Size                620758056 bytes
Database Buffers            1879048192 bytes
Redo Buffers                   3276800 bytes

RMAN> 2> 3> 4> 5> 6> 7> 8>
allocated channel: t1
channel t1: SID=19 device type=DISK

allocated channel: t2
channel t2: SID=28 device type=DISK

Starting recover at 19-MAY-25
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/data/orcl/system01.dbf
destination for restore of datafile 00004: /u01/data/orcl/users01.dbf
channel t1: reading from backup piece /u01/backup/dg_0d3pski3_1_1
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u01/data/orcl/sysaux01.dbf
destination for restore of datafile 00003: /u01/data/orcl/undotbs01.dbf
channel t2: reading from backup piece /u01/backup/dg_0e3pski3_1_1
channel t1: piece handle=/u01/backup/dg_0d3pski3_1_1 tag=FORSTANDBY
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
channel t2: piece handle=/u01/backup/dg_0e3pski3_1_1 tag=FORSTANDBY
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:01

Finished recover at 19-MAY-25

released channel: t1

released channel: t2

RMAN>

Recovery Manager complete.
[root@11rac02 backup]#
5、备库收集数据文件路径信息
备库恢复完数据库后，备库使用当前的控制文件收集备库的数据文件路径信息。

SQL> select file#,name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/u01/data/orcl/system01.dbf

         2
/u01/data/orcl/sysaux01.dbf

         3
/u01/data/orcl/undotbs01.dbf


     FILE#
----------
NAME
--------------------------------------------------------------------------------
         4
/u01/data/orcl/users01.dbf
  
                    
6、备库恢复控制文件
备库收集完数据文件路径信息后，需要恢复一下备库的控制文件，即此控制文件是从主库备份过来的控制文件，为最新的控制文件：

SQL> shutdown immediate;
SQL>  startup nomount
$ rman target /
RMAN>  restore controlfile from '/u01/backup/standby_20250519.ctl';
SQL> alter database open;

Database altered.
7、开启DG同步机制
备库开启恢复

SQL> ALTER DATABASE recover managed standby DATABASE using current logfile disconnect FROM SESSION;

Database altered.

SQL>  col value format a30
SQL>  col delay_time format a30
SQL>  set lines 1000
SQL>  select inst_id,name,value,datum_time from gv$dataguard_stats;

   INST_ID NAME                             VALUE                          DATUM_TIME
---------- -------------------------------- ------------------------------ ------------------------------
         1 transport lag                    +00 00:00:00                   05/20/2025 00:10:31
         1 apply lag                        +00 00:00:00                   05/20/2025 00:10:31
         1 apply finish time
         1 estimated startup time           6

SQL>
至此，备库增量恢复完成，主备数据库同步正常。

碰到的问题
$ORACLE_HOME/bin/rman target /  log=/u01/backup/recover_20250519.log <<EOF
run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
recover database noredo; --noredo 表示恢复过程中不尝试应用任何归档日志或联机重做日志（Redo Log）
release channel t1;
release channel t2;
}
EOF
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=17 device type=DISK

allocated channel: t2
channel t2: SID=40 device type=DISK

Starting recover at 19-MAY-25
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/data/orcl/system01.dbf
destination for restore of datafile 00004: /u01/data/orcl/users01.dbf
channel t1: reading from backup piece /u01/backup/dg_0d3pski3_1_1
channel t2: starting incremental datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u01/data/orcl/sysaux01.dbf
destination for restore of datafile 00003: /u01/data/orcl/undotbs01.dbf
channel t2: reading from backup piece /u01/backup/dg_0e3pski3_1_1
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/19/2025 23:18:07
ORA-19870: error while restoring backup piece /u01/backup/dg_0d3pski3_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1

解决办法：

$ORACLE_HOME/bin/rman target /  log=/u01/backup/recover_20250519.log <<EOF
shutdown immediate;  ---- 加入这1行
startup mount;       ---- 加入这1行
run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
recover database noredo;
release channel t1;
release channel t2;
}
EOF