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

周末不忘写一篇文档！

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

一、环境描述Oracle 12c ASM RHEL 7.0 单实例到单实例的DataGuard

二、问题描述出现GAPSQL> select * from v$archive_gap;   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID---------- ------------- -------------- ----------         1           223            225          1SQL> SQL> select message from v$dataguard_status;MESSAGE--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ARC0: Archival startedARC1: Archival startedARC2: Archival startedARC1: Becoming the 'no FAL' ARCHARC2: Becoming the heartbeat ARCHARC2: Becoming the active heartbeat ARCHARC3: Archival startedAttempt to start background Managed Standby Recovery processMRP0: Background Managed Standby Recovery process startedPrimary database is in MAXIMUM PERFORMANCE modeRFS[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 ApplyMedia Recovery Log +DATADG/arch/log_1_222_961866198_585bafa7.arcMedia Recovery Waiting for thread 1 sequence 223Fetching gap sequence in thread 1, gap sequence 223-22520 rows selected.SQL> SQL> select name,value from v$dataguard_stats;NAME                             VALUE-------------------------------- ----------------------------------------------------------------transport lag                    +00 00:07:57apply lagapply finish timeestimated startup time           16SQL> 

三、处理过程1、备库执行，查看最新scn，取最小值SQL> col current_scn for 999999999SQL> select current_scn from v$database;CURRENT_SCN-----------    7930161SQL> 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 hh34:mi:ss') CREATION_TIME,STATUS,LAST_TIME,name from v$datafile where CREATION_CHANGE#>7930161;no rows selected3.主库增量备份数据文件和控制文件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> run2> {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: t1channel t1: SID=76 device type=DISKallocated channel: t2channel t2: SID=56 device type=DISKStarting backup at 17-DEC-2017 08:07:54channel t1: starting compressed full datafile backup setchannel t1: specifying datafile(s) in backup setinput datafile file number=00001 name=+DATADG/ORCL/DATAFILE/system.258.954622821input datafile file number=00006 name=+DATADG/ORCL/DATAFILE/users.259.954622957channel t1: starting piece 1 at 17-DEC-2017 08:07:55channel t2: starting compressed full datafile backup setchannel t2: specifying datafile(s) in backup setinput datafile file number=00003 name=+DATADG/ORCL/DATAFILE/sysaux.257.954622639input datafile file number=00004 name=+DATADG/ORCL/DATAFILE/undotbs1.260.954622959channel t2: starting piece 1 at 17-DEC-2017 08:07:55channel t2: finished piece 1 at 17-DEC-2017 08:08:10piece handle=/home/oracle/dg_0msmb3ur_1_1 tag=FORSTANDBY comment=NONEchannel t2: backup set complete, elapsed time: 00:00:15channel t2: starting compressed full datafile backup setchannel t2: specifying datafile(s) in backup setincluding current control file in backup setchannel t2: starting piece 1 at 17-DEC-2017 08:08:13channel t2: finished piece 1 at 17-DEC-2017 08:08:14piece handle=/home/oracle/dg_0nsmb3va_1_1 tag=FORSTANDBY comment=NONEchannel t2: backup set complete, elapsed time: 00:00:01channel t1: finished piece 1 at 17-DEC-2017 08:08:38piece handle=/home/oracle/dg_0lsmb3ur_1_1 tag=FORSTANDBY comment=NONEchannel t1: backup set complete, elapsed time: 00:00:43Finished backup at 17-DEC-2017 08:08:38released channel: t1released channel: t2RMAN> ALTER DATABASE CREATE standby controlfile AS '/home/oracle/standby_20171217.ctl'; Statement processedRMAN> exitRecovery Manager complete.$lsdg_0lsmb3ur_1_1  dg_0msmb3ur_1_1  dg_0nsmb3va_1_1  standby_20171217.ctl$scp /home/oracle/* oracle@192.168.1.112:/home/oracledg_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    $4.备库执行恢复数据库sqlplus / as sysdbastartup mount;RMAN> backup current controlfile format '/home/oracle/standby_1217.ctl';Starting backup at 17-DEC-2017 08:52:45using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setchannel ORA_DISK_1: starting piece 1 at 17-DEC-2017 08:52:46channel ORA_DISK_1: finished piece 1 at 17-DEC-2017 08:52:47piece handle=/home/oracle/standby_1217.ctl tag=TAG20171217T085245 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 17-DEC-2017 08:52:47RMAN> 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:40using channel ORA_DISK_1channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +DATADG/ORCLDG/DATAFILE/system.258.954626559destination for restore of datafile 00006: +DATADG/ORCLDG/DATAFILE/users.261.954626717channel ORA_DISK_1: reading from backup piece /home/oracle/dg_0lsmb3ur_1_1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 12/17/2017 08:57:41ORA-19870: error while restoring backup piece /home/oracle/dg_0lsmb3ur_1_1ORA-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:23using channel ORA_DISK_1channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +DATADG/ORCLDG/DATAFILE/system.258.954626559destination for restore of datafile 00006: +DATADG/ORCLDG/DATAFILE/users.261.954626717channel ORA_DISK_1: reading from backup piece /home/oracle/dg_0lsmb3ur_1_1channel ORA_DISK_1: piece handle=/home/oracle/dg_0lsmb3ur_1_1 tag=FORSTANDBYchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00003: +DATADG/ORCLDG/DATAFILE/sysaux.259.954626615destination for restore of datafile 00004: +DATADG/ORCLDG/DATAFILE/undotbs1.260.954626701channel ORA_DISK_1: reading from backup piece /home/oracle/dg_0msmb3ur_1_1channel ORA_DISK_1: piece handle=/home/oracle/dg_0msmb3ur_1_1 tag=FORSTANDBYchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished recover at 17-DEC-2017 08:58:26RMAN> 备库恢复完数据库后，备库使用当前的控制文件收集备库的数据文件路径信息SQL> col name for a50SQL> 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.954626717SQL> 恢复控制文件RMAN> shutdown immediate;database dismountedOracle instance shut downRMAN> exitRecovery Manager complete.$lsdg_0lsmb3ur_1_1  dg_0msmb3ur_1_1  dg_0nsmb3va_1_1  standby_1217.ctl  standby_20171217.ctl$ls -ltotal 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$rman target /Recovery Manager: Release 12.1.0.2.0 - Production on Sun Dec 17 09:06:25 2017Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.connected to target database (not started)RMAN> startup nomount;Oracle instance startedTotal System Global Area    1241513984 bytesFixed Size                     2923872 bytesVariable Size                452985504 bytesDatabase Buffers             771751936 bytesRedo Buffers                  13852672 bytes--恢复控制文件RMAN> restore controlfile from '/home/oracle/standby_20171217.ctl';Starting restore at 17-DEC-2017 09:07:06using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=1 device type=DISKchannel ORA_DISK_1: copied control file copyoutput file name=+DATADG/orcldg/standby.ctlFinished restore at 17-DEC-2017 09:07:07--加载控制文件RMAN> alter database mount;Statement processedreleased channel: ORA_DISK_1RMAN> --注册数据文件RMAN> catalog start with '+DATADG/ORCLDG/DATAFILE/';Starting implicit crosscheck backup at 17-DEC-2017 09:09:16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=46 device type=DISKCrosschecked 11 objectsFinished implicit crosscheck backup at 17-DEC-2017 09:09:17Starting implicit crosscheck copy at 17-DEC-2017 09:09:17using channel ORA_DISK_1Finished implicit crosscheck copy at 17-DEC-2017 09:09:17searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================...省略.....File Name: +DATADG/ORCLDG/ARCHIVELOG/2017_11_29/thread_1_seq_273.314.961336961File Name: +DATADG/ORCLDG/DATAFILE/system.258.954626559File Name: +DATADG/ORCLDG/DATAFILE/sysaux.259.954626615File Name: +DATADG/ORCLDG/DATAFILE/undotbs1.260.954626701File Name: +DATADG/ORCLDG/DATAFILE/users.261.954626717searching for all files that match the pattern +DATADG/ORCLDG/DATAFILE/no files found to be unknown to the databaseRMAN> 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 1ORA-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.954625683ORA-15173: entry 'ORCL' does not exist in directory '/'2017-12-17 11:45:52.647000 +08:00Completed: alter database clear logfile group 142017-12-17 11:46:02.748000 +08:00alter database clear logfile group 15处理过程SQL> select group# from v$standby_log;    GROUP#----------        11        12        13        14--初始化standby redo logSQL> 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: DISABLEDConfiguration Status:SUCCESS   (status updated 57 seconds ago)DGMGRL>