How to perform Incomplete Recovery

RMAN> backup database;


user is sys


SQL> select count(*) from t
  2  /

  COUNT(*)
----------
         0

SQL> begin
  2    for i in 1..50
  3    loop
  4     insert into t values (i);
  5    end loop;
  6  end;
  7  .
SQL> /

PL/SQL procedure successfully completed.

SQL> commit
  2  /

Commit complete.

SQL> select count(*) from t
  2  /

  COUNT(*)
----------
        50

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

TO_CHAR(SYSDATE,'DD
-------------------
13-05-2008 16:09:26

SQL> delete from t where a between 1 and 25
  2  /

25 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> select count(*) from t
  2  /

  COUNT(*)
----------
        25

RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> set until time "to_date('13-05-2008 16:09:26','DD-MM-YYYY HH24:MI:SS')";
6> recover database;
7> }

database closed
database dismounted
Oracle instance shut down

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

Total System Global Area     171966464 bytes

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

executing command: SET until clause

Starting recover at 13-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/13/2008 16:13:00
RMAN-06555: datafile 1 must be restored from backup created before 13-MAY-08

 
Yours datafiles are newer than 13-MAY-2008 16:09:26 which contains the committed 
data ,for incomplete recovery how will you apply redo??

To perform incomplete media recovery, you must restore all datafiles from backups 
created prior to the time to which you want to recover.

RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> set until time "to_date('13-05-2008 16:09:26','DD-MM-YYYY HH24:MI:SS')";
6> restore database;
7> recover database;
8> }

database dismounted
Oracle instance shut down

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

Total System Global Area     171966464 bytes

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

executing command: SET until clause

Starting restore at 13-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

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:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DB
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2008_
_13\O1_MF_NNNDF_TAG20080513T155846_42LX37BF_.BKP tag=TAG20080513T155846
channel ORA_DISK_1: restore complete
Finished restore at 13-MAY-08

Starting recover at 13-MAY-08
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 13-MAY-08


RMAN> alter database open resetlogs
2> ;

database opened

SQL> conn sys/sys as sysdba
Connected.
SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 A                                                  NUMBER

SQL> select count(*)
  2    from t
  3  /

  COUNT(*)
----------
        50

SQL>