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

二、问题描述出现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 hh34: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>

周末不忘写一篇文档！

相关阅读