Rolling Forward Standby database with missing Archivelogs

This is the typical scenario where the PROD DB archive logs files were not being shipped to one of the destinations. There could be many reason for this scenario, we are going to assume that the standby side DB was down.

In order to fix this, we need to run a cumulative backup on the source starting from the SCN number of the current DG database and copied all blocks with a higher SCN than the Data Guard database. This copy is then used on the standby side to perform a recovery. Once the DB catch up, place the database in recovery until disconnect session mode and the DB will be fully recovered.
Below are the steps I followed for a successful recovery.
1. Startup the target DB.
2. Find SCN on Target DB.
3. Cancel recovery process.
4. Backup source using “from SCN”
5. SCP backup sets to target
6. Catalog incremental backup
7. Recover database
8. Get datafile list
9. Backup standby controlfile.
10. Restore controlfile
11. Catalog datafiles to new controlfile.
11. Rename datafile
12. Clear standby logfiles
13. Start recovery process.

1. After starting up the Oracle standby instance, Oracle tried to fetch the missing archives from the source, however because of the length of time they were not available and the attempt was unsuccessful.
Mon Aug Feb 13 16:00:23 2012
Fetching gap sequence in thread 1, gap sequence 25084-25304
Tues Feb 14 12:05:12 2012
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 25084-25304
DBID 647392 branch 17784324
FAL[client]: All defined FAL servers have been attempted.

2. Find the SCN number in the standby DB:

select current_scn from v$database;
CURRENT_SCN
-----------
57154168307

3. If the apply process is running and not able to fetch the archive log gap files then the recovery process need to be cancelled and the DB shutdown .

alter database recover managed standby database cancel;
shutdown;

4. Now using the recorded SCN an incremental backup is run on the source database. This backs up all blocks which have changed since this SCN and will be applied directly on the target.

[PROD]$ rman target=/
run {
 allocate channel d1 type disk;
 allocate channel d2 type disk;
BACKUP INCREMENTAL FROM SCN 59156548562 DATABASE FORMAT '/stdby_tmp/stdby_%U' tag 'FORSTANDBY';
release channel d1;
release channel d2;
}
...
...
channel d2: backup set complete, elapsed time: 00:09:26
Finished backup at 13-AUG-12

5. Then SCP copy these backup sets onto the target environment.

6. Catalog these new backups for use by the controlfile on the target.

[STDBY]$ rman target /
RMAN> catalog start with '/opt/backup_drop';

using target database control file instead of recovery catalog
searching for all files that match the pattern /opt/backup_drop

List of Files Unknown to the Database
=====================================
File Name: /opt/backup_drop/stdby_hsdhwehs_1_1
File Name: /opt/backup_drop/stdby_lajdiewd_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/backup_drop/stdby_hsdhwehs_1_1
File Name: /opt/backup_drop/stdby_bknj4c2m_1_1

7. Mount the database and recover using the incremental backups recently cataloged.

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 13-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2144 instance=LVDEGDB1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=2162 instance=LVDEGDB1 devtype=DISK
channel ORA_DISK_2: starting incremental datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA01/LVDEGDB1/datafile/system.245.1547895
…

8. Get a list of the datafiles which are part of the database.

set lines 200
col name format a60
select file#, name from v$datafile order by file# ;

FILE#      NAME
---------- --------------------------------------------------------------------------------
1          +DATA01/LVDEGDB1/datafile/system.245.1547895
…… ………

9. Create a new standby controlfile from source DB and SCP to DG environment.

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/stdby_tmp/stdby_ctl_%U' tag 'FORSTDBY';

10. Shutdown the Standby Database, place in mount no-mode and restore the standby control file.

RMAN> RESTORE STANDBY CONTROLFILE FROM '/opt/backup_drop/st_ctl_khfjdf6kd';

Starting restore at 20-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2172 instance=avhforp1 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:14
output filename=+REDO01/LVDEGDB1/controlfile/current.253.22545
output filename=+DATA01/LVDEGDB1/controlfile/current.784.25344
Finished restore at 12-AUG-12

11. Since the source and the target datafile paths are different with the new controlfile we need to let the existing data file path in the DG environment to be updated. Using the catalog command we can achieve this.

RMAN> CATALOG START WITH '+DATA01/LVDEGDB1/DATAFILE';

Starting implicit crosscheck backup at 12-AUG-12
allocated channel: ORA_DISK_1
allocated channel: ORA_DISK_2

RMAN> CATALOG START WITH '+DATA01/LVDEGDB1/DATAFILE';

Starting implicit crosscheck backup at 12-AUG-12
allocated channel: ORA_DISK_1
allocated channel: ORA_DISK_2
Crosschecked 197 objects
Finished implicit crosscheck backup at 12-AUG-12

Starting implicit crosscheck copy at 12-AUG-12
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 12-AUG-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
searching for all files that match the pattern +DATA01/LVDEGDB1/DATAFILE

List of Files Unknown to the Database
=====================================
File Name: +data01/LVDEGDB1/DATAFILEPGC_DATA.758.44986253
….
….
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

12. Update the DG controlfile with new datafile path (if different diskgroups used).

RMAN> SWITCH DATABASE TO COPY;

13. On the standby before restarting the Managed recovery process, clear the standby redo log files.

ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;

14. on the standby start-up the recovery process.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

15. Reconfirming the SCN number on source and target. The SCN’s are much closer than before

On source:

select current_scn from v$database;

CURRENT_SCN ----------- 57154854789

On standby:

select current_scn from v$database;

CURRENT_SCN
-----------
57154851498

Secondary database synced with Primary without using archive logs!