AI 助理
备案控制台
开发者社区 数据库 文章 正文

Oracle 12c DataGuard 出现GAP 增量恢复的处理方法

2017-11-15 1323
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

DG出现故障基本上有两个方法，一个是全量备份恢复，一个是增量备份恢复，这里我们测试增量备份恢复方法。

一、环境描述
Oracle 12c ASM 
RHEL 7.0 
单实例到单实例的DataGuard
二、问题描述
出现GAP

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID
---------- ------------- -------------- ----------
         1           223            225          1

SQL> 

SQL> select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
ARC3: Archival started
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process (PID:3973)

MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RFS[2]: Assigned to RFS process (PID:3984)
RFS[3]: Assigned to RFS process (PID:3986)
ARC3: Beginning to archive thread 1 sequence 222 (7930074-7930162)
RFS[4]: Assigned to RFS process (PID:3988)
ARC3: Completed archiving thread 1 sequence 222 (0-0)
Managed Standby Recovery starting Real Time Apply
Media Recovery Log +DATADG/arch/log_1_222_961866198_585bafa7.arc
Media Recovery Waiting for thread 1 sequence 223
Fetching gap sequence in thread 1, gap sequence 223-225

20 rows selected.

SQL> 

SQL> select name,value from v$dataguard_stats;

NAME                             VALUE
-------------------------------- ----------------------------------------------------------------
transport lag                    +00 00:07:57
apply lag
apply finish time
estimated startup time           16

SQL> 
三、处理过程

1、备库执行，查看最新scn，取最小值
SQL> col current_scn for 999999999
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7930161

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

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

2.主库执行

--查看是否有新创建的数据文件
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#>7930161;

no rows selected

3.主库增量备份数据文件和控制文件

run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
BACKUP as compressed backupset INCREMENTAL FROM SCN 7930161 DATABASE FORMAT '/home/oracle/dg_%U' tag 'FORSTANDBY';
release channel t1;
release channel t2;
}

ALTER DATABASE CREATE standby controlfile AS '/home/oracle/standby_20171217.ctl'; 

RMAN> 

RMAN> run
2> {
3> allocate channel t1 type disk;
4> allocate channel t2 type disk;
5> BACKUP as compressed backupset INCREMENTAL FROM SCN 7930161 DATABASE FORMAT '/home/oracle/dg_%U' tag 'FORSTANDBY';
6> release channel t1;
7> release channel t2;
8> }

allocated channel: t1
channel t1: SID=76 device type=DISK

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

Starting backup at 17-DEC-2017 08:07:54
channel t1: starting compressed full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATADG/ORCL/DATAFILE/system.258.954622821
input datafile file number=00006 name=+DATADG/ORCL/DATAFILE/users.259.954622957
channel t1: starting piece 1 at 17-DEC-2017 08:07:55
channel t2: starting compressed full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATADG/ORCL/DATAFILE/sysaux.257.954622639
input datafile file number=00004 name=+DATADG/ORCL/DATAFILE/undotbs1.260.954622959
channel t2: starting piece 1 at 17-DEC-2017 08:07:55
channel t2: finished piece 1 at 17-DEC-2017 08:08:10
piece handle=/home/oracle/dg_0msmb3ur_1_1 tag=FORSTANDBY comment=NONE
channel t2: backup set complete, elapsed time: 00:00:15
channel t2: starting compressed full datafile backup set
channel t2: specifying datafile(s) in backup set
including current control file in backup set
channel t2: starting piece 1 at 17-DEC-2017 08:08:13
channel t2: finished piece 1 at 17-DEC-2017 08:08:14
piece handle=/home/oracle/dg_0nsmb3va_1_1 tag=FORSTANDBY comment=NONE
channel t2: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 17-DEC-2017 08:08:38
piece handle=/home/oracle/dg_0lsmb3ur_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:00:43
Finished backup at 17-DEC-2017 08:08:38

released channel: t1

released channel: t2

RMAN> ALTER DATABASE CREATE standby controlfile AS '/home/oracle/standby_20171217.ctl'; 

Statement processed

RMAN> exit

