oracle基于增量备份如何解决dataguard gap问题
本篇内容介绍了"oracle基于增量备份如何解决dataguard gap问题"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
Dataguard alert日志中报错：
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. ------------------------------------------------------------------------- |
发现数据库出现了gap。
决定用基于rman增量备份的方式来解决gap：
先备份备库的spfile：
SQL> create pfile='/tmp/pfile180731.ora' from spfile; File created. |
当前的gap
SQL> select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID ---------- ------------- -------------- ---------- 1 95 98 1 |
备库当前的scn
SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 5694880 |
主库上根据备库查询的scn增量备份，注意备份控制文件
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注册备份文件
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恢复
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 |
报错是因为忘记将日志应用取消了，而且数据库还是open状态
取消日志应用，mount数据库
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. |
再次登入rman，尝试恢复：
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 |
拷贝restore出来的控制文件到原路径
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. |
MRP0进程已经在等待105好归档了。
主库切一下日志：
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 |
"oracle基于增量备份如何解决dataguard gap问题"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！
