Recover Critical Truncated Table

One of the Application programmer truncated the critical table in the evening around 3.46 PM and they need to recover the truncated table. They have to decide to go to the time based incomplete recovery of the exact 03.45 PM. 

Find the Exact time for recovery and set the date and time format along with time based recovery scripts.
SQL> Select count(*) from employee;
COUNT(*)
----------
14

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

TO_CHAR(SYSDATE,'DD
-------------------
24-05-2009:15:45:42
SQL> truncate table employee;
Table truncated.
SQL> select count(*) from employee;
COUNT(*)
----------
0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL>
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:58:56 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database
RMAN> run
      {
      set until time "to_date('24-05-2009:15:45:42','DD-MM-YYYY HH24:MI:SS')";
      restore database;
      recover database;
      }
executing command: SET until clause
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 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.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P tag=TAG20090524T1534
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09
Starting recover at 24-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687713476.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC thread=1 sequence=2
media recovery complete, elapsed time: 00:00:05
Finished recover at 24-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 24 16:01:31 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open resetlogs;
Database altered.
SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from employee;
COUNT(*)
----------
14

Note: If you use the ‘alter database open resetlogs’ command from SQL*PLUS then you must need to use ‘reset database’ command to connect RMAN catalog for fresh backup.