Ideal procedure to take and apply incremental backup to resolve archive logs gap

1. Check the scn from which the incremental backup needs to be taken on standy databas :

Run below sql query on standby database:


select min(to_char(checkpoint_change#)) from v$datafile_header;

e.g:

SQL> select min(to_char(checkpoint_change#)) from v$datafile_header;

MIN(TO_CHAR(CHECKPOINT_CHANGE#))

----------------------------------------

77982638190

SQL>

In general, current_scn from v$database on standby database is taken which is wrong practice. if we use current_scn for taking incremental backup and scn of any datafile on standby database is lower than the current_scn, then even after applying the incremental backup on standby database, the MRP on standby database will still be stuck on old archive logs(which it was asking before applying incremental backup). so always use the min scn from v$datafile_header.

2. Defer the sync from primary onto standby database

alter system set log_archive_dest_state_2=defer scope=both;

3. Take the incremental backup and controlfile for standby backup on primary:

Put below rman script in a rcv file say <target_dir>/incr.rcv

run {

allocate channel dsk_1 device type disk;

allocate channel dsk_2 device type disk;

backup as compressed backupset incremental from scn 115203415483 database maxpiecesize 2G format '<target_dir>/dbname_incr_%d_%t_%t_%s_%p.rman';

backup current controlfile for standby format '<target_dir>/ctrl_stndby_dbname.ctl';

release channel dsk_1;

release channel dsk_2;

}

#77982638190 = scn taken from standby database above in step 1.

# MAXPIECESIZE = it defines the output backup filesize. it is optional. you can remove it or increase/decrease the size as per your need.

4. Once the incremental backup is completed, transfer or make available the backup files on to the standby database.

5. Stop MRP on the standby database/

alter database recover managed standby database cancel;


6. Shutdown all the instances of the standby database.

7. Startup only one instance in nomount state.

startup nomount

8. Restore the standby control file from the standby controlfile backup taken in step 3 above.

rman target /

restore standby controlfile from '<standby controlfile backup directory location>';

9. Mount the database

sql ‘alter database mount standby database’;


10. Switch the datafiles.

Since the controlfile is taken from the primary database, so we will need to update the controlfile on standby with the actual datafiles location of standby database and switch each of the datafile.

catalog start with '<datafiles location>';

Run report schema command to get the file_id of all the datafiles and note the file_id of all the datafiles.

switch each of the datafiles using below command:

switch datafile 1 to copy;
switch datafile 2 to copy;
switch datafile 3 to copy;
switch datafile 4 to copy;
switch datafile 5 to copy;
...
..
.
switch datafile n to copy;

where n = file_id of last datafile as observed in report schema.


11. Delete expired backupset information on the standby database:

delete expired backupset;

12. Catalog the incremental backup:

rman target /
catalog start with '<directory location of incremental backup on standby database>’;

13. Check incarnation of primary and standby database.

list incarnation;

If the incarnation is different on standby, then Reset incarnation of standby db to that of incarnation value of primary db.

e.g. if incarnation value on standby database is 3 whereas the incarnation value on primary database is 2. so, you should run below rman command on standby database:

reset database to incarnation 2.

14. Perform recover database on standby database:

recover database;

this step is important to apply the incremental backup. if you skip this step and directly start the MRP, the MRP will still be stuck on old archive logs. so to forward the MRP sequence value, this step is important  to run.

15. Start managed recovery process:

alter database recover managed standby database using current logfile disconnect from session;

MRP process stuck at old sequence even after restoring incremental backup from primary database.

The below article applies to oracle database 11gR2 release and further releases.

Issue:
MRP process on physical standby stuck at old sequence of archive log even after recovering the standby database using the incremental backup from the primary database.


Diagnosis:
The incarnation of database at the standby database was different (or say newer) from the primary database. Please see the below logs:


Standby database:
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       MWQ      2446434119       ORPHAN  9515432557014 07-AUG-17
2       2       MWQ      2446434119       ORPHAN  9515432560577 07-AUG-17
3       3       MWQ      2446434119       CURRENT 9515667276518 22-AUG-17

RMAN>


Primary database:
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       MWQ      2446434119       PARENT  9515432557014 07-AUG-17
2       2       MWQ      2446434119       CURRENT 9515432560577 07-AUG-17

RMAN> 


Resolution:

Before executing 'recover database noredo' to recover the incremental backup data on standby database, reset the incarnation of standby database as that of current incarnation value of primary database using below rman command:

RMAN> reset database to incarnation 2;

using target database control file instead of recovery catalog
database reset to incarnation 2
RMAN>