oracle基于增量备份解决dataguard gap问题

2018-07-31T18:10:11.540837+08:00 Primary database   is in MAXIMUM PERFORMANCE mode RFS[6]: Assigned   to RFS process (PID:18880) RFS[6]: No   standby redo logfiles available for T-1 RFS[6]: Opened   log for T-1.S-102 dbid 2547745710 branch 981132078 2018-07-31T18:10:20.970874+08:00 Fetching gap   sequence in thread 1, gap sequence 95-95 2018-07-31T18:12:12.543715+08:00 FAL[client]:   Failed to request gap sequence  GAP - thread 1 sequence 95-95  DBID 2547745710 branch 981132078 FAL[client]: All   defined FAL servers have been attempted. ------------------------------------------------------------------------- Check that the   CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is   defined to a value that's sufficiently large enough to   maintain adequate log switch information to resolve archivelog gaps. -------------------------------------------------------------------------

SQL> create   pfile='/tmp/pfile180731.ora' from spfile;   File created.

SQL> select *   from v$archive_gap;      THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID ----------   ------------- -------------- ----------          1            95             98          1

SQL> select   to_char(current_scn) from v$database;   TO_CHAR(CURRENT_SCN) ---------------------------------------- 5694880

rman target /   Recovery   Manager: Release 12.2.0.1.0 - Production on Tue Jul 31 18:43:05 2018   Copyright (c)   1982, 2017, Oracle and/or its affiliates.    All rights reserved.   connected to   target database: MINGDB (DBID=2547745710)   RMAN> run 2> { 3>  allocate channel d1 type disk; 4>  allocate channel d2 type disk; 5>  allocate channel d3 type disk; 6>  backup as   compressed backupset incremental from SCN 5694880 database format   '/opt/mingdbdata/archive/incre_db_%d_%T_%s.bak' include current controlfile   for standby; 7>  release channel d1; 8>  release channel d2; 9>  release channel d3; 10> }   using target   database control file instead of recovery catalog allocated   channel: d1 channel d1:   SID=37 device type=DISK   allocated   channel: d2 channel d2:   SID=14 device type=DISK   allocated   channel: d3 channel d3:   SID=40 device type=DISK   Starting backup   at 31-JUL-18 RMAN-06755:   warning: datafile 2: incremental-start SCN is too recent; using checkpoint   SCN 1119999 instead RMAN-06755:   warning: datafile 4: incremental-start SCN is too recent; using checkpoint   SCN 1119999 instead RMAN-06755:   warning: datafile 6: incremental-start SCN is too recent; using checkpoint   SCN 1119999 instead channel d1:   starting compressed full datafile backup set channel d1:   specifying datafile(s) in backup set input datafile   file number=00014 name=/opt/mingdbdata/data/MINGPDB1/soe01.dbf input datafile   file number=00015 name=/opt/mingdbdata/data/MINGPDB1/tbs_ming01.dbf channel d1:   starting piece 1 at 31-JUL-18 channel d2:   starting compressed full datafile backup set channel d2:   specifying datafile(s) in backup set input datafile   file number=00008 name=/opt/mingdbdata/data/MINGPDB1/system01.dbf input datafile   file number=00010 name=/opt/mingdbdata/data/MINGPDB1/undotbs01.dbf channel d2:   starting piece 1 at 31-JUL-18 channel d3:   starting compressed full datafile backup set channel d3:   specifying datafile(s) in backup set input datafile   file number=00001   name=/opt/mingdbdata/data/MINGDB/datafile/o1_mf_system_fn8w9k88_.dbf input datafile   file number=00007   name=/opt/mingdbdata/data/MINGDB/datafile/o1_mf_users_fn8wbl6h_.dbf channel d3:   starting piece 1 at 31-JUL-18 channel d3:   finished piece 1 at 31-JUL-18 piece   handle=/opt/mingdbdata/archive/incre_db_MINGDB_20180731_67.bak   tag=TAG20180731T184314 comment=NONE channel d3:   backup set complete, elapsed time: 00:00:07 channel d3:   starting compressed full datafile backup set channel d3:   specifying datafile(s) in backup set input datafile   file number=00009 name=/opt/mingdbdata/data/MINGPDB1/sysaux01.dbf input datafile   file number=00011 name=/opt/mingdbdata/data/MINGPDB1/tbs_pdbadmin01.dbf channel d3:   starting piece 1 at 31-JUL-18 channel d2:   finished piece 1 at 31-JUL-18 piece   handle=/opt/mingdbdata/archive/incre_db_MINGDB_20180731_66.bak   tag=TAG20180731T184314 comment=NONE channel d2:   backup set complete, elapsed time: 00:00:08 channel d2:   starting compressed full datafile backup set channel d2:   specifying datafile(s) in backup set input datafile   file number=00003   name=/opt/mingdbdata/data/MINGDB/datafile/o1_mf_sysaux_fn8w9tvc_.dbf input datafile   file number=00005   name=/opt/mingdbdata/data/MINGDB/datafile/o1_mf_undotbs1_fn8wb0wo_.dbf channel d2:   starting piece 1 at 31-JUL-18 channel d3:   finished piece 1 at 31-JUL-18 piece   handle=/opt/mingdbdata/archive/incre_db_MINGDB_20180731_68.bak   tag=TAG20180731T184314 comment=NONE channel d3:   backup set complete, elapsed time: 00:00:02 channel d3:   starting compressed full datafile backup set channel d3:   specifying datafile(s) in backup set input datafile   file number=00002   name=/opt/mingdbdata/data/MINGDB/70A2182C94E1412DE053B7D91FAC647A/datafile/o1_mf_system_fn8w9pls_.dbf skipping   datafile 00002 because it has not changed channel d3:   backup cancelled because all files were skipped channel d3:   starting compressed full datafile backup set channel d3:   specifying datafile(s) in backup set input datafile   file number=00004 name=/opt/mingdbdata/data/MINGDB/70A2182C94E1412DE053B7D91FAC647A/datafile/o1_mf_sysaux_fn8w9yob_.dbf skipping   datafile 00004 because it has not changed channel d3:   backup cancelled because all files were skipped channel d3:   starting compressed full datafile backup set channel d3:   specifying datafile(s) in backup set input datafile   file number=00006   name=/opt/mingdbdata/data/MINGDB/70A2182C94E1412DE053B7D91FAC647A/datafile/o1_mf_undotbs1_fn8wb2lm_.dbf skipping   datafile 00006 because it has not changed channel d3:   backup cancelled because all files were skipped channel d3:   starting compressed full datafile backup set channel d3:   specifying datafile(s) in backup set including   standby control file in backup set channel d3:   starting piece 1 at 31-JUL-18 channel d3:   finished piece 1 at 31-JUL-18 piece   handle=/opt/mingdbdata/archive/incre_db_MINGDB_20180731_73.bak   tag=TAG20180731T184314 comment=NONE channel d3:   backup set complete, elapsed time: 00:00:01 channel d2:   finished piece 1 at 31-JUL-18 piece   handle=/opt/mingdbdata/archive/incre_db_MINGDB_20180731_69.bak tag=TAG20180731T184314   comment=NONE channel d2:   backup set complete, elapsed time: 00:00:11 channel d1:   finished piece 1 at 31-JUL-18 piece   handle=/opt/mingdbdata/archive/incre_db_MINGDB_20180731_65.bak   tag=TAG20180731T184314 comment=NONE channel d1:   backup set complete, elapsed time: 00:00:27 Finished backup   at 31-JUL-18   released   channel: d1   released   channel: d2   released   channel: d3

