Data Guard has a gap sequence problem.

Data Guard has a gap sequence problem.

I. Problem Description

This afternoon, when the customer had a power failure, one of the standby database databases could not be started and the following error was reported.

Media Recovery Waiting for thread 1 sequence 12364Fetching gap sequence in thread 1, gap sequence 12364-12364Wed Jan 17 15:20:57 2018FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 12364-12364 DBID 3677888493 branch 946469421FAL[client]: All defined FAL servers have been attempted.------------------------------------------------------------Check that the CONTROL_FILE_RECORD_KEEP_TIME initializationparameter is defined to a value that's sufficiently largeenough to maintain adequate log switch information to resolvearchivelog gaps.

Ii. Repair operations

1. view the scn of the slave Database

SQL> select current_scn from v$database;CURRENT_SCN-----------  139323967

-- When unexpected inconsistency of the datafile header scn occurs, you need to archive the log as prompted to find the minimum scn.

2. Check whether data files are added to the master database.

SQL> select file#,name from v$datafile where creation_change#> =139323967;no rows selected

Check whether the master database has added data files after this scn. If you add files, manually add them to the slave database.

3. The slave database stops the log application.

SQL> alter database recover managed standby database cancel;Database altered.

4. Incremental backup of the master database and transfer it to the slave Database

Incremental backup of the master database

RMAN> backup incremental from scn  139323967 database format '/backup/cebpm/fullbackup/cebpm_%U' tag 'cebpm';

Upload to slave Database

cebpm:/backup/cebpm/fullbackup@cebpm>scp cebpm_* 132.237.0.206:/backup/cebpm/rman

5. Restore on the slave Database

Start the slave database to the mount state, and then execute the following statement

cebpmxc:/data/oradata/cebpmxc/archivelog@xiechuang>rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 17 16:21:54 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: CEBPM (DBID=3677888493)RMAN> catalog start with '/backup/cebpm/rman';using target database control file instead of recovery catalogsearching for all files that match the pattern /backup/cebpm/rmanList of Files Unknown to the Database=====================================File Name: /backup/cebpm/rman/cebpm_p1sotn4f_1_1File Name: /backup/cebpm/rman/cebpm_otsotn28_1_1File Name: /backup/cebpm/rman/cebpm_ousotn31_1_1File Name: /backup/cebpm/rman/cebpm_ovsotn3g_1_1File Name: /backup/cebpm/rman/cebpm_orsotn0l_1_1File Name: /backup/cebpm/rman/cebpm_ossotn1p_1_1File Name: /backup/cebpm/rman/cebpm_p0sotn40_1_1File Name: /backup/cebpm/rman/cebpm_opsotlob_1_1File Name: /backup/cebpm/rman/cebpm_p2sotn4u_1_1File Name: /backup/cebpm/rman/cebpm_oqsotmg8_1_1Do you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging doneList of Cataloged Files=======================File Name: /backup/cebpm/rman/cebpm_p1sotn4f_1_1File Name: /backup/cebpm/rman/cebpm_otsotn28_1_1File Name: /backup/cebpm/rman/cebpm_ousotn31_1_1File Name: /backup/cebpm/rman/cebpm_ovsotn3g_1_1File Name: /backup/cebpm/rman/cebpm_orsotn0l_1_1File Name: /backup/cebpm/rman/cebpm_ossotn1p_1_1File Name: /backup/cebpm/rman/cebpm_p0sotn40_1_1File Name: /backup/cebpm/rman/cebpm_opsotlob_1_1File Name: /backup/cebpm/rman/cebpm_p2sotn4u_1_1File Name: /backup/cebpm/rman/cebpm_oqsotmg8_1_1RMAN> recover database noredo;Starting recover at 2018/01/17 16:26:05allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=226 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00002: /data/oradata/cebpmxc/datafile/sysaux01.dbfdestination for restore of datafile 00007: /data/oradata/cebpmxc/datafile/sysaux02.dbfdestination for restore of datafile 00017: /data/oradata/cebpmxc/datafile/hagj01.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_ossotn1p_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_ossotn1p_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00004: /data/oradata/cebpmxc/datafile/users01.dbfdestination for restore of datafile 00005: /data/oradata/cebpmxc/datafile/zyscm01.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_otsotn28_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_otsotn28_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:25channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00010: /data/oradata/cebpmxc/datafile/zyscm02.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_ovsotn3g_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_ovsotn3g_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00011: /data/oradata/cebpmxc/datafile/zyscm03.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_ousotn31_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_ousotn31_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00016: /data/oradata/cebpmxc/datafile/zyscm05.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_p1sotn4f_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_p1sotn4f_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00019: /data/oradata/cebpmxc/datafile/ceshi01.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_orsotn0l_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_orsotn0l_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /data/oradata/cebpmxc/datafile/system01.dbfdestination for restore of datafile 00006: /data/oradata/cebpmxc/datafile/zyscm_index01.dbfdestination for restore of datafile 00008: /data/oradata/cebpmxc/datafile/system02.dbfdestination for restore of datafile 00009: /data/oradata/cebpmxc/datafile/undotbs02.dbfdestination for restore of datafile 00013: /data/oradata/cebpmxc/datafile/zyscm_index02.dbfdestination for restore of datafile 00014: /data/oradata/cebpmxc/datafile/zyscm_index03.dbfdestination for restore of datafile 00015: /data/oradata/cebpmxc/datafile/zyscm_index04.dbfdestination for restore of datafile 00018: /data/oradata/cebpmxc/datafile/hagj_index01.dbfdestination for restore of datafile 00020: /data/oradata/cebpmxc/datafile/ceshi_index01.dbfdestination for restore of datafile 00021: /data/oradata/cebpmxc/datafile/zyscm_mview01.dbfdestination for restore of datafile 00022: /data/oradata/cebpmxc/datafile/zyscm_mview_index01.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_opsotlob_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_opsotlob_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:14:06channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00003: /data/oradata/cebpmxc/datafile/undotbs01.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_oqsotmg8_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_oqsotmg8_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:09:45channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00012: /data/oradata/cebpmxc/datafile/zyscm04.dbfchannel ORA_DISK_1: reading from backup piece /backup/cebpm/rman/cebpm_p0sotn40_1_1channel ORA_DISK_1: piece handle=/backup/cebpm/rman/cebpm_p0sotn40_1_1 tag=CEBPMchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15Finished recover at 2018/01/17 16:51:38

