oracle基于增量备份如何解决dataguard gap问题-关系型数据库-互联网-天达云

oracle基于增量备份如何解决dataguard gap问题
更新：HHH 时间：2023-1-7

本篇内容介绍了“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

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

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