适用场景:备库产生GAP且主库归档已经删除,如何快速恢复(oracle12c新特性)
参考:Rolling Forward a Physical Standby Using Recover From Service Command in 12c (Doc ID 1987763.1)
备注:此方案适用于数据量大的库,如果数据量不大可以考虑直接重新初始化
在12c之前,利用基于SCN的增量备份恢复备库的过程较为复杂。大致过程如下:
1,确定备库的最大SCN
2,在主数据库上进行基于备库SCN#的增量备份。
3,将增量备份复制到备用主机
4,将备份catalog到备用控制文件中。
5,取消备库的MRP,并在备用数据库上recover增量备份。
6,在主库上为备库创建一个Standby控制文件。
7,用新创建的Standby控制文件打开备库。
8,开始备用数据库的日志应用。
在12c之后,可以使用RECOVER…FROM SERVICE命令将物理备用数据库前滚至与主数据库同步。执行该命令包括以下操作:
1,创建一个基于备库数据文件头的SCN开始的增量备份,该备份包括对主数据库上的数据文件的所有更改。
2,将增量备份通过网络传输到物理备用数据库。
3,将增量备份应用于物理备用数据库。
以上步骤将完成备库数据文件前滚到与主数据库相同的时间点。但是,由于备用控制文件仍包含旧的SCN值(低于备用数据文件的SCN值),为了完成备库的同步,依然需要重新生成Standbu控制文件。
具体实施和测试步骤如下:
一、模拟断掉备库的日志传输
dgmgrl:edit database orclst2 set Property LogShipping='OFF';
二、主库模拟创建新表并插入数据
create table test (id int);
begin
for i in 1 .. 1000
loop
insert into test values (i);
commit;
end loop;
end;
/
三、主库切换日志,确认未传输到备库,删除主库的归档日志
sqlplus:alter system switch logfile;
注:备库需确认test表未同步,且归档未同步
四、打开备库日志传输,确认备库产生gap
gmgrl:edit database orclst2 set Property LogShipping='ON';
五、执行recover from service命令,开始forword备库
1、停止日志应用
RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
或
DGMGRL:edit database orclst3 set state='APPLY-OFF';
2、启动备库至Mount状态
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
3、确认主备库不同步的数据文件
set line 200
col HXFNM for a55
col FHSCN for a15
select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;
4、查看备用数据库当前的SCN,主要目的是GAP期间如果Primary有新增加的数据文件时需要
RMAN> SELECT CURRENT_SCN FROM V$DATABASE;
using target database control file instead of recovery catalog
CURRENT_SCN
-----------
2703380
SELECT file# FROM V$DATAFILE WHERE creation_change# >= 2703380;
5、RECOVER…FROM SERVICE命令将刷新备库数据文件,将其前滚到与主库相同的时间点。
RMAN> recover database from service orcl noredo using compressed backupset;
Starting recover at 02-JAN-2021 18:47:47
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00001: /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00003: /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00004: /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00005: /u01/app/oracle/oradata/orcl/users02.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00007: /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 02-JAN-2021 18:48:07
6、再次确认主备库数据库文件SCN是否一致
set line 200
col HXFNM for a55
col FHSCN for a15
select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;
7、将备库启动到nomount状态
RMAN> shutdown immediate;
RMAN> startup nomount;
8、从主库恢复备用控制文件
RMAN> restore standby controlfile from service <primary_tns_servcie>;
RMAN> alter database mount;
RMAN> report schema;
Starting implicit crosscheck backup at 02-JAN-2021 18:51:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 02-JAN-2021 18:51:32
Starting implicit crosscheck copy at 02-JAN-2021 18:51:32
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-JAN-2021 18:51:32
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/arch/ORCLST2/autobackup/2020_12_24/o1_mf_s_1060006549_hy8dvgl7_.bkp
File Name: /u01/app/oracle/arch/ORCLST2/autobackup/2020_12_24/o1_mf_s_1060004592_hy8bofwz_.bkp
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLST2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 820 SYSTEM *** /u01/app/oracle/oradata/orcl/system01.dbf
3 630 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 55 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf
5 10 USERS *** /u01/app/oracle/oradata/orcl/users02.dbf
7 5 USERS *** /u01/app/oracle/oradata/orcl/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 132 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
备注:如果主备库控制文件中数据文件路径及名称完全一致,则忽略第9步骤。通常情况下,如果使用了OMF方式管理数据文件,都需要执行以下步骤
9、在standby controlfile中更新数据文件和临时文件的名字
RMAN> Catalog start with '/u01/app/oracle/oradata/orcl/';
RMAN> switch database to copy;
10、根据第4步中查询到的SCN,确认主库是否有该SCN后新创建的数据文件,如果有则需要从主库还原这些数据文件到备库
主库确认是否有新建数据文件需执行如下语句:
SELECT file# FROM V$DATAFILE WHERE creation_change# >= 1984232;
如果查询结果为空,则跳转到12步,否则从11步开始执行
11、如果第10步查询到的结果为数据文件21号,参考下面的方式将21号数据文件restore到备库(命令在备库执行即可)
RMAN> RUN
{
SET NEWNAME FOR DATABASE TO '/';
RESTORE DATAFILE 21 FROM SERVICE orcl;
}
12、使用如下方法更新备库控制文件中的online redolog和standby redolog的名称
SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
....
13、恢复ADG日志应用
主库切换归档日志
SQL>alter system archive log current;
备库打开,开启日志应用
SQL>alter database open;
DGMGRL> edit database orclst2 set state='apply-on';
注:ASM中有软连接的文件catalog start with不识别 ,需要catalog datafilecopy '指定数据文件位置';