Recovery Manager complete.
<roidb01:orcl:/home/oracle>$ls
dg_0lsmb3ur_1_1  dg_0msmb3ur_1_1  dg_0nsmb3va_1_1  standby_20171217.ctl
<roidb01:orcl:/home/oracle>$scp /home/oracle/* oracle@192.168.1.112:/home/oracle
dg_0lsmb3ur_1_1                                                                        100%  472KB 472.0KB/s   00:00    
dg_0msmb3ur_1_1                                                                        100% 1640KB   1.6MB/s   00:00    
dg_0nsmb3va_1_1                                                                        100% 1136KB   1.1MB/s   00:00    
standby_20171217.ctl                                                                   100%   11MB  11.1MB/s   00:00    
<roidb01:orcl:/home/oracle>$

4.备库执行恢复数据库

sqlplus / as sysdba
startup mount;

RMAN> backup current controlfile format '/home/oracle/standby_1217.ctl';

Starting backup at 17-DEC-2017 08:52:45
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 17-DEC-2017 08:52:46
channel ORA_DISK_1: finished piece 1 at 17-DEC-2017 08:52:47
piece handle=/home/oracle/standby_1217.ctl tag=TAG20171217T085245 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-2017 08:52:47

RMAN> catalog start with '/home/oracle';

searching for all files that match the pattern /home/oracle
....省略...........

报错
RMAN> recover database noredo;

Starting recover at 17-DEC-2017 08:57:40
using channel ORA_DISK_1
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/ORCLDG/DATAFILE/system.258.954626559
destination for restore of datafile 00006: +DATADG/ORCLDG/DATAFILE/users.261.954626717
channel ORA_DISK_1: reading from backup piece /home/oracle/dg_0lsmb3ur_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/17/2017 08:57:41
ORA-19870: error while restoring backup piece /home/oracle/dg_0lsmb3ur_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
解决
--备库取消实时同步进程
SQL> alter database recover managed standby database cancel;

Database altered.

重新执行恢复
RMAN> recover database noredo;

Starting recover at 17-DEC-2017 08:58:23
using channel ORA_DISK_1
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/ORCLDG/DATAFILE/system.258.954626559
destination for restore of datafile 00006: +DATADG/ORCLDG/DATAFILE/users.261.954626717
channel ORA_DISK_1: reading from backup piece /home/oracle/dg_0lsmb3ur_1_1
channel ORA_DISK_1: piece handle=/home/oracle/dg_0lsmb3ur_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
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: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: +DATADG/ORCLDG/DATAFILE/sysaux.259.954626615
destination for restore of datafile 00004: +DATADG/ORCLDG/DATAFILE/undotbs1.260.954626701
channel ORA_DISK_1: reading from backup piece /home/oracle/dg_0msmb3ur_1_1
channel ORA_DISK_1: piece handle=/home/oracle/dg_0msmb3ur_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 17-DEC-2017 08:58:26

RMAN> 

备库恢复完数据库后，备库使用当前的控制文件收集备库的数据文件路径信息
SQL> col name for a50
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +DATADG/ORCLDG/DATAFILE/system.258.954626559
         3 +DATADG/ORCLDG/DATAFILE/sysaux.259.954626615
         4 +DATADG/ORCLDG/DATAFILE/undotbs1.260.954626701
         6 +DATADG/ORCLDG/DATAFILE/users.261.954626717

SQL> 

恢复控制文件
RMAN> shutdown immediate;

database dismounted
Oracle instance shut down

RMAN> exit

Recovery Manager complete.
<roidb02:orcldg:/home/oracle>$ls
dg_0lsmb3ur_1_1  dg_0msmb3ur_1_1  dg_0nsmb3va_1_1  standby_1217.ctl  standby_20171217.ctl
<roidb02:orcldg:/home/oracle>$ls -l
total 25056
-rw-r----- 1 oracle oinstall   483328 Dec 17 08:09 dg_0lsmb3ur_1_1
-rw-r----- 1 oracle oinstall  1679360 Dec 17 08:09 dg_0msmb3ur_1_1
-rw-r----- 1 oracle oinstall  1163264 Dec 17 08:09 dg_0nsmb3va_1_1
-rw-r----- 1 oracle asmadmin 10682368 Dec 17 08:52 standby_1217.ctl
-rw-r----- 1 oracle oinstall 11649024 Dec 17 08:09 standby_20171217.ctl
<roidb02:orcldg:/home/oracle>$rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Dec 17 09:06:25 2017

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area    1241513984 bytes

Fixed Size                     2923872 bytes
Variable Size                452985504 bytes
Database Buffers             771751936 bytes
Redo Buffers                  13852672 bytes

--恢复控制文件
RMAN> restore controlfile from '/home/oracle/standby_20171217.ctl';

Starting restore at 17-DEC-2017 09:07:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATADG/orcldg/standby.ctl
Finished restore at 17-DEC-2017 09:07:07

--加载控制文件
RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> 

--注册数据文件
RMAN> catalog start with '+DATADG/ORCLDG/DATAFILE/';

Starting implicit crosscheck backup at 17-DEC-2017 09:09:16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 17-DEC-2017 09:09:17

Starting implicit crosscheck copy at 17-DEC-2017 09:09:17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-DEC-2017 09:09:17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
...省略.....
File Name: +DATADG/ORCLDG/ARCHIVELOG/2017_11_29/thread_1_seq_273.314.961336961
File Name: +DATADG/ORCLDG/DATAFILE/system.258.954626559
File Name: +DATADG/ORCLDG/DATAFILE/sysaux.259.954626615
File Name: +DATADG/ORCLDG/DATAFILE/undotbs1.260.954626701
File Name: +DATADG/ORCLDG/DATAFILE/users.261.954626717

searching for all files that match the pattern +DATADG/ORCLDG/DATAFILE/
no files found to be unknown to the database

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATADG/ORCLDG/DATAFILE/system.258.954626559"
datafile 3 switched to datafile copy "+DATADG/ORCLDG/DATAFILE/sysaux.259.954626615"
datafile 4 switched to datafile copy "+DATADG/ORCLDG/DATAFILE/undotbs1.260.954626701"
datafile 6 switched to datafile copy "+DATADG/ORCLDG/DATAFILE/users.261.954626717"

RMAN> 
RMAN> alter database recover managed standby database using current logfile disconnect from session;

Statement processed

日志有报错
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/orcldg_ora_22731.trc:
ORA-00313: open failed for members of log group 14 of thread 1
ORA-00312: online log 14 thread 1: '+DATADG/ORCL/ONLINELOG/group_14.270.954625683'
ORA-17503: ksfdopn:2 Failed to open file +DATADG/ORCL/ONLINELOG/group_14.270.954625683
ORA-15173: entry 'ORCL' does not exist in directory '/'
2017-12-17 11:45:52.647000 +08:00
Completed: alter database clear logfile group 14
2017-12-17 11:46:02.748000 +08:00
alter database clear logfile group 15

处理过程
SQL> select group# from v$standby_log;

    GROUP#
----------
        11
        12
        13
        14

--初始化standby redo log
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database clear logfile group 11;

Database altered.

SQL> alter database clear logfile group 12;

Database altered.

SQL> alter database clear logfile group 13;

Database altered.

SQL> alter database clear logfile group 14;

Database altered.

--启动实时应用
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

5.验证
DGMGRL> show configuration;

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  orcl   - Primary database
    orcldg - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

DGMGRL>









本文转自 roidba 51CTO博客，原文链接：http://blog.51cto.com/roidba/2051485，如需转载请自行联系原作者

文章标签：
关系型数据库
Oracle
数据库
SQL
关键词：
Oracle数据库一键上云dataguard
Oracle数据库一键上云12c
Oracle数据库一键上云增量
Oracle数据库一键上云处理方法
Oracle数据库一键上云gap
余二五
目录
相关文章
pbeskoyq7ffm4
|
3月前
|
Oracle 关系型数据库 数据库
手把手教你Oracle DataGuard主备切换(switchover)
手把手教你Oracle DataGuard主备切换(switchover)
pbeskoyq7ffm4
483 4
老板这功能得加钱
|
6月前
|
运维 Oracle 容灾
Oracle dataguard 容灾技术实战（笔记），教你一种更清晰的Linux运维架构
Oracle dataguard 容灾技术实战（笔记），教你一种更清晰的Linux运维架构
老板这功能得加钱
109 2
pbeskoyq7ffm4
|
3月前
|
Oracle 网络协议 安全
Oracle 11g DataGuard搭建保姆级教程
Oracle 11g DataGuard搭建保姆级教程
pbeskoyq7ffm4
195 4
pbeskoyq7ffm4
|
3月前
|
Oracle 网络协议 关系型数据库
Oracle DataGuard主备切换之自动切换
Oracle DataGuard主备切换之自动切换
pbeskoyq7ffm4
145 2
vohelon
|
4月前
|
存储 Oracle 关系型数据库
Oracle 12c支持哪些数据类型？
【7月更文挑战第20天】Oracle 12c支持哪些数据类型？
vohelon
76 2
vohelon
|
4月前
|
SQL Oracle 关系型数据库
Oracle 12c有哪些新特性？
【7月更文挑战第20天】Oracle 12c有哪些新特性？
vohelon
61 2
以山向海
|
4月前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle增量备份
【7月更文挑战第18天】
以山向海
49 2
三分钟热度的鱼
|
4月前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在online模式下增量抓取Oracle数据时，在archive_log切换时，出现数据丢失的情况，是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
三分钟热度的鱼
52 0
冲冲冲c
|
5月前
|
SQL DataWorks Oracle
DataWorks产品使用合集之datax解析oracle增量log日志该如何操作
DataWorks作为一站式的数据开发与治理平台，提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案，帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述，涵盖数据处理的各个环节。
冲冲冲c
57 0
1941623231718325
|
6月前
|
监控 Oracle 关系型数据库
Oracle 12c的Adaptive执行计划：数据的“聪明导航员”
【4月更文挑战第19天】Oracle 12c的Adaptive执行计划是数据库查询的智能优化工具，能根据实际运行情况动态调整执行策略。它像一个聪明的导航系统，不仅生成初始执行计划，还能实时监控并适应统计信息和资源变化，例如自动切换索引或调整并行度。此外，它支持自适应连接和统计信息收集，提升处理复杂查询和变化数据环境的能力。数据管理员应充分利用这一特性来优化查询性能和用户体验。
1941623231718325
75 1