Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to solve the dataguard gap problem based on oracle incremental backup". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Error reported in Dataguard alert log:

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 Tmuri 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 was found in the database.

Decided to use incremental backup based on rman to solve gap:

Back up the spfile of the repository first:

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

File created.

Current gap

SQL > select * from v$archive_gap

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID

--

1 95 98 1

Prepare the current scn of the database

SQL > select to_char (current_scn) from v$database

TO_CHAR (CURRENT_SCN)

-

5694880

Scn incremental backup on the main database according to the query of the standby database, pay attention to the backup control files

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

Transfer incremental files to the standby library

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

Standby library control file path

SQL > select name from v$controlfile

NAME

/ opt/mingdbdata/data/MINGDB/controlfile/o1_mf_fn8w9go7_.ctl

Backup files for rman catalog registration

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

Reserve rman recovery

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

The error was reported because I forgot to cancel the log application, and the database is still in open state.

Cancel log application, mount database

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.

Log in to rman again and try to recover:

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

Shut down the database:

RMAN > shutdown immediate

Database dismounted

Oracle instance shut down

Copy the control file from restore to the original path

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

Open the database and open the log application:

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.

Verify:

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.

The MRP0 process is already waiting for 105 to be archived.

The main database cuts the log:

SQL > alter system switch logfile

System altered.

The maximum log applied to the repository at this time has been caught up.

SQL > select thread#,max (SEQUENCE#) from v$archived_log where applied='YES' group by thread#

THREAD# MAX (SEQUENCE#)

--

1 105

This is the end of the content of "how oracle solves the dataguard gap problem based on incremental backup". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.