oracle@bd-dev-mingshuo-183:/opt/mingdbdata/archive$scp   *.bak oracle@172.31.217.182:/tmp/dbbackup

SQL> select   name from v$controlfile;   NAME -------------------------------------------------------------------------------- /opt/mingdbdata/data/MINGDB/controlfile/o1_mf_fn8w9go7_.ctl

RMAN> catalog   start with '/tmp/*.bak';   searching for   all files that match the pattern /tmp/*.bak no files found   to be unknown to the database   RMAN> catalog   start with '/tmp/dbbackup/';   searching for   all files that match the pattern /tmp/dbbackup/   List of Files   Unknown to the Database ===================================== File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_67.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_65.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_69.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_73.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_68.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_66.bak   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/dbbackup/incre_db_MINGDB_20180731_67.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_65.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_69.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_73.bak File Name: /tmp/dbbackup/incre_db_MINGDB_20180731_68.bak File Name:   /tmp/dbbackup/incre_db_MINGDB_20180731_66.bak

RMAN>  run 2>  { 3>  allocate channel d1 type disk; 4>  allocate channel d2 type disk; 5>  allocate channel d3 type disk; 6>  restore standby controlfile to   '/home/oracle/control01.ctl'; 7>  recover database noredo; 8>  release channel d1; 9>  release channel d2; 10>  release channel d3; 11>  }   allocated   channel: d1 channel d1:   SID=30 device type=DISK   allocated   channel: d2 channel d2:   SID=792 device type=DISK   allocated   channel: d3 channel d3:   SID=32 device type=DISK   Starting restore   at 31-JUL-18   channel d1:   starting datafile backup set restore channel d1:   restoring control file output file   name=/home/oracle/control01.ctl channel d1:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_73.bak channel d1:   piece handle=/tmp/dbbackup/incre_db_MINGDB_20180731_73.bak   tag=TAG20180731T184314 channel d1:   restored backup piece 1 channel d1:   restore complete, elapsed time: 00:00:01 Finished restore   at 31-JUL-18   Starting recover   at 31-JUL-18 channel d1:   starting incremental datafile backup set restore channel d1:   specifying datafile(s) to restore from backup set destination for   restore of datafile 00001: /opt/mingdbdata/data/MINGDB/datafile/o1_mf_system_fn8w9k88_.dbf destination for   restore of datafile 00007:   /opt/mingdbdata/data/MINGDB/datafile/o1_mf_users_fn8wbl6h_.dbf channel d1:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_67.bak channel d2:   starting incremental datafile backup set restore channel d2:   specifying datafile(s) to restore from backup set destination for   restore of datafile 00003:   /opt/mingdbdata/data/MINGDB/datafile/o1_mf_sysaux_fn8w9tvc_.dbf destination for   restore of datafile 00005: /opt/mingdbdata/data/MINGDB/datafile/o1_mf_undotbs1_fn8wb0wo_.dbf channel d2:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_69.bak channel d3:   starting incremental datafile backup set restore channel d3:   specifying datafile(s) to restore from backup set destination for   restore of datafile 00008: /opt/mingdbdata/data/MINGPDB1/system01.dbf destination for   restore of datafile 00010: /opt/mingdbdata/data/MINGPDB1/undotbs01.dbf channel d3:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_66.bak released   channel: d1 released   channel: d2 released   channel: d3 RMAN-00571:   =========================================================== RMAN-00569:   =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571:   =========================================================== RMAN-03002:   failure of recover command at 07/31/2018 19:00:07 ORA-19870: error   while restoring backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_67.bak ORA-19573:   cannot obtain exclusive enqueue for datafile 1 ORA-45909:   restore, recover or block media recovery may be in progress

SQL> alter   database recover managed standby database cancel;   Database   altered. SQL> shutdown   immediate   Database closed. Database   dismounted. ORACLE instance   shut down. startup mount SQL> SQL>   ORACLE instance started.   Total System   Global Area 3254779904 bytes Fixed Size                  8797928 bytes Variable   Size            1124073752 bytes Database   Buffers         2097152000 bytes Redo   Buffers               24756224 bytes Database   mounted.

oracle@bd-dev-mingshuo-182:/tmp$rman   target /   Recovery   Manager: Release 12.2.0.1.0 - Production on Tue Jul 31 19:04:16 2018   Copyright (c)   1982, 2017, Oracle and/or its affiliates.    All rights reserved.   connected to   target database: MINGDB (DBID=2547745710, not open)   RMAN>  run 2>  { 3>  allocate channel d1 type disk; 4>  allocate channel d2 type disk; 5>  allocate channel d3 type disk; 6>  restore standby controlfile to '/home/oracle/control01.ctl'; 7>  recover database noredo; 8>  release channel d1; 9>  release channel d2; 10>  release channel d3; 11>  }   using target   database control file instead of recovery catalog allocated   channel: d1 channel d1:   SID=24 device type=DISK   allocated   channel: d2 channel d2:   SID=785 device type=DISK   allocated   channel: d3 channel d3:   SID=25 device type=DISK   Starting restore   at 31-JUL-18   control file is   already restored to file /home/oracle/control01.ctl restore not   done; all files read only, offline, excluded, or already restored Finished restore   at 31-JUL-18   Starting recover   at 31-JUL-18 channel d1:   starting incremental datafile backup set restore channel d1:   specifying datafile(s) to restore from backup set destination for   restore of datafile 00001:   /opt/mingdbdata/data/MINGDB/datafile/o1_mf_system_fn8w9k88_.dbf destination for   restore of datafile 00007:   /opt/mingdbdata/data/MINGDB/datafile/o1_mf_users_fn8wbl6h_.dbf channel d1:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_67.bak channel d2:   starting incremental datafile backup set restore channel d2:   specifying datafile(s) to restore from backup set destination for   restore of datafile 00003:   /opt/mingdbdata/data/MINGDB/datafile/o1_mf_sysaux_fn8w9tvc_.dbf destination for   restore of datafile 00005:   /opt/mingdbdata/data/MINGDB/datafile/o1_mf_undotbs1_fn8wb0wo_.dbf channel d2:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_69.bak channel d3:   starting incremental datafile backup set restore channel d3: specifying   datafile(s) to restore from backup set destination for   restore of datafile 00008: /opt/mingdbdata/data/MINGPDB1/system01.dbf destination for   restore of datafile 00010: /opt/mingdbdata/data/MINGPDB1/undotbs01.dbf channel d3:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_66.bak channel d1:   piece handle=/tmp/dbbackup/incre_db_MINGDB_20180731_67.bak   tag=TAG20180731T184314 channel d1:   restored backup piece 1 channel d1:   restore complete, elapsed time: 00:00:01 channel d1:   starting incremental datafile backup set restore channel d1:   specifying datafile(s) to restore from backup set destination for   restore of datafile 00009: /opt/mingdbdata/data/MINGPDB1/sysaux01.dbf destination for   restore of datafile 00011: /opt/mingdbdata/data/MINGPDB1/tbs_pdbadmin01.dbf channel d1:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_68.bak channel d2:   piece handle=/tmp/dbbackup/incre_db_MINGDB_20180731_69.bak   tag=TAG20180731T184314 channel d2:   restored backup piece 1 channel d2:   restore complete, elapsed time: 00:00:01 channel d2:   starting incremental datafile backup set restore channel d2:   specifying datafile(s) to restore from backup set destination for   restore of datafile 00014: /opt/mingdbdata/data/MINGPDB1/soe01.dbf destination for   restore of datafile 00015: /opt/mingdbdata/data/MINGPDB1/tbs_ming01.dbf channel d2:   reading from backup piece /tmp/dbbackup/incre_db_MINGDB_20180731_65.bak channel d3:   piece handle=/tmp/dbbackup/incre_db_MINGDB_20180731_66.bak   tag=TAG20180731T184314 channel d3:   restored backup piece 1 channel d3:   restore complete, elapsed time: 00:00:01 channel d1:   piece handle=/tmp/dbbackup/incre_db_MINGDB_20180731_68.bak   tag=TAG20180731T184314 channel d1:   restored backup piece 1 channel d1:   restore complete, elapsed time: 00:00:00 channel d2:   piece handle=/tmp/dbbackup/incre_db_MINGDB_20180731_65.bak   tag=TAG20180731T184314 channel d2:   restored backup piece 1 channel d2:   restore complete, elapsed time: 00:00:02   Finished recover   at 31-JUL-18   released   channel: d1   released channel:   d2   released   channel: d3

RMAN>   shutdown immediate;   database   dismounted Oracle instance   shut down

oracle@bd-dev-mingshuo-182:~$   cp control01.ctl /opt/mingdbdata/data/MINGDB/controlfile/o1_mf_fn8w9go7_.ctl

oracle@bd-dev-mingshuo-182:~$sqlplus   / as sysdba   SQL*Plus:   Release 12.2.0.1.0 Production on Tue Jul 31 19:08:40 2018   Copyright (c)   1982, 2016, Oracle.  All rights   reserved.   Connected to an   idle instance.   SQL> startup ORACLE instance started.   Total System   Global Area 3254779904 bytes Fixed Size                  8797928 bytes Variable   Size            1124073752 bytes Database   Buffers         2097152000 bytes Redo   Buffers               24756224 bytes Database   mounted. Database opened. SQL> alter   database recover managed standby database disconnect from session;   Database   altered.

SQL> select   process,status,sequence# from v$managed_standby;   PROCESS   STATUS        SEQUENCE# ---------   ------------ ---------- DGRD      ALLOCATED             0 ARCH      CONNECTED             0 ARCH      CONNECTED             0 ARCH      CONNECTED             0 ARCH      CONNECTED             0 DGRD      ALLOCATED             0 RFS       IDLE                  0 RFS       IDLE                105 RFS       IDLE                  0 MRP0      WAIT_FOR_LOG        105   10 rows   selected.

SQL> alter   system switch logfile;   System altered.

SQL> select   thread#,max(SEQUENCE#) from v$archived_log where applied='YES' group by   thread# ;       THREAD# MAX(SEQUENCE#) ----------   --------------          1            105