Steps to restore oracle database using RMAN & resetting the incarnation.
Step#1
Connect to the rman & identify the backup piece
<server_name>:<OSuser_name> 49> rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 17 22:21:14 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: <DB_name> (not mounted)
RMAN> connect catalog rman/****@RCAT;
connected to recovery catalog database
Database needs to be in nomunt state.
Step#2
Restore the ctl file
RMAN> restore controlfile from '/tmp/DD_Backup/<server_name>/<DB_name>/database/<DB_name>_ctl_spfile_c-238613855-20160717-05';
Starting restore at 17-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=317 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=380 device type=DISK
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
output file name=/oracle/<DB_name>/control1/cntrl<DB_name>.dbf
output file name=/oracle/<DB_name>/control2/cntrl<DB_name>.dbf
output file name=/oracle/<DB_name>/control3/cntrl<DB_name>.dbf
Finished restore at 17-JUL-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
step #3 check the incarnation & reset the incarnation to use old backups
RMAN> list incarnation of database <DB_name>;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
3038857 3038858 <DB_name> 238613855 PARENT 1 10-JAN-11
3038857 25197016 <DB_name> 238613855 CURRENT 220087209 17-JUL-16
RMAN> reset database to incarnation 3038858;
database reset to incarnation 3038858
RMAN>
Step#3 restore the DB where the app team wants to rollforward
RMAN> run
{
allocate channel dev1 type disk;
allocate channel dev2 type disk;
set until time "to_date('2016-07-17:09:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
release channel dev1;
release channel dev2;
}
2> 3> 4> 5> 6> 7> 8> 9> 10>
allocated channel: dev1
channel dev1: SID=317 device type=DISK
allocated channel: dev2
channel dev2: SID=380 device type=DISK
executing command: SET until clause
Starting restore at 17-JUL-16
channel dev1: starting datafile backup set restore
channel dev1: specifying datafile(s) to restore from backup set
channel dev1: restoring datafile 00002 to /oracle/<DB_name>/sapdata888/undo_1/undo.data1
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6307_1_917424023_53rathsn_1_1
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00005 to /oracle/<DB_name>/sapdata1/user_1/user.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6309_1_917424078_55rathue_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6309_1_917424078_55rathue_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:26
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00001 to /oracle/<DB_name>/sapdata777/system_1/system.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6310_1_917424086_56rathum_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6310_1_917424086_56rathum_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:35
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00003 to /oracle/<DB_name>/sapdata777/sysaux_1/sysaux.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6311_1_917424181_57rati1l_1_1
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6307_1_917424023_53rathsn_1_1 tag=TAG20160717T080023
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:01:04
channel dev1: starting datafile backup set restore
channel dev1: specifying datafile(s) to restore from backup set
channel dev1: restoring datafile 00004 to /oracle/<DB_name>/sapdata1/sr3db_1/sr3db.data1
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6308_1_917424023_54rathsn_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6311_1_917424181_57rati1l_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:27
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6308_1_917424023_54rathsn_1_1 tag=TAG20160717T080023
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:02:15
Finished restore at 17-JUL-16
Starting recover at 17-JUL-16
starting media recovery
archived log for thread 1 with sequence 16600 is already on disk as file /oracle/<DB_name>/oraarch/<DB_name>arch1_16600_740086239.dbf
channel dev1: starting archived log restore to default destination
channel dev1: restoring archived log
archived log thread=1 sequence=16598
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6313_1_917424329_59rati69_1_1
channel dev2: starting archived log restore to default destination
channel dev2: restoring archived log
archived log thread=1 sequence=16599
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6314_1_917424329_5arati69_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6314_1_917424329_5arati69_1_1 tag=TAG20160717T080528
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:02
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6313_1_917424329_59rati69_1_1 tag=TAG20160717T080528
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:00:08
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16598_740086239.dbf thread=1 sequence=16598
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16599_740086239.dbf thread=1 sequence=16599
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16600_740086239.dbf thread=1 sequence=16600
media recovery complete, elapsed time: 00:00:05
Finished recover at 17-JUL-16
released channel: dev1
released channel: dev2
RMAN> alter database open RESETLOGS;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
step#4
Take a full backup of database once database is open.
Step#1
Connect to the rman & identify the backup piece
<server_name>:<OSuser_name> 49> rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 17 22:21:14 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: <DB_name> (not mounted)
RMAN> connect catalog rman/****@RCAT;
connected to recovery catalog database
Database needs to be in nomunt state.
Step#2
Restore the ctl file
RMAN> restore controlfile from '/tmp/DD_Backup/<server_name>/<DB_name>/database/<DB_name>_ctl_spfile_c-238613855-20160717-05';
Starting restore at 17-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=317 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=380 device type=DISK
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
output file name=/oracle/<DB_name>/control1/cntrl<DB_name>.dbf
output file name=/oracle/<DB_name>/control2/cntrl<DB_name>.dbf
output file name=/oracle/<DB_name>/control3/cntrl<DB_name>.dbf
Finished restore at 17-JUL-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
step #3 check the incarnation & reset the incarnation to use old backups
RMAN> list incarnation of database <DB_name>;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
3038857 3038858 <DB_name> 238613855 PARENT 1 10-JAN-11
3038857 25197016 <DB_name> 238613855 CURRENT 220087209 17-JUL-16
RMAN> reset database to incarnation 3038858;
database reset to incarnation 3038858
RMAN>
Step#3 restore the DB where the app team wants to rollforward
RMAN> run
{
allocate channel dev1 type disk;
allocate channel dev2 type disk;
set until time "to_date('2016-07-17:09:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
release channel dev1;
release channel dev2;
}
2> 3> 4> 5> 6> 7> 8> 9> 10>
allocated channel: dev1
channel dev1: SID=317 device type=DISK
allocated channel: dev2
channel dev2: SID=380 device type=DISK
executing command: SET until clause
Starting restore at 17-JUL-16
channel dev1: starting datafile backup set restore
channel dev1: specifying datafile(s) to restore from backup set
channel dev1: restoring datafile 00002 to /oracle/<DB_name>/sapdata888/undo_1/undo.data1
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6307_1_917424023_53rathsn_1_1
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00005 to /oracle/<DB_name>/sapdata1/user_1/user.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6309_1_917424078_55rathue_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6309_1_917424078_55rathue_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:26
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00001 to /oracle/<DB_name>/sapdata777/system_1/system.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6310_1_917424086_56rathum_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6310_1_917424086_56rathum_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:35
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00003 to /oracle/<DB_name>/sapdata777/sysaux_1/sysaux.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6311_1_917424181_57rati1l_1_1
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6307_1_917424023_53rathsn_1_1 tag=TAG20160717T080023
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:01:04
channel dev1: starting datafile backup set restore
channel dev1: specifying datafile(s) to restore from backup set
channel dev1: restoring datafile 00004 to /oracle/<DB_name>/sapdata1/sr3db_1/sr3db.data1
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6308_1_917424023_54rathsn_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6311_1_917424181_57rati1l_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:27
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6308_1_917424023_54rathsn_1_1 tag=TAG20160717T080023
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:02:15
Finished restore at 17-JUL-16
Starting recover at 17-JUL-16
starting media recovery
archived log for thread 1 with sequence 16600 is already on disk as file /oracle/<DB_name>/oraarch/<DB_name>arch1_16600_740086239.dbf
channel dev1: starting archived log restore to default destination
channel dev1: restoring archived log
archived log thread=1 sequence=16598
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6313_1_917424329_59rati69_1_1
channel dev2: starting archived log restore to default destination
channel dev2: restoring archived log
archived log thread=1 sequence=16599
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6314_1_917424329_5arati69_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6314_1_917424329_5arati69_1_1 tag=TAG20160717T080528
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:02
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6313_1_917424329_59rati69_1_1 tag=TAG20160717T080528
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:00:08
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16598_740086239.dbf thread=1 sequence=16598
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16599_740086239.dbf thread=1 sequence=16599
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16600_740086239.dbf thread=1 sequence=16600
media recovery complete, elapsed time: 00:00:05
Finished recover at 17-JUL-16
released channel: dev1
released channel: dev2
RMAN> alter database open RESETLOGS;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
step#4
Take a full backup of database once database is open.
