how to recover database with previous incarnation

Assume this demo is in 9i not in 10g

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> alter database archivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.


SQL> create table t (a number)
  2  /

Table created.


RMAN> backup database
2> ;

Starting backup at 12-DEC-07
.
.

Finished backup at 12-DEC-07

RMAN>



SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
  2    from dual
  3  /

TO_CHAR(SYSDATE,'DD
-------------------
12-12-2007 15:09:42

SQL> drop table t
  2  /

Table dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 

RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog

.
.

Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 12-DEC-07



RMAN> alter database open resetlogs
2> ;

database opened

RMAN> list incarnation
2> ;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL1    1025591162       PARENT  1          09-MAR-04
2       2       ORCL1    1025591162       PARENT  318842     12-DEC-07<---------set this
3       3       ORCL1    1025591162       CURRENT 331575     12-DEC-07<-----its current

SQL> conn sys/sys as sysdba
Connected.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER


SQL> drop table t
  2  /

Table dropped.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 


RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 12/12/2007 15:31:22
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time


RMAN> shutdown immediate

using target database controlfile instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes


RMAN> restore controlfile from 'C:\oracle\..\ORCL1\BACKUPSET\2007_12_12\O1_MF_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP'
2> ;

Starting restore at 12-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL01.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL02.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL03.CTL
Finished restore at 12-DEC-07

RMAN> startup mount

database is already started
database mounted

RMAN> reset database to incarnation 2
2> ;

Starting implicit crosscheck backup at 12-DEC-07
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 12-DEC-07

Starting implicit crosscheck copy at 12-DEC-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-DEC-07

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

List of Cataloged Files
=======================
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF
_1_5_3OZFHKNJ_.ARC
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_MF_
NCSNF_TAG20071212T150529_3OZDPGX9_.BKP

database reset to incarnation 2



RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause

ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
Starting restore at 12-DEC-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF

restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB
F
restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF

restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M
F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529
channel ORA_DISK_1: restore complete
Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\FLASH_RECOV
ERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF_1_5_3OZFHKNJ_.ARC
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_1
2_12\O1_MF_1_5_3OZFHKNJ_.ARC thread=1 sequence=5
media recovery complete
Finished recover at 12-DEC-07


RMAN> alter database open resetlogs
2> ;

database opened

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

SQL> select name from v$database
  2  /

NAME
---------
ORCL1