6. Create the standby controlfile file on the master database and transfer it to the slave database.

RMAN> backup current controlfile for standby format '/backup/cebpm/fullbackup/cebpm_ctl.bck'; 

7. Backup database recovery Control File

RMAN> startup nomountOracle instance startedTotal System Global Area    6413680640 bytesFixed Size                     2265224 bytesVariable Size               1207963512 bytesDatabase Buffers            5184159744 bytesRedo Buffers                  19292160 bytesRMAN> restore standby controlfile from '/backup/cebpm/rman/cebpm_ctl.bck';Starting restore at 2018/01/17 17:08:20using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=98 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/data/oradata/cebpmxc/controlfile/control01.ctloutput file name=/data/oradata/cebpmxc/controlfile/control02.ctlFinished restore at 2018/01/17 17:08:21RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

8. Clear the standby Database Log Group

sql> alter database clear logfile group 1; 

NOTE: If standby log mode is used, you do not need to clear it.
 

sql> alter database clear logfile group 1;alter database clear logfile group 1*error at line 1:ora-19527: physical standby redo log must be renamedora-00312: online log 1 thread 1: '/data/oradata/cebpmxc/redo/redo01.l

NOTE: If standby log mode is not used, several groups need to be cleared.

9. Reset flashback in the standby Database

sql> alter database flashback off; 

10. The slave database re-receives and applies logs.

SQL> alter database recover managed standby database using current logfile disconnect from session;Database altered

Iii. Verification

1. SQL operations

Execute alter system switch logfile in the master database;
Execute select max (sequence #) from v $ archived_log in the master and slave databases respectively. If they are consistent, the repair is successful.

2. Use the alert file

Master database:

Wed Jan 17 17:34:08 2018Thread 1 advanced to log sequence 14197 (LGWR switch)  Current log# 4 seq# 14197 mem# 0: /data1/oradata/cebpm/redo/redo04.log  Current log# 4 seq# 14197 mem# 1: /data2/oradata/cebpm/redo/redo04.rdoArchived Log entry 40323 added for thread 1 sequence 14196 ID 0xdb3769ed dest 1:Wed Jan 17 17:34:09 2018LNS: Standby redo logfile selected for thread 1 sequence 14197 for destination LOG_ARCHIVE_DEST_2LNS: Standby redo logfile selected for thread 1 sequence 14197 for destination LOG_ARCHIVE_DEST_3

Slave database:

Media Recovery Waiting for thread 1 sequence 14196 (in transit)Recovery of Online Redo Log: Thread 1 Group 12 Seq 14196 Reading mem 0  Mem# 0: /data/oradata/cebpmxc/redo/redo12_stb01.logRFS[1]: Selected log 11 for thread 1 sequence 14197 dbid -617078803 branch 946469421Wed Jan 17 17:38:52 2018Archived Log entry 13 added for thread 1 sequence 14196 ID 0xdb3769ed dest 1:Media Recovery Waiting for thread 1 sequence 14197 (in transit)Recovery of Online Redo Log: Thread 1 Group 11 Seq 14197 Reading mem 0  Mem# 0: /data/oradata/cebpmxc/redo/redo11_stb01.log

Now that the DG recovery is complete, remember that the last step is to open flashback.