共计 15462 个字符,预计需要花费 39 分钟才能阅读完成。
本篇内容介绍了“oracle 基于增量备份如何解决 dataguard gap 问题”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
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 问题”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站,丸趣 TV 小编将为大家输出更多高质量的实用文章!