Oracle Tips

RMAN incremental backups can be used to synchronize a physical standby database with the primary database. This is extremely helpful if ever your standby database go for unsync and in the meantime you have deleted archivelogs from primary database. Instead of rebuilding your standby database you can just pass the incremental backups to standby database and apply them. Using the RMAN BACKUP INCREMENTAL FROM SCN command, you can create a backup on the primary database that starts at the standby database's current SCN, which can then be used to roll the standby database forward in time.
However in some cases the RECOVER DATABASE NOREDO; command does not apply the incremental changes to the standby database and it just completes in few seconds.
RMAN> RECOVER DATABASE NOREDO;
In ideal case, the SCN of the database (CURRENT_SCN) and the datafile scn should not have a huge difference also most of the datafiles should be in same scn range. In the below example datafiles are of different scn that makes the incremental backup based on CURRENT_SCN cannot be used.
In order to determine the SCN of datafiles in standby database run this query in standby,

SQL> select distinct checkpoint_change# from v$datafile_header order by 1;

CHECKPOINT_CHANGE#
------------------
        5693326362
        5695628398
        5697331648
        5699036567

The reason could be either the some of datafiles may be offline for long time, Read-only datafiles or the files are copied to standby one by one by the time there are changes going in the primary that cause the scn difference between datafiles.
1) As there might be SCN differences in datafiles on standby, we need to determine the lowest SCN so that we will take backup in primary using that SCN. In STANDBY execute the below query to identify the lowest SCN

SQL> select min (checkpoint_change#) from v$datafile_header where status in (select status from dba_tablespaces where status <> 'READ ONLY') order by 1;
CHECKPOINT_CHANGE#
------------------
   5693326362

2) Take SCN based incremental backup from PRIMARY where SCN is from the result of step 1

RMAN> BACKUP INCREMENTAL FROM SCN 5693326362
DATABASE FORMAT '/tmp/bddipdcforstandby%U' tag 'FORSTANDBY' 

3) Move the incremental backup to Standby. You can use scp OS command in order to move the files to standby database. If you are using ASM file system then you can use cp command. I will write in detail how we can transfer a file using ASM from one machine to another.
4) Catalog the backup piece in standby database.

RMAN> catalog backuppiece '+data/rollup/bddipdcforstandbyl2n6hv3s_1_1';

using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=+DATA/rollup/bddipdcforstandbyl2n6hv3s_1_1 RECID=25 STAMP=779475153

RMAN> catalog backuppiece '+data/rollup/bddipdcforstandbyl3n6i2jd_1_1';

using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=+DATA/rollup/bddipdcforstandbyl3n6i2jd_1_1 RECID=26 STAMP=779475319

RMAN> catalog backuppiece '+data/rollup/bddipdcforstandbyl4n6i4vj_1_1';

cataloged backup piece
backup piece handle=+DATA/rollup/bddipdcforstandbyl4n6i4vj_1_1 RECID=27 STAMP=779475346

RMAN>  catalog backuppiece '+data/rollup/bddipdcforstandbyl9n6i7h2_1_1';

cataloged backup piece
backup piece handle=+DATA/rollup/bddipdcforstandbyl9n6i7h2_1_1 RECID=28 STAMP=779475401

RMAN>  catalog backuppiece '+data/rollup/bddipdcforstandbylan6ia4o_1_1';

cataloged backup piece
backup piece handle=+DATA/rollup/bddipdcforstandbylan6ia4o_1_1 RECID=29 STAMP=779475548

5) Now mount the standby database and try to apply the incremental changes to the STANDBY using and it will apply the incremental changes to the standby database.

RMAN> RECOVER DATABASE NOREDO;


[oracle@DRS-DB-01 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 1 16:52:03 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BDDIPDC (DBID=1508613995)


RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area   10689474560 bytes

Fixed Size                     2216344 bytes
Variable Size               7650414184 bytes
Database Buffers            3019898880 bytes
Redo Buffers                  16945152 bytes

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 01-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=402 instance=bddipdc1 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: +DATA/bddipdrs/datafile/idencraft_data.387.729880671
destination for restore of datafile 00008: +DATA/bddipdrs/datafile/idencraft_doc.369.729880871
destination for restore of datafile 00009: +DATA/bddipdrs/datafile/listdb_data.386.729880995
.
.
Finished recover at 02-APR-12
destination for restore of datafile 00147: +DATA/bddipdrs/datafile/system.597.777275367
channel ORA_DISK_1: reading from backup piece +DATA/rollup/bddipdcforstandbyl2n6hv3s_1_1
6) In RMAN, connect to the PRIMARY database and create a standby control file backup: 
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyctrl.ctrl'; 
7) Transfer this standby controlfile backup from primary to standby server. 
$ scp /tmp/ForStandbyctrl.ctrl standby:/tmp
8) If the datafile names are different than primary, then we need to save the name of datafiles on standby first, which we can refer after restoring controlfile from primary backup to verify if any discrepancy. So, run below query from Standby and save results for further use. 

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

9) From RMAN, connect to STANDBY database and restore the standby control file:

RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT; 
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyctrl.ctrl'; 

10) Shut down the STANDBY database and startup mount:

RMAN> SHUTDOWN; 
RMAN> STARTUP MOUNT; 

11) Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations. However if structure is same then no need to perform this step
Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

RMAN> CATALOG START WITH '+DATA/rollup/datafile/'; 

If any datafiles have been added to Primary AFTER scn 5693326362 they will also have to be restored to the standby host and cataloged as shown above before doing the switch.
To determine if any files have been added to Primary since the standby current scn:

SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 5693326362
 
RMAN> SWITCH DATABASE TO COPY;

12) If the STANDBY database needs to be configured for FLASHBACK use the below step to enable.

SQL> ALTER DATABASE FLASHBACK OFF; 
SQL> ALTER DATABASE FLASHBACK ON; 

13) On standby database, clear all standby redo log groups:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; 
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; 
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

14) On the STANDBY database, start the MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;