RMAN: Incremental Backup

Below explains a method which can be used to restore oracle incremental level backup to restore rman backups on new server to  significantly reduce restore time.
Following steps are involved.

Take incremental level 0 backup on source server.

1. Restore incremental level 0 backup on destination server (Just restore database, Don't recover database).
2. Add a new datafile on source db.
3. Take another incremental level 1 backup on source DB.
4. Move newly taken incremental level 1 backup on destination server.
5. Restart destination DB in nomount state and recover controlfile from latest level 1 backup.
6. Mount database.
7. Restore database. It will skip files already restored. It will just restore newly added datafile.
8. Run recovery process. It will apply changes from Incremental level 1 backup.
9. Open database with resetlogs.

###############################################
TAKE LEVEL 0 BACKUP ON SOURCE SERVER 
###############################################
C:\Users\farhat>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 3 11:00:33 2013

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

connected to target database: TEST (DBID=2114642825)

RMAN> run {
2> allocate channel c1 device type disk format 'D:\oracle\backup\%U';
3>  backup incremental level 0 as compressed backupset database plus archivelog;
4> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=130 device type=DISK


Starting backup at 03-DEC-13
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=254 RECID=178 STAMP=833188289
input archived log thread=1 sequence=255 RECID=179 STAMP=833188289
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0EOQJ2TM_1_1 tag=TAG20131203T110213 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=188 STAMP=833194933
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0FOQJ2TT_1_1 tag=TAG20131203T110213 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
Finished backup at 03-DEC-13

Starting backup at 03-DEC-13
channel c1: starting compressed incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00003 name=D:\ORACLE\ORADATA\UNDOTBS01.DBF
input datafile file number=00006 name=D:\ORACLE\ORADATA\FLASHBACK.DBF
input datafile file number=00002 name=D:\ORACLE\ORADATA\SYSAUX01.DBF
input datafile file number=00001 name=D:\ORACLE\ORADATA\SYSTEM01.DBF
input datafile file number=00005 name=D:\ORACLE\ORADATA\SDE.DBF
input datafile file number=00004 name=D:\ORACLE\ORADATA\USERS01.DBF
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0GOQJ2U1_1_1 tag=TAG20131203T110225 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
channel c1: starting compressed incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0HOQJ2VQ_1_1 tag=TAG20131203T110225 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-DEC-13

Starting backup at 03-DEC-13
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=189 STAMP=833195007
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0IOQJ2VV_1_1 tag=TAG20131203T110327 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-DEC-13
released channel: c1
#######################################################
RESTORE INCREMENTAL LEVEL 0 BACKUP ON DESTINATION SERVER
#######################################################


C:\Users\Inam>set oracle_sid=test

C:\Users\Inam>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 3 12:10:49 2013

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     730714112 bytes

Fixed Size                     2258792 bytes
Variable Size                411043992 bytes
Database Buffers             310378496 bytes
Redo Buffers                   7032832 bytes

RMAN> restore controlfile from 'd:\oracle\backup\0HOQJ2VQ_1_1';

Starting restore at 03-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=128 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\ORACLE\ORADATA\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\CONTROL2.CTL
Finished restore at 03-DEC-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 03-DEC-13
Starting implicit crosscheck backup at 03-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=128 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 03-DEC-13

Starting implicit crosscheck copy at 03-DEC-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-DEC-13

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\SDE.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\ORACLE\ORADATA\FLASHBACK.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\BACKUP\0GOQJ2U1_1_1
channel ORA_DISK_1: piece handle=D:\ORACLE\BACKUP\0GOQJ2U1_1_1 tag=TAG20131203T1
10225
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 03-DEC-13

Shutdown database on destination server

RMAN> shutdown immediate;

database dismounted
Oracle instance shut down

############################################################
ADD A NEW DATAFILE ON SOURCE SERVER
############################################################

C:\Users\farhat>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 3 12:08:37 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace inctest datafile 'd:\oracle\oradata\inctest.dbf' size 500M;

Tablespace created.


######################################################
TAKE INCREMENTAL LEVE 1 BACKUP ON SOURCE BACKUP
######################################################

RMAN> run {
2> allocate channel c1 device type disk format 'D:\oracle\backup\%U';
3>  backup incremental level 1 as compressed backupset database plus archivelog;
4> }

allocated channel: c1
channel c1: SID=130 device type=DISK


Starting backup at 03-DEC-13
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=188 STAMP=833194933
input archived log thread=1 sequence=4 RECID=189 STAMP=833195007
input archived log thread=1 sequence=5 RECID=190 STAMP=833197991
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0JOQJ5T7_1_1 tag=TAG20131203T115311 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-DEC-13

Starting backup at 03-DEC-13
channel c1: starting compressed incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00003 name=D:\ORACLE\ORADATA\UNDOTBS01.DBF
input datafile file number=00006 name=D:\ORACLE\ORADATA\FLASHBACK.DBF
input datafile file number=00002 name=D:\ORACLE\ORADATA\SYSAUX01.DBF
input datafile file number=00001 name=D:\ORACLE\ORADATA\SYSTEM01.DBF
input datafile file number=00005 name=D:\ORACLE\ORADATA\SDE.DBF
input datafile file number=00004 name=D:\ORACLE\ORADATA\USERS01.DBF
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0KOQJ5TC_1_1 tag=TAG20131203T115315 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:25
channel c1: starting compressed incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0LOQJ602_1_1 tag=TAG20131203T115315 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-DEC-13

Starting backup at 03-DEC-13
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=191 STAMP=833198087
channel c1: starting piece 1 at 03-DEC-13
channel c1: finished piece 1 at 03-DEC-13
piece handle=D:\ORACLE\BACKUP\0MOQJ608_1_1 tag=TAG20131203T115447 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-DEC-13
released channel: c1

############################################################
RESTORE INCREMENTAL LEVEL 1 BACKUP ON DESTINATION SERVER
############################################################
Copy newly taken level 1 backup to destination server.
Start database in nomount state.

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     730714112 bytes

Fixed Size                     2258792 bytes
Variable Size                411043992 bytes
Database Buffers             310378496 bytes
Redo Buffers                   7032832 bytes

###################################################
RESTORE CONTROLFILE FROM LATEST INCREMENTAL LEVEL BACKUP
###################################################
This step very important. We will delete old controlfile from oradata folder and restore controlfile included in level 1 backup. Its backup information will be used while recovering database up to level of incremental level 1.

RMAN> restore controlfile from 'D:\oracle\backup\0ROQJ719_1_1';

Starting restore at 03-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=128 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\ORACLE\ORADATA\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\CONTROL2.CTL
Finished restore at 03-DEC-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 03-DEC-13
Starting implicit crosscheck backup at 03-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=128 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 03-DEC-13

Starting implicit crosscheck copy at 03-DEC-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-DEC-13

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

skipping datafile 1; already restored to file D:\ORACLE\ORADATA\SYSTEM01.DBF
skipping datafile 2; already restored to file D:\ORACLE\ORADATA\SYSAUX01.DBF
skipping datafile 3; already restored to file D:\ORACLE\ORADATA\UNDOTBS01.DBF
skipping datafile 4; already restored to file D:\ORACLE\ORADATA\USERS01.DBF
skipping datafile 5; already restored to file D:\ORACLE\ORADATA\SDE.DBF
skipping datafile 6; already restored to file D:\ORACLE\ORADATA\FLASHBACK.DBF
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to D:\ORACLE\ORADATA\INCTEST.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\BACKUP\0QOQJ6U8_1_1
channel ORA_DISK_1: piece handle=D:\ORACLE\BACKUP\0QOQJ6U8_1_1 tag=TAG20131203T1
21048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 03-DEC-13

RMAN> recover database;

Starting recover at 03-DEC-13
using channel ORA_DISK_1
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 00001: D:\ORACLE\ORADATA\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SYSAUX01.DBF
destination for restore of datafile 00003: D:\ORACLE\ORADATA\UNDOTBS01.DBF
destination for restore of datafile 00004: D:\ORACLE\ORADATA\USERS01.DBF
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SDE.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\FLASHBACK.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\BACKUP\0KOQJ5TC_1_1
channel ORA_DISK_1: piece handle=D:\ORACLE\BACKUP\0KOQJ5TC_1_1 tag=TAG20131203T1
15315
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00001: D:\ORACLE\ORADATA\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SYSAUX01.DBF
destination for restore of datafile 00003: D:\ORACLE\ORADATA\UNDOTBS01.DBF
destination for restore of datafile 00004: D:\ORACLE\ORADATA\USERS01.DBF
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SDE.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\FLASHBACK.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\BACKUP\0QOQJ6U8_1_1
channel ORA_DISK_1: piece handle=D:\ORACLE\BACKUP\0QOQJ6U8_1_1 tag=TAG20131203T1
21048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

unable to find archived log
archived log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/03/2013 12:22:49
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 9 and starting SCN of 3165567379

It will recover database up to level of incremental level 1 backup.

Now open database with resetlogs.

RMAN> alter database open resetlogs;

database opened

RMAN>