11g Oracle Point-in-time recovery (Time based)

Point-in-time recovery (Time based)
----------------------------------------
1.
ORACLE_HOME=/u01/app/oracle/product/11.0.1/db_1
export ORACLE_HOME

ORACLE_SID=orcl1
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin

$ sqlplus / as sysdba
or
SQL> connect / as sysdba;
or
SQL> connect sys as sysdba;

SQL> Shutdown immediate;

2.
Restore DB datafiles from backup location to the location where DB files are.

$ cd /u01/oracle/orabackup/orcl1/hot/
$ pwd
$ cp *.dbf /u01/app/oracle/oradata/orcl1/*

3. sqlplus / as sysdba

SQL> startup mount

SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';

4.
SQL> recover database until time 'yyyy-mm-dd:hh:mm:ss';

e.g.
SQL> recover database until time '2009-09-26:19:18:12';
or
SQL> recover database until time '09/26/2009 19:18:12';
(based on nls_date_format)

Enter auto when prompted for archived redo log to apply

5.
alter database open resetlogs;

Note: For this to work you should have all of your datafile backup and archived redo log
upto the point-in-time specified for the recovery and the correct control file.

----------------------------------------------------------

- Suresh