Oracle-DG环境中Gap

2021-11-12 00:02:10

目标

用于解决丢失或损坏的归档日志文件，无法解决的归档间隙或在不应用大量归档日志文件的情况下，让备用数据库前滚恢复主备一致同步的问题。

适用环境

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]

解决方案

步骤

  • Stop the managed recovery process (MRP) on the STANDBY database
  • Determine the SCN of the STANDBY database.
  • Take an incremental backup off the PRIMARY database
  • Transfer all backup sets to STANDBY server
  • Catalog the backups in STANDBY controlfile.
  • Recover the STANDBY database with the cataloged incremental backup:
  • In RMAN, connect to the PRIMARY database and create a standby control file backup:
  • Copy the standby control file backup to the STANDBY system. If the backup is written to NFS device, this step can be skipped.
  • Capture datafile information in STANDBY database.
  • From RMAN, connect to STANDBY database and restore the standby control file:
  • Mount the standby:
  • Catalog datafiles in STANDBY if location/name of datafiles is different
  • Configure the STANDBY database to use flashback (optional)
  • On STANDBY database, clear all standby redo log groups:
  • On the STANDBY database, start the MRP

详细过程步骤

1) 停止备库MRP进程（managed recovery process）

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

  • 备库启动MOUNT状态；

  • 如果RAC环境，确保只启动一个DB实例到MOUNT状态

  • 若启用broker,需要使用dgmgrl工具关闭MRP进程

    • edit database '<Standby db_unique_name>' set STATE='APPLY-OFF' ;

2) 确定备库的 SCN 号

找到需从主库做增备的SCN号，从下面找到最小的SCN号

-- 备库查询
SELECT CURRENT_SCN FROM V$DATABASE;
select min(checkpoint_change#) min_df from v$datafile_header;
select min(checkpoint_change#) min_df from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');

下面以scn=3162298举例说明过程

3) 在主库基于SCN号进行rman增量备份

--  method 1
BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

-- 方法2
BACKUP COPY INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
确认主库是否新增数据文件
-- SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > <checkpoint_change# from 2>;

SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298;

执行rman将新增数据文件备份出来

backup datafile <missing file number>, <missing file number>, <missing file number>, <missing file number> format '/tmp/ForStandby_%U' tag 'FORSTANDBY';

4) 将步骤3的备份文件传递到备库

scp /tmp/ForStandby_* standby:/tmp

5) （在备库）将备份片信息注册到备库控制文件

RMAN> CATALOG START WITH '/tmp/ForStandby';

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

List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_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: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
将主库新增的数据文件还原到备库
run
{
    set newname for datafile X to '+DISKGROUP';
    set newname for datafile Y to '+DISKGROUP';
    set newname for datafile Z to '+DISKGROUP';
    ....
    restore datafile x,y,z,....;
}

6) 将增备数据恢复到备库

RMAN> RECOVER DATABASE NOREDO;

starting recover at 03-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=28 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/STBY/datafile/system.297.688213333
destination for restore of datafile 00002: +DATA/STBY/datafile/undotbs1.268.688213335
destination for restore of datafile 00003: +DATA/STBY/datafile/sysaux.267.688213333
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 03-JUN-09

7) 在主库中使用RMAN创建standby控制文件的备份

BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

8) 把standby的控制文件传递到备库

scp /tmp/ForStandbyCTRL.bck standby:/tmp

9) 在备库中捕获数据文件信息

后面通过步骤7的备份控制文件还原到备库，由于备库的数据文件名称可呢与主库不一样时，控制文件中数据文件的信息就与当前存在的数据文件信息不一致。因此，需要先保存当前的数据文件的名称信息以备将来使用。

-- sqlplus / as sysdba
spool standby_datafile_names.txt
set pagesize 1000;
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off

10) 使用RMAN将standby控制文件还原到备库

RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

Starting restore at 03-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+DATA/STBY/controlfile/current.257.688583989
Finished restore at 03-JUN-09

11) 将备库启动到MOUNT状态:

RMAN> ALTER DATABASE MOUNT;

12) 如果备库数据文件路径或名称与Catalog中的不一致时

Note: We recommend checking the incarnation for primary and standby before completing this step.

注意：在完成当前步骤前，先检查主库和备库的化身（incarnation）

-- 检查命令
RMAN> list incarnation;

在步骤10通过还原控制文件的方式恢复到备库，该控制文件中的数据文件路径/名称跟主库的信息一致，如果备库的目录结构与主库不一致或使用OMF管理时，需要在备库使用RMAN工具重命名备库的数据文件的目录结构。

在备库的每个磁盘组（或数据文件目录）执行如下步骤

RMAN> CATALOG START WITH '+DATA/STBY/datafile/';

List of Files Unknown to the Database
=====================================
File Name: +DATA/STBY/DATAFILE/SYSTEM.309.685535773
File Name: +DATA/STBY/DATAFILE/SYSAUX.301.685535773
File Name: +DATA/STBY/DATAFILE/UNDOTBS1.302.685535775
File Name: +DATA/STBY/DATAFILE/SYSTEM.297.688213333
File Name: +DATA/STBY/DATAFILE/SYSAUX.267.688213333
File Name: +DATA/STBY/DATAFILE/UNDOTBS1.268.688213335

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

List of Cataloged Files
=======================
File Name: +DATA/STBY/DATAFILE/SYSTEM.297.688213333
File Name: +DATA/STBY/DATAFILE/SYSAUX.267.688213333
File Name: +DATA/STBY/DATAFILE/UNDOTBS1.268.688213335

如果在该增量备份的之后新增数据文件，备库服务器不会自动这部分的数据文件。可以参考文档
Note:1531031.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary的步骤操作

通过上面执行RMAN SWITCH 命令已经将还原的数据文件注册到catalog中。

确认当前备库的SCN之后是否在主库中新增数据文件

SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298;
无新增数据文件

查询结果返回0行记录时，表明没有新增数据文件。就可以执行switch命令更新备库控制文件中的数据文件路径及名称。

RMAN> SWITCH DATABASE TO COPY;

  • 如果 'switch database to copy' 命令由于"RMAN-06571: datafile .. does not have recoverable copy"执行失败，说明备库的数据文件并没有全部注册更新到控制文件或存在备库数据文件路径或名称与主库相同。

    • 需要单独将路径或名称不同的数据文件执行switch命令
    • switch datafile <number> to copy;

  • 与步骤9的查询输出结果对比是否存在差异，以确保已将所有数据文件添加到备用数据库中

  • 再次运行步骤2中的查询，以确认增量应用已将数据文件前滚。（现在，SCN应该大于初始值。）

13) 开启备库的闪回功能（可选）

ALTER DATABASE FLASHBACK OFF; 
ALTER DATABASE FLASHBACK ON;

14) 清空备库的所有redo日志组

select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
......

15) 在备库启动 MRP进程

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

如果使用broker，需要使用DGMGRL工具启动MRP

DGMGRL> edit database '<Standby db_unique_name>' set STATE='APPLY-ON' ;

附录

参考文档

Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)

上一篇：DG常用运维命令及常见问题解决


下一篇：dirmap的配置和简单使用